sql效率分析二

一、关于索引的知识

要写出运行效率高的sql，需要对索引的机制有一定了解，下面对索引的基本知识做一介绍。

1、 索引的优点和局限
索引可以提高查询的效率，但会降低dml操作的效率。
所以建立索引时需要权衡。对于dml操作比较频繁的表，索引的个数不宜太多。

2、 什么样的列需要建索引？
经常用于查询、排序和分组的列（即经常在where、order或group by子句中出现的列）。

3、 主键索引和复合索引
对于一张表的主键，系统会自动为其建立索引。
如果一张表的几列经常同时作为查询条件，可为其建立复合索引。

4、 建立索引的语句
createindexi_staffonstaff(empno);
createindexi_agentonagent(empno, start_date);

5、 删除索引的语句
dropindexI_staff;
dropindexI_agent;

6、 查询索引的语句
法一：利用数据字典
表一：all_indexes查看一张表有哪些索引以及索引状态是否有效
主要字段： index_name,table_name,status
例如：selectindex_name,status
fromall_indexes
wheretable_name=’STAFF_INFO’;
INDEX_NAMESTATUS
--------------------- -----------
I_STAFF VALID
表二：all_ind_columns查看一张表在哪些字段上建了索引
主要字段： table_name,index_name,column_name,column_position
例如： selectindex_name,column_name,column_position
fromall_ind_columns
wheretable_name=’AGENT’
INDEX_NAMECOLUMN_NAMECOLUMN_POSITON
--------------------- -------------------------------------------------
I_AGENT EMPNO1
I_AGENT START_DATE2
由此可见，agent表中有一个复合索引（empno, start_date ）
法二：利用toad工具
toad用户界面比sql*plus友好，并且功能强大。你可以在toad编辑器中键入表名，按F4，便可见到这张表的表结构以及所有索引列等基本信息。

7、 索引的一些特点
1): 不同值较多的列上可建立检索，不同值少的列上则不要建。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
2): 如果在索引列上加表达式，则索引不能正常使用
例如：b1,c1分别是表b,c的索引列
select*frombwhereb1/30< 1000 ;
select*fromcwhereto_char(c1,’YYYYMMDD HH24:MI:SS’) = ‘200203 14:01:01’;
以上都是不正确的写法