第一天：索引的使用
Oracle对象教程：索引(Index)创建使用，索引：对数据库表中的某些列进行排序，便于提高查询效率。
当我们在某本书中查找特定的章节内容时，可以先从书的目录着手，找到该章节所在的页码，然后快速的定位到该页。这种做法的前提是页面编号是有序的。如果页码无序，就只能从第一页开始，一页页的查找了
数据库中索引(Index)的概念与目录的概念非常类似。如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，Oracle会自动的引用该索引，先从索引表中查询出符合条件记录的ROWID，由于ROWID是记录的物理地址，因此可以根据ROWID快速的定位到具体的记录，表中的数据非常多时，引用索引带来的查询效率非常可观（总结一句话：如果你查询一个老师年龄为30岁，那么你所先要把所有老师全部查询出来，然后根据年龄=30再查找出对应的老师 ，但是如果你给这条老师年龄等于三十老师加上索引的话，那么就可以大大减少查询的效率）
·如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该列创建索引。
·当从很多行的表中查询少数行时，也要考虑创建索引。有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的10%时，索引就非常有用。
Oracle数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。
CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])
语法结构：创建索引
1. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。
2. index_name：指定索引名。
3. tabl_name：指定要为哪个表创建索引。
4. column_name：指定要对哪个列创建索引。我们也可以对多列创建索引;这种索引称为组合索引。
案例4：为EMP表的ENAME列创建创建唯一索引，为EMP表的工资列创建普通索引，把JOB列先变为小写再创建索引。
代码演示：创建索引
|
SQL> CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME); ① Index created SQL> CREATE INDEX IDX_SAL ON EMP(SAL); ② Index created SQL> CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB)); ③ Index created |
代码解析：
① 为SCOTT.EMP表的ENAME列创建唯一索引。
② 为SCOTT.EMP表的SAL列创建索引。
③ 在查询中可能经常使用job的小写作为条件的表达式，因此创建索引时，可以先对JOB列中的所有值转换为小写后创建索引，而这时需要使用lower函数，这种索引称为基于函数的索引。
在select语句查询时，Oracle系统会自动为查询条件上的列应用索引。索引就是对某一列进行排序，因此在索引列上，重复值越少，索引的效果越明显。
Oracle可以为一些列值重复非常多且值有限的列(比如性别列)上创建位图索引。关于Oracle更多的索引类型(比如反向键索引等)，请参考Oracle官方文档。
第二天 索引的使用：（索引的使用并不是由开发者自己调用，而是主要是由oracle来选择调用，至于调用不调用是由着oracle来处理的）
创建索引不是为了在sql语句中用的，而是可以大大提高系统的性能。
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
建议每张表不超过5个索引。
常见的索引限制问题：
9、使用不等于操作符(<>, !=)
下面这种情况，即使在列dept_id有一个索引，查询语句仍然执行一次全表扫描
select * from dept where staff_num <> 1000;
但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？
有！
通过把用 or 语法替代不等号进行查询，就可以使用索引，以避免全表扫描：上面的语句改成下面这样的，就可以使用索引了。
select * from dept shere staff_num < 1000 or dept_id > 1000;
10、使用 is null 或 is not null
使用 is null 或is nuo null也会限制索引的使用，因为数据库并没有定义null值。如果被索引的列中有很多null，就不会使用这个索引（除非索引是一个位图索引，关于位图索引，会在以后的blog文章里做详细解释）。在sql语句中使用null会造成很多麻烦。
解决这个问题的办法就是：建表时把需要索引的列定义为非空(not null)
11、使用函数
如果没有使用基于函数的索引，那么where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询就不会使用索引：
select * from staff where trunc(birthdate) = '01-MAY-82';
但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。
select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);
12、比较不匹配的数据类型
比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。
select * from dept where dept_id = 900198;
这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引
select * from dept where dept_id = '900198';
13、使用like子句
使用like子句查询时，数据需要把所有的记录都遍历来进行判断，索引不能发挥作用，这种情况也要尽量避免。
Like 的字符串中第一个字符如果是‘%’则用不到索引 Column1 like ‘aaa%’ 是可以的 Column1 like ‘%aaa%’用不到
14．使用IN
尽管In写法要比exists简单一些，exists一般来说性能要比In要高的多 用In还是用Exists的时机 当in的集合比较小的时候,或者用Exists无法用到选择性高的索引的时候，用In要好，否则就要用Exists 例：select count(*) from person_info where xb in (select xb_id from dic_sex); Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …);
Select * from person_info where zjhm=3101….;将会对person_info全表扫描 Select * from person_info where zjhm =‘3101…’才能用到索引
假定TEST表的dt字段是date类型的并且对dt建了索引。 如果要查‘20041010’一天的数据.下面的方法用不到索引 Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’; 而select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1 将会用到索引。
15．如果能不用到排序，则尽量避免排序。 用到排序的情况有 集合操作。Union ,minus ,intersect等,注：union all 是不排序的。 Order by Group by Distinct In 有时候也会用到排序 确实要排序的时候也尽量要排序小数据量
,尽量让排序在内存中执行，有文章说，内存排序的速度是硬盘排序的1万倍。
在排序的字段上创建索引，让排序在内存中执行，加快排序速度。
16.在基于CBO的优化器（花费）下，表的统计数据过期。也可能导致不使用索引。
解决：执行表分析。获取表的最新信息。
17.获取的数据量过大，全部扫描效率更高
18.索引字段的值分散率太低，值太集中，如类型字段都是1,2， 状态类型Y-有效/N-无效。这类型的字段最好别建索引。
尽管在这些字段上建立了索引，但对全表数据区分度不大。最后还是会全表扫描。