多一个一对多连接到同一台性能
假设我的影片是由多个表引用像Profile
表Client
,Address
,Phone
,Account
...通过所有这些表client_id
列。多一个一对多连接到同一台性能
所以一个客户端可能有多个配置文件,多个地址等。
我需要通过单个查询获取所有Client
的信息。
如果我写的查询像
SELECT C.*, PR.*, AD.*, PH.*, AC.* FROM CLIENT C
LEFT OUTER JOIN PROFILE PF ON C.ID=PF.CLIENT_ID
LEFT OUTER JOIN ADDRESS AD ON C.ID=AD.CLIENT_ID
LEFT OUTER JOIN PHONE PH ON C.ID=PH.CLIENT_ID
LEFT OUTER JOIN ACCOUNT AC ON C.ID=AC.CLIENT_ID
和帐户有10个配置文件,10个地址,10部手机和10个账户我想最终10000(10x10x10x10)排在我的结果集,但我需要只有40(10 + 10 + 10 + 10)。
我应该更好地编写多个查询还是有简单的方法来使数据库产生更少的行?
UPD:加入样品数据:
Client
ID | Name | ...
------ | -----------|-----
1 | "John Doe" |
Profile
ID | CLIENT_ID | TYPE | ...
------ | --------- | ----------- | ---
1 | 1 | "Primary" |
2 | 1 | "Secondary" |
3 | 1 | "Work" |
4 | 1 | "Office" |
5 | 1 | "Vacation" |
...
'地址'
ID | CLIENT_ID | ADDRESS_TEXT | ...
------ | --------- | ------------ | ---
1 | 1 | "Paris, ..." |
2 | 1 | "London..." |
3 | 1 | "Tokyo..." |
4 | 1 | "Moscow" |
5 | 1 | "New York" |
...
'帐户'
ID | CLIENT_ID | ACCOUNT_NUM | ...
------ | --------- | ------------ | ---
1 | 1 | "0" |
2 | 1 | "0172456123" |
3 | 1 | "12340" |
4 | 1 | "6789134834" |
5 | 1 | "2378166341" |
...
我不知道ResultSet期望得到哪个响应。通常这只是加入,一切都可以 - 这是我第一次开始考虑性能。
据我所知,主要问题是结果集中的数据重复。因为您不需要为任何地址复制任何电话号码。我想这个决定可能是排名每一行和列
with CLIENT(ID) as (
select 1 from dual)
,PROFILE (CLIENT_ID, PROFILE) as (
select 1, 'p1' from dual union all
select 1, 'p2' from dual union all
select 1, 'p4' from dual
)
,ADDRESS (CLIENT_ID, ADDRESS) as (
select 1, 'H1' from dual union all
select 1, 'H2' from dual
)
,PHONE (CLIENT_ID, PHONE) as (
select 1, '+13123411' from dual union all
select 1, '+1234512344' from dual union all
select 1, '+12345123133' from dual union all
select 1, '+12345123123' from dual union all
select 1, '+1234512144' from dual union all
select 1, '+12345123123' from dual union all
select 1, '+1234512144' from dual union all
select 1, '+12345123123' from dual union all
select 1, '+1234512144' from dual union all
select 1, '+12345123123' from dual union all
select 1, '+1234512144' from dual
)
,ACCOUNT (CLIENT_ID, ACCOUNT) as (
select 1, 'Acc1' from dual union all
select 1, 'acc2' from dual
)
SELECT PROFILE ,ADDRESS ,PHONE ,ACCOUNT
from
(select rownum as RN, PROFILE from PROFILE PR where PR.CLIENT_ID IN (select id from CLIENT c where c.id = 1)) a
full outer join (select rownum as RN, ADDRESS from ADDRESS PR where PR.CLIENT_ID IN (select id from CLIENT c where c.id = 1)) b on a.rn = b.rn
full outer join (select rownum as RN, PHONE from PHONE PR where PR.CLIENT_ID IN (select id from CLIENT c where c.id = 1)) c on a.rn = c.rn
full outer join (select rownum as RN, ACCOUNT from ACCOUNT PR where PR.CLIENT_ID IN (select id from CLIENT c where c.id = 1)) d on a.rn = d.rn
已经看到这个黑客的地方 - 看起来很hacky :-( –
+---------+ +---------+
| | | |
| Client |------------<| Profile |
| | +---------+
| |
| | +---------+
| | | |
| |------------<| Address |
| | +---------+
| |
| | +---------+
| | | |
| |------------<| Account |
| | +---------+
+---------+
加入它对于你的描述,我觉得你的数据模型如上图所示;如果我错了,请纠正我。
假设上述内容是正确的,那么您在这里得到的是一个“鸿沟陷阱”。基本上,地址和帐户(例如)之间的关系是不明确的。我们知道客户的地址;我们知道客户有很多帐户;但我们不知道哪个地址属于哪个帐户。对于你的应用程序,这可能没有意义。事实上,重新阅读您的问题,解决方案可能就像三个单独的查询一样简单。
select c.*, p.*
from client c
join profile p
on c.id = client_id
;
select c.*, a.*
from client c
join address a
on c.id = client_id
;
select c.*, ac.*
from client c
join account ac
on c.id = client_id
;
ID NAME ID CLIENT_ID PROFILE_TY
---------- ---------- ---------- ---------- ----------
1 John Doe 1 1 Primary
1 John Doe 2 1 Secondary
1 John Doe 3 1 Work
1 John Doe 4 1 Office
1 John Doe 5 1 Vacation
ID NAME ID CLIENT_ID ADDRESS
---------- ---------- ---------- ---------- ----------
1 John Doe 1 1 Paris
1 John Doe 2 1 London
1 John Doe 3 1 Tokyo
1 John Doe 4 1 Moscow
1 John Doe 5 1 New York
ID NAME ID CLIENT_ID ACCOUNT_NU
---------- ---------- ---------- ---------- ----------
1 John Doe 1 1 0
1 John Doe 2 1 0172456123
1 John Doe 3 1 12340
1 John Doe 4 1 6789134834
1 John Doe 5 1 2378166341
但这将是3 rountrips数据库。我想知道是否有“标准”的方式来解决这个问题。 –
@DmitryZvorygin。你还没有真正回答我提出的问题。(例如)ACCOUNT和ADDRESS之间是否存在真正的关系?我的例子中显示的输出是否正确?让我们在考虑往返行程之前弄清楚结果 – BobC
是的,您的输出是正确的 - 配置文件,帐户和地址是 –
这是一个关于性能或数据表示的问题吗?只要client_id列被编入索引,当前查询就会预处理好。 – jarlh
提供一些样本数据和预期结果以获得更详细的答案 – JohnHC
@jarlh,这是性能问题。所有列都被编入索引。我害怕结果有10K行,99%的数据因为连接而重复。 –