【sql调优之执行计划】使用hint(三)Hints for Query Transformations

版本：9208或1024

Hints for Query Transformations

查询转换的hints

下面列出来的这些hint建议进行sql查询转换

No_query_transformation(10g 增加)

Use_concat

No_expand

Rewrite

Expand_gset_to_union

Norewrite

Merge

No_merge

Star_transformation

Fact

No_fact

 

No_query_transformation(10g)

这个参数告诉优化器跳过查询转换，但是不限制or，视图合并，子查询unnesting，星型转换，物化视图重写，句法为: /*+ No_query_transformation */

Use_concat

使用use_concat hint用来是where后面的or条件转换为union all的集合操作，一般来说，这个转换只在使用concatenation的cost要比其他的低时使用。

No_expand

这个hint阻止优化器对or或者in-list条件进行扩展。

示例：

SQL> select /*+  */* from t_policy a

  2  where a.policy_id = 123

  3  or a.send_code = '1111111111';

 

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=17 Bytes=8058

          )

 

   1    0   CONCATENATION

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_POLICY' (Cost=1 Card

          =1 Bytes=474)

 

   3    2       INDEX (RANGE SCAN) OF 'IDX_POLICY__HEAD_SEND' (NON-UNI

          QUE) (Cost=2 Card=1)

 

   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_POLICY' (Cost=1 Card

          =1 Bytes=474)

 

   5    4       INDEX (UNIQUE SCAN) OF 'PK_T_POLICY' (UNIQUE) (Cost=2

          Card=1)

 

 

 

 

SQL> select /*+ NO_EXPAND */* from t_policy a

  2  where a.policy_id = 123

  3  or a.send_code = '1111111111';

 

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=19 Card=17 Bytes=805

          8)

 

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_POLICY' (Cost=19 Card=

          17 Bytes=8058)

 

   2    1     BITMAP CONVERSION (TO ROWIDS)

   3    2       BITMAP OR

   4    3         BITMAP CONVERSION (FROM ROWIDS)

   5    4           INDEX (RANGE SCAN) OF 'PK_T_POLICY' (UNIQUE) (Cost

          =2)

 

   6    3         BITMAP CONVERSION (FROM ROWIDS)

   7    6           SORT (ORDER BY)

   8    7             INDEX (RANGE SCAN) OF 'IDX_POLICY__HEAD_SEND' (N

          ON-UNIQUE) (Cost=4)

 

 

 

 

SQL>

 

Rewrite和 no_rewrite

用来选择使用物化视图重写或者不适用物化视图重写

 

Merge和No_merge

示例（版本1024）

SQL> select /*+ */

  2   a.empno

  3    from scott.emp a,

  4         (select b.deptno, sum(b.sal) sal from scott.emp b group by b.deptno) bb

  5   where a.deptno = bb.deptno;

 

Execution Plan

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

Plan hash value: 269884559

 

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

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

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

|   0 | SELECT STATEMENT     |      |    14 |   280 |     6  (34)| 00:00:01 |

|*  1 |  HASH JOIN           |      |    14 |   280 |     6  (34)| 00:00:01 |

|   2 |   VIEW               |      |     3 |    39 |     3  (34)| 00:00:01 |

|   3 |    HASH GROUP BY     |      |     3 |    24 |     3  (34)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| EMP  |    14 |   112 |     2   (0)| 00:00:01 |

|*  5 |   TABLE ACCESS FULL  | EMP  |    13 |    91 |     2   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - access("A"."DEPTNO"="BB"."DEPTNO")

   5 - filter("A"."DEPTNO" IS NOT NULL)

 

SQL>

使用merge合并：

SQL> select /*+ merge(bb)*/

  2   a.empno

  3    from scott.emp a,

  4         (select b.deptno, sum(b.sal) sal from scott.emp b group by b.deptno) bb

  5   where a.deptno = bb.deptno;

 

Execution Plan

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

Plan hash value: 4115741206

 

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

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

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

|   0 | SELECT STATEMENT    |      |    28 |   616 |     6  (34)| 00:00:01 |

|   1 |  HASH GROUP BY      |      |    28 |   616 |     6  (34)| 00:00:01 |

|*  2 |   HASH JOIN         |      |    66 |  1452 |     5  (20)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL| EMP  |    13 |   247 |     2   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS FULL| EMP  |    13 |    39 |     2   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("A"."DEPTNO"="B"."DEPTNO")

   3 - filter("A"."DEPTNO" IS NOT NULL)

   4 - filter("B"."DEPTNO" IS NOT NULL)

 

SQL>

上面可以看到，使用了merge提示以后强制进行了视图合并，延迟了group by的时间，注意到两个执行计划中的rows和bytes的数量。这在有些时候有些用处，比如说，可以通过关联条件来减少做group by的数据行，这个需要结合具体情况来看。

No_merge则是禁用一些视图的合并

 

Star_transformation和No_Star_transformation

使用星型查询转换和不使用星型查询转换的hint。

Fact和no_fact

结合星型转换一起使用。

Unnest和no_unnest

子查询非嵌套和嵌套的提示。如下示例：

SQL> select a.owner

  2    from test.t_test_clusterfactor1 a

  3   where a.object_id in

  4         (select /*+ unnest*/

  5           b.object_id

  6            from test.t_test_clusterfactor2 b, test.t_test_clusterfactor3 c

  7           where c.object_id = b.object_id);

 

Execution Plan

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

Plan hash value: 578563338

 

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

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

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

|   0 | SELECT STATEMENT        |                         |     1 |    22 |       |   216  (32)| 00:00:01 |

|*  1 |  HASH JOIN SEMI         |                         |     1 |    22 |  1136K|   216  (32)| 00:00:01 |

|   2 |   TABLE ACCESS FULL     | T_TEST_CLUSTERFACTOR1   | 55006 |   483K|       |    94  (19)| 00:00:01 |

|   3 |   VIEW                  | VW_NSO_1                | 55005 |   698K|       |    57  (51)| 00:00:01 |

|*  4 |    HASH JOIN            |                         | 55005 |   537K|       |    57  (51)| 00:00:01 |

|   5 |     INDEX FAST FULL SCAN| IND_TEST_CLUSTERFACTOR2 | 55007 |   268K|       |    22  (37)| 00:00:01 |

|   6 |     INDEX FAST FULL SCAN| IND_TEST_CLUSTERFACTOR3 | 55010 |   268K|       |    22  (37)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

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

   4 - access("C"."OBJECT_ID"="B"."OBJECT_ID")

 

SQL>

 

SQL> select a.owner

  2    from test.t_test_clusterfactor1 a

  3   where a.object_id in

  4         (select /*+ no_unnest*/

  5           b.object_id

  6            from test.t_test_clusterfactor2 b, test.t_test_clusterfactor3 c

  7           where c.object_id = b.object_id);

 

Execution Plan

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

Plan hash value: 2597140566

 

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

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

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

|   0 | SELECT STATEMENT   |                         | 55006 |   483K|    97  (19)| 00:00:01 |

|*  1 |  FILTER            |                         |       |       |            |          |

|   2 |   TABLE ACCESS FULL| T_TEST_CLUSTERFACTOR1   | 55006 |   483K|    95  (19)| 00:00:01 |

|   3 |   NESTED LOOPS     |                         |     1 |    10 |     2   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR3 |     1 |     5 |     1   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR2 |     1 |     5 |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "TEST"."T_TEST_CLUSTERFACTOR3"

              "C","TEST"."T_TEST_CLUSTERFACTOR2" "B" WHERE "C"."OBJECT_ID"="B"."OBJECT_ID" AND

              "B"."OBJECT_ID"=:B1 AND "C"."OBJECT_ID"=:B2))

   4 - access("C"."OBJECT_ID"=:B1)

   5 - access("C"."OBJECT_ID"="B"."OBJECT_ID")

       filter("B"."OBJECT_ID"=:B1)

 

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

转载于:http://blog.itpub.net/16179598/viewspace-672998/