CBO hint:no_unnest,push_subq,push_pred的用法

sundog315

好！！！总结的好。

space6212

great !

yanggq

aks5201314

楼主总结的很好，学习一下！

anlinew

好帖沉这么快
mark一下，呵呵

zergduan

借地方 问一下
hash anti right join和hash anti join如何控制

比如
1。这样的SQL可以通过push_subq来优化
MYDB@MYDB10G >select *
  2    from t1, t2
  3   where t1.object_id = t2.object_id
  4     --and t1.object_id is not null
  5     and t1.object_id not in (select t3.object_id
  6                                from t3
  7                               --where t3.object_id is not null
  8                               group by object_id, object_name
  9                              having count(*) > 0)
10  /
已用时间:  00: 00: 02.06

执行计划
----------------------------------------------------------
Plan hash value: 2242802760

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 11653 |  1934K|       | 29406  (21)| 00:05:53 |
|*  1 |  FILTER              |      |       |       |       |            |          |
|*  2 |   HASH JOIN          |      | 11654 |  1934K|  1104K|   200   (1)| 00:00:03 |
|   3 |    TABLE ACCESS FULL | T2   | 11654 |   967K|       |    45   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | T1   | 11845 |   983K|       |    46   (0)| 00:00:01 |
|*  5 |   FILTER             |      |       |       |       |            |          |
|   6 |    HASH GROUP BY     |      |     1 |    13 |       |     5  (20)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| T3   |   304 |  3952 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T3" "T3" GROUP BY
              "OBJECT_ID","OBJECT_NAME" HAVING LNNVL("OBJECT_ID"<>:B1) AND COUNT(*)>0))
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   5 - filter(LNNVL("OBJECT_ID"<>:B1) AND COUNT(*)>0)

2。但是如果把subquery和parentquery的连接建加上not null，就可以使用hash join anti or hash join right anti

我尝试了各种方法，都没法控制到底是用hash join anti 还是 hash join right anti~ 也就是是否使用内建的view作为驱动表~

包括 ordered, leading @squb,t1,t3。。。。

请问对于下面的查询，如何控制呢？
MYDB@MYDB10G >select *
  2    from t1, t2
  3   where t1.object_id = t2.object_id
  4     and t1.object_id is not null
  5     and t1.object_id not in (select t3.object_id
  6                                from t3
  7                               where t3.object_id is not null
  8                               group by object_id, object_name
  9                              having count(*) > 0)
10  /
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 329456636

------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          | 11638 |  2079K|       |   206   (2)| 00:00:03 |
|*  1 |  HASH JOIN RIGHT ANTI |          | 11638 |  2079K|       |   206   (2)| 00:00:03 |
|   2 |   VIEW                | VW_NSO_1 |    16 |   208 |       |     5  (20)| 00:00:01 |
|*  3 |    FILTER             |          |       |       |       |            |          |
|   4 |     HASH GROUP BY     |          |    16 |   208 |       |     5  (20)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL| T3       |   304 |  3952 |       |     4   (0)| 00:00:01 |
|*  6 |   HASH JOIN           |          | 11654 |  1934K|  1104K|   200   (1)| 00:00:03 |
|   7 |    TABLE ACCESS FULL  | T2       | 11654 |   967K|       |    45   (0)| 00:00:01 |
|*  8 |    TABLE ACCESS FULL  | T1       | 11845 |   983K|       |    46   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="$nso_col_1")
   3 - filter(COUNT(*)>0)
   5 - filter("T3"."OBJECT_ID" IS NOT NULL)
   6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   8 - filter("T1"."OBJECT_ID" IS NOT NULL)

anlinew

SQL> alter system set optimizer_features_enable='11.2.0.1';

系统已更改。

SQL> select  * from test_obj1 a
  2  where a.object_id not in (select object_id from test_obj2);

已选择12172行。


执行计划
----------------------------------------------------------
Plan hash value: 3145936244

--------------------------------------------------------------------------------
-----

| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time
    |

--------------------------------------------------------------------------------
-----

|   0 | SELECT STATEMENT        |           | 12197 |  1107K|    57   (2)| 00:00
:01 |

|*  1 |  HASH JOIN RIGHT ANTI NA|           | 12197 |  1107K|    57   (2)| 00:00
:01 |

|   2 |   TABLE ACCESS FULL     | TEST_OBJ2 |  1000 |  4000 |     5   (0)| 00:00
:01 |

|   3 |   TABLE ACCESS FULL     | TEST_OBJ1 | 13171 |  1144K|    51   (0)| 00:00
:01 |

--------------------------------------------------------------------------------
-----


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

   1 - access("A"."OBJECT_ID"="OBJECT_ID")


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        993  consistent gets
          0  physical reads
          0  redo size
     556288  bytes sent via SQL*Net to client
       9337  bytes received via SQL*Net from client
        813  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      12172  rows processed

SQL> alter system set optimizer_features_enable='10.2.0.1';

系统已更改。

SQL> select  * from test_obj1 a
  2  where a.object_id not in (select object_id from test_obj2);

已选择12172行。


执行计划
----------------------------------------------------------
Plan hash value: 324612266

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           | 13170 |  1144K| 33078   (1)| 00:06:37 |
|*  1 |  FILTER            |           |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST_OBJ1 | 13171 |  1144K|    51   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST_OBJ2 |     1 |     4 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT 0 FROM "TEST_OBJ2" "TEST_OBJ2" WHERE
              LNNVL("OBJECT_ID"<>:B1)))
   3 - filter(LNNVL("OBJECT_ID"<>:B1))


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     205899  consistent gets
          0  physical reads
          0  redo size
     556288  bytes sent via SQL*Net to client
       9337  bytes received via SQL*Net from client
        813  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      12172  rows processed

shiri512003

oracle优化器也是越来越强悍啊

棉花糖ONE

试试swap_joins

viadeazhu

针对zerg的问题，有个很有趣的结果：
如果你select t1.*，那么t3 t1会先做hash_aj，然后再t2.
如果你select t2.*, 那么t3 t2会先做hash_aj,然后再t1.

select *
from t1, t2
where t1.object_id = t2.object_id
  and t1.object_id is not null
  and t1.object_id not in (select t3.object_id
                             from t3
                            where t3.object_id is not null
                            group by object_id, object_name
                           having count(*) > 0);
Plan hash value: 4008314795
------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          | 34527 |  5833K|       |   670   (4)| 00:00:07 |
|*  1 |  HASH JOIN RIGHT ANTI |          | 34527 |  5833K|       |   670   (4)| 00:00:07 |
|   2 |   VIEW                | VW_NSO_1 |  1818 | 23634 |       |   131   (7)| 00:00:02 |
|*  3 |    FILTER             |          |       |       |       |            |          |
|   4 |     HASH GROUP BY     |          |  1818 | 36360 |       |   131   (7)| 00:00:02 |
|*  5 |      TABLE ACCESS FULL| T3       | 36347 |   709K|       |   125   (2)| 00:00:02 |
|*  6 |   HASH JOIN           |          | 36345 |  5678K|  3272K|   537   (3)| 00:00:06 |
|   7 |    TABLE ACCESS FULL  | T2       | 36347 |  2839K|       |   127   (4)| 00:00:02 |
|*  8 |    TABLE ACCESS FULL  | T1       | 36346 |  2839K|       |   127   (4)| 00:00:02 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."OBJECT_ID"="$nso_col_1")
   3 - filter(COUNT(*)>0)
   5 - filter("T3"."OBJECT_ID" IS NOT NULL)
   6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   8 - filter("T1"."OBJECT_ID" IS NOT NULL)



select t1.*
from t1, t2
where t1.object_id = t2.object_id
  and t1.object_id is not null
  and t1.object_id not in (select t3.object_id
                             from t3
                            where t3.object_id is not null
                            group by object_id, object_name
                           having count(*) > 0);

Plan hash value: 3043464937
-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          | 34527 |  3270K|   387   (5)| 00:00:04 |
|*  1 |  HASH JOIN             |          | 34527 |  3270K|   387   (5)| 00:00:04 |
|   2 |   TABLE ACCESS FULL    | T2       | 36347 |   141K|   125   (2)| 00:00:02 |
|*  3 |   HASH JOIN RIGHT ANTI |          | 34528 |  3135K|   259   (6)| 00:00:03 |
|   4 |    VIEW                | VW_NSO_1 |  1818 | 23634 |   131   (7)| 00:00:02 |
|*  5 |     FILTER             |          |       |       |            |          |
|   6 |      HASH GROUP BY     |          |  1818 | 36360 |   131   (7)| 00:00:02 |
|*  7 |       TABLE ACCESS FULL| T3       | 36347 |   709K|   125   (2)| 00:00:02 |
|*  8 |    TABLE ACCESS FULL   | T1       | 36346 |  2839K|   127   (4)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - access("T1"."OBJECT_ID"="$nso_col_1")
   5 - filter(COUNT(*)>0)
   7 - filter("T3"."OBJECT_ID" IS NOT NULL)
   8 - filter("T1"."OBJECT_ID" IS NOT NULL)


select t2.*
from t1, t2
where t1.object_id = t2.object_id
  and t1.object_id is not null
  and t1.object_id not in (select t3.object_id
                             from t3
                            where t3.object_id is not null
                            group by object_id, object_name
                           having count(*) > 0);

Plan hash value: 523046018
-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          | 34527 |  3270K|   387   (5)| 00:00:04 |
|*  1 |  HASH JOIN             |          | 34527 |  3270K|   387   (5)| 00:00:04 |
|*  2 |   HASH JOIN RIGHT ANTI |          | 34528 |   573K|   258   (5)| 00:00:03 |
|   3 |    VIEW                | VW_NSO_1 |  1818 | 23634 |   131   (7)| 00:00:02 |
|*  4 |     FILTER             |          |       |       |            |          |
|   5 |      HASH GROUP BY     |          |  1818 | 36360 |   131   (7)| 00:00:02 |
|*  6 |       TABLE ACCESS FULL| T3       | 36347 |   709K|   125   (2)| 00:00:02 |
|*  7 |    TABLE ACCESS FULL   | T1       | 36346 |   141K|   125   (2)| 00:00:02 |
|   8 |   TABLE ACCESS FULL    | T2       | 36347 |  2839K|   127   (4)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - access("T1"."OBJECT_ID"="$nso_col_1")
   4 - filter(COUNT(*)>0)
   6 - filter("T3"."OBJECT_ID" IS NOT NULL)
   7 - filter("T1"."OBJECT_ID" IS NOT NULL)
24 rows selected.