SQL优化之--索引.doc

ORACLE SQL 优化——索引使用 一：为什么引入索引 索引的目的其实很简单，就是避免顺序的逐行的扫描，避免大量的磁盘输入输出，提高系统的效率。 二：试验索引的作用 表现一条SQL语句的好坏，大多数的情况下，我们一般观察该语句的执行效率，即得到结果所需的时间。但基于ORACLE的缓存、数据库的访问人数和硬件网络的繁忙与否等外界条件的影响。得到的结果并不令人满意。我在此采用的是利用TOAD的EXPLAIN PLAN 的cost来分析索引对SQL产生的影响。 （1）.plan_table 表 （2）.ALTER SESSION SET OPTIMIZER_MODE=first_rows 1 前提条件： 表： TEST_STU （30万条数据） STUID STUNAME COURSENAME 1404004 Nicholas Chinese 1404005 Cherry Chemistry Stuid：学生号 Stuname：学生姓名 Coursename： 选修课程名 TEST_COURSE COURSEID COURSENAME TEACHERID 031 Chinese 54001 032 English 54005 Courseid：课程号 Coursename：课程名 Teacherid：代课教师编号 TEST_TEA TEACHERID TEACHERNAME SEX 54001 guorong M 54002 jingtao M Teacherid：教师编号 Teachername：教师名称 Sex：性别 2 有无索引的条件下的运行情况 2.1 查询所有索引 SELECT INDEX_NAME,TABLE_NAME,UNIQUENESS FROM USER_INDEXES 清除索引 DROP INDEX INDEX_NAME //适用于仅和本表有关联的索引 ALTER TABLE TABLE_NAME DROP PRIMARY KEY CASCADE //用于删除主键（主键也是一种索引） ALTER TABLE TABLE_NAME DROP UNIQUE(COL_NAME) //用于删除唯一索引 ALTER TABLE TEST_BALLCLUB DROP CONSTRAINT INDEX_NAME //用于删除外键引起的索引 2.3 分析SQL语句 SQL SELECT * FROM TEST_STU 2 WHERE STUNAME=’huping’ EXPLAIN 一下 上面的语句 ,所消耗的cost为：115，115 创建索引： CREATE INDEX STUID_INDEX ON TEST_STU ( STUNAME) EXPLAIN 一下 上面的语句，所消耗的cost为：2，2，1 删除索引： DROP INDEX STUID_INDEX 效果是明显的，因为索引避免了全局扫面所花的不必要的时间和开资。 SQL SELECT * 2 FROM TEST_STU stu , 3 TEST_COURSE cor , 4 TEST_TEA tea 5 WHERE stu . coursename=cor . coursename 6 AND cor . teacherid= tea. teacherid 7 AND tea.teacherid=’54006’ 对ORDER BY 的影响更大，例如，在最后加上 “ORDER BY stuname” 同样对GROPU BY也一样成立。 COST : 126 , 126 , 4 , 2 , 2 , 2 , 115 在从分析表中，可以看到，COST主要消耗在了全局扫面TEST_STU表上了， 所以，在TEST_STU 的 COURSENAME上添加一个索引 CREATE INDEX stu_coursename_index ON TEST_STU(COURSENAME) DROP INDEX stu_coursename_index SQL SELECT * 2 FROM TEST_STU 3