如何写好复杂sql的hint?

54 阅读 0 评论 36 点赞

我是靠谱客的博主幽默时光，最近开发中收集的这篇文章主要介绍如何写好复杂sql的hint?，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1. 我们都知道什么是hint,但是如何写好复杂sql的hint,保证他比较稳定的执行:

比如：我们看这样一个sql:

SQL> create table t1(n1 integer,n2 integer);
Table created.
SQL> create table t2(n1 integer,n2 integer);
Table created.
SQL> select * from t2
where t2.n2 = 1
and exists
(select null from t1 where t1.n1 = t2.n1);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2132554994
---------------------------------------------------------------------------
| Id
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
|
---------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
1 |
26 |
4
(0)| 00:00:01 |
|*
1 |
FILTER
|
|
|
|
|
|
|*
2 |
TABLE ACCESS FULL| T2
|
1 |
26 |
2
(0)| 00:00:01 |
|*
3 |
TABLE ACCESS FULL| T1
|
1 |
13 |
2
(0)| 00:00:01 |
---------------------------------------------------------------------------

如果这个时候我们想要走另外一种执行计划，比如nested loop：

那这个时候hint怎么写呢?

有些人会这样写:

SQL>
select /*+nl_sj(t1) leading(t2)*/* from t2 where t2.n2 = 1 and exists (select null from t1 where t1.n1 = t2.n1);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2132554994
---------------------------------------------------------------------------
| Id
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
|
---------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
1 |
26 |
4
(0)| 00:00:01 |
|*
1 |
FILTER
|
|
|
|
|
|
|*
2 |
TABLE ACCESS FULL| T2
|
1 |
26 |
2
(0)| 00:00:01 |
|*
3 |
TABLE ACCESS FULL| T1
|
1 |
13 |
2
(0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T1" "T1" WHERE
"T1"."N1"=:B1))
2 - filter("T2"."N2"=1)
3 - filter("T1"."N1"=:B1

可以看到这种hint并没有起作用，这里大家要明白，这里t1,t2并不是平等的两表关联，而实际上是t2表为主表，t1表只是过滤条件：

2. 那我们怎么写这种模块hint呢？

这里就要引入一个模块hint概念了:使用qb_name

比如刚才那个我们可以改写成这样:

select /*+qb_name(main)*/ * from t2 where t2.n2 = 1 and exists (select /*+qb_name(subq) nl_sj*/null from t1 where t1.n1 = t2.n1);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 799559032
-------------------------------------------------------------------------------
| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
1 |
39 |
4
(0)| 00:00:01 |
|
1 |
NESTED LOOPS SEMI
|
|
1 |
39 |
4
(0)| 00:00:01 |
|*
2 |
TABLE ACCESS FULL | T2
|
1 |
26 |
2
(0)| 00:00:01 |
|*
3 |
VIEW
| VW_SQ_1 |
1 |
13 |
2
(0)| 00:00:01 |
|
4 |
TABLE ACCESS FULL| T1
|
1 |
13 |
2
(0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T2"."N2"=1)
3 - filter("N1"="T2"."N1")

执行计划就走对了,定义了qb_name之后最大的好处就是再设定其他hint时，可以指定qb_name:

也就是/*+ qb_name (queryblock ) */
qb_name是定义一个查询模块的别名的意思:

3. 我们来看个例子:

SQL> explain plan for select /*+qb_name(daodao)*/* from dual t;
Explained.
SQL> select * from table(dbms_xplan.display(null, null, 'ALL'));
PLAN_TABLE_OUTPUT
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
1 |
2 |
2
(0)| 00:00:01 |
|
1 |
TABLE ACCESS FULL| DUAL |
1 |
2 |
2
(0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
1 - DAODAO / T@DAODAO
--这里的模块名是DAODAO
SQL> explain plan for select * from dual t;
SQL> select * from table(dbms_xplan.display(null, null, 'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id
| Operation
| Name | Rows
| Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
1 |
2 |
2
(0)| 00:00:01 |
|
1 |
TABLE ACCESS FULL| DUAL |
1 |
2 |
2
(0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - SEL$1 / T@SEL$1
----这里的模块名是SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T"."DUMMY"[VARCHAR2,1]
18 rows selected.

这两个执行计划的差别可以看出：其实在oracle内部这个查询模块叫SEL$1,我们也可以直接指定qb_name。

其中sel$表示查询的意思，我们有时候还会看到ins$2, upd$3, del$4,分别表示插入，更新，删除模块。

4. 我们再看如何用qb_name 写我们平常的hint：（全表和索引）

SQL> create table daodao1 as select* from dba_objects;
Table created.
SQL> create index idx_daodao on daodao1(object_id);
Index created.
SQL> explain plan for select /*+qb_name(qb) full(@qb t)*/* from daodao1 t where object_id = 5;
--这里@qb t表示的是qb模块下表t
Explained.
SQL> select * from table(dbms_xplan.display(null, null, 'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4181185770
-----------------------------------------------------------------------------
| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
|
1 |
177 |
155
(2)| 00:00:02 |
|*
1 |
TABLE ACCESS FULL| DAODAO1 |
1 |
177 |
155
(2)| 00:00:02 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - QB / T@QB
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=5)

使用索引的hint:

SQL> explain plan for select /*+qb_name(qb) index(@qb t idx_daodao)*/* from daodao1 t where object_id >= 5;
--这里表示qb模块走表t的idx_daodao索引
Explained.
SQL> select * from table(dbms_xplan.display(null, null, 'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4040965138
--------------------------------------------------------------------------------
----------
| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)|
Time
|
--------------------------------------------------------------------------------
----------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
| 40725 |
7039K|
862
(1)|
00:00:11 |
|
1 |
TABLE ACCESS BY INDEX ROWID| DAODAO1
| 40725 |
7039K|
862
(1)|
00:00:11 |
|*
2 |
INDEX RANGE SCAN
| IDX_DAODAO | 40725 |
|
114
(1)|
00:00:02 |

5 . 有时候如果对于那些自己没把握写hint的sql,但是又希望他走自己想走的执行计划，怎么办呢？答案是：让oracle帮自己写:这里我们可以使用oracle的执行计划的outline功能：

SQL> explain plan for select /*+qb_name(qb) index(@qb t idx_daodao)*/* from daodao1 t where object_id >= 5;
Explained.
SQL> select * from table(dbms_xplan.display(null, null, 'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4040965138
------------------------------------------------------------------------------------------
| Id
| Operation
| Name
| Rows
| Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
|
| 40725 |
7039K|
862
(1)| 00:00:11 |
|
1 |
TABLE ACCESS BY INDEX ROWID| DAODAO1
| 40725 |
7039K|
862
(1)| 00:00:11 |
|*
2 |
INDEX RANGE SCAN
| IDX_DAODAO | 40725 |
|
114
(1)| 00:00:02 |
------------------------------------------------------------------------------------------
Outline Data
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"QB" "T"@"QB" ("DAODAO1"."OBJECT_ID"))
OUTLINE(@"QB")
OUTLINE_LEAF(@"QB")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/

我们只需要把这部分:/*+ BEGIN_OUTLINE_DATA INDEX(@"QB" "T"@"QB" ("DAODAO1"."OBJECT_ID")) OUTLINE(@"QB") OUTLINE_LEAF(@"QB") ALL_ROWS OPTIMIZER_FEATURES_ENABLE('10.2.0.1') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA*/拿出来作为hint就可以基本保障是我们想要的这种执行计划了，这对于那种经常要跑的存储过程，稳定执行计划功能非常好，我们在生产中大量使用这种hint。

6. 总结: 对于比较复杂的执行计划，我们可以使用qb_name，和借助oracle的outline等两种方法来解决。一般在实践中倾向于先用qb_name执行出sql要的执行计划，然后可以使用oracle给出的hint进行巩固。