oracle 索引（index）原理、使用、分类、注意事项

注意点
① 索引在大表中才有意义
②在经常出现在条件中的字段上建立索引
③索引层次不要超过4层
④ 索引要占用大约为表的1.2倍的硬盘和内存空间。
⑤更新数据时，系统必须要花费时间来更新索引，不恰当的索引将降低系统性能。如：在比较少用的字段上建立索引；在逻辑型的字段或者值只有固定几种的字段上建立索引。
⑥ 经常联合的字段要创建索引
⑦复合索引创建中，经常使用的列要放在前面
⑧复合索引使用时，至少要包含第一列，WHERE 条件中只有第一列索引被使用了，优化器才会使用索引。
⑨列中有很多空值，但会经常查询非空值需要建立索引。

索引上避免使用项
①索引上避免使用非算式符如：not 、 ! 、<> ,当使用这些符号时，优化器会跳过索引，遍历全表进行查询。
②避免在最前面使用通配符。
③避免在索引上使用null 比较。

**索引原理 **
当没有索引时在使用查询 例如 name = ‘字段值’ 时会遍历所有行进行对比，当使用索引时，数据库将 name 字段的值+rowid 构建成索引条目，并按升序排列，存储到索引段中，查询时即可以直接查找到对应的地方。

索引使用

1. 创建单列索引

CREATE INDEX 索引名称 ON 表名(列名);

2. 创建 复合索引

CREATE INDEX 索引名称 ON  表名(列名1，列名2....);

3. 修改索引
   a) 重名命

   ALTER INDEX 索引新名称 RENAME TO 索引原名称;
   

   b）合并索引，索引在使用一段时间后会产生碎片，可以重建索引和合并索引，合并索引更加方便无需额外存储空间，成本低。

   ALTER INDEX 索引名称 COALESCE;
   

   c) 重建索引

   ALTER INDEX 索引名称 REBUILD;
   

   d)删除索引

   DROP INDEX 索引名称;
   

   e) 查看索引

   SELECT * FROM ALL_INDEXES  WHERE TABLE_NAME= 表名;
   

索引分类
1. B树索引
默认使用，由二叉树构成，叶节点（双向链表结构）存储索引列和rowid；
所有叶节点深度相同，不管条件是什么，查询速度基本相同；
能适应精准查询、模糊查询、比较查询；
适合使用场景：列基数（列不重复值的个数）大时适合使用B数索引
2. 位图索引
适合场景：对于基数小的列适合简历位图索引（例如性别等）
3. 单列索引/复合索引
4. 函数索引
当某些表达式或函数经常使用时，可以将其存入索引中；结果明确的函数放到位图索引中，结果明确的放入B树索引中；索引函数中可以使用每行返回独立结果的函数，如：upper，trim，len，substr，不能使用sum、max、min、avg等。
例：

CREATE INDEX  索引名称 ON 表名 （UPPER( 列名)）; -- 创建
SELECT * FROM 表名 WHERE UPPER(列名) ='WISH';  -- 使用