示例
造测试数据
SQL> create table dbmt.tobj1 as select * from dba_objects where object_type LIKE 'TABLE%' and rownum<10000;
Table created
SQL> create table dbmt.tobj2 as select * from dbmt.tobj1;
Table created.
执行改写前测试SQL
---测试SQL,改写前
alter session set statistics_level=all;
select a.owner,a.object_name,a.created,
(select subobject_name from dbmt.tobj2 where owner=a.owner and object_name=a.object_name and subobject_name is not null and rownum<2) subobject_name,
(select created from dbmt.tobj2 where owner=a.owner and object_name=a.object_name and subobject_name is not null and rownum<2) subobj_created,
(select last_ddl_time from dbmt.tobj2 where owner=a.owner and object_name=a.object_name and subobject_name is not null and rownum<2) subobj_last_ddl_time
from dbmt.tobj1 a where subobject_name is null;
SQL> @x
Display execution plan for last statement for this session from library cache...
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0grzz2m3xqd2m, child number 1
-------------------------------------
select a.owner,a.object_name,a.created, (select subobject_name from
dbmt.tobj2 where owner=a.owner and object_name=a.object_name and
subobject_name is not null and rownum<2) subobject_name, (select
created from dbmt.tobj2 where owner=a.owner and
object_name=a.object_name and subobject_name is not null and rownum<2)
subobj_created, (select last_ddl_time from dbmt.tobj2 where
owner=a.owner and object_name=a.object_name and subobject_name is not
null and rownum<2) subobj_last_ddl_time from dbmt.tobj1 a where
subobject_name is null
Plan hash value: 2895122658
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 204K(100)| 3802 |00:00:00.01 | 137 |
|* 1 | COUNT STOPKEY | | 3802 | | | | 177 |00:00:01.52 | 356K|
|* 2 | TABLE ACCESS FULL| TOBJ2 | 3802 | 1 | 36 | 35 (0)| 177 |00:00:01.52 | 356K|<<<
|* 3 | COUNT STOPKEY | | 3802 | | | | 177 |00:00:01.71 | 356K|
|* 4 | TABLE ACCESS FULL| TOBJ2 | 3802 | 1 | 44 | 35 (0)| 177 |00:00:01.71 | 356K|<<<
|* 5 | COUNT STOPKEY | | 3802 | | | | 177 |00:00:01.76 | 356K|
|* 6 | TABLE ACCESS FULL| TOBJ2 | 3802 | 1 | 44 | 35 (0)| 177 |00:00:01.76 | 356K|<<<
|* 7 | TABLE ACCESS FULL | TOBJ1 | 1 | 3802 | 163K| 35 (0)| 3802 |00:00:00.01 | 137 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
2 - filter(("OBJECT_NAME"=:B1 AND "OWNER"=:B2 AND "SUBOBJECT_NAME" IS NOT NULL))
3 - filter(ROWNUM<2)
4 - filter(("OBJECT_NAME"=:B1 AND "OWNER"=:B2 AND "SUBOBJECT_NAME" IS NOT NULL))
5 - filter(ROWNUM<2)
6 - filter(("OBJECT_NAME"=:B1 AND "OWNER"=:B2 AND "SUBOBJECT_NAME" IS NOT NULL))
7 - filter("SUBOBJECT_NAME" IS NULL)
38 rows selected.
标量子查询中每行需要读TOBJ2表3次
问题点,3个相同的查询,只能返回的列不同
执行改写后测试SQL
---测试SQL,改写后
select a.owner,a.object_name,a.created,
b.subobject_name,
b.subobj_created,
b.subobj_last_ddl_time
from dbmt.tobj1 a left join lateral
(select owner,object_name,subobject_name,created subobj_created,last_ddl_time subobj_last_ddl_time from dbmt.tobj2 where owner=a.owner and object_name=a.object_name and subobject_name is not null and rownum<2) b
on b.owner=a.owner and b.object_name=a.object_name
where a.subobject_name is null;
SQL> @x
Display execution plan for last statement for this session from library cache...
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a8mk8991hp042, child number 0
-------------------------------------
select a.owner,a.object_name,a.created, b.subobject_name,
b.subobj_created, b.subobj_last_ddl_time from dbmt.tobj1 a left join
lateral (select owner,object_name,subobject_name,created
subobj_created,last_ddl_time subobj_last_ddl_time from dbmt.tobj2 where
owner=a.owner and object_name=a.object_name and subobject_name is not
null and rownum<2) b on b.owner=a.owner and b.object_name=a.object_name
where a.subobject_name is null
Plan hash value: 1499940588
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 133K(100)| 3802 |00:00:01.57 | 360K|
| 1 | MERGE JOIN OUTER | | 1 | 3802 | 475K| 133K (1)| 3802 |00:00:01.57 | 360K|
|* 2 | TABLE ACCESS FULL | TOBJ1 | 1 | 3802 | 163K| 35 (0)| 3802 |00:00:00.01 | 137 |
| 3 | BUFFER SORT | | 3802 | 1 | 84 | 133K (1)| 177 |00:00:01.56 | 360K|
| 4 | VIEW | VW_LAT_D4FD8C38 | 3802 | 1 | 84 | 35 (0)| 177 |00:00:01.56 | 360K|
|* 5 | VIEW | VW_LAT_A18161FF | 3802 | 1 | 216 | 35 (0)| 177 |00:00:01.56 | 360K|
|* 6 | COUNT STOPKEY | | 3802 | | | | 177 |00:00:01.56 | 360K|
|* 7 | TABLE ACCESS FULL| TOBJ2 | 3802 | 1 | 52 | 35 (0)| 177 |00:00:01.55 | 360K|
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."SUBOBJECT_NAME" IS NULL)
5 - filter(("B"."OWNER"="A"."OWNER" AND "B"."OBJECT_NAME"="A"."OBJECT_NAME"))
6 - filter(ROWNUM<2)
7 - filter(("OBJECT_NAME"="A"."OBJECT_NAME" AND "OWNER"="A"."OWNER" AND "SUBOBJECT_NAME" IS NOT NULL))
33 rows selected.
性能对比
执行计划对比
—改写前SQL性能
SQL> select EXECUTIONS,BUFFER_GETS/EXECUTIONS gets_per_exec,ELAPSED_TIME/EXECUTIONS from v$sqlarea where sql_id='0grzz2m3xqd2m';
EXECUTIONS GETS_PER_EXEC ELAPSED_TIME/EXECUTIONS
---------- ------------- -----------------------
2 1068821 5022991
—改写后SQL性能
SQL> select EXECUTIONS,BUFFER_GETS/EXECUTIONS gets_per_exec,ELAPSED_TIME/EXECUTIONS from v$sqlarea where sql_id='a8mk8991hp042';
EXECUTIONS GETS_PER_EXEC ELAPSED_TIME/EXECUTIONS
---------- ------------- -----------------------
1 360161 1575141
改写前单次执行逻辑读1068821
改写后单次执行逻辑读360161
SQL> select 1068821/360161 from dual;
1068821/360161
--------------
2.96762004
改写为lateral join后每行只要读TOBJ2表1次,逻辑读是原来的1/3,符合优化预期.