Oracle优化器hint

发表于 更新于 分类于
本文字数： 22k 阅读时长 ≈ 20 分钟
内容包括：等。

Oracle优化器hint

一. 简介

优化器hint可以与SQL语句一起使用来改变执行计划。在SQL中使用hint来提高性能。hints（除了RULE-hint）会使Oracle使用基于成本的优化器，提示实际是SQL语句中的注释，语法格式：

1
2
3
4
# 其中HINT被hint文本所取代，当hint文本的语法不正确时，hint文本被忽略，不会被使用。若没有+号就仅仅是注释。
select /*+ HINT */ name
from emp
where id =1;

hint可以让你做一些通常由优化器做出的决定。作为开发者，你可能知道一些优化器不知道的数据信息。hint提供了一种机制，指示优化器根据特定的标准选择某种查询执行计划。

例如，你可能知道某个索引对某些查询更有选择性。基于这些信息，你可能会选择一个比优化器更有效的执行计划。在这种情况下，可以使用hint来指示优化器使用最佳执行计划。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 优化器选择employees.department_id列上的一个索引，来查找其部门ID 超过50的雇员中的前25行。优化器使用从索引中检索到的rowid，从雇员表中检索相应记录，并将其返回给客户端。第一条记录的检索通常几乎是在瞬间即可完成的。
SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id
FROM hr.employees
WHERE department_id > 50;
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
------------------------------------------------------------------------
|  0 | SELECT STATEMENT | | 26 | 182
|  1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 26 | 182
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | |
------------------------------------------------------------------------

# 无提示SELECT语句的执行计划
SELECT employee_id, department_id
FROM hr.employees
WHERE department_id > 50;
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
------------------------------------------------------------------------
|  0 | SELECT STATEMENT | | 50 | 350 |
|* 1 | VIEW | index$_join$_001 | 50 | 350 |
|* 2 | HASH JOIN | | | |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 50 | 350 |
|  4 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 50 | 350 |

将两个索引联接以尽可能快的返回请求的记录。优化器并不像上述hint那样多次在表和索引间倒腾，而是在EMP_DEPARTMENT_IX索引上使用范围扫描，找出所有部门ID超过50的行，并将这些行放在一个哈希表中。然后优化器读取EMP_EMP_ID_PK索引。对该索引中的每一行，它探测一次该哈希表，以查找相应的部门id。

在这种情况下，数据库不能在完成对EMP_DEPARTMENT_IX索引的范围扫描之前向客户端返回第一行。因此，此生成的计划将需要更长的时间返回第一条记录。与示例中按索引rowid访问表的计划不同，计划使用多数据块 I/O，导致大量读取操作。这种读取使得整个结果集的最后一行会更快地返回。

二. Hint种类

2.1 几种hint类别

  • Single-table：

    单表hint是在一个表或视图上指定的，如：INDEX ，USE_NL。

  • Multi-table

    多表hint与单表hint一样，只是hint可以指定一个或多个表或视图。如： LEADING ，USE_NL(table1 table2) ，USE_NL(table1) ，USE_NL(table2) 。

  • Query block

    查询块hint对单个查询块进行操作，如：STAR_TRANSFORMATION ，UNNEST 。

  • Statement

    语句hint适用于整个SQL语句，如 ALL_ROWS 。

2.2 常见hint

优化器的hint分为以下几类:

  • Hints for Optimization Approaches and Goals：优化方法和目标的hint
    • ALL_ROWS：明确地选择了基于成本的方法来优化语句块，目标是最佳吞吐量（即最小的总资源消耗）。
    • FIRST_ROWS(n)：明确地选择了基于成本的方法来优化语句块，目标是最佳响应时间（返回第一行的最小资源占用）。在较新的Oracle版本中，你应该给这个提示一个参数：FIRST_ROWS(n) 意味着优化器将确定一个执行计划，为返回前n行提供快速响应。
    • CHOOSE：使优化器在基于规则的方法和基于成本的方法之间选择一个SQL语句，基于该语句所访问的表的统计信息的存在。
    • RULE：为一个语句块明确地选择了基于规则的优化。这个提示也导致优化器忽略了为语句块指定的任何其他提示。RULE提示在Oracle 10g中不再起作用。
  • Hints for Access Paths：访问路径的hint
    • FULL：明确地选择了对指定表进行全表扫描。FULL提示的语法是FULL(table)，其中table指定了要进行全表扫描的表的别名（如果别名不存在，则是表名）。
    • ROWID：明确地选择了对指定表进行ROWID扫描。ROWID提示的语法是 ROWID(table) ，其中table指定了要进行ROWID表访问的表的名称或别名。(这个提示在Oracle 10g中被废弃)
    • CLUSTER：明确地选择了集群扫描来访问指定的表。CLUSTER提示的语法是 CLUSTER(table) ，其中table指定了要被集群扫描访问的表的名称或别名。
    • HASH：明确地选择了一个散列扫描来访问指定的表。HASH提示的语法是 HASH(table) ，其中table指定要通过散列扫描访问的表的名称或别名。
    • HASH_AJ：将一个NOT IN子查询转换为一个哈希反连接，以访问指定的表。HASH_AJ提示的语法是 HASH_AJ(table) ，其中table指定了要访问的表的名称或别名（在Oracle 10g中被废弃）。
    • INDEX：明确地选择了对指定表的索引扫描。INDEX提示的语法是 INDEX(table index) ，其中:table指定与要扫描的索引相关的表的名称或别名，index指定要进行索引扫描的索引。这个提示可以选择性地指定一个或多个索引。
    • NO_INDEX：明确地禁止为指定的表建立一组索引。NO_INDEX提示的语法是NO_INDEX(表索引)
    • INDEX_ASC：明确地选择了对指定表的索引扫描。如果语句使用索引范围扫描，Oracle会按照索引值的升序扫描索引条目。
    • INDEX_COMBINE：如果没有索引作为INDEX_COMBINE提示的参数，优化器将在表上使用位图索引的任何布尔组合，具有最佳成本估计。如果给出了某些索引作为参数，优化器将尝试使用这些特定位图索引的一些布尔组合。INDEX_COMBINE的语法是INDEX_COMBINE(表索引)。
    • INDEX_JOIN：明确指示优化器使用一个索引连接作为访问路径。为了使提示产生积极的效果，必须存在足够少的索引，包含解决查询所需的所有列。
    • INDEX_DESC：明确地选择了对指定表的索引扫描。如果语句使用索引范围扫描，Oracle会按照索引值的降序扫描索引条目。
    • INDEX_FFS：这个提示导致执行快速的全索引扫描，而不是全表扫描。
    • NO_INDEX_FFS：不要使用快速全索引扫描（来自Oracle 10g）。
    • INDEX_SS：从查询计划中排除范围扫描（来自Oracle 10g）。
    • INDEX_SS_ASC：从查询计划中排除范围扫描（来自Oracle 10g）。
    • INDEX_SS_DESC：从查询计划中排除范围扫描（来自Oracle 10g）。
    • NO_INDEX_SS：导致优化器排除对指定表的指定索引进行跳过扫描（来自Oracle 10g）。
  • Hints for Query Transformations：查询转换的hint
    • NO_QUERY_TRANSFORMATION：防止优化器进行查询转换（来自Oracle 10g）。
    • USE_CONCAT：强制将查询的WHERE子句中的组合OR条件转化为使用UNION ALL集合操作符的复合查询。通常情况下，只有当使用连接条件的查询成本比不使用连接条件的查询成本低时，才会发生这种转换。
    • NO_EXPAND：阻止优化器对WHERE子句中具有OR条件或IN列表的查询考虑OR扩展。通常情况下，优化器会考虑使用OR扩展，如果它认为成本比不使用它低，就会使用这种方法。
    • REWRITE：强制优化器在可能的情况下以物化视图的方式重写查询，而不考虑成本。无论是否有视图列表，都可以使用REWRITE提示。如果你使用REWRITE和一个视图列表，并且该列表包含一个合格的物化视图，那么Oracle会使用该视图，而不考虑其成本。
    • NOREWRITE / NO_REWRITE：在Oracle 10g中改名为NO_REWRITE。NOREWRITE/NO_REWRITE提示禁用了查询块的查询重写，覆盖了参数 QUERY_REWRITE_ENABLED 的设置。
    • MERGE：可以让你在一个查询中合并视图。
    • NO_MERGE：使Oracle不合并可合并的视图。这个提示最常被用来减少查询的可能排列组合的数量，使优化更快。
    • FACT：表示该表应该被认为是一个事实表。这是在星形转换的背景下使用的。
    • NO_FACT：在星形转换的上下文中使用，向转换表明提示的表不应该被视为事实表。
    • STAR_TRANSFORMATION：使优化器使用已使用转换的最佳计划。如果没有这个提示，优化器可以做出查询优化的决定，使用没有转换的最佳计划，而不是转换后的查询的最佳计划。
    • NO_STAR_TRANSFORMATION：不使用星形转换（来自Oracle 10g）。
    • UNNEST：指定子查询的不嵌套。
    • NO_UNNEST：可以关闭特定子查询块的不嵌套。
  • Hints for Join Orders：联接顺序的hint
    • LEADING：给出这个hint以指示连接中的主导表，将只表示1个表。可以使用ORDERED指定整个表的顺序。语法：LEADING(table) 。
    • ORDERED：使Oracle按照表在FROM子句中出现的顺序连接表。如果你在执行连接的SQL语句中省略了ORDERED提示，优化器会选择连接表的顺序。如果你知道一些优化器不知道的从每个表中选择的行数，你可能想使用ORDERED提示来指定一个连接顺序。这些信息可以让你比优化器更好地选择内表和外表。
  • Hints for Join Operations：连接操作的hint
    • USE_NL：使Oracle以嵌套循环连接的方式将每个指定的表与另一个记录源连接起来，并以指定的表作为内表。USE_NL提示的语法是 USE_NL(table table) ，其中table是一个表的名称或别名，将被用作嵌套循环连接的内表。
    • NO_USE_NL：不要使用嵌套循环（来自Oracle 10g）。
    • USE_NL_WITH_INDEX：指定一个嵌套的循环连接（来自Oracle 10g）。
    • USE_MERGE：会使Oracle以排序合并连接的方式将每个指定的表与另一个行源连接起来。USE_MERGE提示的语法是 USE_MERGE(table) ，其中table是一个要连接到行源的表，这个行源是用排序合并连接的方式连接前面的表而得到的。
    • NO_USE_MERGE：不要使用合并（来自Oracle 10g）。
    • USE_HASH：使Oracle以散列连接的方式将每个指定的表与另一个行源连接。USE_HASH提示的语法是 USE_HASH(table table) ，其中table是一个要连接到行源的表，这个行源是用散列连接的方式连接前面的表而得到的。
    • NO_USE_HASH：不要使用哈希（来自Oracle 10g）。
  • Hints for Parallel Execution：并行执行的hint
    • PARALLEL：允许你指定可用于查询的并发查询服务器的数量。语法是PARALLEL(表号数)。如果在查询中指定了别名，PARALLEL提示必须使用表的别名。然后，PARALLEL提示可以取两个值，在表名后面用逗号分隔。第一个值指定给定表的并行程度，第二个值指定该表如何在并行服务器的实例中被分割。指定DEFAULT或没有值表示查询协调器应该检查初始化参数的设置（在后面的章节中描述）以确定默认的并行程度。
    • NOPARALLEL / NO_PARALLEL：允许你禁用一个表的并行扫描，即使该表是用PARALLEL子句创建的。在Oracle 10g中，这个提示被重新命名为NO_PARALLEL。
    • PQ_DISTRIBUTE：提高了并行连接操作的性能。通过指定连接表的行应该如何在生产者和消费者查询服务器之间分配来做到这一点。使用这个提示会覆盖优化器通常会做出的决定。
    • NO_PARALLEL_INDEX：覆盖了一个索引上的PARALLEL属性设置，以避免平行索引扫描操作。
  • Additional Hints：其他hint
    • APPEND：当APPEND提示与INSERT语句一起使用时，数据被追加到表中。区块中现有的自由空间不被使用。如果一个表或索引被指定为nologging，这个提示与一个插入语句一起使用会产生一个直接的路径插入，从而减少重做的产生。
    • NOAPPEND：覆盖附加模式。
    • CACHE：指定当进行全表扫描时，为提示中的表检索的块被放置在缓冲区缓存的LRU列表中最近使用的一端。这个选项对于小的查询表很有用。在下面的例子中，CACHE提示覆盖了表的默认缓存规范。
    • NOCACHE：当执行全表扫描时，为该表检索的块被放在缓冲区缓存中LRU列表的最近使用的一端。这是缓冲区缓存中块的正常行为。
    • PUSH_PRED：强制将连接谓词推入视图。
    • NO_PUSH_PRED：防止将连接谓词推入视图。
    • PUSH_SUBQ：使非合并子查询在执行计划中尽可能早地被评估。
    • NO_PUSH_SUBQ：导致非合并子查询作为执行计划的最后一步被评估。
    • QB_NAME：为一个查询块指定一个名称（来自Oracle 10g）。
    • CURSOR_SHARING_EXACT：如果安全的话，Oracle可以用绑定变量替换SQL语句中的字面意思。这是由CURSOR_SHARING启动参数控制的。CURSOR_SHARING_EXACT提示使这种行为被关闭。换句话说，Oracle在执行SQL语句时，不会试图用绑定变量替换字面意义。
    • DRIVING_SITE：强制在与Oracle选择的站点不同的站点上对表执行查询。
    • DYNAMIC_SAMPLING：可以让你控制动态采样，通过确定表和索引的更精确的谓词选择性和统计数据来提高服务器性能。你可以将DYNAMIC_SAMPLING的值设置为0到10的数值。级别越高，编译器在动态采样方面投入的精力越多，应用也越广泛。除非你指定一个表，否则采样的默认值是游标级。
    • SPREAD_MIN_ANALYSIS：这个提示省略了一些规则的编译时间优化，主要是详细的依赖图分析，在电子表格上。一些优化，如创建过滤器以有选择地填充电子表格访问结构和有限的规则修剪仍然被使用（来自Oracle 10g）。
  • Hints with unknown status：hint
    • MERGE_AJ：将一个NOT IN子查询转换为一个合并反连接，以访问指定的表。MERGE_AJ提示的语法是 MERGE_AJ(table) ，其中table指定要访问的表的名称或别名。
    • AND_EQUAL：明确地选择了一个执行计划，该计划使用一个访问路径，合并了几个单列索引的扫描。AND_EQUAL提示的语法是 AND_EQUAL(table index index) ，其中table指定了与要合并的索引相关的表的名称或别名，index指定了一个要进行索引扫描的索引。你必须至少指定两个索引。你不能指定超过五个。(在Oracle 10g中被废弃)
    • STAR：迫使大表使用索引上的嵌套循环连接最后被连接。优化器将考虑小表的不同排列组合。(在Oracle 10g中被废弃)
    • BITMAP：BITMAP(table_name index_name) 使用一个位图索引来访问表。(depricated ?)
    • HASH_SJ：使用 Hash Anti-Join 来评估一个NOT IN子查询。在子查询中使用这个提示，而不是在主查询中。当你的大量NOT IN子查询使用FILTER或NESTED LOOPS连接时，使用这个提示。如果HASH_AJ拒绝工作，请尝试MERGE_AJ。（在Oracle 10g中被废弃）
    • NL_SJ：在一个子查询中使用嵌套循环。(在Oracle 10g中被废弃)
    • NL_AJ：在一个子查询中使用反连接。(在Oracle 10g中被废弃)
    • ORDERED_PREDICATES：(在Oracle 10g中被废弃)
    • EXPAND_GSET_TO_UNION：(在Oracle 10g中被废弃)

三. hint语句归纳

3.1 使用列表

  1. /*+ ALL_ROWS*/：表明对语句块选择基于开销的优化方法，并获得最佳吞吐量，使资源消耗最小化。
1
SELECT /*+ ALL_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

  1. /*+ FIRST_ROWS*/：表明对语句块选择基于开销的优化方法，并获得最佳响应时间，使资源消耗最小化。

    1
    SELECT /*+ FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

  2. /*+ CHOOSE*/：表明如果数据字典中有访问表的统计信息，将基于开销的优化方法，并获得最佳的吞吐量;表明如果数据字典中没有访问表的统计信息，将基于规则开销的优化方法。

    1
    SELECT /*+ CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

  3. /*+ RULE*/：表明对语句块选择基于规则的优化方法。

    1
    SELECT /*+ RULE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

  4. /*+ FULL(TABLE)*/：表明对表选择全局扫描的方法。

    1
    SELECT /*+ FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=’SCOTT’;

  5. /*+ ROWID(TABLE)*/：提示明确表明对指定表根据ROWID进行访问。

    1
    SELECT /*+ ROWID(BSEMPMS)*/ FROM BSEMPMS WHERE ROWID>=’AAAAAAAAAAAAAA’ AND EMP_NO=’SCOTT’;

  6. /*+ CLUSTER(TABLE)*/：提示明确表明对指定表选择簇扫描的访问方法，它只对簇对象有效。

    1
    SELECT /*+ CLUSTER*/ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO=’TEC304′ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  7. /*+ INDEX(TABLE INDEX_NAME)*/：表明对表选择索引的扫描方法。

    1
    SELECT /*+ INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=’M';

  8. /*+ INDEX_ASC(TABLE INDEX_NAME)*/：表明对表选择索引升序的扫描方法。

    1
    SELECT /*+ INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=’SCOTT’;

  9. /*+ INDEX_COMBINE*/：为指定表选择位图访问路经，如果INDEX_COMBINE中没有提供作为参数的索引，将选择出位图索引的布尔组合方式。

    1
    SELECT /*+ INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS WHERE SAL<5000000 AND HIREDATE

  10. /*+ INDEX_JOIN(TABLE INDEX_NAME)*/：提示明确命令优化器使用索引作为访问路径。

    1
    SELECT /*+ INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE FROM BSEMPMS WHERE SAL<60000;

  11. /*+ INDEX_DESC(TABLE INDEX_NAME)*/：表明对表选择索引降序的扫描方法。

    1
    SELECT /*+ INDEX_DESC(BSEMPMS PK_BSEMPMS)*/ FROM BSEMPMS WHERE DPT_NO='SCOTT';

  12. /*+ INDEX_FFS(TABLE INDEX_NAME)*/：对指定的表执行快速全索引扫描,而不是全表扫描的办法。

    1
    SELECT /*+ INDEX_FFS(BSEMPMS IN_EMPNAM)*/  FROM BSEMPMS WHERE DPT_NO='TEC305';

  13. /*+ ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/：提示明确进行执行规划的选择，将几个单列索引的扫描合起来。

    1
    SELECT /*+ ADD_EQUAL(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';

  14. /*+ USE_CONCAT*/：对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询。

    1
    SELECT /*+ USE_CONCAT*/ FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

  15. /*+ NO_EXPAND*/：对于WHERE后面的OR 或者IN-LIST的查询语句，NO_EXPAND将阻止其基于优化器对其进行扩展。

    1
    SELECT /*+ NO_EXPAND*/ FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

  16. /*+ NOWRITE*/：禁止对查询块的查询重写操作。

  17. /*+ REWRITE*/：可以将视图作为参数。

  18. /*+ MERGE(TABLE)*/：能够对视图的各个查询进行相应的合并。

    1
    2
    3
    4
    5
    6
    SELECT /*+ MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO 
    FROM BSEMPMS A, (SELET DPT_NO,AVG(SAL) AS AVG_SAL 
                     FROM BSEMPMS B 
                     GROUP BY DPT_NO) V 
    WHERE A.DPT_NO=V.DPT_NO 
      AND A.SAL>V.AVG_SAL;

  19. /*+ NO_MERGE(TABLE)*/：对于有可合并的视图不再合并。

    1
    2
    3
    4
    5
    6
    SELECT /*+ NO_MERGE(V)*/ A.EMP_NO,A.EMP_NAM,B.DPT_NO 
    FROM BSEMPMS A, (SELECT DPT_NO,AVG(SAL) AS AVG_SAL 
                     FROM BSEMPMS B 
                     GROUP BY DPT_NO) V 
    WHERE A.DPT_NO=V.DPT_NO 
      AND A.SAL>V.AVG_SAL;

  20. /*+ ORDERED*/：根据表出现在FROM中的顺序，ORDERED使ORACLE依此顺序对其连接。

    1
    2
    3
    SELECT /*+ ORDERED*/ A.COL1,B.COL2,C.COL3 
    FROM TABLE1 A,TABLE2 B,TABLE3 C 
    WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

  21. /*+ USE_NL(TABLE)*/：将指定表与嵌套的连接的行源进行连接，并把指定表作为内部表。

    1
    2
    3
    SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM 
    FROM BSEMPMS,BSDPTMS 
    WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  22. /*+ USE_MERGE(TABLE)*/：将指定的表与其他行源通过合并排序连接方式连接起来。

    1
    2
    3
    SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ 
    FROM BSEMPMS,BSDPTMS 
    WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  23. /*+ USE_HASH(TABLE)*/：将指定的表与其他行源通过哈希连接方式连接起来。

    1
    2
    3
    SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ 
    FROM BSEMPMS,BSDPTMS 
    WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  24. /*+ DRIVING_SITE(TABLE)*/：强制与ORACLE所选择的位置不同的表进行查询执行。

    1
    2
    3
    SELECT /*+DRIVING_SITE(DEPT)*/  
    FROM BSEMPMS,DEPT@BSDPTMS 
    WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

  25. /*+ LEADING(TABLE)*/：将指定的表作为连接次序中的首表。

  26. /*+ CACHE(TABLE)*/：当进行全表扫描时，CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端。

    1
    SELECT /*+ FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

  27. /*+ NOCACHE(TABLE)*/：当进行全表扫描时，CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端。

    1
    SELECT /*+ FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

  28. /*+ APPEND*/：直接插入到表的最后，可以提高速度。

    1
    insert /*+ append*/ into test1 select * from test4;

  29. /*+ NOAPPEND*/：通过在插入语句生存期内停止并行模式来启动常规插入。

    1
    insert /*+ noappend*/ into test1 select * from test4;
Optimization Approaches Access Methods
ALL_ROWS AND_EQUAL
CHOOSE CLUSTER
FIRST RULES FULL
RULE HASH
Parallel Execution HASH_AJ
APPEND*ORDERED HASH_SJ ***
STAR** INDEX
STAR_TRANSFORMATION* INDEX_ASC
Join Operations INDEX_COMBINE*
DRIVING_SITE* INDEX_DESC
USE_HASH** INDEX_FFS*
USE_MERGE MERGE_AJ**
USE_NL MERGE_SJ***
Additional Hints ROW_ID
CACHE USE_CONCAT
NOCACHE NO_EXPAND***
PUSH_SUBQ REWRITE***
MERGE*** NOREWRITE***
NO_MERGE* Join Orders
PUSH_JOIN_PRED***
NO_PUSH_JOIN_PRED*** NOAPPEND*
ORDERED PREDICATES*** NOPARALLEL
PARALLEL
PARALLEL_INDEX*
NO_PARALLEL_INDEX***

3.2 使用注意

使用提示需要遵循的原则：

  1. 仔细检查提示语法。尽量使用完整注释语法 /*+ hint */ 。
  2. 使用表别名。如果在查询中指定了表别名，那么提示必须也使用表别名。例如：select /*+ index(e,dept_idx) */ from emp e; 。
  3. 不要在提示中使用模式名称：如果在提示中指定了模式的所有者，那么提示将被忽略。例如：select /*+ index(scott.emp,dept_idx) */ from emp; 。
  4. 检验提示。如果提示指定了不可用的访问路径，那么这个提示将被忽略。

导致提示无效的条件：

提示 被忽略的条件
cluster 与非簇表一同使用
hash 与非簇表一同使用
hash_aj 不存在子查询
index 指定的索引不存在
index_combine 不存在位图索引
merge_aj 不存在子查询
parallel 调用的不是TABLE ACCESS FULL计划
push_subq 不存在子查询
star 事实表中存在不恰当的索引
use_concat 在where子句中不存在多个or条件
use_nl 表中不存在索引

几种主要的优化模式：

  1. all_rows：all_rows是基于成本的优化方法，目的是提供整体最佳的吞吐量和最小的资源消耗。all_rows提示倾向使用全表扫描，而且不适用于OLTP数据库。使用all_rows提示应该保障查询中涉及的表和索引拥有使用analyze命令分析得到的统计资料。
  2. rule：rule提示使Oracle为查询提供基于规则的优化模式。在怀疑CBO生成了非优化的执行计划时，通常首先尝试使用rule提示。Rule提示忽略表和索引的统计资料，并且使用基本的试探法生成执行计划。
  3. first_rows：这个提示是基于成本的优化方法，目的是提供最快的反应时间。使用first_rows提示应该保障查询中涉及的表和索引拥有使用analyze命令分析得到的统计资料。
  4. 表的连接提示
    • use_hash提示：use_hash 提示对指定的表进行散列连接。散列连接是Oracle用以驱动表（最小的表）向RAM区中装载记录的方法，RAM区由HASH_AREA_SIZE定义。散列连接适合中间结果比较大的情况。使用散列连接时,HASH_AREA_SIZE对速度影响非常大，如果驱动表不能一次装入内存，那么需要使用TEMP表空间，这种情况下速度比较慢。这个参数可以在session级别动态修改，需要进行散列连接时可以临时增大，速度可能显着增加。
    • use_merge 提示：use_merge 提示强制执行一个排序合并操作。排序合并操作通常与并行查询结合使用，因为排序合并操作倾向于全表扫描。该提示适合于生成大型结果集的查询。
    • use_nl：use_nl提示将强制对目标表执行嵌套循环连接。use_nl提示很少用于SQL调整，因为CBO和RBO更倾向于使用循环嵌套连接。
    • star提示：star 提示强制使用星型查询计划。前提是查询中至少三个表，而且在事实表中存在恰当的索引
  5. 表反连接提示：SQL反连接是指在语句中包含NOT IN 或者NOT EXISTS子句时执行的操作。
    • merge_aj：在使用全表访问比索引访问更好的情况下，可以在NOT IN子查询中使用merge_aj提示以便执行反连接。
    • hash_aj：hash_aj 提示放在NOT IN 子查询中用来希望执行散列连接时，执行散列反连接。hash_aj和merge_aj要求子查询列非空。
  6. INDEX提示：
    • INDEX提示简介：INDEX提示被用于显示指定表名或表名与索引。如果只指定了表名，那么优化器将使用表中的”最优”索引。在永久优化SQL语句中，建议指定表和索引。
    • index_join 提示：index_join 提示明确要求优化器使用索引连接来作为访问路径。
    • and_equal 提示：and_equal 提示可以使多个非唯一的索引合并索引，并且使这些索引操作时就象单个连续索引一样。该提示如果被应用，在查询计划中显示的是AND-EQUAL
    • index_asc 提示：index_asc 提示使用升序索引。这是默认的优化器行为
    • no_index 提示：该提示忽略索引存在，类似full
    • index_combine提示：index_combine 提示用来强制使用位图索引作为表的访问路径。
    • index_ffs提示：索引快速完全扫描可以在不访问任何记录的情况下完成查询。
    • use_concat提示：use_concat提示要求为所有的OR条件使用UNION ALL执行计划，并将这个查询重新书写为多个查询。如果在WHERE子句中存在大量OR条件，可以考虑使用use_concat提示。

总结：

  1. 因为提示放在注释中，所以如果提示通现存的执行计划不兼容，或者提示不正确，有可能被忽略。
  2. 在使用RBO时，可以通过提示将指定的查询更改为CBO。切记要对查询中涉及的所有表和索引进行分析
  3. 在使用CBO的时候，可以通过添加RULE提示或者FIRST_ROWS提示来开始调整一个可以的SQL语句
  4. 提示可以在子查询中使用，但是外部查询的提示不会带入子查询。
  5. 如果在查询计划中发现卡笛尔积（CARTESIAN），则要尽量解决。

四. 深入剖析

4.1 表连接hint

Oracle提供了各种类型的表连接：

  • 嵌套循环连接（nested loops join）：可以看作两层嵌套的for循环

    • 访问次数：驱动表返回几条，被驱动表访问多少次。
    • 驱动表是否有顺序：有。
    • 是否要排序：否。
    • 应用场景：
      1. 关联中有一个表比较小；
      2. 被关联表的关联字段上有索引；
      3. 索引的键值不应该重复率很高

  • 排序合并连接（sort merge join）

    • 访问次数：两张表都只会访问0次或1次。

    • 驱动表是否有顺序：无。

    • 是否要排序：是。

    • 应用场景：当结果集已经排过序。

    • 如果A表的数据为（2,1,4,5,2）,B表的数据为(2,2,1,3,1) ,首先将A表和B表全扫描后排序，如下：

                A    B
          
          1     1
          
          2     1
          
          2     2
          
          4     2
          
          5     3

      因为没有驱动表，所以oracle会随机选择一张表驱动，如果选择了A扫描到1，然后扫描B，当扫描=1的时候则管理，当扫描到B=2时，再以B=2为驱动扫描A表，不是从1开始扫，而是从2开始扫描，交替的进行扫描、关联。

  • 哈希连接（hash join）：先把驱动表的关联字段hash到PGA中（rowid也在PGA中），然后扫描被驱动表，取第一条数据，将关联的字段hash一下探测PGA中的小表，如果匹配则关联，再取第二条…。

    • 访问次数：驱动表和被驱动表都只会访问0次或1次。
    • 驱动表是否有顺序：有。
    • 是否要排序：否。
    • 应用场景：
      1. 一个大表，一个小表的关联；
      2. 表上没有索引；
      3. 返回结果集比较大。

  • 星形连接（star join）等。

因为表连接是所有Oracle SQL执行步骤中最耗时的，Oracle对表连接的hint经常被用来测试各种连接技术的执行速度。

（1）use_hash

use_hash提示要求对指定的表进行散列连接。从本质上讲，散列连接是一种Oracle将驱动表（最小的表，在where子句之后的第一个表）中的行加载到由hash_area_size初始化参数定义的RAM区域的技术。

Oracle使用散列技术来定位较大的第二个表中的记录。在两个表都非常大的情况下，散列连接经常与并行查询相结合。

下面的查询是一个被提示强制使用散列连接和并行查询的例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select /*+ use_hash(e,b) parallel(e, 4) parallel(b, 4) */
   e.ename, hiredate, b.comm
from emp e, bonus b
where e.ename = b.ename;
# 下面是哈希连接的执行计划。注意这个连接中的两个表都使用并行查询来获得它们的记录。
OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT                                                    3
  HASH JOIN                                                          1
PARALLEL_TO_SERIAL
    TABLE ACCESS FULL           EMP                                  1
PARALLEL_TO_PARALLEL
    TABLE ACCESS FULL           BONUS                                2

哈希连接通常比嵌套的循环连接更快，特别是在查询的where子句中，驱动表被过滤成少数行的情况下。

use_hash提示是非常微妙的，有许多条件必须得到满足。发现use_hash提示被忽略的情况并不少见，下面是造成这个问题的一些常见原因：

  • 检查初始化参数：确保你对 optimizer_index_cost_adj 、hash_multiblock_io_count 、optimizer_max_permutations 和 hash_area_size 有进行适当的设置。
  • 验证驱动表：确保较小的表是驱动表（从句中的第一个表）。这是因为哈希连接使用驱动表建立内存阵列。
  • 分析CBO统计数据：检查表的和/或连接的关联表的列是否得到了适当的分析。
  • 检查偏斜的列（skewed columns ）：Histograms只建议用于非均匀的列分布。如果有必要，你可以使用 ordered 提示覆盖基于成本的优化器选择的连接顺序。
  • 检查RAM区域：确保 hash_area_size 足够大，以便在内存中容纳较小的表。否则，Oracle必须写到TEMP表空间，减慢散列连接的速度。

（2）use_merge

use_merge提示强制进行分类合并操作。排序合并操作经常与并行查询一起使用，因为排序合并连接总是对表进行全表扫描。排序合并连接通常最适合于产生非常大的结果集的查询，例如每日报告或表的细节汇总查询，或者在连接键上不具有索引的表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 通过对两个表的并行查询来执行分类合并的
select /*+ use_merge(e,b) parallel(e, 4) parallel(b, 4) */
   e.ename, hiredate, b.comm
from emp e, bonus b
where e.ename = b.ename;
# 注意全表扫描和排序合并操作。
OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT                                                    5
  MERGE JOIN                                                         1
PARALLEL_TO_SERIAL
    SORT JOIN                                                        1
PARALLEL_COMBINED_WITH_PARENT
      TABLE ACCESS FULL         EMP                                  1
PARALLEL_TO_PARALLEL
    SORT JOIN                                                        2
PARALLEL_COMBINED_WITH_PARENT
      TABLE ACCESS FULL         BONUS                                1
PARALLEL_TO_PARALLEL

值得注意的是，排序合并连接并不使用索引来连接表。在大多数情况下，索引访问更快，但是排序合并连接可能适合于没有where子句的大型表连接，或者没有可用索引连接表的查询。

4.2 表反连接hint（Anti-Join）

SQL的反连接是一种操作，一般在SQL语句中指定 NOT IN 或 NOT EXISTS 子句时使用。

1
2
3
4
5
6
7
8
9
10
11
12
13
select
   customer_name
from
   customer
where
   customer_number NOT IN
   (
    select
       customer_number
    from
       bad_credit_history
   )
;

一般不建议使用NOT IN（调用子查询），而是使用NOT EXISTS（调用相关的子查询），因为如果子查询返回的任何记录包含空值，则查询不返回任何记录。

（1）merge_aj

merge_aj用于NOT IN子查询中，来执行反连接，表示全表访问优先于索引访问。

一个NOT IN案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 查询所有没有销售员的部门的名称。
select dname
from dept
where deptno NOT IN
   (select deptno
    from emp
    where job = 'SALESMAN');
# 当子查询中的数据列允许为空值时，这种类型的查询的性能会非常差。子查询对于外部查询块中的每一条记录都要重新执行一次! 下面是执行计划：
OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT                                                    1
  FILTER                                                             1
    TABLE ACCESS FULL             DEPT                               1
    TABLE ACCESS BY INDEX ROWID   EMP                                2
      INDEX RANGE SCAN            JOB_IDX                            1

NOT IN子查询有替代方法，它不会为外层查询块中的每条记录重新生成一次子查询；当外层查询块产生大量的记录时，应该考虑这种方法。

这个方法只能在子查询列上存在NOT NULL谓词并且你在子查询块中有一个hint时使用。根据所需的连接类型，反连接可以通过hash_aj或merge_aj的hint来执行。

注意：反连接提示merge_aj和hash_aj只有在not in子句中要求的列有NOT NULL约束时才会起作用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 优化后：
select dname
from dept
where deptno NOT IN
   (select /*+ merge_aj */ deptno
    from emp
    where job = 'SALESMAN');
# 查询的执行计划发生了变化，合并反连接被调用，代替了过滤操作。
OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT                                                    5
  MERGE JOIN ANTI                                                    1
    SORT JOIN                                                        
      TABLE ACCESS FULL                  DEPT                        1
    SORT UNIQUE                                                      2
      VIEW                               VW_NSO_1                    1
        TABLE ACCESS BY INDEX ROWID      EMP                         1
          INDEX RANGE SCAN               JOB_IDX                     1

（2）hash_aj

hash_aj提示被放置在一个not in子查询中，以便在需要散列连接的情况下执行散列反连接。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select dname
from dept
where deptno NOT IN
   (select /*+ hash_aj */ deptno
    from emp
    where job = 'SALESMAN');
# 执行计划指定了一个散列连接，并对部门表进行全表扫描：
OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT                                                    3
  HASH JOIN ANTI                                                     1
    TABLE ACCESS FULL               DEPT                             1
    VIEW                            VW_NSO_1                         2
      TABLE ACCESS BY INDEX ROWID   EMP                              1
        INDEX RANGE SCAN            JOB_IDX                          1

总之，merge_aj和hash_aj提示可以极大地提高NOT IN子查询的性能，前提是子查询的列是NOT NULL。

五. 案例

5.1 NOT EXISTS优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
select e.col_a, e.col_b, e.col_c, e.col_d, e.col_e, e.col_f, e.col_g,e.col_h
from Table_A e 
where e.col_a = :col_a
  and e.col_b <= :trade_date
  and e.col_b > (:trade_date - 31)
  and e.col_c in (select a.col_c
				      from Table_A a
                      where a.col_a = :col_a 
					    and a.col_b <= :trade_date
                        and a.col_b > (:trade_date - 31)
                        and a.col_d in ('1', '2', '3', '4')
					    and not exists (
                            select 1 from Table_B b 
                            where b.col_a = :col_a 
                              and a.col_c = b.col_c 
                              and a.col_b = b.col_b 
                              and b.trade_type in (6,7))

# 修改为：
                  
select /*+LEADING(D) NO_MERGE(D)*/
	   e.col_a, e.col_b, e.col_c,e.col_d, e.col_e, e.col_f, e.col_g, e.col_h
from Table_A e, (select DISTINCT a.col_c
				 from Table_A a
				 where a.col_a = :col_a
				   and a.col_b <= :trade_date
				   and a.col_b > (:trade_date - 31)
                   and a.col_d in ('1', '2', '3', '4')
                   and not exists (select /*+ HASH_AJ*/1
					               from Table_B b
                                   where b.col_a = :col_a
                                     and a.col_c = b.col_c
                                     and a.col_b = b.col_b
                                     and b.trade_type in (6,7))) D
where e.col_a = :col_a
  and e.col_b <= :trade_date
  and e.col_b > (:trade_date - 31)
  AND e.col_c = D.col_c;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
select c.col_a,c.col_b,c.col_c,c.col_e,c.col_g,c.col_f,c.col_i,
				c.col_d,c.col_j,c.col_k,c.col_l
from Table_A c
where c.col_a = :col_a 
  and c.col_b >= (:trade_date - 31) 
  and c.col_b <= :trade_date 
  and c.col_c in (select col_c 
                  from Table_A a
				  where a.col_a = :col_a 
                    and a.col_b >= (:trade_date - 31)
                    and a.col_b <= :trade_date
                    and a.col_j = 38
                    and a.col_d = '64'
					and a.col_h in ('15','16')
					and not exists (
							select 1 from Table_B b 
                            where b.col_a = :col_a 
                              and a.col_c = b.col_c 
                              and a.col_b = b.col_b 
                              and b.trade_type in (6,7)
                    )
                 )


# 修改为：

SELECT /*+LEADING(D) NO_MERGE(D)*/ 
       C.col_a,
       C.col_b,
       C.col_c,
       C.col_e,
       C.col_g,
       C.col_f,
       C.col_i,
       C.col_d,
       C.col_j,
       C.col_k,
       C.col_l
FROM Table_A C,(SELECT DISTINCT col_c
                FROM Table_A A
                WHERE A.col_a = :col_a
                  AND A.col_b >= (:trade_date - 31)
                AND A.col_b <= :trade_date
                AND A.col_j = 38
                AND A.col_d = '64'
                AND A.col_h IN ('15', '16')
                AND NOT EXISTS (
                    SELECT /*+ HASH_AJ*/1
                    FROM Table_B B
                    WHERE B.col_a = :col_a
                      AND A.col_c = B.col_c
                      AND A.col_b = B.col_b
                      AND B.TRADE_TYPE IN (6,7))) D
WHERE C.col_a = :col_a
  AND C.col_b >= (:trade_date - 31)
  AND C.col_b <= :trade_date
  AND C.col_c = D.col_c
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
select e.col_a, e.col_b, e.col_c, e.col_m, e.col_e, e.col_g, e.col_f, e.col_i,e.col_n, e.col_o, e.col_d, e.col_p, e.col_q, e.col_r, e.col_s, e.col_t, e.col_u, e.col_j, e.col_v, e.col_w, e.col_k, e.col_x
from Table_A e
where e.col_a = :col_a
  and e.col_b <= :trade_date
  and e.col_b > (:trade_date - 31)
  and e.col_j in (1,2,4,5,6,7,10,11,18,37)
  and not exists (
      select 1 
      from Table_B b 
      where b.col_a = :col_a 
        and e.col_c = b.col_c 
        and e.col_b = b.col_b
        and b.trade_type in (6,7))
order by e.col_a, e.col_b, e.col_c, e.col_m, e.col_y

# 修改为： 

select e.col_a, e.col_b, e.col_c, e.col_m, e.col_e,e.col_g, e.col_f, e.col_i,e.col_n, e.col_o, e.col_d, e.col_p,e.col_q, e.col_r, e.col_s, e.col_t,e.col_u, e.col_j, e.col_v, e.col_w, e.col_k, e.col_x
from Table_A e
where e.col_a = :col_a
  and e.col_b <= :trade_date
  and e.col_b > (:trade_date - 31)
  and e.col_j in (1,2,4,5,6,7,10,11,18,37)
  and not exists ( 
      select /*+ HASH_AJ*/ 1
      from Table_B b
      where b.col_a = :col_a
        and e.col_c = b.col_c
        and e.col_b = b.col_b
        and b.trade_type in (6,7))
order by e.col_a, e.col_b, e.col_c, e.col_m, e.col_y

参考：

🔗 Using Optimizer Hints

🔗 Hints for Oracle sql performance

🔗 ORACLE常用SQL优化hint语句

🔗 oracle中hint 详解

🔗 Table Join Hints

🔗 Table Anti-Join Hints

🔗 嵌套循环连接(nested loops join)原理

相关文章