【Oracle篇】精细化查询优化：如何有效使用Hint对优化器的执行计划进行干预（第五篇，总共七篇）

时间：2024-11-04 07:13:15

????《博主介绍》：✨又是一天没白过，我是奈斯，DBA一名✨

????《擅长领域》：✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux，也在扩展大数据方向的知识面✌️

????????????大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注????????????

    博主最近刷B站总能看到一些关于宇宙????的短视频，每次都让我觉得宇宙好大，地球相比之下真的好渺小。这种对比让我开始想，人生的意义到底是什么呢？是追求轰轰烈烈，还是平淡如水？虽然人生的选择权在我们自己手里，但也得承认，有时候天时、地利、人和这些外部条件也会对我们有所影响。不管环境怎么样，还是得尽力过好每一天，让每天都过得有意义，才算的上是不负韶华????。

    今天这篇作为执行计划的第五篇，和各位集帅一起讨论下使用Hint对优化器的执行计划进行干预。首先需要明白为什么需要人工干预SQL语句的执行计划，不是Oracle的优化器会根据统计信息对每个SQL语句执行最优的执行计划的吗？干哈还需要人工来指定执行计划呢？那么我先给出答案： 在大多数情况优化器是会给出最优的执行计划，但并不是所有的情况 ，比如我现在有一个这样的场景，我需要进行全表查询，但需要尽快返回查询结果的前100行数据，然后剩下的数据慢慢返回，这种情况在网页开发中非常常见，在网页开发中显示每页显示多少行数据优先显示，然后点击下一页继续显示数据，默认情况下Oracle会进行全表扫描，但如果通过/*+ FIRST_ROWS(100)*/进行hint干预的话，Oracle会尽快返回查询结果的前100行数据，那么就显著提高查询效率。这只是众多需要hint干预的一种情况，还有很多其他情况，那么带着这些问题，开始今天的内容吧。还是老规矩为了让大家更容易消化和逐个理解，我将分成七篇文章来进行介绍，以便大家劳逸结合而不至于感觉到阅读枯燥，七篇的内容分别如下：

  • 第一篇：统计信息和动态采样的深度剖析
  • 第二篇：全面理解优化器和SQL语句的解析步骤(含执行计划的详细分析和四种查看方式)
  • 第三篇：SQL执行计划之访问路径(含表级别、B树索引、位图索引、簇表四大类访问路径)
  • 第四篇：SQL执行计划之多表连接(含内连接、外连接、半连接、反连接、笛卡尔连接五种连接方式和嵌套、哈希、排序合并三种连接算法)
  • 第五篇：精细化查询优化：如何有效使用Hint对优化器的执行计划进行干预（当前篇）
  • 第六篇：掌握SQL Tuning Advisor优化工具：从工具使用到SQL优化的全方位指南
  • 第七篇：SQL性能优化实战案例(从15秒优化到0.08秒)

                                  

目录

一、使用Hint对优化器的执行计划进行干预

Hint的优点：

Hint的缺点：

Hint的范围：

Hint写法：

联接顺序Hint准则：

1、Hint干预的类型

1.1 单表：单表Hint是在一个表或视图上指定

1.1.1 FULL Hint

1.1.2 INDEX Hint

1.1.3 INDEX_ASC Hint

1.1.4 INDEX_DESC Hint

1.1.5 INDEX_FFS Hint

1.1.6 INDEX_JOIN Hint

1.1.7 INDEX_SS Hint

1.1.8 INDEX_SS_ASC Hint

1.1.9 INDEX_SS_DESC Hint

1.2 多表：多表Hint类似于单表Hint，只是该Hint可以指定多个表或视图

1.2.1 LEADING Hint

1.2.2 MERGE Hint

1.2.3 USE_BAND Hint

1.2.4 USE_CONCAT Hint

1.2.5 USE_CUBE Hint

1.2.6 USE_HASH Hint

1.2.7 USE_MERGE Hint

1.2.8 USE_NL Hint

1.2.9 USE_NL_WITH_INDEX Hint

1.3 查询块：查询块Hint对单个查询块进行操作

1.3.1 STAR_TRANSFORMATION Hint  

1.3.2 UNNEST Hint

1.4 声明：声明Hint适用于整个SQL语句

1.4.1 ALL_ROWS Hint

1.4.2 FIRST_ROWS Hint

          

干预优化器执行计划的几种技术方式：

    Oracle提供了多种干预执行计划的技术，包括DBMS_STATS、SQL profiles、SQL Plan Management、参数（优化器相关），和最后的hint方式。下图描述了干预优化器执行计划的五种方式：

技术 描述
参数（优化器相关） 参数在数据库实例和会话级别影响许多类型的优化器行为。
Hints Hint是SQL语句中的特殊注释，它将指令传递给优化器。Hint最重要的作用就是告诉优化器，按照人工定义的计划，来执行SQL语句的执行计划。
DBMS_STATS

官方文档中将DBMS_STATS包定义为了干预优化器执行计划的技术。但是严格意义上来说DBMS_STATS只是用来收集统计信息的包，统计信息越精准，优化器选择的执行计划就越优，但并不能通过DBMS_STATS包指定想要的执行计划，因为DBMS_STATS收集完成统计信息之后，优化器会根据统计信息对每个SQL语句执行最优的执行计划，这个过程是Oracle优化器完成的，人工并没有参与。    

        

关于DBMS_STATS包的理论和使用可以参考博主之前写的文章哦，文章直通车????【Oracle篇】统计信息和动态采样的深度剖析（第一篇，总共七篇）_oracle 动态采样-****博客????
SQL profiles

Oracle绑定执行计划有两种方式：SQL Profile和SQL Plan Management (SPM)。其中对于Oracle 10g 及之前的版本，一般采用SQL Profile来绑定执行计划，而对于Oracle 11g以后的版本，一般采用SPM来实现自动化的执行计划管理。
SQL plan management

    干预优化器执行计划的五种技术方式今天只介绍Hints方式。 关于参数（优化器相关）这种方式；关于DBMS_STATS这种方式参考我之前的文章即可；关于SQL profiles和SQL plan management这两种方式因为文章篇幅的原因这篇文章就不做介绍了哦，并且执行计划这个系列也没有介绍这两种方式，因为这两种方式涉及到的内容非常多，需要专门开设一个系列去讲解 。博主会尽快完善这个系列啦????

                    

需要干预优化器执行计划的几种情况：

    在Oracle数据库中，基于代价的优化器（Cost-Based Optimizer，CBO）通常能够自动选择最优的执行计划来执行SQL语句。然而，在某些特定情况下，优化器可能无法选择出最优的执行计划，这时就需要干预优化器的执行计划。以下是需要进行干预的几种典型情况：

  1. 优化器选择不当的执行计划：有时优化器可能因为统计信息不准确、物理结构不合理（如缺少合适的索引）或数据分布的变化等原因，选择了不理想的执行计划，导致SQL语句执行效率低下。此时，DBA可以通过hint来指定存取路径或连接类型，引导优化器生成更优的执行计划。
  2. 特定场景下的优化需求：在某些特定场景下，如需要快速返回查询结果的前几行（而不是全部结果），或者当全表扫描比索引扫描更有效时，DBA可以通过hint来指定优化器的优化目标或访问路径，以满足特定的业务需求。
  3. 数据结构和数据规模发生重大变化：当数据结构或数据规模发生重大变化时，原有的执行计划可能不再适用。此时，DBA可以通过hint来指示优化器使用新的存取路径或连接类型，以适应变化后的数据环境。
  4. 避免复杂的提示和保持执行计划稳定性：在某些情况下，仅通过转换优化器的模式就可以获得非常好的执行计划，此时无需额外使用复杂的hint。然而，在某些复杂查询中，为了确保优化器产生最优的执行计划，可能需要指定全套的hint，包括存取路径、连接类型、连接顺序等。同时，为了避免执行计划的不稳定，DBA也需要在必要时使用hint来固定执行计划。
  5. 应对特殊表或索引的访问需求：对于某些特殊的表或索引，如聚簇索引或分区表，DBA可能需要通过hint来指示优化器按照特定的方式访问数据，以提高查询性能。

    需要注意的是，虽然干预优化器是一种强大的优化手段，但过多的干预可能会导致执行计划不稳定，甚至恶化查询性能。因此，在干预之前，应该先通过分析查询语句的执行计划和性能指标，确定需要优化的地方，并选择适当的执行计划来提高查询性能。同时，也需要谨慎考虑数据库版本和配置对执行计划的影响，并在使用hint后进行适当的测试和验证。

                          

一、使用Hint对优化器的执行计划进行干预

    使用Hint来影响优化器模式（optimizer mode）、查询转换（query transformation）、访问路径（access path）、连接顺序（join order）和连接方法（join methods）。   

    当发现一条SQL的执行效率很低（也就是执行耗时很长），那么首先就是查看当前SOL的执行计划，如果分析之后发现当前执行计划不是最优的，那么就需要考虑为什么CBO选择了错误的执行计划。

    通过hint的方式来改变SOL的执行计划，比较这两条SQL的效率，作出哪种执行计划更优。当CBO选择错误的执行计划，我们需要考虑表的分析是否是最新的，是否对相关的列做了直方图，是否对分区表做了全局或者分区分析等因素。

总结：hint最重要的作用就是告诉优化器，按照人工定义的计划，来执行SQL语句的执行计划。

 

Hint的优点：

    在测试环境中，Hint对于测试特定访问路径的性能非常有用。例如：小伙伴们可能知道走索引对某些查询更具选择性，在这种情况下，使用Hint会让优化器生成更好的执行计划，如下图所示：

         

Hint的缺点：

    Oracle 7中引入了Hint，由于Hint是人工定义的计划，所以这部分是额外的部分，也就是每次执行SQL时都要写上就比较麻烦，Oracle官网给的好办法就是使用Hint进行测试，如果使用Hint的执行计划提高了SQL效率，然后通过使用其他技术来管理执行计划。Oracle提供了几个SQL优化工具用于替代Hint，并且Oracle官网强烈建议使用这些工具而不是Hint，包括SQL调优顾问（SQL Tuning Advisor）、SQL计划管理（SQL plan management）和SQL性能分析器（SQL Performance Analyzer），以解决优化器无法解决的性能问题。

        

Hint的范围：

    在语句块中指定Hint时，该Hint将应用于相应的查询块、表或语句块中的整个语句。Hint会覆盖任何实例级或会话级参数。

    Hint支持的语句有：

  • MERGE、SELECT、INSERT、UPDATE、DELETE语句中
  • 父语句或者子查询中
  • 集合查询中（UNION、MINUS、INTERSECT）

                  

Hint写法：

    Hint注释必须紧跟在SQL语句块的第一个关键字之后。可以使用两种注释样式：斜线星号（/*）或一对破折号（--）。加号（+）提示分隔符必须紧跟在注释分隔符之后，如以下片段所示：

SELECT /*+ hint_text */ ...

    一个语句中只能有一个包含Hint的注释，但它可以包含许多空格分隔的Hint。例如，一个复杂的查询可能包括多个表连接。如果只为指定的表指定INDEX提示，则优化器必须确定剩余的访问路径和相应的连接方法。优化器可能不会使用INDEX提示，因为连接方法和访问路径会阻止它。下图使用多个Hint来指定确切的连接顺序。

SELECT   /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk) 
           USE_MERGE(j) FULL(j) */
         e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM     employees e1, employees e2, job_history j
WHERE    e1.employee_id = e2.manager_id
AND      e1.employee_id = j.employee_id
AND      e1.hire_date = j.start_date
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;

小提示：如果Hint的写法有问题，Oracle也不会提示错误哦（这点真鸡肋。。。），Oracle会直接忽略掉写法有问题的Hint。

            

        

联接顺序Hint准则：

    在某些情况下，可以在SQL语句中指定联接顺序提示，这样它就不会访问对结果没有影响的行。

    联接中的驱动表是与其他表联接的表。一般来说，驱动表包含过滤条件，该条件消除了表中最高百分比的行。连接顺序对SQL语句的性能有很大的影响。

    考虑以下准则：

  1. 当索引更有效地检索请求的行时，避免全表扫描。
  2. 当可以使用获取少量行的不同索引时，请避免使用从驱动表中获取许多行的索引。
  3. 选择联接顺序，以便在联接顺序的后面将较少的行联接到表中。

    以下示例显示了如何有效地调整连接顺序：

SELECT *
FROM   taba a, 
       tabb b, 
       tabc c
WHERE  a.acol BETWEEN   100 AND   200
AND    b.bcol BETWEEN 10000 AND 20000
AND    c.ccol BETWEEN 10000 AND 20000
AND    a.key1 = b.key1
AND    a.key2 = c.key2;
  1. 选择驱动表和驱动索引(如果有)：上例中的前三个条件都是应用于单个表的筛选条件。最后两个条件是连接条件。筛选条件决定了驱动表和索引的选择。一般来说，驱动表包含过滤条件，可消除最高百分比的行。因为100到200的范围相对于acol的范围来说比较窄，但是10000和20000的范围比较大，taba就是驾驶台，其他都一样。对于嵌套循环连接，连接通过连接索引进行，连接索引是主键或外键上的索引，用于将该表连接到连接树中较早的表。除了驱动表，很少在非连接条件下使用索引。因此，在选择taba作为驱动表之后，使用b.key1和c.key2上的索引分别驱动到tabb和tabc中。
  2. 选择最佳连接顺序，最早使用最佳未用过滤器：通过首先连接到具有最好的仍未使用的过滤器的表，可以减少后续连接的工作量。因此，如果bcol BETWEEN比ccol BETWEEN更严格(拒绝更高百分比的行),那么如果在tabc之前联接tabb，最后的联接会变得更容易(具有更少的行)。
  3. 可以使用ORDERED或STAR提示来强制联接顺序。

     

1、Hint干预的类型

    Hint可以对四种类型进行干预，分别可以对单表（Single-table）、多表（Multitable）、查询块（query blocks）、声明（statements）使用Hint。并且Oracle提供了快100个Hint干预，这篇文章也不可能介绍所有，只是把我认为重要的Hint给介绍，其他需要学习的可以参考官网文档哦，敬上Hint官网链接：Comments

                    

1.1 单表：单表Hint是在一个表或视图上指定

1.1.1 FULL Hint

使用语法：

/*+ FULL ( [ @ queryblock ] tablespec ) */

          

介绍：

FULL Hint提示优化器对指定表执行全表扫描。例如：

SELECT /*+ FULL(e) */ employee_id, last_name
  FROM hr.employees e 
  WHERE last_name LIKE :b1;

    数据库对employees表执行全表扫描以执行此语句，即使WHERE子句中的条件使last_name列上有索引。

    employees表在FROM子句中有别名e，因此Hint必须通过别名而不是引用该表。即使在FROM子句中指定了表名，但也不要在Hint中指定它们。

        

1.1.2 INDEX Hint

使用语法：

/*+ INDEX ( [ @ queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */

   

介绍：    

    INDEX Hint指示优化器对指定表使用索引扫描。您可以将INDEX提示用于基于函数的索引、域索引、B树索引、位图索引和位图连接索引。

  • 如果index Hint指定了一个可用的索引，那么数据库将对该索引执行扫描。优化器不会考虑全表扫描或表上另一个索引的扫描。
  • 对于多个索引组合的提示，Oracle建议使用INDEX_COMBINE而不是INDEX，因为它是一个更通用的提示。如果INDEX提示指定了可用索引的列表，则优化器会考虑列表中每个索引的扫描成本，然后以最低成本执行索引扫描。如果这种访问路径的成本最低，数据库还可以选择扫描此列表中的多个索引并合并结果。数据库不考虑全表扫描或对提示中未列出的索引的扫描。
  • 如果INDEX提示未指定索引，则优化器会考虑对表上每个可用索引的扫描成本，然后以最低成本执行索引扫描。如果这种访问路径的成本最低，数据库还可以选择扫描多个索引并合并结果。优化器不考虑全表扫描。

    INDEX Hint示例：

SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id
  FROM employees 
  WHERE department_id > 50;

   

1.1.3 INDEX_ASC Hint

使用语法：

/*+ INDEX_ASC ( [ @ queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */

        

介绍：    

INDEX_ASC提示指示优化器对指定表使用索引扫描。如果语句使用索引范围扫描，则Oracle数据库会按索引值的升序扫描索引条目。每个参数的作用与INDEX Hint中的相同。

    范围扫描的默认行为是按索引值的升序扫描索引条目，或按降序扫描索引条目。此提示不会更改索引的默认顺序，因此仅指定index提示。但是，如果默认行为发生变化，您可以使用INDEX_ASC提示显式指定升序范围扫描。

   

1.1.4 INDEX_DESC Hint

使用语法：

/*+ INDEX_DESC ( [ @ queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */

        

介绍：    

    INDEX_DESC提示指示优化器对指定表使用降序索引扫描。如果语句使用索引范围扫描，并且索引是升序的，则Oracle会按索引值的降序扫描索引条目。在分区索引中，结果在每个分区内按降序排列。对于降序索引，此提示有效地取消了降序，导致按升序扫描索引条目。每个参数的作用与INDEX提示中的相同。例如：

SELECT /*+ INDEX_DESC(e emp_name_ix) */ *
  FROM employees e;

       

1.1.5 INDEX_FFS Hint

使用语法：

/*+ INDEX_FFS ( [ @ queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */

        

介绍：   

INDEX_FFS Hint指示优化器执行索引快速全扫描（Index Fast Full Scans），而不是全表扫描。每个参数的作用与INDEX提示中的相同。例如：

SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name
  FROM employees e;

       

1.1.6 INDEX_JOIN Hint

使用语法：

/*+ INDEX_JOIN ( [ @ queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */

      

介绍：    

INDEX_JOIN Hint指示优化器使用索引联接作为访问路径。为了使提示产生积极的效果，必须存在足够少的索引，其中包含解析查询所需的所有列。

    每个参数的作用与INDEX Hint中的相同。例如，以下查询使用索引联接来访问manager_id和department_id列，这两列都在employees表中索引。

SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id
  FROM employees e
  WHERE manager_id < 110
    AND department_id < 50;

1.1.7 INDEX_SS Hint

使用语法：

/*+ INDEX_SS ( [ @ queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */

     

介绍：    

INDEX_SS Hint指示优化器对指定表执行索引跳跃扫描（Index Skip Scans）。如果语句使用索引范围扫描，则Oracle会按索引值的升序扫描索引条目。在分区索引中，结果在每个分区内按升序排列。

    每个参数的作用与INDEX Hint中的相同。例如：

SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name
  FROM employees e
  WHERE first_name = 'Steven';

1.1.8 INDEX_SS_ASC Hint

使用语法：

/*+ INDEX_SS_ASC ( [ @ queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */

        

介绍：    

INDEX_SS_ASC提示指示优化器对指定的表执行索引跳过扫描。如果语句使用索引范围扫描，那么Oracle数据库将按照索引值的升序扫描索引条目。在分区索引中，结果在每个分区中按升序排列。每个参数的作用与INDEX Hint中的相同。

    范围扫描的默认行为是按照索引值的升序扫描索引项，或者按照降序扫描降序索引项。该提示不会更改索引的默认顺序，因此除了INDEX_SS提示之外，不会指定任何其他内容。但是，如果默认行为发生变化，可以使用INDEX_SS_ASC提示显式指定升序范围扫描。

  

1.1.9 INDEX_SS_DESC Hint

使用语法：

/*+ INDEX_SS_DESC ( [ @ queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */

     

介绍：    

INDEX_SS_DESC提示指示优化器对指定的表执行索引跳过扫描。如果语句使用索引范围扫描，并且索引是升序的，那么Oracle将按照索引值的降序扫描索引条目。在分区索引中，结果在每个分区中按降序排列。对于降序索引，该提示有效地取消了降序，从而以升序扫描索引条目。

    每个参数的作用与index Hint中的相同。例如：

SELECT /*+ INDEX_SS_DESC(e emp_name_ix) */ last_name
  FROM employees e
  WHERE first_name = 'Steven';

1.2 多表：多表Hint类似于单表Hint，只是该Hint可以指定多个表或视图

1.2.1 LEADING Hint

使用语法：

/*+ LEADING ( [ @ queryblock ] tablespec [ tablespec ]...  ) */

     

介绍：    

LEADING提示指示优化器使用指定的一组表作为执行计划中的前缀。这个提示比有序提示更通用。例如：

SELECT /*+ LEADING(e j) */ *
    FROM employees e, departments d, job_history j
    WHERE e.department_id = d.department_id
      AND e.hire_date = j.start_date;

    如果指定的表由于连接图中的依赖关系而无法按指定的顺序首先连接，则忽略LEADING Hint。如果指定了两个或更多冲突的LEADING Hint，则所有这些提示都将被忽略。如果指定ORDERED提示，它将覆盖所有LEADING Hint。

   

1.2.2 MERGE Hint

使用语法：

/*+ MERGE [ ( @ queryblock )  | ( [ @ queryblock ] tablespec ) ] */

  

介绍：    

    MERGE提示允许合并查询中的视图。

    如果视图的查询块在选择列表中包含GROUP BY子句或DISTINCT运算符，则只有在启用了复杂视图合并时，优化器才能将视图合并到访问语句中。如果子查询不相关，也可以使用复杂合并将IN子查询合并到访问语句中。示例：

SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary
   FROM employees e1,
        (SELECT department_id, avg(salary) avg_salary 
           FROM employees e2
           GROUP BY department_id) v 
   WHERE e1.department_id = v.department_id
     AND e1.salary > v.avg_salary
   ORDER BY e1.last_name;

    使用不带参数的MERGE提示时，应该将其放在视图查询块中。当MERGE与作为参数的视图名称一起使用时，它应该放在周围的查询中。

    

1.2.3 USE_BAND Hint

使用语法：

/*+ USE_BAND ( [ @ queryblock ] tablespec [ tablespec ]... ) */

     

介绍：    

USE_BAND提示指示优化器使用带连接将每个指定的表与另一个行源连接起来。例如：

SELECT /*+ USE_BAND(e1 e2) */
  e1.last_name
  || ' has salary between 100 less and 100 more than '
  || e2.last_name AS "SALARY COMPARISON"
FROM employees e1, employees e2
WHERE e1.salary BETWEEN e2.salary - 100 AND e2.salary + 100;

1.2.4 USE_CONCAT Hint

使用语法：

/*+ USE_CONCAT [ ( @ queryblock ) ] */

       

介绍：    

USE_CONCAT提示指示优化器使用UNION ALL集合运算符将查询的WHERE子句中的组合OR条件转换为复合查询。如果没有这个提示，只有当使用连接的查询成本比不使用连接的成本低时，才会发生这种转换。USE_CONCAT提示覆盖了成本考虑。例如：

SELECT /*+ USE_CONCAT */ *
  FROM employees e
  WHERE manager_id = 108
     OR department_id = 110;

     

1.2.5 USE_CUBE Hint

使用语法：

/*+ USE_CUBE ( [ @ queryblock ] tablespec [ tablespec ]... ) */

      

介绍：    

    当联接的右侧是一个多维数据集时，USE_CUBE提示指示优化器使用多维数据集联接将每个指定的表与另一个行源联接起来。如果优化器基于统计分析决定不使用多维数据集连接，那么您可以使用USE_CUBE来覆盖该决定。

   

1.2.6 USE_HASH Hint

使用语法：

/*+ USE_HASH ( [ @ queryblock ] tablespec [ tablespec ]... ) */

    

介绍：    

    USE_HASH提示指示优化器使用散列连接将每个指定的表与另一个行源连接起来。例如：

SELECT /*+ USE_HASH(l h) */ *
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id
    AND l.order_id > 2400;

1.2.7 USE_MERGE Hint

使用语法：

/*+ USE_MERGE ( [ @ queryblock ] tablespec [ tablespec ]... ) */

      

介绍：    

    USE_MERGE提示指示优化器使用排序合并联接将每个指定的表与另一个行源联接起来。例如：

SELECT /*+ USE_MERGE(employees departments) */ * 
  FROM employees, departments 
  WHERE employees.department_id = departments.department_id;

    建议将USE_NL和USE_MERGE提示与LEADING和ORDERED提示一起使用。当被引用的表被强制作为连接的内部表时，优化器使用这些提示。如果被引用的表是外部表，则忽略提示。

1.2.8 USE_NL Hint

使用语法：

/*+ USE_NL ( [ @ queryblock ] tablespec [ tablespec ]... ) */

    

介绍：

    USE_NL提示指示优化器使用指定的表作为内部表，通过嵌套循环联接将每个指定的表联接到另一个行源。

    USE_NL提示指示优化器使用指定的表作为内部表，通过嵌套循环联接将每个指定的表联接到另一个行源。

    建议将USE_NL和USE_MERGE提示与LEADING和ORDERED提示一起使用。当被引用的表被强制作为连接的内部表时，优化器使用这些提示。如果被引用的表是外部表，则忽略提示。

    在下面的示例中，通过提示强制执行嵌套循环，通过全表扫描访问orders，并将筛选条件l.order_id = h.order_id应用于每一行。对于满足筛选条件的每一行，order_items通过索引order_id进行访问。

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id;

    向查询中添加索引提示可以避免对订单进行全表扫描，从而产生一个类似于大型系统中使用的执行计划，尽管它在这里可能不是特别有效。

   

1.2.9 USE_NL_WITH_INDEX Hint

使用语法：

/*+ USE_NL_WITH_INDEX ( [ @ queryblock ] tablespec [ indexspec [ indexspec ]... ] ) */

   

介绍：    

    USE_NL_WITH_INDEX提示指示优化器使用指定的表作为内部表，通过嵌套循环联接将指定的表联接到另一个行源。例如：

SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ *
  FROM orders h, order_items l
  WHERE l.order_id = h.order_id
    AND l.order_id > 2400;

    以下条件适用：

  • 如果没有指定索引，那么优化器必须能够使用至少有一个连接谓词的索引作为索引键。
  • 如果指定了索引，那么优化器必须能够将该索引与至少一个连接谓词一起用作索引键。

1.3 查询块：查询块Hint对单个查询块进行操作

1.3.1 STAR_TRANSFORMATION Hint  

使用语法：

/*+ STAR_TRANSFORMATION [ ( @ queryblock ) ] */

    

介绍：

    STAR_TRANSFORMATION提示指示优化器使用使用了转换的最佳计划。如果没有提示，优化器可能会做出查询优化决策，使用没有转换时生成的最佳计划，而不是转换后查询的最佳计划。例如：

SELECT /*+ STAR_TRANSFORMATION */ s.time_id, s.prod_id, s.channel_id
  FROM sales s, times t, products p, channels c
  WHERE s.time_id = t.time_id
    AND s.prod_id = p.prod_id
    AND s.channel_id = c.channel_id
    AND c.channel_desc = 'Tele Sales';

    即使指定了提示，也不能保证转换会发生。只有在合理的情况下，优化器才会生成子查询。如果没有生成子查询，则没有已转换的查询，并且使用未转换查询的最佳计划，而不管提示如何。

         

1.3.2 UNNEST Hint

使用语法：

/*+ UNNEST [ ( @ queryblock ) ] */

介绍：

    UNNEST提示指示优化器取消嵌套并将子查询的正文合并到包含它的查询块的正文中，从而允许优化器在评估访问路径和连接时将它们放在一起考虑。

    在取消嵌套子查询之前，优化器首先验证语句是否有效。然后，语句必须通过启发式和查询优化测试。UNNEST提示指示优化器只检查子查询块的有效性。如果子查询块有效，则启用子查询取消嵌套，而不检查试探法或开销。

1.4 声明：声明Hint适用于整个SQL语句

1.4.1 ALL_ROWS Hint

    ALL_ROWS提示指示优化器以最佳吞吐量(即最小的总资源消耗)为目标优化语句块。例如，优化器使用查询优化方法来优化此语句，以获得最佳吞吐量：

SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
  FROM employees
  WHERE employee_id = 107;

    如果在SQL语句中指定ALL_ROWS或FIRST_ROWS Hint，并且如果数据字典没有关于该语句访问的表的统计信息，则优化器使用默认统计值，例如为这些表分配的存储，来估计缺失的统计信息并随后选择执行计划。这些估计可能不如DBMS_STATS包收集的估计准确，因此应该使用DBMS_STATS包收集统计信息。

    如果为访问路径或联接操作指定提示以及ALL_ROWS或FIRST_ROWS提示，则优化器会优先考虑提示指定的访问路径和联接操作。

                

1.4.2 FIRST_ROWS Hint

    FIRST_ROWS提示指示Oracle优化单个SQL语句以获得快速响应，选择最有效地返回前n行的计划。对于整数，指定要返回的行数。

    例如，优化器使用查询优化方法来优化以下语句，以获得最佳响应时间：

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
  FROM employees
  WHERE department_id = 20;

    在本例中，每个部门都包含许多员工。用户希望尽快显示部门20的前10名雇员。

    优化器在DELETE和UPDATE语句块以及包含任何阻塞操作（如排序或分组）的SELECT语句块中忽略此提示。此类语句无法优化以获得最佳响应时间，因为Oracle数据库必须在返回第一行之前检索语句访问的所有行。如果在任何此类语句中指定此提示，则数据库将优化以获得最佳吞吐量。

    每篇文章我都认真对待，只求质量不求数量，所以博主大概产出一篇文章需要4天到7天，这篇文章从构思到发布用了10天，所以真心不容易，觉得写的好的小伙伴请不吝自己的小手进行一键三连，点赞、收藏、加关注哦????。

相关文章