解决Oracle SQL语句性能问题（10.5）——常用Hint及语法（7）（其他Hint）

10.5.3. 常用hint

10.5.3.7. 其他Hint

1）cardinality：显式的指示优化器为SQL语句的某个行源指定势。该Hint具体语法如下所示。

SQL> select /*+ cardinality([@qb] [table] card ) */ ...;

--注：

      1）这里，第一个参数（@qb）为可选参数，指定查询语句块名；第二个参数（table）也为可选参数，指为其指定势的表的别名或表名；第三个参数（card）为指定的势。如果没有表被传给该Hint，该Hint确定的值就是整个SQL语句行源的势。

      2）该Hint最早出现于Oracle 9.0.0版本。

2）selectivity：显式的指示优化器为SQL语句的特定表指定选择性。该Hint具体语法如下所示。

SQL> select /*+ selectivity(table sel) */ ...;

--注：

      1）这里，第一个参数（table）代表为其指定选择性的表的别名或表名；第二个参数（sel）为指定的选择性。

      2）如果仅一个表被指定，选择性为满足该表所有相关谓词的行源行数的分数。如果一组表被指定，选择性为满足所有相应谓词的这些表连接后返回行源行数的分数。

      3）如果cardinality和selectivity两个Hint在同一组表上被同时使用，则两个Hint都被忽略掉。

      4）该Hint最早出现于Oracle 10g版本。

3）push_pred：显式的指示优化器将SQL语句中主查询的相关谓词推入特定视图中。该Hint具体语法如下所示。

SQL> select /*+ push_pred[(@qb)|([@qb] v)] */ ...;

--注：

      1）这里，参数（@qb|[@qb] v）为可选参数，其中，@qb指定查询语句块名，v指定SQL语句中from子句中的视图。

      2）该Hint最早出现于Oracle 8.1.0版本。

4）no_push_pred：显式的指示优化器禁止将SQL语句中主查询的相关谓词推入特定视图中。该Hint具体语法如下所示。

SQL> select  /*+ no_push_pred[(@qb)|([@qb] v)] */ ...;

--注：

      1）这里，参数（@qb|[@qb] v）为可选参数，其中，@qb指定查询语句块名，v指定SQL语句中from子句中的视图。

      2）该Hint最早出现于Oracle 8.1.0版本。

5）qb_name：显式的指示优化器为SQL语句中的特定查询块命名。该Hint具体语法如下所示。

SQL> select /*+ qb_name(qb) */ ...;

--注：

      1）这里，参数（qb）为特定查询块的命名，可以为任何符合命名规则的名字，但最好有一定意义。

      2）该Hint一般和其他Hint配合使用，且用于子查询，具体如下所示。

SQL> select /*+ push_subq(@qb) */ ... from ...

where colname in(select /*+ qb_name(qb) */ ...);

      3）该Hint最早出现于Oracle 10.1.0.3版本。

6）push_subq：显式的指示优化器将SQL语句中特定的、未被展开的子查询推到执行计划中尽可能早的位置。默认的，未被展开的子查询会被置于执行计划中的最后。该Hint具体语法如下所示。

SQL> select /*+ push_subq[(@qb)] */ ...;

--注：

      1）这里，参数（@qb）为可选参数，指定查询语句块名。

      2）一般该Hint和qb_name被结合使用，这里@qb即为通过qb_name命名的子查询。具体如下所示：

SQL> select /*+ push_subq(@qb) */ ... from ...

Where colname in(select /*+ qb_name(qb) */ ...);

      3）该Hint最早出现于Oracle 8.1.0版本。

7）no_push_subq：显式的指示优化器将SQL语句中特定的、未被展开的子查询推到执行计划中的最后位置。该Hint具体语法如下所示。

SQL> select /*+ no_push_subq[(@qb)] */ ...;

--注：

      1）这里，参数（@qb）为可选参数，指定查询语句块名。

      2）一般该Hint和qb_name被结合使用，这里@qb即为通过qb_name命名的子查询。具体如下所示：

SQL> select /*+ no_push_subq(@qb) */ ... from ...

where colname in(select /*+ qb_name(qb) */ ...);

      3）该Hint最早出现于Oracle 8.1.0版本。

8）dynamic_sampling：显式的指示优化器为SQL语句中的特定表指定动态取样的级别。该动态取样行为通过提供更加精确的相关谓词选择性和表及索引的统计数据来提升SQL语句性能。该Hint具体语法如下所示。

SQL> select /*+ dynamic_sampling([@qb] [table] integer) */ ...;

--注：

      1）这里，第一个参数（@qb）为可选参数，指定查询语句块名；第二个参数（table）也为可选参数，指定要动态取样的表的别名或表名；第三个参数（integer）为动态取样的级别。除非你确定一张表，否则，默认将在游标范围进行动态取样。

      2）可以设置该Hint 的值为1到10，级别越高，优化器为动态取样付出的成本就越多，取样结果的适用性就越广泛。

      3）该动态取样行为通过参数“optimizer_dynamic_sampling”设置，该Hint会在SQL语句层面覆盖系统或会话层面相关动态取样的设置。

      4）该Hint最早出现于Oracle 9.2.0版本。

9）cache：显式的指示优化器当SQL语句中的特定表被以全表扫描方式访问时,该表中被访问的数据块放于数据缓冲区中LRU列表的最近最多被使用端（热端）。该Hint具体语法默认如下所示。

SQL> select /*+ cache([@qb] table)*/ ...;

--注：

      1）这里，第一个参数（@qb）为可选参数，指定查询语句块名；第二个参数为其数据块被放于LRU列表热端的表别名或表名。

       2）该Hint一般用于小查找表的场景，该Hint覆盖数据库默认的数据缓冲区行为。

      3）该Hint最早出现于Oracle 8.1.0版本。

10）nocache：显式的指示优化器当SQL语句中的特定表被以全表扫描方式访问时,该表中被访问的数据块放于数据缓冲区中LRU列表的最近最少被使用端（冷端）,这也是数据库默认的缓冲行为（具体细节与Oracle版本相关）。该Hint具体语法如下所示。

SQL> select| /*+ nocache([@qb] table)*/ ...;

--注：

      1）这里，第一个参数（@qb）为可选参数，指定查询语句块名；第二个参数为其数据块被放于LRU列表冷端的表别名或表名。

      2）该Hint最早出现于Oracle 8.1.0版本。

11）opt_param：显式的指示优化器为SQL语句指定特定参数值。该Hint具体语法如下所示。

SQL> select /*+ opt_param(par_name par_value) */ ...;

--注：

      1）这里，第一个参数（par_name）为要设置的参数名，需用单引号括起来；第二个参数（par_value）为要设置的参数值，字符型值需用单引号括起来，数字型值不需单引号。

      2）该Hint仅对下列参数有效：

• optimizer_dynamic_sampling
• optimizer_index_caching
• optimizer_index_cost_adj
• optimizer_use_pending_statistics
• optimizer_secure_view_merging
• star_transformation_enabled
• optimizer_degree_policy
• optimizer_degree_limit
• 部分隐含参数：（“_hash_join_enabled”,“_b_tree_bitmap_plans”,“_complex_view_merging”等）

      3）该Hint最早出现于Oracle 10.2.0.1版本。

12）optimizer_features_enable：显式的指示优化器为SQL语句指定“optimizer_features_enable”参数值，以便该SQL语句可以使用特定版本的优化器特性。该Hint具体语法如下所示。

SQL> select /*+ optimizer_features_enable('opm_version') */ ...;

--注：

      1）这里，参数（opm_version）为优化器版本，例如：8.1.7,9.2.0,10.2.0.1,11.2.0.3等。

      2）该Hint最早出现于Oracle 10.1.0.3版本。

13）cpu_costing：显式的指示优化器为SQL语句开启CPU成本计算。这是优化器默认的成本计算模型（具体细节与Oracle版本相关），PLAN_TABLE表的CPU_COST列存储CPU成本。该Hint具体语法如下所示。

SQL> select /*+ cpu_costing(table index) */ ...;

--注：

      1）这里，第一个参数（table）指为其开启CPU成本计算的表的别名或表名；第二个参数（index）指为其开启CPU计算的索引名。

      2）该Hint最早出现于Oracle 9.0.0版本。

14）no_cpu_costing：显式的指示优化器为SQL语句关闭CPU成本计算。这样，优化器将采用IO成本模型为该SQL语句进行成本计算。该Hint具体语法如下所示。

SQL> select /*+ no_cpu_costing */ ...;

--注：

      1）该Hint最早出现于Oracle 9.0.0版本。

15）swap_join_inputs：显式的指示优化器为SQL语句执行计划中特定哈希连接的构建表和探测表进行顺序交换。该Hint具体语法如下所示。

SQL> select /*+ swap_join_inputs(table) */ ...;

--注：

      1）这里，参数（table）为SQL语句执行计划中特定哈希连接的探测表。

      2）该Hint最早出现于Oracle 8.1.0版本。

16）append_values：显式的指示优化器运行带values子句的SQL语句时，绕开数据缓冲（Buffer Cache），使用Direct Path方式直接加载数据。该Hint具体语法如下所示。

SQL> insert /*+ append_values */ ...;

--注：

      1）该Hint最早出现于Oracle 11.2.0.1版本。

17）cursor_sharing_exact：显式的指示优化器禁止使用绑定变量安全替代SQL语句中的相应可替代文字。该Hint具体语法如下所示。

SQL> select /*+ cursor_sharing_exact */ ...;

--注：

      1）该替代行为通过参数“cursor_sharing”进行控制。

      2）该Hint导致该替代行为在SQL语句级被禁用。换句话说，Oracle不再试图用绑定变量替换使用该Hint的SQL语句中的相应可替代文字，而是直接解析或执行该SQL语句。

      3）该Hint最早出现于Oracle 9.0.0版本。

18）driving_site：显式的指示优化器在分布式环境中指定一个特定数据库实例来执行相关SQL语句中特定表上的操作，进而该SQL语句也将在该数据库实例上被完成。该Hint能被用于基于规则或基于成本的优化器中。该Hint具体语法如下所示。

SQL> select /*+ driving_site([@qb] table) */ ...;

--注：

      1）这里,第一个参数（@qb）为可选参数，指定查询语句块名；第二个参数（table）为SQL语句中，为其指定数据库实例的特定表的别名或表名，该Hint一般被用来优化分布式SQL语句。

      2）该Hint最早出现于Oracle 8.1.0版本。

19）spread_min_analysis：显式的指示优化器省去报表规则编译时的优化工作，例如：详细的依赖图分析。其他优化工作，如选择性的填充报表存取结构和有限的规则探测还是会被执行。如果规则数非常多，例如:几百之多，那么，该Hint有助于减少编译时间。该Hint具体语法如下所示。

SQL> select /*+ spread_min_analysis */ ...;

--注：

      1）该Hint无需任何参数传入。

      2）该Hint最早出现于Oracle 10g版本。

20）spread_no_analysis：显式的指示优化器省去报表的分析工作。当使用该Hint时，规则探测和过滤产生也被省去。即使有报表分析，那么，编译时间也是最短的。该Hint具体语法如下所示。

SQL> select /*+ spread_no_analysis */ ...;

--注：

      1）该Hint无需任何传入参数。

      2）该Hint最早出现于Oracle 10g版本。