查询:
select
b1.codeName,
b2.codeName,
b3.codeName
from tabA a
left join tabB b1
on a.code1=b1.code
left join tabB b2
on a.code2=b2.code
left join tabB b3
on a.code3= b3.code
where 1=1
这种查询,对于tabB表扫描次数太频繁,请问如何更改比较合理。
22 个解决方案
#1
顶一下...
#2
表A三个字段看起来好像一样啊,为什么不用一个字段就行?
#3
不能的,
比如 那个三个字段是:中国,美国,英国
B表则是国家表。
#4
列转行,再行转列,B表访问次数少了
但是至于那个执行时间快,不确定,具体执行下或看一下执行计划吧
SELECT MAX(DECODE(A.CONAME,'code1',B.codeName)),
MAX(DECODE(A.CONAME,'code2',B.codeName)),
MAX(DECODE(A.CONAME,'code3',B.codeName))
FROM
(select code1 as code,'code1' as colname,rowid as id from TABLEA
union all
select code2 as code,'code2' as colname,rowid as id from TABLEA
union all
select code3 as code,'code3' as colname,rowid as id from TABLEA) A,TABLEB B
WHERE A.CODE=B.CODE
GROUP BY A.ID
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code2=b1.code then b1.codename end),
(case when a.code3=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code2=b1.code then b1.codename end),
(case when a.code3=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
select max(case when a.code1=b.code then b.CODNAME end),
max(case when a.code2=b.code then b.CODNAME end),
max(case when a.code3=b.code then b.CODNAME end)
from a left join b on a.code1=b.code or a.code2=b.code or a.code3= b.code
group by a.rowid
#17
--如果满足要求的话 楼主看看这个
SELECT code1, code2, code3, wm_concat(t2.name)
FROM t t1
LEFT JOIN tb t2
ON (t1.code1 = t2.code OR t1.code2 = t2.code OR t1.code3 = t2.code)
GROUP BY code1, code2, code3
#18
该回复于2014-09-22 08:10:19被版主删除
#19
这个问题也困扰了我很久,可不可以建一个物化视图将这个结果集存起来,以后直接查物化视图。
#20
select * from a left join b on a.id = b.id and a.name = b.name and a.whatever = b.whatever
#21
楼主解决了吗,分享下
#22
遇到了同样的问题,楼主解决了吗?
#1
顶一下...
#2
表A三个字段看起来好像一样啊,为什么不用一个字段就行?
#3
表A三个字段看起来好像一样啊,为什么不用一个字段就行?
不能的,
比如 那个三个字段是:中国,美国,英国
B表则是国家表。
#4
列转行,再行转列,B表访问次数少了
但是至于那个执行时间快,不确定,具体执行下或看一下执行计划吧
SELECT MAX(DECODE(A.CONAME,'code1',B.codeName)),
MAX(DECODE(A.CONAME,'code2',B.codeName)),
MAX(DECODE(A.CONAME,'code3',B.codeName))
FROM
(select code1 as code,'code1' as colname,rowid as id from TABLEA
union all
select code2 as code,'code2' as colname,rowid as id from TABLEA
union all
select code3 as code,'code3' as colname,rowid as id from TABLEA) A,TABLEB B
WHERE A.CODE=B.CODE
GROUP BY A.ID
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
#13
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code2=b1.code then b1.codename end),
(case when a.code3=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code2=b1.code then b1.codename end),
(case when a.code3=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
select max(case when a.code1=b.code then b.CODNAME end),
max(case when a.code2=b.code then b.CODNAME end),
max(case when a.code3=b.code then b.CODNAME end)
from a left join b on a.code1=b.code or a.code2=b.code or a.code3= b.code
group by a.rowid
#17
--如果满足要求的话 楼主看看这个
SELECT code1, code2, code3, wm_concat(t2.name)
FROM t t1
LEFT JOIN tb t2
ON (t1.code1 = t2.code OR t1.code2 = t2.code OR t1.code3 = t2.code)
GROUP BY code1, code2, code3
#18
该回复于2014-09-22 08:10:19被版主删除
#19
这个问题也困扰了我很久,可不可以建一个物化视图将这个结果集存起来,以后直接查物化视图。
#20
select * from a left join b on a.id = b.id and a.name = b.name and a.whatever = b.whatever