测试目的：将朱查询的限制条件放到子查询的where中，查看性能影响。
测试数据：
create table t1 as select object_id,object_name from dba_objects;
create table t2 as select object_id,object_name from user_objects;
create table t3 as select rownum object_id,table_name object_name from user_tables;
analyze table t1 compute statistics for table for all columns;
analyze table t2 compute statistics for table for all columns;
analyze table t3 compute statistics for table for all columns;
SQL> select count(*) from t1;
COUNT(*)
----------
26341
SQL> select count(*) from t2;
COUNT(*)
----------
13135
SQL> select count(*) from t3;
COUNT(*)
----------
5891
开始测试：
1.主查询条件出现在子查询的where中（确实有开发是这样写的SQL，或许是动态生成SQL或者因为不太了解SQL编写规范手动写的）：
SQL> select *
2 from t1
3 where t1.object_id in
4 (select t2.object_id
5 from t2
6 where t1.object_name in (select t3.object_name from t3));
已选择5890行。
执行计划
----------------------------------------------------------
Plan hash value: 1276346997
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25583 | 2723K| | 244K (1)| 00:48:50 |
|* 1 | HASH JOIN SEMI | | 25583 | 2723K| 2280K| 244K (1)| 00:48:50 |
| 2 | TABLE ACCESS FULL | T1 | 25585 | 1973K| | 32 (0)| 00:00:01 |
| 3 | VIEW | VW_SQ_1 | 77M| 2213M| | 89634 (1)| 00:17:56 |
| 4 | NESTED LOOPS | | 77M| 2213M| | 89634 (1)| 00:17:56 |
| 5 | TABLE ACCESS FULL| T2 | 13135 | 166K| | 17 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T3 | 5891 | 97K| | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2.OBJECT_ID" AND "T1"."OBJECT_NAME"="ITEM_1")
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
37 recursive calls
0 db block gets
316742 consistent gets
0 physical reads
0 redo size
184781 bytes sent via SQL*Net to client
4728 bytes received via SQL*Net from client
394 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5890 rows processed
从执行计划可以看出，其实t2和t3走了笛卡尔积（SQL中这两个确实没有关联条件，从两个表nested loops后的返回基数77M可以看出，这两个表是走了笛卡尔积的）。笛卡尔积结果跟t1做hash join，t1将object_name传递给nested loop是的结果。返回5890条数据，消耗316742逻辑读。
这种性能方面的问题，我们是完全可以通过注意SQL的编码规范来规避的。
2.改写SQL，将主查询的限制条件放到与该其同级别的WHERE中。
SQL> select *
2 from t1
3 where t1.object_id in (select t2.object_id from t2)
4 and t1.object_name in (select object_name from t3);
已选择5890行。
执行计划
----------------------------------------------------------
Plan hash value: 3620957986
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13135 | 1398K| 60 (4)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI | | 13135 | 1398K| 60 (4)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T3 | 5891 | 97K| 9 (0)| 00:00:01 |
|* 3 | HASH JOIN RIGHT SEMI| | 13135 | 1180K| 50 (2)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 13135 | 166K| 17 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 25585 | 1973K| 32 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")
3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
575 consistent gets
0 physical reads
0 redo size
184781 bytes sent via SQL*Net to client
4728 bytes received via SQL*Net from client
394 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5890 rows processed
从执行计划来看，Oracle选择的表连接顺序都是最优的，逻辑读也从原来的316742下降到575，速度也提升很多。
切记：写SQL时一定要将对表的限制条件写到与该表同级别的where条件里。
