《index篇》
原文链接：https://blog.csdn.net/weixin_45629994/article/details/127227870
索引的目的
提高查询效率
索引分两种
- 聚集索引(物理):
一个表中只能有一个聚集索引
一般是 id 自增长 - 非聚集索引(逻辑):
索引的缺点
增加额外的存储空间
降低增删改的效率
什么是索引
当对该表进行指定条件的查询时，会将所有的记录都读取出来，一条条与查询条件进行比较，这样会导致时间和I/O开销都非常大。有了索引之后，只需要在索引中找到符合查询条件的索引值字段，就可以通过保存在索引中的ROWID快速找到表中对应的记录。（将表看作一本书，索引就是目录，没有目录你就要一页一页翻，有了目录，根据目录中标记的页码（ROWID）去指定页中寻找你想要的内容即可，懂了吗？😁）
个人总结的建索引原则
建立和规划索引时，必须选择合适的表和列，如果选择的不合适，可能会出现抱着木柴救火的绝美画面，不仅无法提高查询速度，反而会极大降低DML操作的速度，我总结出建立索引需要注意的几点：
- 索引应该建立在where语句频繁引用的表的列上。
- 索引应该建立在经常需要基于某列或某几列排序的表的列上。
- 一个表中的索引不是越多越好。索引主要用于加快查询速度，但索引越多会导致DML的速度越慢，所以索引的个数需要你好好权衡一下。
- 指定索引块空间的使用参数。基于表建立索引时，Oracle 会将相应表的列数据添加到索引块中。为索引块添加数据时，Oracle会按照PCTFREE参数在索引块中预留部分空间，该预留空间是为将来的INSERT操作准备的。如果将来在表上执行大量INSERT操作，那么应该在建立索引时设置较大的PCTFREE。
- 将表和索引部署到相同的表空间中，可以简化表空间的管理。将表和索引部署到不同的表空间中，可以提高访问性能。
- 当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。
- 最好不要在小表上建立索引，杀鸡何须宰牛刀？
- 多表连接时应在连接列上建立索引。
建立索引和index分类
Oracle创建索引的流程是，首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录ROWID存储在索引段中。
Oracle索引也分几个类型，
- 按照索引数据的存储方式可以分为B树索引、位图索引、反向健索引和基f函数的案引；
- 按照索引列的唯性又可以分为唯有和非唯索引；
- 按照索引列的个数又可以分为单列索引和复合索引，下面我就分类讲解一下各个索引。
B树索引
B树索引是Oracle默认的索引类型，它以B树结构组织并存储索引数据，不懂什么是B树的可以百度一下。B树索引中的数据是以升序方式排序的，B树索引由根块、分支块和叶块组成，其中主要数据都集中在叶子节点上。
Oracle采用这种方式的索引，可以确保无论索引条目在何处，只需要花费相同的I/O即可获取它，所以它被称为B（Balanced）树索引。如果在Where子句中要经常引用某列或某几列，应该基于这些列建立B树索引。代码如下：
create index tiger_index on tigerlearn(tiger)
pctfree 25 --可以默认不写
tablespace Tiger_TS;
为tigerlearn表的tiger列创建索引tiger_index，子句pctfree指定为将来insert操作所预留的空闲空间，子句tablespace用于指定索引段所在的表空间。假设表已经包含了大量数据，那么在建立索引时应该仔细规划pctfree的值，以便为以后的insert操作预留空间。
位图索引
在B树索引中，通过在索引中保存排序的索引列的值以及记录物理地址ROWID来实现快速查找，但对于一些特殊的表，B树索引效率较低，比如一个表中有一列，数据只有正确、错误两个取值，如果建立B树索引，使用该索引对表进行检索时，将返回近一半的数据，效率非常低下，这种情况我们定义一个变量“基数”，比如一个表有10000行数据，一列只有100个取值，基数为1%。Oracle推荐，当一个列的基数小于1%时，适合建立位图索引。代码如下：
create bitmap index tiger_index_bitmap on tigerlearn(tiger)
tablespace Tiger_TS;
初始化参数CREATE_BITMAP_AREA_SIZE用于指定建立位图索引时分配的位图区大小，默认值为8MB，该参数值越大建立位图索引的速度就越快。修改该参数的代码如下：
alter system set create_bitmap_are_size = 8388608
scope = spfile
反向键索引
Oracle会自动为表的主键创建B树索引，通常主键会是一个递增的序列编号，如果使用默认的B树索引，当数据量巨大时会导致索引数据分布不均，叶子节点可能会偏向某一个方向，这时就需要另一个索引机制，反向键索引，它可以将添加的数据随机分散到索引中，它在顺序递增列上建立索引时非常有用。反向键索引的工作原理非常简单，在存储结构方面它与常规的B树索引相同。然而，如果用户使用序列在表中输入记录，则反向键索引首先指向每个列键值的字节，然后在反向后的新数据上进行索引。例如，如果用户输入的索引列为2011,则反向转换后为1102; 9527 反向转换后为7259。需要注意的是，刚才提及的两个序列编号是递增的，但是当进行反向键索引时却是非递增的。这意味着，如果将其添加到子叶节点中，则可能会在任意的子叶节点中进行。这样就使得新数据在值的范围上的分布通常比原来的有序数更均匀。代码如下：
create bitmap index tiger_index_reverse on tigerlearn(tiger) reverse
tablespace Tiger_TS;
创建这个函数索引后，如果在查询条件中包含相同的lower函数，则会提高查询的执行效率，如果没有创建函数索引，而是创建了普通的B树索引，在查询中使用lower函数会执行全表进行搜索，因为lower数据不在表中也不在索引中。
修改、删除、查询索引
修改索引
对一个表建立索引后，对表的DML操作不断增加，索引中会产生越来越多的存储碎片，会拉低索引的工作效率，可以采用两种方式来清除碎片，重建索引或合并索引，重建索引就是简单的新建一个索引，然后把原来的索引删除。
合并索引代码：
alter index tiger_index coalesce deallocate unused;
重建索引可重新指定索引对象的表空间，代码如下：
alter index tiger_index rebulid
tablespace emp;--可以不写，默认原来的表空间
删除索引
删除索引的方式与索引的创建方式有关，如果使用create index语句显式地创建该索引，则可以用drop index语句删除该索引。
drop index tiger_index
如果索引是在定义约束时由Oracle自动创建的，则必须禁用或删除该约束本身。
显示索引信息
通过查询数据字典视图dba_indexes,可以显示数据库中的所有索引;
通过查询数据字典视图all_indexes,可以显示当前用户可访问地所有索引;
通过查询数据字典视图user_indexes,可以显示当前用户地索引信息。
通过查询数据字典视图dba_ind_columns,可以显示所有索引地表列信息；
通过查询数据字典视图all_ind_columns,可以显示当前用户可访问地所有索引地表列信息；
通过查询数据字典视图user_ind_columns,可以显示当前用户索引地表列信息。
建立索引时，Oracle会为索引分配相应地索引字段，索引数据被存储在索引段中，段名与索引名完全相同。
通过查询索引段视图user_segments，可以显示当前用户所拥有的段分配的信息。
通过查询数据字典视图dba_ind_expressions,可以显示数据库中所有函数索引所对应的函数或表达式。
通过查询数据字典user_ind_expression,可以显示当前用户的所有函数索引所对应的函数或表达式。
e.g.
select index_name,index_type
from dba_indexes
where owner = 'tiger'
