示例
1.造测试数据
SQL> create table dbmt.tobj1 as select * from dba_objects where object_type in ('TABLE','INDEX') and rownum<10000;
Table created.
SQL> create table dbmt.tobj2 as select * from dbmt.tobj1;
Table created.2.执行改写前测试SQL
—测试sql ,改写前
select a.object_id,(select sum(object_id) from dbmt.tobj2 where owner=a.owner),(select sum(data_object_id) from dbmt.tobj2 where owner=a.owner) from dbmt.tobj1 a;
标量子查询中每行需要读TOBJ2表2次
----改写完成的sql
select a.object_id,x.sum_value,y.sum_value from dbmt.tobj1 a,
(SELECT sum(object_id) sum_value,owner from dbmt.tobj2 GROUP BY owner)x,
(SELECT sum(data_object_id) sum_value,owner from dbmt.tobj2 GROUP BY owner)y
where a.owner=x.owner(+) and a.owner=y.owner(+);
3.性能对比
—改写前SQL性能
SQL> select EXECUTIONS,BUFFER_GETS/EXECUTIONS gets_per_exec,ELAPSED_TIME/EXECUTIONS from v$sqlarea where sql_id='3vr66xq0xmn6s';
EXECUTIONS GETS_PER_EXEC ELAPSED_TIME/EXECUTIONS
---------- ------------- -----------------------
7 8226.71429 45602.4286
Elapsed: 00:00:00.00—改写后SQL性能
SQL> select EXECUTIONS,BUFFER_GETS/EXECUTIONS gets_per_exec,ELAPSED_TIME/EXECUTIONS from v$sqlarea where sql_id='d0xsq865xqs8m';
EXECUTIONS GETS_PER_EXEC ELAPSED_TIME/EXECUTIONS
---------- ------------- -----------------------
7 371 24172.2857改写前单次执行逻辑读8226
改写后单次执行逻辑读371
SQL> select 8226/371 from dual;
8226/371
----------
22.1725067逻辑读减少22倍，符合优化预期