sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle性能优化 >

【学习笔记】Oracle SQL优化子查询和谓词推入研究测试

时间:2016-12-22 11:16   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于OracleSQL语句优化的文章,通过实验详细研究Oracle 子查询和谓词推入的效果。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: about subquery unnest/push pred

今天抽空做了下关于子查询和谓词推入的实验,我这里通过vm 10gR2环境来模拟展示。

SQL> conn roger/roger
Connected.

SQL> create table t1 as select * from dba_objects where object_id < 3000;
Table created.

SQL> create table t2 as select * from dba_objects;
Table created.

SQL> create index idx_id1_t2 on t2(object_id,owner);
Index created.

SQL> create index idx_id1_t1 on t1(object_id);
Index created.

SQL> set autot traceonly exp

SQL> analyze table t1 compute statistics for all indexed columns;
Table analyzed.

SQL>  analyze table t2 compute statistics for all indexed columns;

Table analyzed.

SQL> set autot traceonly
SQL> select object_id from t1 where exists (
  2  select 1 from t2 where t1.object_id=t2.object_id-10);

2911 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2210107937

------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    17 |   203   (1)| 00:00:03 |
|*  1 |  HASH JOIN SEMI       |            |     1 |    17 |   203   (1)| 00:00:03 |
|   2 |   INDEX FAST FULL SCAN| IDX_ID1_T1 |  2950 | 38350 |     5   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | T2         | 49910 |   194K|   198   (1)| 00:00:03 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID"-10)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
        713  recursive calls
          0  db block gets
       1136  consistent gets
        553  physical reads
          0  redo size
      40114  bytes sent via SQL*Net to client
       2534  bytes received via SQL*Net from client
        196  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
       2911  rows processed

上面的例子中,oracle自动将子查询进行了展开,下面我们使用no_unnest hint来不让oracle在这里进行子查询展开,就通过嵌套在子查询里面,那么这样比如就会走fiter了,如下:

SQL>  alter system flush shared_pool;

System altered.

SQL> select object_id
  2    from t1
  3   where exists (select /*+no_unnest*/
  4           1
  5            from t2
  6           where t1.object_id = t2.object_id - 10);

2911 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |  4442   (1)| 00:00:54 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |  2950 |  8850 |    13   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |   499 |  1996 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2" WHERE
              "T2"."OBJECT_ID"-10=:B1))
   3 - filter("T2"."OBJECT_ID"-10=:B1)


在10g中,oracle默认就会进行子查询的展开,这是通过一个隐含参数来进行控制的,如下:

SQL> show parameter unnest

NAME TYPE VALUE
———————————— ———– ——————————
_distinct_view_unnesting boolean FALSE
_unnest_subquery boolean TRUE
SQL>

我们可以发现,_unnest_subquery 参数默认是true.

当子查询返回的结果集如果很小的话,这个时候其实是可以走fiter的,换句话讲,这个时候就可以不进行子查询的展开。

那么什么情况下存在子查询的情况,oracle不会进行子查询的展开呢 ?

—包含rownum

SQL> select object_id
  2    from t1
  3   where exists (select 1 from t2 where t1.object_id = t2.object_id-10 and rownum < 20);

2911 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4265634519

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |  4442   (1)| 00:00:54 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | T1   |  2950 |  8850 |    13   (0)| 00:00:01 |
|*  3 |   COUNT STOPKEY     |      |       |       |            |          |
|*  4 |    TABLE ACCESS FULL| T2   |   499 |  1996 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE ROWNUM<20 AND
              "T2"."OBJECT_ID"-10=:B1))
   3 - filter(ROWNUM<20)
   4 - filter("T2"."OBJECT_ID"-10=:B1)


—包含cube、rollup 等函数

SQL> select object_id
  2    from t1
  3   where exists (select 1,sum(object_id) from t2 where t1.object_id = t2.object_id-10 group by rollup(t2.object_id));

2911 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3739889183

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     3 |   293K  (1)| 00:58:38 |
|*  1 |  FILTER               |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL   | T1   |  2950 |  8850 |    13   (0)| 00:00:01 |
|   3 |   SORT GROUP BY ROLLUP|      |   499 |  1996 |   199   (2)| 00:00:03 |
|*  4 |    TABLE ACCESS FULL  | T2   |   499 |  1996 |   198   (1)| 00:00:03 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" WHERE
              "T2"."OBJECT_ID"-10=:B1 GROUP BY  ROLLUP ("T2"."OBJECT_ID")))
   4 - filter("T2"."OBJECT_ID"-10=:B1)
—包含union all、union、INTERSECT、 MINUS等

SQL> select object_id
  2    from t1
  3   where exists (select 1 from t2 where t1.object_id = t2.object_id-10  union all
  4    select object_id from t2 where object_id < 500);

2950 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2568596142

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     1 |     4 |   296K  (1)| 00:59:14 |
|*  1 |  FILTER             |            |       |       |            |          |
|   2 |   TABLE ACCESS FULL | T1         |  2950 | 11800 |    13   (0)| 00:00:01 |
|   3 |   UNION-ALL         |            |       |       |            |          |
|*  4 |    TABLE ACCESS FULL| T2         |   511 |  2555 |   198   (1)| 00:00:03 |
|*  5 |    INDEX RANGE SCAN | IDX_ID1_T2 |   417 |  2085 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS ( (SELECT 1 FROM "T2" "T2" WHERE
              "T2"."OBJECT_ID"-10=:B1) UNION ALL  (SELECT "OBJECT_ID" FROM "T2" "T2"
              WHERE "OBJECT_ID"<500)))
   4 - filter("T2"."OBJECT_ID"-10=:B1)
   5 - access("OBJECT_ID"<500)
SQL> select object_id
  2    from t1
  3   where exists (select 1 from t2 where t1.object_id = t2.object_id-10  union
  4    select object_id from t2 where object_id < 500);

2950 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 583540251

-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     1 |     4 |   299K  (2)| 00:59:49 |
|*  1 |  FILTER              |            |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | T1         |  2950 | 11800 |    13   (0)| 00:00:01 |
|   3 |   SORT UNIQUE        |            |   928 |  4640 |   203   (3)| 00:00:03 |
|   4 |    UNION-ALL         |            |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| T2         |   511 |  2555 |   198   (1)| 00:00:03 |
|*  6 |     INDEX RANGE SCAN | IDX_ID1_T2 |   417 |  2085 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS ( (SELECT 1 FROM "T2" "T2" WHERE
              "T2"."OBJECT_ID"-10=:B1)UNION (SELECT "OBJECT_ID" FROM "T2" "T2" WHERE
              "OBJECT_ID"<500)))
   5 - filter("T2"."OBJECT_ID"-10=:B1)
   6 - access("OBJECT_ID"<500)
SQL> select object_id
  2      from t1
  3      where exists (select 1 from t2 where t1.object_id = t2.object_id-10  MINUS
  4      select object_id from t2 where object_id < 500);

2911 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1945478487

-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     1 |     4 |   299K  (2)| 00:59:49 |
|*  1 |  FILTER              |            |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | T1         |  2950 | 11800 |    13   (0)| 00:00:01 |
|   3 |   MINUS              |            |       |       |            |          |
|   4 |    SORT UNIQUE NOSORT|            |   511 |  2555 |   199   (2)| 00:00:03 |
|*  5 |     TABLE ACCESS FULL| T2         |   511 |  2555 |   198   (1)| 00:00:03 |
|   6 |    SORT UNIQUE NOSORT|            |   417 |  2085 |     4  (25)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN | IDX_ID1_T2 |   417 |  2085 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS ( (SELECT 1 FROM "T2" "T2" WHERE
              "T2"."OBJECT_ID"-10=:B1)MINUS (SELECT "OBJECT_ID" FROM "T2" "T2" WHERE
              "OBJECT_ID"<500)))
   5 - filter("T2"."OBJECT_ID"-10=:B1)
   7 - access("OBJECT_ID"<500)
—包含start with…connect by

SQL> select object_id
  2    from t1
  3   where exists (select 1
  4            from t2
  5           start with owner='SYS'
  6           connect by object_id >100 and object_id < 200);

2950 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1433996639

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |  2950 | 11800 |    15   (0)| 00:00:01 |
|*  1 |  FILTER                       |            |       |       |            |          |
|   2 |   TABLE ACCESS FULL           | T1         |  2950 | 11800 |    13   (0)| 00:00:01 |
|*  3 |   CONNECT BY WITHOUT FILTERING|            |       |       |            |          |
|*  4 |    INDEX FAST FULL SCAN       | IDX_ID1_T2 | 23084 |   247K|    43   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | T2         | 51088 |   249K|   198   (1)| 00:00:03 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM "T2" "T2" CONNECT BY "OBJECT_ID">100 AND
              "OBJECT_ID"<200 START WITH "OWNER"='SYS'))
   3 - filter("OBJECT_ID">100 AND "OBJECT_ID"<200)
   4 - filter("OWNER"='SYS')


我们再来看下试图合并,首先我们创建几个测试表。

SQL> create table t3 as select * from dba_objects where object_id < 10000;

Table created.

SQL> analyze table t3 compute statistics;

Table analyzed.
SQL> create or replace view t_view
  2  as
  3  select t1.* from t1,t2
  4  where
  5  t1.object_id=t2.object_id;

View created.

SQL>
SQL> set autot traceonly  exp
SQL> select t3.object_name
  2    from t3, t_view
  3   where t3.object_name = t_view.object_name(+)
  4     and t3.object_id = 888;

Execution Plan
----------------------------------------------------------
Plan hash value: 1962414821

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     1 |    86 |    95   (3)| 00:00:02 |
|*  1 |  HASH JOIN OUTER        |            |     1 |    86 |    95   (3)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL     | T3         |     1 |    20 |    37   (0)| 00:00:01 |
|   3 |   VIEW                  | T_VIEW     |  2950 |   190K|    57   (2)| 00:00:01 |
|*  4 |    HASH JOIN            |            |  2950 | 76700 |    57   (2)| 00:00:01 |
|   5 |     TABLE ACCESS FULL   | T1         |  2950 | 61950 |    13   (0)| 00:00:01 |
|   6 |     INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 |   249K|    43   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))
   2 - filter("T3"."OBJECT_ID"=888)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

SQL>
从上面可以看到t3的条件进行了fiter操作,并没有推进到view里面去,下面我们使用push_pred hint进行强制推进下。

SQL> alter system flush shared_pool;

System altered.

SQL>
SQL> select /*+push_pred(t_view)*/ t3.object_name
  2    from t3, t_view
  3   where t3.object_name = t_view.object_name(+)
  4     and t3.object_id = 888;

Execution Plan
----------------------------------------------------------
Plan hash value: 3014823912

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     1 |    22 |    51   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER     |            |     1 |    22 |    51   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL     | T3         |     1 |    20 |    37   (0)| 00:00:01 |
|   3 |   VIEW PUSHED PREDICATE | T_VIEW     |     1 |     2 |    14   (0)| 00:00:01 |
|   4 |    NESTED LOOPS         |            |     1 |    26 |    14   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL   | T1         |     1 |    21 |    13   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN    | IDX_ID1_T2 |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T3"."OBJECT_ID"=888)
   5 - filter("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")
   6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
实际上这里如果进行merge的话,也可以实现类似的效果,如下:
SQL> select /*+merge(t_view)*/t3.object_name
  2    from t3, t_view
  3   where  t3.object_name=t_view.object_name
  4     and t3.object_id = 888;

Execution Plan
----------------------------------------------------------
Plan hash value: 2983785075

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     1 |    46 |    52   (2)| 00:00:01 |
|   1 |  NESTED LOOPS       |            |     1 |    46 |    52   (2)| 00:00:01 |
|*  2 |   HASH JOIN         |            |     1 |    41 |    51   (2)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T3         |     1 |    20 |    37   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1         |  2950 | 61950 |    13   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN  | IDX_ID1_T2 |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T3"."OBJECT_NAME"="T1"."OBJECT_NAME")
   3 - filter("T3"."OBJECT_ID"=888)
   5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
SQL> select /*+no_merge(t_view)*/t3.object_name
  2    from t3, t_view
  3   where  t3.object_name=t_view.object_name
  4     and t3.object_id = 888;

Execution Plan
----------------------------------------------------------
Plan hash value: 2958396757

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     1 |    86 |    95   (3)| 00:00:02 |
|*  1 |  HASH JOIN              |            |     1 |    86 |    95   (3)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL     | T3         |     1 |    20 |    37   (0)| 00:00:01 |
|   3 |   VIEW                  | T_VIEW     |  2950 |   190K|    57   (2)| 00:00:01 |
|*  4 |    HASH JOIN            |            |  2950 | 76700 |    57   (2)| 00:00:01 |
|   5 |     TABLE ACCESS FULL   | T1         |  2950 | 61950 |    13   (0)| 00:00:01 |
|   6 |     INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 |   249K|    43   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME")
   2 - filter("T3"."OBJECT_ID"=888)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

当sql查询中出现view,实际上使用merge/no_merge和push_pred,no_push_pred我感觉有些类似。

如下一段关于merge 和 push_pred的描述:

Merge / no_merge: if you use a complex view (e.g. aggregate view, or join view) in your query, should you rewrite
the query to merge the tables in the view into a single from clause with all the other tables (merge), or should
you evaluate the view to produce a “standalone” result set and then join the result set to the remaining tables (no_merge).

Push_pred / no_push_pred: If you have a non-mergeable view (possible because of a no_merge hint) in your query, how
should you operate the join from other tables; should you create one large view result and join it once (no_push_pred)
or should you push the join predicate down into the view definition and recreate the view result set for every driving
row from another table (push_pred).

merge 就是把view展开,那么你查看执行计划时就看不到view acces patch的信息了,no_merge则相反。
所以push_pred(谓词推入)就是是说将谓词条件推入到view中。

由于要发现push pred操作,必须保证试图不能被merge,所以通常在实验观察时会同时使用no_merge hint.

细心一点我们还能发现,针对谓词推入的操作,其外部操作只能是nest loop。

到最后我们可以简单的总结下,针对如下情况,子查询是不能展开的:

1.子查询存在 ROWNUM
2.子查询存在 CUBE,ROLLUP
3.子查询存在 UNION, UNION ALL,INTERSECT、 MINUS
4.子查询存在 START WITH ,CONNECT BY 字句

通常我发现针对右外连接的情况下,oracle优化器默认也不会进行view merge操作,如下:

SQL> select t3.object_name from t3,t_view where t3.object_name=t_view.object_name(+)
  2  and t3.object_id=888;

Execution Plan
----------------------------------------------------------
Plan hash value: 1962414821

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     1 |    88 |    95   (3)| 00:00:02 |
|*  1 |  HASH JOIN OUTER        |            |     1 |    88 |    95   (3)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL     | T3         |     1 |    22 |    37   (0)| 00:00:01 |
|   3 |   VIEW                  | T_VIEW     |  2950 |   190K|    57   (2)| 00:00:01 |
|*  4 |    HASH JOIN            |            |  2950 | 76700 |    57   (2)| 00:00:01 |
|   5 |     TABLE ACCESS FULL   | T1         |  2950 | 61950 |    13   (0)| 00:00:01 |
|   6 |     INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 |   249K|    43   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))
   2 - filter("T3"."OBJECT_ID"=888)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
事实上针对这样的情况,强制使用merge hint也不起作用,如果不存在右外连接的情况下,是可以自动进行merge的:

SQL> select /*+merge(t_view)*/t3.object_name from t3,t_view where t3.object_name=t_view.object_name(+)
  2  and t3.object_id=888;

Execution Plan
----------------------------------------------------------
Plan hash value: 1962414821

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |     1 |    88 |    95   (3)| 00:00:02 |
|*  1 |  HASH JOIN OUTER        |            |     1 |    88 |    95   (3)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL     | T3         |     1 |    22 |    37   (0)| 00:00:01 |
|   3 |   VIEW                  | T_VIEW     |  2950 |   190K|    57   (2)| 00:00:01 |
|*  4 |    HASH JOIN            |            |  2950 | 76700 |    57   (2)| 00:00:01 |
|   5 |     TABLE ACCESS FULL   | T1         |  2950 | 61950 |    13   (0)| 00:00:01 |
|   6 |     INDEX FAST FULL SCAN| IDX_ID1_T2 | 51088 |   249K|    43   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T3"."OBJECT_NAME"="T_VIEW"."OBJECT_NAME"(+))
   2 - filter("T3"."OBJECT_ID"=888)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

SQL> alter system flush shared_pool;

System altered.

SQL>  select t3.object_name from t3,t_view where t3.object_name=t_view.object_name
  2  and t3.object_id=888;

Execution Plan
----------------------------------------------------------
Plan hash value: 2983785075

----------------------------Oracle о ------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |     1 |    48 |    52   (2)| 00:00:01 |
|   1 |  NESTED LOOPS       |            |     1 |    48 |    52   (2)| 00:00:01 |
|*  2 |   HASH JOIN         |            |     1 |    43 |    51   (2)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T3         |     1 |    22 |    37   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1         |  2950 | 61950 |    13   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN  | IDX_ID1_T2 |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T3"."OBJECT_NAME"="T1"."OBJECT_NAME")
   3 - filter("T3"."OBJECT_ID"=888)
   5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
该默认行为是oracle通过一个参数来进行控制的,如下:
SQL> show parameter view

NAME TYPE VALUE
———————————— ———– ——————————
_complex_view_merging boolean TRUE
_distinct_view_unnesting boolean FALSE
_partition_view_enabled boolean TRUE
_project_view_columns boolean TRUE
_push_join_union_view boolean TRUE
_push_join_union_view2 boolean TRUE
_simple_view_merging boolean TRUE
optimizer_secure_view_merging boolean TRUE

在10gR2版本中 _simple_view_merging 参数默认是true。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle SQL优化子查询和谓词推入研究测试

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1428.html

Oracle研究中心

关键词:

subquery unnest/push pred

Oracle子查询和谓词推入详细测试