oracle索引优化

结论：决定一个SQL执行效率的是执行计划, 而不是SQL的写法
1、能用唯一索引，一定用唯一索引
2、能加非空，就加非空约束
3、联合索引的顺序不同，影响索引的选择（不同值越少的列，越要放在前面），即命中率高的放在前面
4、一定要统计表的信息，索引的信息，柱状图的信息
只有做到以上四点，数据库才会正确的选择执行计划

查看执行计划的方法：

1、在PL/SQL DEVELOPER中，在sql窗口下，按F5 即可查询执行计划

2、在Command窗口中或者sql*plus下

      第一步、 explain plan for {sql语句};    示例：explain plan for select * from scott.emp;

      第二步、 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

index索引的8种使用模式
索引的使用对数据库的性能有巨大的影响。
共有五类不同的使用模式。

1、INDEX UNIQUE SCAN    效率最高，主键或唯一索引
2、INDEX FULL SCAN      有顺序的输出，不能并行读索引
      问题：如果表中建立了多个索引，Oracle是把所有的索引都扫描一遍么？

3、INDEX FAST FULL SCAN  读的最块，可以并行访问索引，但输出不按顺序
4、INDEX RANGE SCAN      给定的区间查询
5、INDEX SKIP SCAN       联合索引，不同值越少的列，越要放在前面
6、SCAN DESCENDING
7、index join
8、bitmap join

在SQL优化过程中常见HINT的用法(前10个比较常用, 前3个最常用)：

1. /*+ INDEX */ 和 /*+ INDEX(TABLE INDEX1, index2) */ 和/*+ INDEX(tab1.col1 tab2.col2) */和 /*+ NO_INDEX */ 和 /*+ NO_INDEX(TABLE INDEX1, index2) */

表明对表选择索引的扫描方法. 第一种不指定索引名是让oracle对表中可用索引比较并选择某个最佳索引; 第二种是指定索引名且可指定多个索引; 第三种是10g开始有的, 指定列名, 且表名可不用别名; 第四种即全表扫描; 第五种表示禁用某个索引, 特别适合于准备删除某个索引前的评估操作. 如果同时使用了INDEX和NO_INDEX则两个提示都会被忽略掉.
例如:SELECT /*+ INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

2. /*+ ORDERED */
FROM子句中默认最后一个表是驱动表,ORDERED将from子句中第一个表作为驱动表. 特别适合于多表连接非常慢时尝试.
例如:SELECT /*+ ORDERED */ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

3. /*+ PARALLEL(table1,DEGREE) */ 和 /*+ NO_PARALLEL(table1) */
该提示会将需要执行全表扫描的查询分成多个部分（并行度）执行, 然后在不同的操作系统进程中处理每个部分. 该提示还可用于DML语句. 如果SQL里还有排序操作, 进程数会翻倍,此外还有一个一个负责组合这些部分的进程,如下面的例子会产生9个进程. 如果在提示中没有指定DEGREE, 那么就会使用创建表时的默认值. 该提示在默认情况下会使用APPEND提示. NO_PARALLEL是禁止并行操作,否则语句会使用由于定义了并行对象而产生的并行处理.
例如:select /*+ PARALLEL(tab_test,4) */ col1, col2 from tab_test order by col2;

4. /*+ FIRST_ROWS */ 和 /*+ FIRST_ROWS(n) */
表示用最快速度获得第1/n行, 获得最佳响应时间, 使资源消耗最小化.
在update和delete语句里会被忽略, 使用分组语句如group by/distinct/intersect/minus/union时也会被忽略.
例如:SELECT /*+ FIRST_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

5. /*+ RULE */
表明对语句块选择基于规则的优化方法.
例如:SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

6. /*+ FULL(TABLE) */
表明对表选择全局扫描的方法.
例如:SELECT /*+ FULL(A) */ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

7. /*+ LEADING(TABLE) */
类似于ORDERED提示, 将指定的表作为连接次序中的驱动表.

8. /*+ USE_NL(TABLE1,TABLE2) */
将指定表与嵌套的连接的行源进行连接,以最快速度返回第一行再连接,与USE_MERGE刚好相反.
例如:SELECT /*+ ORDERED USE_NL(BSEMPMS) */ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

9. /*+ APPEND */ 和 /*+ NOAPPEND */
直接插入到表的最后,该提示不会检查当前是否有插入操作所需的块空间而是直接添加到新块中, 所以可以提高速度. 当然也会浪费些空间, 因为它不会使用那些做了delete操作的块空间. NOAPPEND提示则相反,所以会取消PARALLEL提示的默认APPEND提示.
例如:insert /*+ append */ into test1 select * from test4;
insert /*+ parallel(test1) noappend */ into test1 select * from test4;

10. /*+ USE_HASH(TABLE1,table2) */
将指定的表与其它行源通过哈希连接方式连接起来.为较大的结果集提供最佳响应时间. 类似于在连接表的结果中遍历每个表上每个结果的嵌套循环, 指定的hash表将被放入内存, 所以需要有足够的内存（hash_area_size或pga_aggregate_target）才能保证语句正确执行, 否则将在磁盘里进行.
例如:SELECT /*+ USE_HASH(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;