通过内部的hint来控制执行计划

通过内部的hint来控制执行计划

SQL> create table t1(c1 number, c2 number);

Table created.

SQL> create table t2(c1 number, c2 number);

Table created.

SQL> create table t3(c1 number, c2 number);

Table created.


示例一

1)select * from t1 where exists (select 1 from t2 where t1.c1=t2.c1)，如何让这个查询走nest loop，表t2为驱动。

SQL> select * from t1 where exists (select 1 from t2 where t1.c1=t2.c1);

未选定行

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));----------- 默认的执行计划是hash join

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID  2s3ytddz3pszf, child number 0
-------------------------------------
select * from t1 where exists (select 1 from t2 where t1.c1=t2.c1)

Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     5 (100)|          |
|*  1 |  HASH JOIN SEMI    |      |     1 |    39 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     1 |    26 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      DB_VERSION('11.1.0.7')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
      FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
      LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "T2"@"SEL$2")
      END_OUTLINE_DATA
  */




已选择58行。


根据Outline Data部分，我们可以仿照写出如下的hint.
SQL> select /*+ use_nl(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1") LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1") */
exists (select 1 from t2 where t1.c1=t2.c1);

未选定行

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID  aku38p8aarqmw, child number 0
-------------------------------------
select /*+ use_nl(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1") */* from t1 where
exists (select 1 from t2 where t1.c1=t2.c1)

Plan hash value: 2519264338

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     5 (100)|          |
|   1 |  NESTED LOOPS       |      |     1 |    39 |     5  (20)| 00:00:01 |
|   2 |   SORT UNIQUE       |      |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL | T1   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------


Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      DB_VERSION('11.1.0.7')

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
      FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
      USE_NL(@"SEL$5DA710D3" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
也可以自己指定query block的名字。
SQL> select /*+ use_nl([email=t2@c]t2@c[/email] t1) leading([email=t2@c]t2@c[/email]) */ * from t1 where exists (select /*+ qb_name(c) */1 from t2 where t1.c1=t2.c1);
未选定行
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9xzh3cjfu1jgn, child number 0
-------------------------------------
select /*+ use_nl([email=t2@c]t2@c[/email] t1) leading([email=t2@c]t2@c[/email]) */ * from t1 where exists
(select /*+ qb_name(c) */1 from t2 where t1.c1=t2.c1)
Plan hash value: 2519264338
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     5 (100)|          |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   1 |  NESTED LOOPS       |      |     1 |    39 |     5  (20)| 00:00:01 |
|   2 |   SORT UNIQUE       |      |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL | T1   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      DB_VERSION('11.1.0.7')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3CE84023")
      UNNEST(@"C")
      OUTLINE(@"SEL$1")
      OUTLINE(@"C")
      FULL(@"SEL$3CE84023" [email=]"T2"@"C[/email]")
      FULL(@"SEL$3CE84023" [email=]"T1"@"SEL$1[/email]")
      LEADING(@"SEL$3CE84023" [email=]"T2"@"C[/email]" [email=]"T1"@"SEL$1[/email]")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      USE_NL(@"SEL$3CE84023" [email=]"T1"@"SEL$1[/email]")
      END_OUTLINE_DATA
  */


示例2
select * from                                                                             
(                                                                                         
select                                                                                    
      v.c1 as v_c1,                                                                       
      v.c2 as v_c2,                                                                       
      t3.c2 as t3_c2                                                                     
from                                                                                      
      (select     
       t1.c1,                                                                             
       t2.c2                                                                              
      from                                                                                
       t1, t2                                                                             
      where                                                                              
       t1.c1 = t2.c1) v,                                                                  
      t3                                                                                 
where                                                                                    
      v.c1 = t3.c1                                                                        
) x                                                                                       
;         
如何让上面的查询走hash join，并且join的顺序是t1 ,t2 ,t3

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID  dvt15ap8szdbv, child number 0
-------------------------------------
select * from ( select       v.c1 as v_c1,       v.c2 as v_c2,
t3.c2 as t3_c2 from       (select        t1.c1,        t2.c2       from
       t1, t2       where        t1.c1 = t2.c1) v,       t3 where
v.c1 = t3.c1 ) x

Plan hash value: 2745117478

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT      |      |       |       |     7 (100)|          |
|*  1 |  HASH JOIN            |      |     1 |    65 |     7  (15)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |    52 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | T1   |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------


Outline Data
-------------

  /*+

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      DB_VERSION('11.1.0.7')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5C160134")
      MERGE(@"SEL$335DD26A")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$335DD26A")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$2")

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
      OUTLINE(@"SEL$3")
      FULL(@"SEL$5C160134" "T2"@"SEL$3")
      FULL(@"SEL$5C160134" "T3"@"SEL$2")
      FULL(@"SEL$5C160134" "T1"@"SEL$3")
      LEADING(@"SEL$5C160134" "T2"@"SEL$3" "T3"@"SEL$2" "T1"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$5C160134" "T3"@"SEL$2")
      USE_HASH(@"SEL$5C160134" "T1"@"SEL$3")
      END_OUTLINE_DATA
  */
同样的我们根据Outline Data部分，可以模仿写出如下语句，控制SQL语句的执行计划
SQL> select /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2" ) */* from

  2  (
  3  select
  4          v.c1 as v_c1,
  5          v.c2 as v_c2,
  6          t3.c2 as t3_c2
  7  from
  8          (select
  9                  t1.c1,
10                  t2.c2
11          from
12                  t1, t2
13          where
14                  t1.c1 = t2.c1) v,
15          t3
16  where
17          v.c1 = t3.c1
18  ) x
19  ;

未选定行


SQL>  select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
SQL_ID  1w9nxggt0062t, child number 0
-------------------------------------
select /*+ LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3"
"T3"@"SEL$2" ) */* from ( select       v.c1 as v_c1,       v.c2 as
v_c2,       t3.c2 as t3_c2 from       (select        t1.c1,
t2.c2       from        t1, t2       where        t1.c1 = t2.c1) v,
  t3 where       v.c1 = t3.c1 ) x

Plan hash value: 261998084

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     7 (100)|          |
|*  1 |  HASH JOIN          |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    39 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------


Outline Data
-------------

  /*+

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      DB_VERSION('11.1.0.7')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5C160134")
      MERGE(@"SEL$335DD26A")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$335DD26A")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$2")

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
      OUTLINE(@"SEL$3")
      FULL(@"SEL$5C160134" "T1"@"SEL$3")
      FULL(@"SEL$5C160134" "T2"@"SEL$3")
      FULL(@"SEL$5C160134" "T3"@"SEL$2")
      LEADING(@"SEL$5C160134" "T1"@"SEL$3" "T2"@"SEL$3" "T3"@"SEL$2")
      USE_HASH(@"SEL$5C160134" "T2"@"SEL$3")
      USE_HASH(@"SEL$5C160134" "T3"@"SEL$2")
      END_OUTLINE_DATA
  */
也可以自己指定query block 的名字
SQL> explain plan for
  2  select * from
  3  (
  4  select
  5        /*+ leading(t1@inline t2@inline t3) */
  6        v.c1 as v_c1,
  7        v.c2 as v_c2,
  8        t3.c2 as t3_c2
  9  from
10        (select /*+ qb_name(inline) */
11         t1.c1,
12         t2.c2
13        from
14         t1, t2
15        where
16         t1.c1 = t2.c1) v,
17        t3
18  where
19        v.c1 = t3.c1
20  ) x
21  ;
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    65 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    39 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |    26 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T3   |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-688783/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-688783/