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

待我称王封你为后i 2022-07-03 21:23 217阅读 0赞

# 单表访问路径 #

##### 全表扫描 #####

*  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)