Oracle11高性能开发--（5）SQL调优

单表访问路径

全表扫描

• Hints：FULL
• Operation和Options：TABLE ACCESS FULL
• 适用场景：需要对表的大部分数据进行处理
  优化目标：减少访问数据量（分区）、变为索引扫描（使用索引字段）
• 示例：

select * from c_fraud_detail t

Rowid访问

• Operation和Options：TABLE ACCESS BY USER ROWID
• 适用场景：快速对一条记录进行操作
• 优化目标：已经是最快路径
• 示例：

select * from c_fraud_detail t where rowid=?

唯一索引扫描

• Hints：index no_index
• Operation和Options：INDEX UNIQUE SCAN
• 适用场景：需要对表的使用唯一索引字段进行=过滤
• 示例：

select * from c_fraud_detail t where t.id = '2016fd3b-7aa6-44d0-80f9-4d2c76de8289‘

索引范围扫描

• Hints：index no_index
• Operation和Options： INDEX RANGE SCAN
• 适用场景：对B树索引字段进行范围查找或对Bitmap索引字段进行查找
• 示例：

select * from c_fraud_detail t where  id between '1' and '3'

索引全扫描

• Hints：index no_index index_asc index_desc
• Operation和Options：INDEX FULL SCAN
• 适用场景：需要对表使用索引进行排序
• 示例：

select /*+index(t idx_c_fraud_detail_id)*/ * from c_fraud_detail t order by id asc

索引快速全扫描

• Hints：index_ffs no_index_ffs
• Operation和Options： INDEX FAST FULL SCAN
• 适用场景：索引中包含需要查找的全部字段
• 示例：

select id from c_fraud_detail

索引跳跃扫描

• Hints：inde_ss on_index_ss index_ss_asc index_ss_desc
• Operation和Options：INDEX SKIP SCAN
• 适用场景：查询条件只用到了组合索引的部分字段时，强制使用组合索引
• 示例：

select /*+index_ss(t idx_c_fraud_detail_sic_acq_re)*/* from c_fraud_detail t where t.sd_acq_inst_id_num = '1' and t.sd_retl_id = '1'

索引连接

• Hints：index_join
• View的Object Name：index$_join
• 适用场景：索引中包含全部的select字段,相比于全表扫描可能会有更少的IO_COST
• 示例：

select /*+index_join(t idx_c_fraud_detail_id idx_c_fraud_detail_import_id)*/id,import_id from c_fraud_detail partition t

多索引组合

• Hints：index_combine
• Operation：bitmap or/and/minus
• 适用场景：多个位图索引可以自动组合，B树索引需要使用Hints才能组合
• 示例：

select /*+index_combine(t idx_c_fraud_detail_id idx_c_fraud_detail_import_id)*/id,import_id from c_fraud_detail t  where id ='111' and import_id ='222'

多表连接方式

嵌套循环

• Hints：use_nl no_use_nl use_nl_with_index leading(指定驱动表)
• Operation：nested loops
• 运作方式：循环驱动表每条符合条件的记录去访问被驱动表
• 适用场景：驱动表符合条件的记录数据量小，且对被驱动表采用索引等方式快速访问
• 示例：

select /*+ use_nl(m,d)*/*
  from c_master m, c_fraud_detail d
 where m.sm_key = d.sd_key

HASH连接

• Hints：use_hash no_use_hash
• Operation：hash join
• 运作方式：对两张表要匹配的字段进行Hash处理，然后进行连接，只能用于=连接
• 适用场景：两张表数据量大
• 示例：

select /*+ noparallel leading(d) use_hash(m,d)*/*
  from c_master m, c_fraud_detail d
 where m.sm_key = d.sd_key and m.sm_user_city = d.sm_user_city

排序合并连接

• Hints：use_merge no_use_merge
• 运作方式：采用对两张表按匹配的字段进行排序，然后进行连接，性能一般低于Hash连接，但可用于非等于连接条件
• 适用场景：两张表数据量大，且使用非等于连接条件
  示例：

select *
      from C_DETAIL_RULE dr, C_ACT a
     where a.actdatekey > dr.dd_tran_dat_tim

反连接

• Operation：hash join anti/merge join anti
• 运作方式：对not in、not exists等条件使用反连接，包括排序合并和hash连接两种方式
• 适用场景：两张表数据量大
• 示例：

select * from C_DETAIL d
 where d.id not in 
       (select dr.DETAIL_ID
          from c_Detail_Rule dr)