SQL优化 第八章 案例模拟 8.1 union all优化

8.1.1 构造数据环境

drop table test1;

drop table test2;

drop table test3;

create table test1 as select * from dba_objects;

create table test2 as select * from test1;

create table test3 as select * from test1;

insert into test2  select * from test2;

insert into test2  select * from test2;

insert into test3  select * from test3;

insert into test3  select * from test3;

create index ix_test1_01 on test1(object_id);

create index ix_test2_01 on test2(object_id);

create index ix_test3_01 on test3(object_id);

8.1.2 问题SQL

select count(t1.OWNER),count(t.OBJECT_TYPE)

from test1 t1

  inner join (select * from test2 t2

              union  all

              select * from test3 t3) t

  on t1.object_id=t.object_id

where t1.OWNER='SCOTT';

在最后count之前只返回208条数据，但是逻辑读时9000+，还有20个物理读，因为t1.owner=’SCOTT’返回数据量很少，object_id列还有索引，完全可以走嵌套循环，但是，连接列谓词没有推入，造成了大表走Hash。

8.1.3 优化方法1 hint

select /*+ PUSH_PRED(t)*/count(t1.OWNER),count(t.OBJECT_TYPE)

from test1 t1

  inner join (select /*+ index(t2 ix_test2_01)*/ * from test2 t2

              union  all

              select /*+ index(t3 ix_test3_01)*/  * from test3 t3) t

  on t1.object_id=t.object_id

where t1.OWNER='SCOTT';

8.1.4 优化方法2 拆分union

select count(OWNER),count(OBJECT_TYPE) from

(select t1.OWNER,t2.OBJECT_TYPE

from test1 t1

  inner join test2 t2

  on t1.object_id=t2.object_id

where t1.OWNER='SCOTT'

union all

select t1.OWNER,t3.OBJECT_TYPE

from test1 t1

  inner join test3 t3

  on t1.object_id=t3.object_id

where t1.OWNER='SCOTT');

因为要访问test1两次，性能比直接加hint稍微差一些。

8.1.5 直接在视图内部连接

select count(t1.OWNER),count(t.OBJECT_TYPE)

from test1 t1

  inner join (select t2.*

              from test2 t2 ,test1 t1

              where t1.OWNER='SCOTT'

                and t1.object_id=t2.object_id

              union  all

              select t3.*

              from test3 t3,test1 t1

              where t1.OWNER='SCOTT'

                and t1.object_id=t3.object_id) t

  on t1.object_id=t.object_id

where t1.OWNER='SCOTT';

这个要访问test1 三次，比直接hint也差一些。

8.1.6 共同部分用with

with tmp as

(select /*+ NO_MERGE*/ * from test1 t1 where t1.OWNER='SCOTT')

select count(OWNER),count(OBJECT_TYPE) from

(select t1.OWNER,t2.OBJECT_TYPE

from tmp t1

  inner join test2 t2

  on t1.object_id=t2.object_id

union all

select t1.OWNER,t3.OBJECT_TYPE

from tmp t1

  inner join test3 t3

on t1.object_id=t3.object_id);

这个执行计划比较长，就不贴了。逻辑读更低了，但是有1个物理读，不知道为什么。