oracle-dba-索引

索引
全表扫描：通过多块读操作读取，大大减少io次数和提高全表扫描速度，只有在全表扫描的时候才能使用多块读。
通过Explain plan解释执行计划
Explain plan for select * from dual;
select * from table(dbms_xplan.display);

通过行id(ROWID)读取
采用单快读方式。
通过执行计划确定单块读
Explain plan for select * from dept where rowed=’AAA’;
Select * from table(dbms_xplan.display);

索引读取
扫描分为2步
扫描索引得到相应的rowid，逻辑读
通过rowid从表中读取相应的数据，物理读
单块io读，因为索引小且经常使用，通常被cache。
因为涉及到逻辑读和物理读，所以性能较低
改进性能取决2参数
1：数据的选择性
2：表数据在数据块上的分布
选择性高，查询的结果数据越少，选择性地，则相反。
相关的行在表中的存储位置不靠拢，会减少索引的益处。也就是看数据块的分散程度

增加索引会导致Insert，delete性能下降，update性能提升。
通过执行计划，判断索引作用
Explain plan from select * from emp where empno=10;
Select * from table(dbms_xplan.display);

索引扫描类型
索引唯一扫描
通过唯一键，主键，oracle通常返回一个数据行
Explain plan for select * from emp where empno=10;
Select * from table(dbms_xplan.display);
索引范围扫描
如下情况oracle会使用范围扫描
 唯一键上使用range操作符(>,<,>=,<=,between)
 在组合索引上，只使用部分列进行查询，导致查询很多行
 对非唯一索引列上进行查询
Explain plan for select * from emp where empno>10;
Select * from table(dbms_xplan.display);

索引全扫描
当使用索引全扫描时，查询出的数据必须全部从索引中得到
Explain plan for select * from emp;
Select * from table(dbms_xplan.display);

索引快速扫描
扫描索引块中的所有数据块，不进行数据排序，此种方式下，可以使用多块读功能，并行读功能，最大化数据的吞吐量
BE_IX索引是一个多列索引
Explain plan for select name from emp;
Select * from table(dbms_xplan.display);

限制索引使用的情况
使用<>,!=会导致索引失效
Explain plan for select * from emp where empno<>10;
Select * from table(dbms_xplan.display);

使用is null或者is not null
创建索引
Create index idx_sal_emp on emp(sal);
根据表名查询表中所有索引
Select column_name from user_ind_columns where table_name=’EMP’;
Explain plan for select empno,ename,deptno from emp where sal is not null;
Select * from table(dbms_xplan.display);
建议要是想用索引的话，对列添加not noll或者default方式。

使用函数
不使用基于函数的索引，在where子句中对存在索引的列使用函数，会导致优化器忽略索引。常见的函数如trunc，substr，to_date，to_char
Explain plan for select * from emp where trunc(hiredate)=’03-12月-81’；
Select * from table(dbms_xplan.display);
避免方案
1：使用基于函数的索引
2：避免索引列上使用sql函数
如：
Explain plan for select * from emp where hiredate>’03-12月-81’ and hiredate <(todate(‘03-12月-81’)+0.99999);

比较不匹配的数据类型
Explain plan for select name from user where name =123;
这里自动加函数to_number(name)，对于不匹配的数据类型，oracle隐式转换。之所以不加到123上，是因为123是常量，常量不能改变。
解决方案
name =’123’;保证类型一致即可

集群因子
优化器决定是否使用索引取决2因素
 选择性：选择性高意味着不同的值越多，使用索引返回的值越少
 集群因子：是索引与他所基于的表相比得出的有序性度量，用于检查在索引访问之后执行的表查找的成本，使用索引访问数据之后的表查找成本计算依据。
集群因子含义：如果通过一个索引扫描一张表，需要访问表的数据块的数量
计算方法如下
1. 扫描一个索引
2. 比较某行的rowid和前一行的rowid，如果这2个rowid不属于同一个数据块，集群因子+1
3. 整个索引扫面完毕后，得到该索引的集群因子
如果集群因子接近表存储的块数，则说明这张表是按照索引字段的顺序存储的。如果集群因子接近行的数量，说明这张表不是按索引字段顺序存储的。
集群因子*选择性参数=访问索引开销。
如果一个表大多数访问时按照某个索引做索引扫描，将该表的数据按照索引字段的顺序重新组织，可以提高该表的访问性能。

二元高度
索引查找2过程
1. 根据树定位，找出rowid(索引查找)
2. 根据rowid找出表中的数据行(表数据查找)
进行索引查找的时，首先从树根开始读数据，通过中间节点，最后定位到叶节点，整个过程只能进行单数据块的读取。
每定位一次就读取一个数据块，定位次数即是二元高度。
查看索引2元高度
Select blevel ,index_name from user_indexes where index_name=’emp_depa’;
影响2元高度因素：表中索引列的非null值/索引列中值的宽度
删除行，索引2元高度不影响。重建会降低2元高度。

直方图
作用：在分析表和索引时，记录数据分布。通过该信息，基于成本的优化器可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。
使用范围：表的任何列上构建。通常索引列上构建。
原因：帮助优化器在表中数据严重倾斜时做出更好的规划。如果一个表中的列(通常索引列)数据发生严重的倾斜，在这个列上构建直方图，能够给予优化器更好的参考意见。

在sys用户下面的temp_dept表的name列上建立直方图，这个直方图有10个桶
Exec dbms_stats.gather_table_stats(‘SYS’,’TEMP_DEPT’,METHOD_OPT=>’FOR COLUMNS SIZE 10 name’);
将整个列上所有行数据分成10个存储桶。
默认直方图会产生75个存储桶，范围1~254。
创建索引
CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name on [schema.]table_name
(column_name[DESC|ASC][,column_name[DESC|ASC]]…..)
[REVERSE]
[TABLESPACE tablespace_name]
[PCTFREE n]
[INITRANS n]
[MAXTRANS n]
[instorage state]
[LOGGING|NOLOGGING]
[NOSORT]
 Unique：索引唯一
 Bitmap：创建位图索引
 Desc|asc:索引降序or升序
 Reverse：创建反向键索引
 Tablespace：指定表空间
 Pctfree：索引块中预先保留的空间比例
 Initrans：每一个索引块中分配的事务数
 Maxtrans：每个索引块中分配的最大事务数
 Instorage state:索引中区段extent如何分配
 Logging|nologging：说明”要记录|不记录”索引相关的操作。并保存在联机重做日志中。
 Nosrt：不需要在创建索引时在按键值进行排序

Create index emp_ename_idx on emp(ename);
使用数据字典查看索引信息
Select index_name,index_type,table_name,tablespace_name from user_indexes;
查看索引所对应的表空间信息
Select a.index_name “索引名” ,a.tablespace_name “索引对应的表空间名” ,
b.file_name “索引对应的磁盘文件” from dba_indexes a, dba_data_files b where a.tablespace_name=b.tablespace_name ;
创建索引空间
Create tablespace index_tbs datafile ‘路径’ size 100m aotoextend on;
创建表多列索引并指定索引空间
Create index emp_ename_sal_idx on emp(ename,sal) tablespace index_tbs;
查看多列索引的信息
Select index_name,table_name,tablespace_name from user_indexes where index_name like ‘emp’;
查看索引是建立在表的哪几列,使用数据字典USER_IND_COLUMNS

查看索引
查询与索引列相关的信息,索引对应的表名，索引基于那些列创建
Select index_name,table_name ,column_name from user_ind_columns where index_name like ‘emp%’;
查看索引信息
Select index_name,table_name,table_owner,dropped,tablespace_name from user_indexes where index_name like ’表名%’;
Dropped:是否删除。

B树索引
Oracle默认索引类型,可以单列也可以是组合索引(max 32col)。保存在索引列上有值的每个数据行的rowid。
oracle不会对索引列上包含null值的行进行索引，如果索引是一个组合索引，而其中列上包含null，这一行会包含索引列中
B树索引结构，根节点，分支节点，叶子节点。
叶子节点：存储实际的索引列的值和该列所对应的记录的行id(rowid)。Row是oracle唯一的指针，指向该行的物理位置。双向链表。
分支节点：包含其他分支节点。

位图索引
Oracle11g enterprise edition支持
使用位图标识被索引的列值
适用：没有大量更新任务的数据仓库，因为使用位图索引时，每个位图索引项与表中大量的行有关联。当表有大量数据更新操作时，位图索引相应的需要做大量修改。而且索引所占用的磁盘空间也会增加。并且索引在更新时受影响的索引需要锁定，所以位图索引不适合有大量更新操作的oltp系统。虽然可以通过重建索引类位图索引。
场合：列值的数量要求较少|中等(索引列基数较小)，就是表行记录少。
创建位图
Create bitmap index emp_job_bitmap_idx on emp(job);
使用位图索引插入数据时注意：
1. 位图索引在批处理(单用户)操作中加载表(插入操作)方面比b树好
2. 当有多个会话同时向表中插入数据行时不应该用位图索引
3. 每条记录都增加新值时，b树索引比位图快3倍。

位图中因为对rowid进行压缩存放(一个rowid范围+位图)，所以每次锁定的都是整个rowid位图，因此对表的位图索引进行更新的时，并发性差容易死锁。
位图索引限制：
1. 基于规则的优化器不会考虑位图索引
2. Alter table语句并修改包含位图索引的列时，位图索引失效
3. 位图索引不包含任何列数据，不能用于任何类型的完整性检查，如主键，唯一键约束。
4. 不能被声明为唯一索引
5. 位图索引最大长度30
HASH索引
Hash索引必须要使用hash cluster。
非常适合做数据仓库(相对静态值)；
在存储数据时，所有与这个集群键相关的行都存储在一个数据块上，如果数据都存储在同一个数据块上，并且将hash索引作为where子句的确切匹配条件，oracle就可以通过执行一个hash函数和一个io来访问数据。
Select * from table_name where col=？ 散列函数[hash键|集群键|数据]
查询过程变换为等价查询，最后匹配hash列和确切值，最后基于hash函数确定行的物理位置。

创建cluster表
Create cluster credit_cluster(
Card_no varchar2(16),
Transdate date sort
)hashkeys 100000 hash is ora_hash(card_no) size 2186;

Create cluster credit_orders(
Card_no varchar2(16),
Transdate date sort,
Amount number
)cluster credit_cluster (Card_no, Transdate);
Hash列为表1的card_no，集群键列为表2的Card, Transdate

Hash索引可能是最快的访问数据库的方法，但是其本身也有缺点
 集群键上不同值数目必须在创建hash集群之前先确定，需要在创建hash集群时候指定这个值，低估集群键不同值的数值可能会导致集群的冲突(2个集群键有相同的hash值)
 一旦值设置过低，需要从重建cluster。
 Hash集群可能浪费空间，无法确定需要多少空间来维护某个集群键上所有的行，会造成空间浪费
 不能为集群的未来增长分配好附加空间，hash集群不是最好的选择
 应用程序经常进行全表扫描，hash集群不是最好的选择。由于需要为未来分配好集群的剩余的空间，全表扫描非常浪费资源。

反向键索引
作用：指在创建索引过程中对索引列创建的索引键值的字节反向
好处：将值连续插入到索引中时反向键能避免争用
场景：如果一个索引值是按照序列值递增，那么连续插入大量数据时，所有的记录都将插入B树索引结构中的最右侧的叶子节点，并且会写入同一叶子节点中。难以避免产生争用问题而影响索引性能。引入反向键索引使得每个键值被颠倒顺序。将序列性的键值分散开，使得键值平衡的保存在叶子节点中
键值颠倒如下
LarryyrraL
24688642

创建反向键索引
Create index emp_sal_reverse_idx on emp(sal) reverse;
通过数据字典视图查看反向键索引信息
Select index_name,index_type,table_name from user_indexes where index_name like ’EMP%’;

基于函数的索引
在用户查询数据时，如果查询语句的where子句中有函数存在，oracle使用函数索引将加快查询速度。基于函数的索引使用表的列的函数值作为键值建立索引结构。
创建基于upper函数的函数索引
Create index dept_dname_idx on dept(UPPER(dname));
创建一个基于dept中列dname的函数索引，创建该索引时首先将该列dname中的值转换成大写，然后对大写的dname创建索引，放入索引表。

此时查询时，oracle就不必对where子句的条件做转换并逐条检索，对于选择的结果也不必使用upper函数在做转换的计算。显然此时使用基于函数的索引会极大提高查询速度。
Select upper(dname) from dept where upper(dname) =’NEW YORK’;
查看索引信息，column_name为系统赋予的一个值，因为这个列不是oracle可以使用明确的列名标识的，他是某个列的函数。
SELECT INDEX_NAME,TABLE_NAME,COLUMN_NAME FROM USER_IND_COLUMNS;
注意事项
 基于函数的索引只针对一种函数，对于其余函数不起作用
 控制函数的数量，因为会影响DML

监控索引的使用
创建索引需要监控索引使用情况，这样就能判断创建的索引的有效性。对于无效的索引可以删除，以释放磁盘空间并减少dml操作带来的修改索引的各种开销。
启动对索引的监控
Alter index emp_ename_bitmap_idx monitoring usage;
等待用户对表emp的各种操作，对于OLTP(联机事务处理)系统这个周期可能很短，对数据仓库则需要更多时间来监控。
终止监控索引
Alter index emp_ename_bitmap_idx nomonitoring usage;
查看索引使用情况
Select index_name,table_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;
Mon:是否监控索引
Used：是否使用索引
Start/end_monitoring:监控开始/结束时间

重建索引
在删除操作后，删除值所占用的索引空间不能被索引自动重新使用，而插入操作后会不断使得索引变大。
重建索引并迁移表空间，使得索引空间可以重用删除值所占用的空间，使索引更加紧凑,不影响用户使用索引，但是不能使用ddl操作和dml。
Alter index emp_ename_bitmap_idx
rebuild //此处添加 online 的话说明联机重建索引，并且可以使用dml。
tablespace index_tbs1
pctfree 30
storage (next 100k)
;
查看索引名，表名，表空间名，状态。Valid表示有效
Select index_name,table_name,tablespace_name,status from user_indexes;

维护索引
查看当前索引的参数设置
Select index_name,pct_free,pct_increase,initial_extent,next_extent from user_indexes;
修改pctfree, storage 参数
Alter index emp_ename_bitmap_idx
pctfree 30 单位%。
storage (next 100k) next_extents参数修改无效，因为索引存储的表空间是本地管理。
;

手工增加索引磁盘空间
Alter index emp_job_bitmap_idx allocate extent;
Oracle对于每个索引默认的extent区段数为1，此时为他增加一个区段。
Select segment_name,segment_type,tablespace_name,extents from user_segments
Where segment_type=’INDEX’ and segment_name like ‘表名%’;

合并索引碎片,释放磁盘空间
Alter index emp_job_bitmap_idx coalesce;

删除索引,并查看是否删除
Drop index 索引名;
Select * from user_indexes where index_name like ‘索引名%’;