05 ---- 索引

———————————————

——— 索引丶优缺点 ———

———————————————

创建索引可以大大提高系统的性能。
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

如果一张表20个索引，往里面写入100万条数据，怎么优化？
先把索引全删了，再把记录写进去，再建立索引。

————————————————

——— 索引丶考虑使用 ———

————————————————

考虑使用索引的条件：

Oracle数据库会为表的主键和包含唯一约束的列自动创建索引
索引可以提高查询的效率，
但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响
在select语句查询时，Oracle系统会自动为查询条件上的列应用索引。
索引就是对某一列进行排序，因此在索引列上，重复值越少，索引的效果越明显。

应该创建索引：

1. 在经常需要搜索的列上，可以加快搜索的速度；
2. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
3. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
4. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

不应该创建索引：

1. 对于那些在查询中很少使用或者参考的列不应该创建索引。

这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。
相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

1. 对于那些只有很少数据值的列也不应该增加索引。

这是因为，由于这些列的取值很少，例如人事表的性别列，
在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。
增加索引，并不能明显加快检索速度。

1. 对于那些定义为text, image和bit数据类型的列不应该增加索引。

这是因为，这些列的数据量要么相当大，要么取值很少。

1. 当修改性能远远大于检索性能时，不应该创建索引。

这是因为，修改性能和检索性能是互相矛盾的。
当增加索引时，会提高检索性能，但是会降低修改性能。
当减少索引时，会提高修改性能，降低检索性能。
因此，当修改性能远远大于检索性能时，不应该创建索引。

—————————————-

——— 索引丶失效 ———

—————————————-

• 隐式转换导致索引失效

由于表的字段tu_mdn定义为varchar2(20)，
但在查询时把该字段作为number类型
以where条件传给Oracle，这样会导致索引失效

 -- 错误的例子：
    SELECT * FROM TEST WHERE TU_MDN = 13333333333;
 -- 正确的例子：
    SELECT * FROM TEST WHERE TU_MDN = '13333333333';

• 对索引列进行运算导致索引失效

对索引列进行包括+，-，*，/，! 等运算

-- 错误的例子：

   SELECT * FROM TEST WHERE ID-1=9;

-- 正确的例子：

   SELECT * FROM TEST WHERE ID=10;

• 使用Oracle内部函数导致索引失效

对于这样情况应当创建基于函数的索引

-- 错误的例子：

   SELECT * FROM TEST WHERE ROUND(ID)=10; 

-- 正确的例子：

     首先建立函数索引： 

                CREATE INDEX TEST_ID_FBI_IDX ON TEST(ROUND(ID));

     然后： SELECT * FROM TEST WHERE ROUND(ID)=10;

• 其他失效条件

1. 查询时，WHERE使用 <> 、not in 、not exist、!= 等判断
2. like “%_” 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
3. 单独引用复合索引里非第一位置的索引列。应总是使用索引的第一个列，

如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。

1. 字符型字段为数字时在where条件里不添加引号.
2. 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况

—————————————-

——— 索引丶分类 ———

—————————————-

唯一索引（用的最多）
1、何时创建：当某列任意两行的值都不相同
2、当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立

组合索引
1、何时创建：当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建
2、组合索引中列的顺序是任意的，也无需相邻。
但是建议将最频繁访问的列放在列表的最前面

位图索引
1、何时创建：列中有非常多的重复的值时候。例如某列保存了“性别”信息。
2、Where 条件中包含了很多OR操作符。较少的update操作，因为要相应的跟新所有的bitmap

基于函数的索引
1、何时创建：在WHERE条件语句中包含函数或者表达式时
2、函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。

反向键索引、键压缩索引、索引组织表(IOT)、分区索引

数据库中索引（Index）的概念与目录的概念非常类似。
如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。
创建索引就是对某些特定列中的数据排序，生成独立的索引表。

在某列上创建索引后，如果该列出现在查询条件中，
Oracle会比较全表扫描与索引扫描的代价，如果索引扫描代价小，那Oracle会自动引用该索引，
先从索引表中查询出符合条件记录的ROWID，由于ROWID是记录的物理地址，
因此可以根据ROWID快速的定位到具体的记录，
表中的数据非常多时，引用索引带来的查询效率非常可观

—————————————-

——— 索引丶代码 ———

—————————————-

语法结构：创建索引

-- 创建索引

CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])


1.    UNIQUE:      指定索引列上的值必须是唯一的。称为唯一索引。
2.    index_name： 指定索引名。
3.    tabl_name：  指定要为哪个表创建索引。
4.    column_name：指定要对哪个列创建索引。我们也可以对多列创建索引；这种索引称为组合索引。



----------------------------------------------------------



【例】为EMP表的ENAME列创建创建唯一索引，
      为EMP表的SAL列创建普通索引，
      把JOB列先变为小写再创建索引。

--为ENAME创建唯一索引
CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME);  


--为EMP表的SAL列创建普通索引
CREATE INDEX IDX_SAL ON EMP(SAL);


--将JOB转成小写再为函数创建索引
CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB));

语法结构：删除索引

DROP INDEX 索引名；

CREATE TABLE T (ID INT);
CREATE INDEX IND_1 ON T(ID);

BEGIN
  FOR I IN 1..1000000 LOOP
    INSERT INTO T VALUES (I);
    IF MOD(I, 100) = 0 THEN
       COMMIT;
    END IF;
  END LOOP;
END;

ANALYZE INDEX IND_1 VALIDATE STRUCTURE;
SELECT NAME, HEIGHT, PCT_USED, DEL_LF_ROWS/LF_ROWS FROM INDEX_STATS;

DELETE T WHERE ROWNUM<700000;
ANALYZE INDEX IND_1 VALIDATE STRUCTURE;
SELECT NAME, HEIGHT, PCT_USED, DEL_LF_ROWS/LF_ROWS FROM INDEX_STATS;

ALTER INDEX IND_1 REBUILD       [ONLINE] [TABLESPACE NAME];