BI项目事实表(tba)和20多个代码表(tbb1、tbb2、tbb3、tbb4 ……………)左连接效率问题.
SQL:
select nvl(tbb1.b1,'ZZZ') as b1,
nvl(tbb2.b2,'ZZZ') as b2,
nvl(tbb3.b3,'ZZZ') as b3,
nvl(tbb4.b4,'ZZZ') as b4,
……………
度量1,
度量2
from tba,tbb1,tbb2,tbb3,tbb4 ……………
where tba.b1 = tbb1.b1(+)
and tba.b2 = tbb2.b2(+)
and tba.b3 = tbb3.b3(+)
and tba.b4 = tbb4.b4(+)……………请问如果优化sql的效率,其中事实表(tba)1500万数据,
其中20多个代码表(tbb1、tbb2、tbb3、tbb4 ……………)有4个是3000条记录,其他代码表都是40条记录以内。
SQL:
select nvl(tbb1.b1,'ZZZ') as b1,
nvl(tbb2.b2,'ZZZ') as b2,
nvl(tbb3.b3,'ZZZ') as b3,
nvl(tbb4.b4,'ZZZ') as b4,
……………
度量1,
度量2
from tba,tbb1,tbb2,tbb3,tbb4 ……………
where tba.b1 = tbb1.b1(+)
and tba.b2 = tbb2.b2(+)
and tba.b3 = tbb3.b3(+)
and tba.b4 = tbb4.b4(+)……………请问如果优化sql的效率,其中事实表(tba)1500万数据,
其中20多个代码表(tbb1、tbb2、tbb3、tbb4 ……………)有4个是3000条记录,其他代码表都是40条记录以内。
看一下执行计划。
你会发现上面那段sql语句会跑得飞快~~~
先看看执行计划再说,执行计划是对你这条sql的执行效率的最好的诠释。
你主表1500W,不会是全部命中吧?如果是全部查询,别优化SQL了,没用。