索引

索引
主键字段，唯一约束字段，子表的外键约束字段上，需要有索引；

数据表空间：块-区-段-表空间，块-数据文件-表空间

全表扫描：为
1、什么是全表扫描?
从端头读取到低高水位位置，进行全表扫描：

高水位：一个段中由多个区，高水位仅仅是为了标示目前块已经使用到哪里了；
全表扫描就是扫表表中所有的行，实际上是扫描表中所有的数据块，因为Oracle中最小的存储单位是Oracle block。
扫描所有的数据块就包括高水位线以内的数据块，即使是空数据块在没有被释放的情形下也会被扫描而导致I/O增加。
在全表扫描期间，通常情况下，表上这些相邻的数据块被按顺序(sequentially)的方式访问以使得一次I/O可以读取多个数据块。
一次读取更多的数据块有助于全表扫描使用更少的I/O，对于可读取的数据块被限制于参数DB_FILE_MULTIBLOCK_READ_COUNT。

2、何时发生全表扫描?
a、表上的索引失效或无法被使用的情形(如对谓词使用函数、计算、NULL值、不等运算符、类型转换)
b、查询条件返回了整个表的大部分数据
c、使用了并行方式访问表
d、使用full 提示
e、统计信息缺失时使得Oracle认为全表扫描比索引扫描更高效
f、表上的数据块小于DB_FILE_MULTIBLOCK_READ_COUNT值的情形可能产生全表扫描

维护数据库数据完整性的技巧；
理解索引概念，会建立索引；
管理oracle的权限和角色；
加快查询速度
索引的种类：b-数索引、位图索引、反向键索引、函数索引、分区索引、聚集索引
oracle 访问数据的3中方式：全表扫描、使用索引、若oracle使用索引则会使用rowid来访问数据；

1.b树索引：由3个基本组成部分：根节点、支节点、叶节点 为oracle默认索引、其中叶子节点为rowid，指向改行的物理位置，叶子节点为双向链表，包含上一个和下一个的指针，便于搜索，oracle不会对索引列上包含的null或非null值的行进行索引当发生数据更新时，只从叶子节点进行改变，所以更新速度会快，不记录空值，当使用is null时不可以使用索引；

建立b-数索引
create index emp_idx on tbal(job);
建立多列（非唯一）索引;
create index emp_idx on tab(jog,date);
查看多列索引信息视图：
user_ind_columns;
select index_name,table_name,column_name from user_ind_columns ;

如
select from customer where name=’sp’;
因为经常根据name进行搜索，所以在name列建立索引；
create index indexname on customer (name);
删除索引：
drop index indexname;
复合索引：
select from customer where name=’sp’ and job=’ee’;

create index indexname on customer (job,name复合索引（也称为级联索引）是一个在表中创建多个列的索引。在一个组合索引中的列可以出现在任何顺序中，不需要在表中相邻。

复合索引可以加快检索的数据，选择语句中的子句引用，或在复合索引中的列的主要部分。因此，定义的列的顺序是很重要的。一般来说，最常用的访问或最有选择性的列第一。

select * from xiaona where sal>2000 order by id asc;
按照升序建立索引：
create index indexname on xiaona (sal asc);
set autot trace exp; —查看执行计划；
会影响性能，是根据sal先建立索引，再进行排序；

create index indexname on xiaona(name) unusable;
建立索引不可用，为不可用，之后将数据导入，再讲索引重建；
select index_name,status from user_indexs;

alter index xiaona(name) rebuild;将索引重建，将索引变为可用；

创建文本索引：
create index indexname on xiaona(name) indextype is xiaona.ss;

反向索引

函数索引支持disable,enable,只支持函数索引；

2.位图索引
什么是位图索引
而位图索引主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等),
索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码,
位置编码中的每一位表示键值对应的数据行的有无.一个块可能指向的是几十甚至成百上千行数据的位置.
这种方式存储数据,相对于BTree索引,占用的空间非常小,创建和使用非常快.
当根据键值查询时,可以根据起始Rowid和位图状态,快速定位数据.
相对于b数索引，位图索引占用的空间小，适用于有大量重复数据的表，不适用于有大量更新操作的表，因为存储方式为010101,当有大量更新操作时，会从a块开始查找，等待a查找结束后，再从b块开找，所以速度会变慢，不适用大量dml操作
使用位图索引标识被索引的列值，不适用于大量更新操作的数据仓库，因为使用位图索引对每个位图索引项与表中大量的行有关联，当表中有大量的数据插入时有大量的位图变动导致不适用可以使用空值当使用is null时可以使用索引；

select * from emp where job =’CLECK’;
job 唯一值少，可建立位图索引；
create index indexname on emp(job);B树索引
create bitmap index indexname on emp(job);位图索引
查看位图索引：select index_type,table_name,status from user_indexes where index_name like ‘%emp%’;
其中index_type 为bitmap 则为位图索引；

3.反向键索引
create index emp_reverse_index on emp(job) reverse;

4.hash索引
使用hash索引，必须使用hash cluster 查询匹配hash列和确切的值，最后确定行和物理存储位置；

5.函数索引

如果查询where子句中有函数存在，则使用函数索引会加快查询速度；

create index emp_idx on dept(upper(name));
此时使用查询视图user_indexes 中index_type 为function_based_normal,即为函数索引；
此时使用查询
select * from dept where (upper(name))=’AAA’;

6.分区索引
本地索引 create index emp_idx on products(pro_date) local tablespace tbs;
特点：分区表与索引一一对应的，即每一个分区表的索引类与该分区表在同一个分区的存储，使得索引和基表得以均匀分布，如果修改了基表分区，则oracle自动维护对应的索引分区，如果索引的目的是查询则多使用本地分区索引；

全局索引：全局索引的可以分区，也可以不分区，索引和基表可以不存在一一对应关系；

监控索引：
select index_name from user_indexs;
开启：alter index indexname monitoring usage;
终止：alter index indexname nomonitoring usage;

select * from v$object_usage;

索引重命名：
alter index indexname rename to index_new_name;

分区表上的索引：
全局索引、本地索引；global,local9全局索引，只有一个段，本地索引，分区的数量=段的数量；
如全局索引，增加分区，就需要耗时很长时间，所以在分区表上建立索引，大部分使用本地索引；
首先查看分区在哪个列上；
desc user_part_key_columns;
select name,column _name from user_part_key_columns;
create index indexname on xiaona(name) local;

使用全局索引，索引键值必须和分区键值相同，这就是所谓的前缀索引。Oracle不支持非前缀的全局分区索引，如果需要建立非前缀分区索引，索引必须建成本地索引。
http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#i5671
：直接重建索引不影响客户使用索引，但是影响dml,ddl操作；
alter index emp_ename_bitmap_idx rebuild;
重建索引并迁移表空间：
alter index dept dname idex rebulid tablespace index;
使用user_indexes验证其重建索引有效性及表空间迁移变化；
select index name,table_name ,tablespace_name .status from user_index;
其中status为valid说明索引有意义；
联机重建索引：不影响客户使用索引及dml操作，但影响使用ddl操作；
alter index dept dname idx rebuild online;

删除索引：
drop index dept_dname_idx;

使用索引原则：
1.在大表上建立才有意义；
2.在where子句经常引用的列才有意义；
3.索引的层次不要超过4层；

缺点：
1建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保护索引。
2更新数据时候，系统必须要有额外的空间来同时对索引进行更新，以维持数据和索引的一致性；
实践表明，不恰当的索引不但于事无补，反而会降低系统性能；

比如在如下字段建立索引应该是不恰当的；
1。很少或从不引用的字段；
2。逻辑性字段，如男或者女，等；

按照数据存储方式：
分为：b*树、反向、位图索引；
b树索引，根、分支、叶，每个叶反映到一行；
根据索引列的个数分类：
分为：单列索引、复合索引；
按照索引列唯一值：
分为唯一索引、非唯一索引；
此外还有函数索引、全局索引、分区索引、压缩索引等；

显示所有索引信息：
在一张表上可以有多个索引，通过查询数据字典视图，dba_indexes,和user_indexes,可以显示索引信息，其中dba_indexes用于显示
数据库所有索引信息，而user_indexes 用于显示用户索引信息；

select index_name,index_type from user_indexes where table_name=’’;

显示索引列：
通过查询数据字段视图 user_ind_columns ,可以显示索引对应的列的信息；
select table_name,column_name,from user_ind_columns where index_name=’IND_EAM’;

也可以通过pl/sql等工具查看索引信息；

三、
使用表和索引压缩，
为了增强sql查询性能，可以对表或物化视图进行压缩，减少查询时间；
思想：即将表中重复的数据去掉，采用算法来替换这些重复值，在需要时通过算法重建这些重复的数据，以实现压缩；

压缩表：create table emp compress tablespace users as select * from table;
可以通过查看视图user_tables 查看表是否启动压缩；
创建压缩索引：
create index emp_idx on emp(job) compress;

四：详解v$sql视图
v$sql 听过详细的sql代码执行结果信息，可以查看判断效率低下或者耗费资源过多的sql语句，盖世兔保留实例启动以来的所有sql语句；
select sql_text，executions(语句执行的时间量），disk_reads from v$sql;

查询逻辑读取次数最多的sql语句；
select sql_text,buffer-gets(从缓冲区读取），parse-calls from v$sql；

五、sql语句查询需要装trace或者explaine;
其中set autotrace traceonly 跟踪语句执行情况；

分区索引字典
DBA_PART_INDEXES 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类新(local/global,)
Dba_ind_partitions每个分区索引的分区级统计信息
Dba_indexesminusdba_part_indexes，可以得到每个表上有哪些非分区索引

http://docs.oracle.com/database/121/CNCPT/schemaob.htm#CNCPT312