將主查詢條件寫到子查詢裡執行效果會怎樣？

shuangoracle發表於2012-06-04
--測試環境
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;
t1--49830;t2--37;t3--16
開始測試：
1.比較簡單的子查詢：子查詢中表直接和主表就有關聯關係
select /*+gather_plan_statistics*/
 (select t2.object_name from t2 where t2.object_id = t1.object_id) object_name
  from t1;
執行計劃：
---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T2   |  49830 |      1 |     36 |00:00:01.75 |     149K|      0 |
|   2 |  TABLE ACCESS FULL| T1   |      1 |  49830 |  49830 |00:00:00.30 |    3546 |    231 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T2"."OBJECT_ID"=:B1)
這個執行很簡單，執行t1，然後根據t1返回行數決定t2掃描次數。

2.複雜點的子查詢：子查詢的子查詢和主表有關聯關係
select /*+gather_plan_statistics*/
 (select t2.object_name
    from t2
   where t2.object_name in
         (select t3.object_name from t3 where t1.object_id = t3.object_id)) object_name
  from t1;
執行計劃：觀看執行次數和實際返回行數  
----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|*  1 |  FILTER            |      |  49830 |        |     15 |00:00:39.89 |    5680K|      2 |
|   2 |   TABLE ACCESS FULL| T2   |  49830 |     37 |   1843K|00:00:07.89 |     149K|      0 |
|*  3 |   TABLE ACCESS FULL| T3   |   1843K|      1 |     15 |00:00:27.95 |    5531K|      2 |
|   4 |  TABLE ACCESS FULL | T1   |      1 |  49830 |  49830 |00:00:00.20 |    3546 |      0 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T3" "T3" WHERE
              "T3"."OBJECT_NAME"=:B1 AND "T3"."OBJECT_ID"=:B2))
   3 - filter("T3"."OBJECT_NAME"=:B1 AND "T3"."OBJECT_ID"=:B2)
首先執行t1，然後執行t2，最後執行t3（從predicate中3可以看出，t3依賴於t1傳過來的引數object_id和t2傳過來的引數object_name）。
仔細分析下Starts和A-Rows發現，執行t1返回49830行，然後執行t2，由於t2和t1沒有任何關聯條件，導致執行t2後返回的行數是t2資料行數與t2執行次數（t1返回行數）相乘；
這也就直接影響到t3執行次數。也就是說不管t3中有多少行滿足條件的資料，都會對錶t3掃描t1和t2行數相乘次。而如果t1和t2返回行數相當大，那麼結果就可想而知了~

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24496749/viewspace-731888/，如需轉載，請註明出處，否則將追究法律責任。

相關文章