select
t1.a,t1.b,t1.c,
t2.d,t2.e,t2.f
from (select * from dbb042 a where substr(a.CSLB,1,2)='11') t1
left join (select * from dbb042 a where substr(a.CSLB,1,2)='12') t2
on t1.a=t2.a and t1.b=t2.b谁能帮我把这个SQL优化下
小弟急用啊,是12张类似于这样的外连接,性能太低了!
t1.a,t1.b,t1.c,
t2.d,t2.e,t2.f
from (select * from dbb042 a where substr(a.CSLB,1,2)='11') t1
left join (select * from dbb042 a where substr(a.CSLB,1,2)='12') t2
on t1.a=t2.a and t1.b=t2.b谁能帮我把这个SQL优化下
小弟急用啊,是12张类似于这样的外连接,性能太低了!
from dbb042 t1, dbb042 t2
where
t1.cslb like '11%'
and t2.cslb like '12%'
and t1.a=t2.a(+)
and t1.b=t2.b(+)
SQL>EXPLAIN PLAN FOR 你的sql语句;
如
SQL>EXPLAIN PLAN FOR SELECT * FROM EMP WHERE EMPNO=7369;
然后
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
查看结果就是前面SQL语句的执行计划。 补充一点:substr(a.CSLB,1,2) 楼主的这种写法会限制索引的。 可以尝试1楼的用like 试试..
不过你们提出的方法确实都不错,我再考虑着要不用Oracle物化视图语法。
不然这样会查询效率很低的!
select
t1.a,t1.b,t1.c,
t2.d,t2.e,t2.f
from dbb042 t1
left join dbb042 t2
on t1.a=t2.a and t1.b=t2.b
where substr(t1.CSLB,1,2)='11' and substr(t2.CSLB,1,2)='12'
--如果数据量比较多,可以建立索引,效果比较明显的
select max(case when substr(CSLB,1,2)='11' then a end) as a,
max(case when substr(CSLB,1,2)='11' then b end) as b,
max(case when substr(CSLB,1,2)='11' then c end) as c,
max(case when substr(CSLB,1,2)='12' then d end) as d,
max(case when substr(CSLB,1,2)='12' then e end) as e,
max(case when substr(CSLB,1,2)='12' then f end) as f
from dbb042
where substr(CSLB,1,2)='11' or substr(CSLB,1,2)='12'
group by a,b
having max(case when substr(CSLB,1,2)='11' then a end) is not null
and max(case when substr(CSLB,1,2)='11' then b end) is not null
and max(case when substr(CSLB,1,2)='12' then a end) is not null
and max(case when substr(CSLB,1,2)='12' then b end) is not null
如果是full join的话,having 子句就不需要了.
这是self join的一般等效写法,在有足够cpu处理能力和内存(主要用于group by的排序操作)的前提下,能显著减少大表扫描的次数,改善性能.
当然如果表扫描不是此SQL执行的瓶颈,那这种写法可能并不奏效,是否由于表扫描占用过多处理时间可以通过查看动态性能视图v$session_longops确定