Hints

Hints是用来约束优化器行为的一种技术。

例如：

• 约束优化器模式

  • all_rows
  • first_rows

• 约束访问路径

  • 基于表的数据访问
  • 基于索引的数据访问

• 约束表关联方式

  • NL：Nested Join
  • MJ：Merge Join
  • HJ：Hash Join

使用范畴：

• 尽量避免在开发中使用。使用后可能会使得优化器变得死板，不利于CBO数学模型的分析
• 辅助DBA用来做性能排查和优化

访问路径相关的hints

全表扫描：/*+ full */

-- 强制全表扫描
select /*+ full(t) */ * from t where object_id=10; -- 该语句中加了hints会使查询变差

全表扫描并不一定都是性能差的，比如全表扫描可以使用多数据块读技术。有时候读取的数据占全表很大一部分时，使用多块读的全表扫描比逐一扫描索引可能还会快一些。

索引一般至少需要2次I/O（也可能不止2次，扫描索引的时候，需要先找索引的根，再继续寻找索引的枝叶），一次去索引中查找，然后根据索引去表中找对应行的其他字段。

另外，全表扫描还可以用到并行的技术。

索引相关：/*+ index */ 强制使用索引、/*+ no_index */强制不使用索引

-- 强制使用t表的idx_t索引
select /*+ index(t idx_t) */ * from t where object_id>10;  -- 该语句中加了hints会使查询变差
-- 强制不使用索引
select /*+ no_index(t idx_t) */ count(object_id) from t;  -- 该语句中加了hints会使查询变差

索引快速扫描（ffs扫描）：/*+ index_ffs */

select /*+ index_ffs(t idx_t) */ count(object_id) from t;

索引快速扫描是对索引的多数据块读，通常是对索引字段的统计等操作。

索引跳跃扫描：/*+ index_ss */

select /*+ index_ss(t idx_t) */ count(*) from t where object_id < 100;

索引跳跃扫描是用于替代全表扫描的一种数据访问方法。对于前导重复率高的联合索引，有时候 index skip scan的性能要好一些。（联合索引中，如果sql语句只查询了联合索引的第二个字段，没有查第一个字段，在oracle 9i之前是要走全表扫描的。9i之后加了跳跃扫描，可以跳过前导第一个字段，直接使用第二个字段在索引中查找）

表关联的hints

嵌套关联（Nested Loop Joins）：/*+ use_nl */ 从一张表中逐条拿出数据到另一个表里遍历查找

select /*+ use_nl(t, t1) */ t.*
from t, t1
where t.object_id=t1.object_id;

NL的场景：

• 关联中有一个表比较小（这种场景也适合hash关联）
• 被关联表的关联字段上有索引
• 索引的键值不应该重复率很高

Hash Join：/*+ use_hash */

把小表通过hash算法build到内存中，拿大表的数据到小表中比对。

select /*+ use_hash(t t1) */ t.*
from t, t1
where t.object_id=t1.object_id;

Hash Join的应用场景：

• 一个大表一个小表的关联
• 表上没有索引
• 返回结果集比较大

Merge Join：/*+ use_merge */

该算法比较耗费资源。先把两个结果集分别排序，排序之后再做关联。

select /*+ use_merge(t t1) */ * 
from t, t1
where t.object_id=t1.object_id;

应用场景：

• 结果集已经排好序。可以省去merg join再排序的资源。

leading：/*+ leading() */ 规定表连接的顺序

CBO会把关联的所有表都当做驱动表计算一下Cost，寻找最优的一个执行计划。使用leading可以指定哪张表作为驱动表，表连接的顺序。

select /*+ leading(t1 t t2) */ t.* 
from t,t1,t2
where t.object_id=t1.object_id
and t1.object_id=t2.object_id;

其他：/*+ append */ 以直接加载的方式插入数据

传统加载方式：Oracle在插入数据时，会优先到该表的高水位以下寻找空闲空间插入数据。

直接加载方式：Oracle出入数据时，直接到高水位以上的空闲空间插入数据，然后将高水位上移。在一定策略下，直接加载方式插入的数据不产生redo。

动态采样：/*+ dynamic sampling */设置动态采样的级别

select /*+ dynamic sampling(3) */ count(*) from t;

级别越高，动态采样越频繁，精准度越高，但是耗费资源也越高。

并行：/*+ parallel */ 指定并行度

select /*+ parallel(t 2) */ count(*) from t;

分布式：/*+ driving_site() */ 决定一个分布式事务中，操作在哪个节点上完成。

select /*+ driving_site(departments) */ *
from employees, departments@rsite
where employees.department_id = departments.department_id;

如果没有hint，远程表（departments）上的数据将被传到本地来做关联。

使用hint，本地的数据将传到远程节点上执行，最后即将结果返回本地。

cardinality：/*+ cardinality() */ oracle官方文档没有该hint，用来模拟一个结果集的cardinality，不必在表中插入很多数据也可以得出大量数据时表的执行计划

select /*+ cardinality(t 100) */ *
from t, t1
where t.object_id = t1.object_id 
and t.object_id > 100;