索引优化使用（转）

提高索引查询效率；
　　alter index idx_name rebuild nologging;

SQL优化器简介
　　
　　基于规则的优化器
　　
　　。总是使用索引
　　。总是从驱动表开始（from子句最右边的表）
　　。只有在不可避免的情况下，才使用全表扫描
　　。任何索引都可以
　　
　　基于成本的优化器
　　
　　。需要表、索引的统计资料
　　Analyze table customer compute statistics;
　　Analyze table customer estimate statistics sample 5000 rows;
　　。表中设置并行度、表分区
　　
　　优化器模式
　　
　　rule模式
　　
　　。总忽略CBO和统计信息而基于规则
　　choose模式
　　
　　。Oracle根据情况选择rule or first_rows or all_rows
　　first_rows 模式
　　
　　。基于成本，以最快的速度返回记录，会造成总体查询速度的下降或消耗更多的资源，倾向索引扫描，适合OLTP系统
　　all_rows模式
　　
　　。基于成本，确保总体查询时间最短，倾向并行全表扫描
　　
　　例如：
　　Select last_name from customer order by last_name;用first_rows时，迅速返回记录，但I/O量大，用all_rows时，返回记录慢，但使用资源少。
　　
　　调整SQL表访问
　　
　　全表扫描
　　
　　。返回记录：未排序表>40%，排序表>7%，建议采用并行机制来提高访问速度，DDS；
　　
　　索引访问
　　
　　。最常用的方法，包括索引唯一扫描和索引范围扫描，OLTP；
　　
　　快速完全索引扫描
　　
　　。访问索引中所有数据块，结果相当于全表扫描，可以用索引扫描代替全表扫描，例如：
　　
　　Select serv_id,count(* ) from tg_cdr01 group by serv_id;
　　
　　评估全表扫描的合法性
　　
　　如何实现并行扫描
　　
　　。永久并行化（不推荐）
　　alter table customer parallel degree 8;
　　
　　。单个查询并行化
　　select /*+ full(emp) parallel(emp,8)*/ * from emp;
　　
　　分区表效果明显
　　
　　优化SQL语句排序
　　
　　排序的操作：
　　
　　。order by 子句
　　。group by 子句
　　。select distinct子句
　　。创建索引时
　　。union或minus
　　。排序合并连接
　　
　　如何避免排序
　　
　　。添加索引
　　。在索引中使用distinct子句
　　。避免排序合并连接
　　
　　使用提示进行调整
　　
　　使用提示的原则
　　
　　。语法：/*+ hint */
　　。使用表别名:select /*+ index(e dept_idx)*/ * from emp e
　　。检验提示
　　
　　常用的提示
　　
　　。rule
　　。all_rows
　　。first_rows
　　。use_nl
　　。use_hash
　　。use_merge
　　。index
　　。index_asc
　　。no_index
　　。index_desc（常用于使用max内置函数）
　　。index_combine(强制使用位图索引)
　　。index_ffs（索引快速完全扫描）
　　。use_concat(将查询中所有or条件使用union all)
　　。parallel
　　。noparallel
　　。full
　　。ordered（基于成本）
　　

某些select 语句中的where子句不使用索引. 这里有一些例子:

1、IS NULL 与 IS NOT NULL
　　不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。
　　任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

2、'!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中

不使用索引: select * from employee where salary<>3000;
使用索引: select account_name from transaction where amount >0;
使用索引: select * from employee where salary<3000 or salary>3000;

3、联接列,'||'是字符连接函数. 就象其他函数那样, 停用了索引

不使用索引: select account_name,amount from transaction where account_name||account_type='AMEXA';
使用索引: select account_name,amount from transaction where account_name = 'AMEX' and account_type=' A';

4、'+'是数学函数. 就象其他数学函数那样, 停用了索引

不使用索引: select account_name, amount from transaction where amount + 3000 >5000;
使用索引: select account_name, amount from transaction where amount > 2000 ;

5、相同的索引列不能互相比较,这将会启用全表扫描

不使用索引: select account_name, amount from transaction where account_name = nvl(:acc_name,account_name);
使用索引: select account_name, amount from transaction where account_name like nvl(:acc_name,'%');

6、带通配符（%）的like语句

不使用索引: select * from employee where last_name like '%cliton%';
使用索引: select * from employee where last_name like 'c%'

7、IN和EXISTS

不使用索引: ... where column in(select * from ... where ...);
使用索引: ... where exists (select 'X' from ...where ...);
同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。

怎样监控无用的索引

Oracle 9i以上，可以监控索引的使用情况，如果一段时间内没有使用的索引，一般就是无用的索引

　　语法为：
　　开始监控：alter index index_name monitoring usage;
　　检查使用状态：select * from v$object_usage;
　　停止监控：alter index index_name nomonitoring usage;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8541256/viewspace-666079/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8541256/viewspace-666079/