ORACLE读数据的两种方式：全表扫描、按rowid读一行；索引对性能的提高取决于数据的选择性和分布性，也即使用索引返回的数据量的多少和数据分散程度。全表扫描是多块读取，基于索引是单块读取。
二、索引对sql语句的影响
select语句、delete命令和update命令的where子句可以借助索引提高性能，但索引列的update命令降低性能，大量行的delete操作也会降低性能。insert语句因为增加索引会降低性能。多列和单列索引对删除操作的性能差别不大。故性能优化要平衡索引对查询的性能提升和对数据修改的性能影响。
三、索引常用视图
1、dba/user/all_indexes 2、dba/user/all_ind_columns
四、创建及使用不可视索引、不使用索引的方法
1、创建及修改不可视索引
create index indx on tb_1(col1,col2,col3) invisible;
alter index indx invisible/visible;
2、使用不可视索引
select /*+ USER_INVISIBLE_INDEXES */ count(*) from dept_rich wher deptno=30;
或将初始化参数optimizer_use_invisible_indexes设置为true;
3、不使用索引
a、使用no_index hint提示强制不使用索引
select /*+ no_index (dept_rich(tab_name),dept_rich_inv_idx*/ count(*) from dept_rich where deptno=30;
b、创建不可视索引
五、组合索引
因为索引具有跳跃式扫描特性，所以组合索引应谨慎选择索引列的顺序。
没有引入跳跃式索引扫描前，除非where使用索引的前导列，否则不会使用索引。
跳跃式索引扫描即使where没有指定前导列，优化器也有可能使用索引，或者索引快速全扫描或者全表扫描。
六、索引抑制
1、索引只能指示已存在的数据，故where子句中使用不等于运算符时，被引用的列的索引无法使用
2、like运算符中只有 like ‘somevalue%'时，即最开始的值不含匹配符时才能使用索引
3、B树索引不对NULL值索引，故where子句中IS NULL和IS NOT NULL时oracle不使用索引，但位图索引搜索NULL值很快。
4、除非是函数索引，否则where对索引列使用函数时，oracle会忽略索引。
5、比较不匹配数据类型会存在隐性类型转换，会执行全表扫，如varchar2自动向number型转换。
如varchar2=0000012345，这会全表扫，修改为varchar2='0000012345'，或to_number(varchar2)=0000012345(前提是前导0没有意义).
七、选择性
对组合索引添加额外的列不会显著改善性能，增加额外列的成本可能 会超出收益
八、集群因子（待完善）
集群因子记录了扫描索引时需要读取的数据块数，若接近数据块数量，则排序良好，若接近行数量则排序不佳。
九、二元高度
把rowid返回给用户进程所要求的io数量。二元高度为3的索引，需要读4个块才能返回行数据，3次读索引1次读表。
十、BLEVEL和索引高度
a、更新操作对索引的影响：索引列被更新，实际上是一个删除和插入操作，旧值被标记为已删除，新值被插入，通过ORACLE的延迟清理清除旧值。
b、删除操作是将索引条目标记为已删除，随后的清理过程清理索引项。
十一、使用直方图
execute dbms_stats.gather_table_stats('scott','company',method_opt=>'forcolumns size 10 company_code'); company表 company_code列上产生10个桶的直方图。(怎么看直方图？)
十二、索引快速全扫描
oracle读取B树索引的所有叶块，以顺序方式一次读取多个块，
在查询列都包含在索引列中，且索引前导列不在where中(可能用到index_ffs提示)以及索引相对表很小的情况下，快速全扫描可使应用程序陡增，如果是组合索引中包含大部分列，索引可能比表达，快速全扫描反而降低性能。
十三、跳跃式扫描
允许优化器使用组合索引，即使前导列没有出现在where子句中。
逻辑读(一致读，consistentget)、物理读的概念。
十四、索引类型
（1）B树索引
默认索引，B树索引保存非空索引列上的每个数据行的ROWID值，列值为NULL的不被索引，但复合索引中某列为NULL值，该行会在索引中，但null值处理为空。
B树索引中，一个ROWID对应一个索引值，更新表时锁定单独行。
（2）位图索引
适用于数据量大胆基数不高的列，通常只选少量列。但用户操作表时(插入操作)，位图比B树索引好，但多用户并发时，不应使用位图索引。
位图索引值使用压缩格式存储，一个索引值包含一系列ROWID,因此在更新时不得不锁定所有相关的ROWID，这种锁定可能造成dml.select语句不受影响。
位图索引的使用限制：CRO不会考虑位图索引；ALTERTABLE 修改位图索引列时位图会失效；位图索引不包含列数据，不能进行完整性检查；不能为唯一索引；最多30列。
（3）哈希索引
使用哈希索引时必须使用哈希集群。使用哈希机群时也就定义了集群键。这个键告诉ORACLE如何在集群上存储表。哈希索引可能是访问数据最快的方法，但要准确估计集群键不同值的数目，且必须建机群时指定该值，建哈希集群时要预估增长空间。
哈希对包含有序值得静态数据非常有效。在限制条件为确定的值而不是范围时非常有用。
（4）索引组织表
表的存储结构为以主键排序的B树结构，索引和数据在同一个存储结构上。为基于主键的访问提供了快速访问机制。对总是通过主键的精确匹配或范围扫描进行访问的表，需要建立索引组织表。可以在索引组织表上建立额外的索引。
（5）反键索引
避免索引数据严重偏斜造成io瓶颈，将索引值翻转存储，使索引分布尽可能平均。
（6）函数索引
为使优化器使用函数索引，必须把初始化参数query_rewrite_enabled设定位true;
（7）分区索引
将索引分成多个物理片断减少io竞争。分区有多种：表分区索引未分区；表未分区索引分区；表和索引都分区。以上都基于CBO。B树和位图可被分区；哈希不能分区。
分区索引分为：本地索引和全局索引。每个类型都可以分为前缀索引(索引的第一个列等于表的分区列)和无前缀索引(索引的第一个列不等于表的分区列)。位图索引只能是本地索引。
本地索引：索引分区与表使用相同的分区键和范围界限，每个分区只包含它所对应的表分区的键值和rowid。
全局索引：索引分区中包含多个表分区的键值。全局分区索引的分区键是与分区表中分区键不同的列或不同的范围。创建全局分区索引时，必须定义分区键的范围和值。全局索引只能是B树索引。oracle默认不维护全局索引，若altertable时不指定update global indexes，就要重建索引。
oracle不支持无前缀的全局分区。与B树索引没有优势，没必要支持。
create table TT(id number,createdate date)
partition by range(createdate)
(
partition Q1 VALUES LESS THAN (TO_DATE('2012-03-30','YYYY-MM-DD')),
partition Q2 VALUES LESS THAN (TO_DATE('2012-06-30','YYYY-MM-DD')),
partition Q3 VALUES LESS THAN (TO_DATE('2012-09-30','YYYY-MM-DD')),
partition Q4 VALUES LESS THAN (TO_DATE('2012-12-31','YYYY-MM-DD')),
partition Q_OTHERS VALUES LESS THAN (MAXVALUE)
);
如果查询的语句的条件是where createdate='2012-10-19' and id>100，则此时查询的是4号分区，假设他有10万条记录。在扫描这10万条记录的时候，
可以使用id列上的索引。这个时候可以在ID列上建立个local nonprofiex索引
create index index_tt1_local on TT(id) local
( partition p1,
partition p2,
partition p3,
partition p4,
partition p5
);
注意：索引分区的数量和其基本的分区数量要一样。
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT1_LOCAL';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
------------------------------ ------------------------------ -------------------- --------
INDEX_TT1_LOCAL P1 TO_DATE(' 2012-03-30 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT1_LOCAL P2 TO_DATE(' 2012-06-30 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT1_LOCAL P3 TO_DATE(' 2012-09-30 USABLE
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
------------------------------ ------------------------------ -------------------- --------
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT1_LOCAL P4 TO_DATE(' 2012-12-31 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT1_LOCAL P5 MAXVALUE USABLE
第二种情况:
如果查询的语句条件只有一个createdate,如where createdate='2010-10-19'，则这种情况就在createdate上建立一个local profiex索引
SQL> create index index_TT2_local on TT(createdate) local;
Index created.
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from dba_ind_partitions where index_name='INDEX_TT2_LOCAL';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
------------------------------ ------------------------------ -------------------- --------
INDEX_TT2_LOCAL Q1 TO_DATE(' 2012-03-30 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT2_LOCAL Q2 TO_DATE(' 2012-06-30 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT2_LOCAL Q3 TO_DATE(' 2012-09-30 USABLE
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
------------------------------ ------------------------------ -------------------- --------
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT2_LOCAL Q4 TO_DATE(' 2012-12-31 USABLE
00:00:00', 'SYYYY-M
M-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
INDEX_TT2_LOCAL Q_OTHERS MAXVALUE USABLE
从上面查询可以看出他和表是equi-partitioned.
第三种情况:
如果查询根本就没有createdate，而是有像where id>100的条件，则就只能在ID列上建立GLOBAL索引了
create index index_tt3_global on TT(id)
global partition by range(id)
(
partition p1 values less than (100000),
partition p2 values less than (200000),
partition p3 values less than (MAXVALUE)
);
从上面可以看出，GLOBAL的索引的分区数和其基本是没有关系的。他甚至可以想下面一个建立索引，即一个普通索引。但是LOCAL的必须和其基本分区数一致。
SQL> create index index_tt3_global on TT(id) global;
Index created.
总之，一般建议建立LOCAL的索引，因为GLOBAL的容易所有的都失效，而LOCAL的最多只在某个分区上失效。索引失效必须一个分区的一个分区的REBUILD。
SQL> alter table test split partition p3 at (30000) into (partition p3, partition p4);
Table altered.
十五 、位图连接索引
(空)
十六、快速重建索引 : 使用rebuild选项，可以使用已有索引而不是整个表快速重建索引。
alter index cust_idx1 rebuild paralleltablespace cust_tblspc storage (pctincrease 0);还可以使用storage子句和parallel子句。
十七、在线重建索引
可以在执行DML时创建或重建索引。DML少时重建更好些。11g之前在线重建需要排他锁，会导致DML延迟造成性能波动。现在快多了，比drop后create要快。
create index index_name on table(col1,col2)online;
alter index index_name rebuild online;
但不能对位图索引rebuild online;对强制完整性约束的索引也不能rebuildonline;???测试不会报错，估计是会阻塞dml才不让rebuild online;
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
