13.oracle索引

实在想不到昵称
已于 2023-05-06 10:56:48 修改
阅读量810
点赞数 1
文章标签： oracle 数据库 b树
于 2023-05-06 10:49:34 首次发布
本文链接：https://blog.csdn.net/qq_45298339/article/details/130522203
版权
一、索引介绍
/*索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据；Oracle存储索引的数据结构是B树，位图索引也是如此，
只不过是叶子节点不同B数索引；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含
索引数据和确定行实际位置的rowid。*/
使用索引的目的
当查询返回的记录数排序表<40%非排序表 <7%且表的碎片较多（频繁增加、删除）时可以加快查询速度减少I/O操作消除磁盘排序

二、索引的分类及结构
1、逻辑上：
Single column/Concatenated 单行索引/多行索引
Unique/NonUnique 唯一索引/非唯一索引

2、物理上：
          B-tree B树索引即平衡树索引
          Bitmap 位图索引
          REVERSE 反向索引
          HASH索引
          Function-based基于函数的索引
          Partitioned/NonPartitioned 分区索引/非分区索引
          Domain 域索引

三、各种索引详解
/*1、 B树索引
    Oracle数据库中最常见的索引类型是b-tree索引，也就是B-树索引，以其同名的计算科学结构命名。CREATE INDEX语句时，
  默认就是在创建b-tree索引。没有特别规定可用于任何情况。
（1）特点：
    适合与大量的增、删、改（OLTP）
    不能用包含OR操作符的查询；
    适合高基数的列（唯一值多）
    典型的树状结构；
    每个结点都是数据块；
    大多都是物理上一层、两层或三层不定，逻辑上三层；
    叶子块数据是排序的，从左向右递增；
    在分支块和根块中放的是索引的范围；

（2）技巧：
    索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以直接满足查询，
  而不用访问表。这就不用从表中检索数据，从而减少了I/O量。

2、位图索引
    位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的
  表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列，但通常它们都只用于
  少量的列。
  例如，您的表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据Sex列的值查
  询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可
  用的位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。
（1）特点：
    适合与决策支持系统；
    做UPDATE代价非常高；
    非常适合OR操作符的查询；
    基数比较少的时候才能建位图索引；
（2）技巧：
    对于有较低基数的列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。
    当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。
    在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。

3、 反向索引
      这个索引不常见，但是特定情况特别有效，比如一个varchar(5)位字段(员工编号)含值
    （10001,10002,10033,10005,10016…）
    这种情况默认索引分布过于密集，不能利用好服务器的并行
    但是反向之后10001,20001,33001,50001,61001就有了一个很好的分布，能高效的利用好并行运算。
  （1）特点：
      不可以将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。
  （2）技巧：
      如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。

5、函数索引
    可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。
  下面的查询就不能使用JOB列上的索引，除非它是基于函数的索引：*/

索引的分类：
按存储形式（即索引中存储的内容不同）：
    1）B-TREE索引   (索引列原始数据+ROWID)
    2）位图索引     (位图+ROWID)
    3）反向键索引    (索引列原始数据的反向存储+ROWID)
    4）基于函数的索引(将索引列原始数据经函数处理后存储+ROWID)
    索引的命名规范：IND_TBNAME_COLNAME
    
1.B-TREE索引 
 （ORACLE的默认索引类型，工作中最常见、使用范围最广的索引）
  语法：
  CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);
  适用场景：列基数比较大的时候使用（行业、身高）
  列基数：该列不重复数据的个数 COUNT(DISTINCT COL)

2.位图索引 （位图+ROWID)
  说明：位图索引在创建时，会扫描整张表，为索引列的每个取值建立一个不重复的位图（BITMAP）来描述该取值
  语法：
  CREATE BITMAP INDEX IND_NAME ON TB_NAME(COL_NAME);
  适用场景：列基数比较小的时候使用（性别、婚姻状况）

3.反向键索引（简称：反向索引）
  说明：可以视作一种特殊的B-TREE索引，存储索引列的反向值
  背景：为防止B-TREE索引在某叶上数据量占比过高而使用的一种索引
  语法：
  CREATE INDEX IND_NAME ON TB_NAME(COL_NAME) REVERSE;
  适用场景：原始数据分支不明显但反向数据分支明显的列（身高：集中在一米七一米八）

4.基于函数的索引
  说明：可以视作一种特殊的B-TREE索引，存储函数处理后的数据
  背景：在某个字段上以原值建立了索引，但是在筛选时在该字段上经常加函数，导致索引无法生效
  语法：
  CREATE INDEX IND_NAME ON TB_NAME(FUNCTION(COL_NAME));
  适用场景：对某列进行筛选时经常需要配合函数使用（例如查找姓名中的首字母）

二、按唯一性（索引列中的数据能否有重复值）
  1.唯一索引 --索引列中不可能出现重复值
  语法：
  CREATE UNIQUE INDEX IND_NAME ON TB_NAME(COL_NAME);
  注意点：
  1）B-TREE索引可以建立唯一索引，位图索引不能建立唯一索引
  2）如果在某列上建立了唯一约束或主键约束，ORACLE会自动在该列上建立一个同名的唯一索引
  2.非唯一索引 --索引列中可能出现重复值
  语法：CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

三、按列的个数（索引覆盖的列的个数）
  1.单列索引  --基于一个列建立的索引
  语法：
  CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);
  2.复合索引(也称为联合索引） --基于两个或两个以上列建立的索引
  语法：
  CREATE INDEX IND_NAME ON TB_NAME(COL_NAME1,COL_NAME2...);

索引建立或使用的规则与建议:
/*1.如果对某大表进行筛选时，某列或某几列频繁出现在WHERE子句中，并且检索出的数据低于总行数的15%（50%），
  应考虑在这些列上建立索引。
2.如果对某大表进行排序时，某列或某几列频繁出现在ORDER BY子句中，应考虑在这些列上建立索引。
3.小表不要建立索引。
4.对于含有空值的列，如果经常在查询时查询非空值，建议在该列上建立索引；如果经常在查询时查询空值，
  建议在该列上建立基于函数的索引。
5.为了提高表连接的性能，应在连接列上建立索引（建立一般普通的索引即可）
6.索引是数据库的一种实体对象，级别类似于表，会占用内存空间，ORACLE会自动进行索引维护，表和索引
  可以建立在不同的表空间。
7.通过索引可以提升数据的查询速度，但是会相对地降低DML语句的操作速度，尤其是插和改的速度，
  ORACLE会花费时间在索引维护上，所以说要把握好索引的数量
8.对于列基数比较大的列，适合B-TREE索引，列基数比较小的列，适合位图索引。
9.对于复合索引，至少要引用到索引列中的第一个列才会使用该索引。
 （该点为早期说法，现行理论有时非第一列查询也能引用，以实际为准）
10.某列可以出现在多个索引中，但相同的某列或某几列无法多次建立索引。 --就是可以有多个组合，
   但不能有重复组合，不同顺序的相同几列视为不同组合
11.索引建立后并不一定会被引用，ORACLE会分析整个SQL后做出最优的执行方式。
12.ORACLE会自动在主键约束和唯一约束列上建立唯一索引。
13.对于一般的B-TREE索引，通配符出现在搜索词的首位时不会引用索引 (有时在首位也会走索引)
14.在索引列上使用<> !=号时，或对空值进行判断时，索引不会生效
*/
索引的删除语法：
  DROP INDEX IND_NAME;
禁用索引的语法：
  ALTER INDEX IND_NAME UNUSABLE;
重建索引的语法：
  ALTER INDEX IND_NAME REBUILD;
注意！【插入完成后统一维护索引】比【一边插入一边维护】的速度要快！

相关数据字典
所有索引
SELECT INDEX_NAME,     --索引名称
       INDEX_TYPE,     --索引类型
       TABLE_NAME,     --表名
       UNIQUENESS,     --是否唯一
       STATUS,         --索引状态  VALID 可用的 UNUSABLE 不可用的
       TABLESPACE_NAME,--表空间
       LOGGING         --是否记录日志
  FROM USER_INDEXES
 WHERE 1=1
   AND INDEX_NAME = 'IND_EMP_DEPTNO'
   AND TABLE_NAME = 'EMP';
   
索引函数
SELECT INDEX_NAME, --索引名称
       TABLE_NAME, --表名
       COLUMN_EXPRESSION --列表达式
  FROM USER_IND_EXPRESSIONS
  WHERE INDEX_NAME ='IND_EMP_UPENAME';
  
  
  
  
四、索引创建完整版
CREATE UNIQUE | BITMAP INDEX <schema>.<index_name>
      ON <schema>.<table_name>
           (<column_name> | <expression> ASC | DESC,
            <column_name> | <expression> ASC | DESC,...)
     TABLESPACE <tablespace_name>
     STORAGE <storage_settings>
     LOGGING | NOLOGGING
     COMPUTE STATISTICS
     NOCOMPRESS | COMPRESS<nn>
     NOSORT | REVERSE
     PARTITION | GLOBAL PARTITION<partition_setting>

UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。
<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”
TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
STORAGE：可进一步设置表空间的存储参数
LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
COMPUTE STATISTICS：创建新索引时收集统计信息
NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区