CBO hint:no_unnest,push_subq,push_pred的用法

棉花糖ONE

workaround :利用no_merge强制命名,这时候不再做查询转换,想怎么玩都行了,qb_name一样可以

SQL> set autot trace exp
SQL>  select  *
  2     from test_1
  3    where object_id not in (select /*+ no_merge(v) NO_SWAP_JOIN_INPUTS(v) */ * from (select
  4                             object_id
  5                              from test_2
  6                             where object_id is not null
  7                             group by object_id
  8                            having count(*) = 1) v)
  9      and object_id is not null;

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |    10 |  1730 |    32   (7)|
|   1 |  HASH JOIN ANTI       |        |    10 |  1730 |    32   (7)|
|   2 |   TABLE ACCESS FULL   | TEST_1 |  4423 |   691K|    15   (0)|
|   3 |   VIEW                |        |  4424 | 57512 |    16   (7)|
|   4 |    FILTER             |        |       |       |            |
|   5 |     HASH GROUP BY     |        |  4424 | 57512 |    16   (7)|
|   6 |      TABLE ACCESS FULL| TEST_2 |  4424 | 57512 |    15   (0)|
---------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


SQL>  select /*+ NO_SWAP_JOIN_INPUTS(@a1 v) */ *
  2     from test_1
  3    where object_id not in (select /*+ no_merge(v)  qb_name(a1) */ * from (select
  4                             object_id
  5                              from test_2
  6                             where object_id is not null
  7                             group by object_id
  8                            having count(*) = 1) v)
  9      and object_id is not null;

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |    10 |  1730 |    32   (7)|
|   1 |  HASH JOIN ANTI       |        |    10 |  1730 |    32   (7)|
|   2 |   TABLE ACCESS FULL   | TEST_1 |  4423 |   691K|    15   (0)|
|   3 |   VIEW                |        |  4424 | 57512 |    16   (7)|
|   4 |    FILTER             |        |       |       |            |
|   5 |     HASH GROUP BY     |        |  4424 | 57512 |    16   (7)|
|   6 |      TABLE ACCESS FULL| TEST_2 |  4424 | 57512 |    15   (0)|
---------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

zergduan

... 为什么我还是不可以呢？ 那里写错了？

MYDB@MYDB10G >explain plan for
  2  select *
  3    from t1, t2
  4   where t1.object_id = t2.object_id
  5     and t1.data_object_id < 100
  6     and t1.object_id is not null
  7     and t1.object_id not in (select /*+  NO_SWAP_JOIN_INPUTS(@SEL$683B0107 VW_NSO_1@SEL$5DA710D3) */
  8                               t3.object_id
  9                                from t3
10                               where t3.object_id is not null
11                               group by object_id, object_name
12                              having count(*) > 0)
13  /

已解释。

已用时间:  00: 00: 00.01
MYDB@MYDB10G >select * from table(dbms_xplan.display(null,null,'+alias'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2554849999

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |    10 |  1830 |    98   (4)| 00:00:02 |
|*  1 |  HASH JOIN             |          |    10 |  1830 |    98   (4)| 00:00:02 |
|*  2 |   HASH JOIN ANTI       |          |    10 |   980 |    52   (4)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL   | T1       |    15 |  1275 |    46   (0)| 00:00:01 |
|   4 |    VIEW                | VW_NSO_1 |    16 |   208 |     5  (20)| 00:00:01 |
|*  5 |     FILTER             |          |       |       |            |          |
|   6 |      HASH GROUP BY     |          |    16 |   208 |     5  (20)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL| T3       |   304 |  3952 |     4   (0)| 00:00:01 |
|   8 |   TABLE ACCESS FULL    | T2       | 11654 |   967K|    45   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / T1@SEL$1
   4 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3
   5 - SEL$683B0107
   7 - SEL$683B0107 / T3@SEL$2
   8 - SEL$5DA710D3 / T2@SEL$1

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - access("T1"."OBJECT_ID"="$nso_col_1")
   3 - filter("T1"."DATA_OBJECT_ID"<100 AND "T1"."OBJECT_ID" IS NOT NULL)
   5 - filter(COUNT(*)>0)
   7 - filter("T3"."OBJECT_ID" IS NOT NULL)

已选择34行。

棉花糖ONE

你的oracle是哪版本的,我10.2.0.4的

zergduan

我上面的有错误，但是修改过来后也一样不成，10.2.0.4

MYDB@MYDB10G >select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

MYDB@MYDB10G >explain plan for
  2  select *
  3    from t1, t2
  4   where t1.object_id = t2.object_id
  5     and t1.data_object_id < 100
  6     and t1.object_id is not null
  7     and t1.object_id not in (select /*+  NO_SWAP_JOIN_INPUTS(@SEL$5DA710D3 VW_NSO_1@SEL$5DA710D3) */
  8                               t3.object_id
  9                                from t3
10                               where t3.object_id is not null
11                               group by object_id, object_name
12                              having count(*) > 0)
13  /

已解释。

MYDB@MYDB10G >select * from table(dbms_xplan.display(null,null,'+alias'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2554849999

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |    10 |  1830 |    98   (4)| 00:00:02 |
|*  1 |  HASH JOIN             |          |    10 |  1830 |    98   (4)| 00:00:02 |
|*  2 |   HASH JOIN ANTI       |          |    10 |   980 |    52   (4)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL   | T1       |    15 |  1275 |    46   (0)| 00:00:01 |
|   4 |    VIEW                | VW_NSO_1 |    16 |   208 |     5  (20)| 00:00:01 |
|*  5 |     FILTER             |          |       |       |            |          |
|   6 |      HASH GROUP BY     |          |    16 |   208 |     5  (20)| 00:00:01 |
|*  7 |       TABLE ACCESS FULL| T3       |   304 |  3952 |     4   (0)| 00:00:01 |
|   8 |   TABLE ACCESS FULL    | T2       | 11654 |   967K|    45   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / T1@SEL$1
   4 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3
   5 - SEL$683B0107
   7 - SEL$683B0107 / T3@SEL$2
   8 - SEL$5DA710D3 / T2@SEL$1

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - access("T1"."OBJECT_ID"="$nso_col_1")
   3 - filter("T1"."DATA_OBJECT_ID"<100 AND "T1"."OBJECT_ID" IS NOT NULL)
   5 - filter(COUNT(*)>0)
   7 - filter("T3"."OBJECT_ID" IS NOT NULL)

已选择34行。

我没有理解错吧~

/*+NO_SWAP_JOIN_INPUTS(@SEL$5DA710D3 VW_NSO_1@SEL$5DA710D3) */

其中@SEL$5DA710D3 是hash join这部操作的block名
VW_NSO_1@SEL$5DA710D3是那个内建的view的名字

[ 本帖最后由 zergduan 于 2010-6-1 13:43 编辑 ]

棉花糖ONE

你的没问题啊,已经不是view那个驱动了啊

zergduan

我说错了。。。 明白了，谢谢~~

MYDB@MYDB10G >explain plan for
  2  select *
  3    from t1, t2
  4   where t1.object_id = t2.object_id
  5     and t1.data_object_id < 100
  6     and t1.object_id is not null
  7     and t1.object_id not in (select /*+ SWAP_JOIN_INPUTS(@SEL$5DA710D3 VW_NSO_1@SEL$5DA710D3) */
  8                               t3.object_id
  9                                from t3
10                               where t3.object_id is not null
11                               group by object_id, object_name
12                              having count(*) > 0)
13  /

已解释。

MYDB@MYDB10G >select * from table(dbms_xplan.display(null,null,'+alias'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 523363264

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |    10 |  1830 |    98   (4)| 00:00:02 |
|*  1 |  HASH JOIN             |          |    10 |  1830 |    98   (4)| 00:00:02 |
|*  2 |   HASH JOIN RIGHT ANTI |          |    10 |   980 |    52   (4)| 00:00:01 |
|   3 |    VIEW                | VW_NSO_1 |    16 |   208 |     5  (20)| 00:00:01 |
|*  4 |     FILTER             |          |       |       |            |          |
|   5 |      HASH GROUP BY     |          |    16 |   208 |     5  (20)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL| T3       |   304 |  3952 |     4   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL   | T1       |    15 |  1275 |    46   (0)| 00:00:01 |
|   8 |   TABLE ACCESS FULL    | T2       | 11654 |   967K|    45   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$683B0107 / VW_NSO_1@SEL$5DA710D3
   4 - SEL$683B0107
   6 - SEL$683B0107 / T3@SEL$2
   7 - SEL$5DA710D3 / T1@SEL$1
   8 - SEL$5DA710D3 / T2@SEL$1

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"."DATA_OBJECT_ID"<100 AND "T1"."OBJECT_ID" IS NOT NULL)

已选择34行。

[ 本帖最后由 zergduan 于 2010-6-1 13:48 编辑 ]

棉花糖ONE

你要想view驱动,你加swap_join_inputs试试,一个hint只能控制一层,不能做到控制多层

shiri512003

oracle在11g里面对这几组hint的分类也是有点意思的
UNNEST                                                               QKSFM_UNNEST
NO_UNNEST                                                         QKSFM_UNNEST

PUSH_PRED                                                          QKSFM_FILTER_PUSH_PRED
NO_PUSH_PRED                                                   QKSFM_FILTER_PUSH_PRED
-- 上面两组是单独的分类了

NO_PUSH_SUBQ                                                  QKSFM_TRANSFORMATION
PUSH_SUBQ                                                         QKSFM_TRANSFORMATION

myseasky2007

好贴

hotdog04

学习