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，改写前
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.

---测试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> 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> 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

SQL> select 1068821/360161 from dual;

1068821/360161
--------------
    2.96762004