博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

Oracle 课程四之索引

Posted on 2014-01-23 14:51  徐正柱-  阅读(1119)  评论(0)  编辑  收藏  举报

 

课程目标

完成本课程的学习后，您应该能够：

理解b*tree索引的结构与特征
了解聚簇因子的产生原因
理解分区索引与全局索引的区别及场景
掌握组合索引的高效设计
位图索引的适用场景
全文索引的适用场景
理解索引失效的场景
 
1.索引的种类

　　索引的种类很多，目前只关注四种：

B*树索引
位图索引
分区索引
全文索引
 
B树索引

　　B*树的构造类似于二叉树，能根据键提供一行或一个行集的快速访问，通常只需很少的读操作就能找到正确的行。不过，需要注意重要的一点，”B*树“中的”B“不代表二叉（binary），而代表平衡（balanced）。B*树索引并不是一颗二叉树，这一点在介绍如何在磁盘上物理地存储B*树时就会了解到。

位图索引

　　在常见的OLAP环境中，我们有大量使用位图索引的例子。但是在OLTP中，位图索引就是应用系统的噩梦。

分区索引

　　全局索引
　　全局分区索引
　　本地索引(局部索引)
全文索引
　　我们常常偶尔会碰见用b*tree、bitmap索引都无法产生很好效果的时候，可以结合业务场景，考虑使用全文索引。
 
2.B*tree索引结构和特征

　Oracle使用平衡树(B-tree)存储索引以便提升数据访问速度。当不使用索引时，用户必须对数据进行顺序扫描(sequential scan)来查找指定的值。如果有 n 行数据，那么平均需要扫描的行为 n/2。因此当数据量增长时，这种方法的开销将显著增长。

　　如果将一个已排序的值列(list of the values)划分为多个区间(range)，每个区间的末尾包含指向下个区间的指针(pointer)，而搜索树(search tree)中则保存指向每个区间的指针。此时在 n 行数据中查询一个值所需的时间为 log(n)。这就是 Oracle索引的基本原理。

　　下图显示了平衡树索引(B-tree index)的结构：

 

　　在一个平衡树索引(B-tree index)中，最底层的索引块(叶块(leaf block))存储了被索引的数据值，以及对应的 rowid。叶块之间以双向链表的形式相互连接。位于叶块之上的索引块被称为分支块，分枝块中包含了指向下层索引块的指针。如果被索引的列存储的是字符数据，那么索引值为这些字符数据在当前数据库字符集中的二进制值。

　　对于唯一索引，每个索引值对应着唯一的一个 rowid。对于非唯一索引，每个索引值对应着多个已排序的 rowid。因此在非唯一索引中，索引数据是按照索引键(index key)及 rowid 共同排序的。键值(key value)全部为 NULL 的行不会被索引，只有位图索引(bitmap index)和簇索引(cluster index)例外。在数据表中，如果两个数据行的全部键值都为 NULL，也不会与唯一索引相冲突。

　　有两种类型的索引块：

　　1、用于搜索的分支块(branch block)

　　2、用于存储索引数据的叶块(leaf block)

　　分支块中存储以下信息：

　　1、最小的键值前缀，用于在(本块的)两个键值之间做出分支选择。

　　2、指向包含所查找键值的子块的指针。

　　包含 n 个键值的分支块含有 n+1 个指针。键值及指针的数量同时还受索引块容量的限制。

　　所有叶块相对于其根分支块的深度是相同的。

　　叶块用于存储以下信息：

　　1、数据行的键值(key value) 。

　　2、键值对应数据行的 ROWID 。

　　所有的 键值-ROWID 对都与其左右的兄弟节点向链接，并按照(key，ROWID)的顺序排序。

　　平衡树数据结构(B-tree structure)具有以下优势：

　　1、平衡树(B-tree)内所有叶块的深度相同，因此获取索引内任何位置的数据所需的时间大致相同。

　　2、平衡树索引(B-tree index)能够自动保持平。

　　3、平衡树内的所有块的使用容量平均在块总容量的 3/4 左右。

　　4、在大区间范围内进行查询时，无论匹配个别值还是搜索一个区间，平衡树都能提供较好的查询性能。

　　5、数据插入(insert)，更新(update)，及删除(delete)的效率较高，且易于维护键值的顺序。

　　6、大型表，小型表利用平衡树进行搜索的效率都较好，且搜索效率不会因数据增长而降低。

2.1 B*tree索引的物理结构

SQL>create table test as select * from dba_objects;
SQL>create index ind_object_name on test(object_name);
SQL>select index_name,s.blevel from user_indexes s where s.index_name='IND_OBJECT_NAME';
INDEX_NAME                                BLEVEL
------------------------------         ----------
IND_OBJECT_NAME                         2

SQL>select object_id from dba_objects s where s.object_name='IND_OBJECT_NAME';
  
OBJECT_ID
   ----------
     73522

SQL>alter session set events  'immediate trace name treedump level 73522';
会话已更改。

在trace的目录中找到对应的dump文件
生成索引的dump文件

转摘：http://blog.csdn.net/stevendbaguo/article/details/9037949
       第一个0的意思是position within previous level block(starting at -1 except root starting at 0)，翻译root是从0开始，其他的从-1开始；
        nrow: number of all index entries 总的索引的数量；
        rrows: number of current index entries  当前索引的数量；
        level: branch block level(leaf block implicitly 0) 枝节点数据块的层次，叶子节点暗含是0；
       16788147（八进制） 可以转换为数据块的位置，如下所示；
        0x1002ab3（十六进制），可以与16788147相互转换。

--begin tree dump
branch: 0x1002ab3 16788147 (0: nrow: 3, level: 2)

branch: 0x1002f04 16789252 (-1: nrow: 244, level: 1)
leaf: 0x1002ab4 16788148 (-1: nrow: 187 rrow: 187)
leaf: 0x1002ab5 16788149 (0: nrow: 181 rrow: 181)
leaf: 0x1002ab6 16788150 (1: nrow: 183 rrow: 183)
leaf: 0x1002ab7 16788151 (2: nrow: 185 rrow: 185)
leaf: 0x1002ab8 16788152 (3: nrow: 187 rrow: 187)
leaf: 0x1002ab9 16788153 (4: nrow: 189 rrow: 189)
leaf: 0x1002aba 16788154 (5: nrow: 190 rrow: 190)
..................................省略部分内容.....................................
----- end tree dump

select dbms_utility.data_block_address_file(16788154) "file",
dbms_utility.data_block_address_block(16788154) "block" from dual;
      file      block
---------- ----------
       4      10938
 alter system dump datafile 4 block 10938;


Dump的内容：
row#0[7996] flag: ------, lock: 0, len=40
col 0; len 30; (30): 
 2f 31 30 30 30 33 32 33 64 5f 44 65 6c 65 67 61 74 65 49 6e 76 6f 63 61 74
 69 6f 6e 48 61
col 1; len 6; (6):  01 00 26 65 00 3e
row#1[7956] flag: ------, lock: 0, len=40
col 0; len 30; (30): 
 2f 31 30 30 30 33 32 33 64 5f 44 65 6c 65 67 61 74 65 49 6e 76 6f 63 61 74
 69 6f 6e 48 61
col 1; len 6; (6):  01 00 26 65 00 3f
row#2[7916] flag: ------, lock: 0, len=40
col 0; len 30; (30): 
 2f 31 30 30 30 33 32 33 64 5f 44 65 6c 65 67 61 74 65 49 6e 76 6f 63 61 74
 69 6f 6e 48 61
col 1; len 6; (6):  01 00 2b 69 00 0c
row#3[7876] flag: ------, lock: 0, len=40
col 0; len 30; (30): 
 2f 31 30 30 30 33 32 33 64 5f 44 65 6c 65 67 61 74 65 49 6e 76 6f 63 61 74
 69 6f 6e 48 61
col 1; len 6; (6):  01 00 2b 69 00 0d
row#4[7836] flag: ------, lock: 0, len=40
col 0; len 30; (30): 
 2f 31 30 30 30 65 38 64 31 5f 4c 69 6e 6b 65 64 48 61 73 68 4d 61 70 56 61
 6c 75 65 49 74
col 1; len 6; (6):  01 00 27 08 00 32



select chr(to_number('2f','xx')) from dual;
 select chr(to_number('31','xx')) from dual;
 select chr(to_number('30','xx')) from dual;
 select chr(to_number('30','xx')) from dual;
 select chr(to_number('30','xx')) from dual;
 select chr(to_number('33','xx')) from dual;
 select chr(to_number('32','xx')) from dual;   
 select chr(to_number('33','xx')) from dual;           
 select chr(to_number('64','xx')) from dual;   
 select chr(to_number('5f','xx')) from dual;     
 select chr(to_number('44','xx')) from dual;
 select chr(to_number('65','xx')) from dual;


01 00 27 08 00 32 如何转换为rowid请见：
http://blog.csdn.net/stevendbaguo/article/details/8215225
索引物理结构信息

 

2.2索引统计信息

SQL> select s.index_name, s.clustering_factor, s.num_rows, s.blevel,s.leaf_blocks from user_indexes s
  2  where s.table_name = 'DIM_DEPT';

INDEX_NAME                     CLUSTERING_FACTOR   NUM_ROWS     BLEVEL
------------------------------ ----------------- ---------- ----------
LEAF_BLOCKS
-----------
IDX_DIM_DEPT3                                111        889          1
          3
索引统计信息

 

2.3 B*Tree索引的三大特征

a.索引树的高度一般都比较低；
b.索引由索引列存储的值及rowid组成；
c.索引本身是有序的；
这些特点将会给数据库的开发设计优化的相关工作带来意想不到的好处。

索引的高度:

a.索引高度验证
Drop table t1 purge;
Drop table t2 purge;
Drop table t3 purge;
Drop table t4 purge;
Drop table t5 purge;
Drop table t6 purge;
Drop table t7 purge;

create table t1 as select rownum as id,rownum+1 as id2 from dual connect by level <=5;
create table t2 as select rownum as id,rownum+1 as id2 from dual connect by level <=50;
create table t3 as select rownum as id,rownum+1 as id2 from dual connect by level <=500;
create table t4 as select rownum as id,rownum+1 as id2 from dual connect by level <=5000;
create table t5 as select rownum as id,rownum+1 as id2 from dual connect by level <=50000;
create table t6 as select rownum as id,rownum+1 as id2 from dual connect by level <=500000;
create table t7 as select rownum as id,rownum+1 as id2 from dual connect by level <=5000000;

Create unique index ind_id_t1 on t1(id);
Create unique index ind_id_t2 on t2(id);
Create unique index ind_id_t3 on t3(id);
Create unique index ind_id_t4 on t4(id);
Create unique index ind_id_t5 on t5(id);
Create unique index ind_id_t6 on t6(id);
Create unique index ind_id_t7 on t7(id);

Select segment_name,bytes/1024||’K’ from user_segments where segment_name like 'IND_ID_T%';
Select index_name,blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor from user_indexes where index_name like 'IND_ID_T%';
实验:2.3索引高度验证:

思考题：在t4和t7用索引取一条数据，是t4快很多吗？

b.索引高度较低的用处
Set autotrace traceonly
Select * from t4 where id=100;
Select * from t7 where id=100;

Drop index ind_id_t4;
Drop index ind_id_t7;

Select * from t4 where id=100;
Select * from t7 where id=100;
实验:2.3索引高度较低的用处:

 

2.4索引的存储

  • Count(*)的优化
Drop table test purge;
Create table test as select * from dba_objects;
Create index ind_object_id on test(object_id);
Set autotrace traceonly
Select count(*) from test;
Select count(*) from test where object_id is not null;
Alter table test modify object_id not null;
Select count(*) from test;
Count(*)优化
  • Sum/avg的优化
Drop table test purge;
Create table test as select * from dba_objects;
Create index ind_object_id on test(object_id);
Set autotrace traceonly
Select sum(object_id) from test;
Select sum(object_id)from test where object_id is not null;
Alter table test modify object_id not null;
Select sum(object_id)from test;
Sum/avg优化
  • Max/min的优化及陷阱
Drop table test purge;
Create table test as select * from dba_objects;
Create index ind_object_id on test(object_id);
Set autotrace traceonly
Select max(object_id) from test;

Drop table t_max purge;
Create table t_max as select * from dba_objects;
Insert into t_max select * from dba_objects;
Insert into t_max select * from dba_objects;
Insert into t_max select * from dba_objects;
Insert into t_max select * from dba_objects;
Insert into t_max select * from dba_objects;
Commit;
Create index ind_t_max on t_max(object_id);
Select count(*) from t_max;
Select max(object_id) from test;
Set autotrace traceonly
Select min(object_id),max(object_id) from t_max;
Alter table t_max modify object_id not null;
Select min(object_id),max(object_id) from t_max;

Select (Select min(object_id) from t_max),
(Select max(object_id) from t_max) from dual;
Max/min的优化及陷阱
  • 索引回表与优化
drop table test purge;
create table test as select *from dba_objects;
create index ind_object_id on test(object_id);
set autotrace traceonly
select * from test where object_id <100;

select object_id from test where object_id <100;
索引回表与优化

 

2.5索引的有序性

  • Order by 排序优化
Drop table test purge;
create table test as select *from dba_objects;
select * from test where object_id <100 order by object_id;
create index ind_object_id on test(object_id);
select * from test where object_id <100 order by object_id;
Order by 排序优化
  •  Distinct 排重优化
Drop table test purge;
create table test as select *from dba_objects;
Alter table test modify object_id not null;
select distinct object_id from test;
create index ind_object_id on test(object_id);
select /*+index(test)*/distinct object_id from test;
Distinct 排重优化

　　大多数情况下用到distinct是因为表记录有重复，我们首先要考虑的是为什么要重复。

  • 索引全扫描和快速扫描
Drop table test purge;
create table test as select *from dba_objects;
Alter table test modify object_id not null;
create index ind_object_id on test(object_id);
Select count(*) from test;
Select object_id from test order by object_id;
Select max(object_id) from test order by object_id;
索引全扫描(INDEX FAST FULL SCAN)和快速扫描(INDEX FULL SCAN)
  • Union 合并的优化(union和union all的区别)
drop table test1 purge;
Drop table test2 purge;
create table test1 as select *from dba_objects where object_type='SYNONYM';
create table test2 as select *from dba_objects where object_type='JAVA CLASS';
Alter table test1 modify object_id not null;
Alter table test2 modify object_id not null;

select object_id from test1
union
select object_id from test2;

create index ind_object_id1 on test1(object_id);
create index ind_object_id2 on test2(object_id);

select object_id from test1
union
select object_id from test2;

select /*+index(test1)*/object_id from test1
union
select /*+index(test2)*/object_id from test2;

select object_id from test1
union all
select object_id from test2;
Union 合并的优化

　　在有些场景下，几个结果集是没有重复的集合进行union,此时用union all可以消除排序。

小结： B*tree索引的应用场景
高效场景：索引字段有着很高的选择性或者结果集很小的时候。
低效场景：索引字段有着很低的选择性或者结果集很大的时候。
 
create table test as select * from dba_objects;
create index ind_objec_id on test(object_id);
exec dbms_stats.gather_table_stats(user,'TEST',cascade => true);
set autotrace traceonly
select /*+index(t)*/* from test t where object_id >20;
select /*+full(t)*/ *from test t where object_id >20;
实验：索引一定高效吗？

 

3.聚簇因子

 

　　索引聚簇因子，指的是按照索引列值进行了排序的索引行序和对应表中数据行序的相似程度，行在数据块中的存储越集中越有序， clustering factor越低。相反行的存储越分散，clustering factor越高 。
　　当clustering factor 很高时，说明rowid是指向很多block的，在一个大的index range scan时，为了读取rowid 指向的block，就需要一一次重复的去读这些block。
　　当clustering factor 值低时，说明rowid指向的记录很可能是存储在相同的block里，这样去读行数据时，只需要在同一个block里读取就可以了。就可以减少重复读取block的次数。

　　则index1访问其中7行，需要2个块，而index2访问3行就需要3个块，可见index2的 聚簇因子是很不好的.

 

　　假如有一个表t有1000万行，有10万个块,我们有个provcode(省)是索引，provcode的distinct有32个，那么如果我们取其中的一个省份按照平均来计算是不是就是1/32的数据，这个比例接近3%，很多人认为这个应该走provode索引，但是如果这个32个值是平均分布，也就是说很可能导致我们取其中一个省份，由于他分布在所有的数据块里面，导致我们相当于要读取整个表，这个性能是非常差的，这个时候全表就效果更好（这里有多块读等因数）

clustering_factor和块数是相等的，是最好情况，最坏的情况是clustering_factor等于num_rows。
Index Clustering Factor说明
       在里面没有提到index Clustering Factor参数，所以这里说明一下。
       简单的说， IndexClustering Factor是通过一个索引扫描一张表，需要访问的表的数据块的数量，即对I/O的影响. 也代表索引键值存储位置是否有序。
       （1）如果越有序，即相邻的键值存储在相同的block，那么这时候ClusteringFactor 的值就越低。
       （2）如果不是很有序，即键值是随即的存储在block上，这样在读取键值时，可能就需要一次又一次的去访问相同的block，从而增加了I/O.

Clustering Factor 的计算方式如下：
（1）扫描一个索引（large index range scan）
（2）比较某行的rowid和前一行的rowid，如果这两个rowid不属于同一个数据块，那么cluster factor增加1
（3）整个索引扫描完毕后，就得到了该索引的cluster factor。
       如果ClusteringFactor接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。
       如果ClusteringFactor接近于行的数量，那说明这张表不是按索引字段顺序存储的。
       在计算索引访问成本的时候，这个值十分有用。Clustering Factor乘以选择性参数（selectivity ）就是访问索引的开销。
       如果这个统计数据不能真实反映出索引的真实情况，那么可能会造成优化器错误的选择执行计划。另外如果某张表上的大多数访问是按照某个索引做索引扫描，那么将该表的数据按照索引字段的顺序重新组织，可以提高该表的访问性能。

3.1聚簇因子计算

　　比较行的当前rowid和前一行的rowid，如果相邻两个rowid不属于同一数据块（在index中的rowid是受限rowid，由文件号block_id和行号组成）则cluster factor增加1。

　　聚簇因子带来的CBO混乱:

　　该实验可以看到Clustering Factor是索引有效性的重要判断标准，其值会影响CBO选择执行计划。但是让我们郁闷的是，这个值永远在不断恶化，就好比一个人从青年慢慢变老的过程，判断力变的迟缓，行动变的迟缓:

drop table t purge;
create table t as select * from dba_objects;
---再插入5次，为了保证数据无序
begin
  for i in 1 .. 5 loop
    insert /*+append*/
    into t
      select * from dba_objects;
    commit;
  end loop;
end;
/
select count(1) from t;

Col segment_name format a30;
select segment_name, blocks, extents, bytes / 1024 / 1024 || 'M' b_size
  from dba_segments
 where owner = 'TEST'
   and segment_name = 'T';
   
create index idx_t_id on t(object_id);

select segment_name,
       segment_type,
       blocks,
       extents,
       bytes / 1024 / 1024 || 'M' "SIZE"
  from dba_segments
 where owner = 'TEST'
   and segment_name = 'IDX_T_ID';
 
exec dbms_stats.gather_table_stats('TEST','T',cascade => true);

select index_name, clustering_factor, num_rows
  from dba_indexes
 where owner = 'TEST'
   and index_name = 'IDX_T_ID';

set autotrace traceonly
set linesize 1000

select * from t where object_id = 100; 
--全表，为什么全表？
select * from t where object_id>1000 and object_id<2000; 
select /*+index(t idx_t_id)*/ * from t where object_id>1000 and object_id<2000; --走索引的代价更高

---降低Clustering Factor解决问题，对表的存储位置需要进行排序
Set autotrace off
create table tt as select * from t where rownum<1;
insert /*+append */ into tt select * from t order by object_id;
commit;

select segment_name, blocks, extents, bytes / 1024 / 1024 || 'M'
  from dba_segments
 where owner = 'TEST'
   and segment_name = 'TT';

create index idx_tt_id on tt(object_id);

select segment_name,
       segment_type,
       blocks,
       extents,
       bytes / 1024 / 1024 || 'M' "SIZE"
  from dba_segments
 where owner = 'TEST'
   and segment_name = 'IDX_TT_ID';

exec dbms_stats.gather_table_stats('TEST','TT',cascade => true);

select owner, index_name, clustering_factor, num_rows,leaf_blocks
  from dba_indexes
 where owner = 'TEST'
   and index_name = 'IDX_TT_ID';

---block数目与clustering_factor非常接近
select blocks from dba_tables where table_name='TT';  
Set autotrace traceonly
select * from tt where object_id>1000 and  object_id<2000; 
select * from t where object_id>1000 and  object_id<2000;
实验：聚簇因子计算

3.2控制聚簇因子
对表进行重构、按照索引顺序将表重建

4.分区索引

4.1全局索引

　　一般一张表超过2G的大小，ORACLE是推荐使用分区表的，分区一般都需要创建索引，说到分区索引，就可以分为：全局索引、分区索引，即：global索引和local索引，前者为默认情况下在分区表上创建索引时的索引方式，并不对索引进行分区（索引也是表结构，索引大了也需要分区，关于索引以后专门写点）而全局索引可修饰为分区索引，但是和local索引有所区别，前者的分区方式完全按照自定义方式去创建，和表结构完全无关，所以对于分区表的全局索引有以下两幅网上常用的图解：

  • 对于分区表的不分区索引（指表分区，但其索引不分区）：

　　创建语法(直接创建即可)：
　　CREATE INDEX <index_name> ON <partition_table_name>(<column_name>);

create table TEST                               
(                                               
  OWNER          VARCHAR2(30) not null,         
  OBJECT_NAME    VARCHAR2(30) not null,         
  SUBOBJECT_NAME VARCHAR2(30),                  
  OBJECT_ID      NUMBER not null,               
  DATA_OBJECT_ID NUMBER,                        
  OBJECT_TYPE    VARCHAR2(19),                  
  CREATED        DATE not null,                 
  LAST_DDL_TIME  DATE not null,                 
  TIMESTAMP      VARCHAR2(19),                  
  STATUS         VARCHAR2(7),                   
  TEMPORARY      VARCHAR2(1),                   
  GENERATED      VARCHAR2(1),                   
  SECONDARY      VARCHAR2(1)                    
)                                               
partition by list (OWNER)                       
(                                               
  partition PART_OWNER1 values ('BI'),         
  partition PART_OWNER2 values ('CTXSYS'),      
  partition PART_OWNER3 values ('DBSNMP'),         
  partition PART_OWNER4 values ('DMSYS'), 
  partition PART_OWNER5 values ('EXFSYS'),   
  partition PART_OWNER6 values ('HR'),       
  partition PART_OWNER7 values ('IX'),      
  partition PART_OWNER8 values ('SYSMAN'),      
  partition PART_OWNER9 values ('SYSTEM'),       
  partition PART_OWNER10 values ('TSMSYS'),    
  partition PART_OTHER values (default)         
);
insert into test select * from dba_objects where object_id is not null; 
Commit;  
create index ind_g_object_id on test(object_id);  
exec dbms_stats.gather_table_stats(user,'test',cascade => true);

--查看索引段
select segment_name,partition_name,segment_type
from user_segments s
where s.segment_name = upper('ind_g_object_id');

select * from test where owner='CTXSYS';
set autotrace trace exp
select * from test where object_id = 40855;

--表的DDL操作会导致索引无效
alter table test drop partition PART_OWNER9;
select * from test where object_id = 40855;
--重建索引后生效
Drop index ind_g_object_id;
create index ind_g_object_id on test(object_id);  
select * from test where object_id = 40855;
全局索引-表分区索引不分区

 

  • 对于分区表的分区索引:

创建语法为：
CREATE [url=]INDEX[/url] INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1)
  GLOBAL PARTITION BY RANGE(COL1)
         PARTITION IDX_P1 values less than (1000000),
         PARTITION IDX_P2 values less than (2000000),
         PARTITION IDX_P3 values less than (MAXVALUE)
  )

此种索引很少使用
Drop table test purge;
create table TEST
(
  OWNER          VARCHAR2(30),
  OBJECT_NAME    VARCHAR2(128),
  SUBOBJECT_NAME VARCHAR2(30),
  OBJECT_ID      NUMBER,
  DATA_OBJECT_ID NUMBER,
  OBJECT_TYPE    VARCHAR2(19),
  CREATED        DATE,
  LAST_DDL_TIME  DATE,
  TIMESTAMP      VARCHAR2(19),
  STATUS         VARCHAR2(7),
  TEMPORARY      VARCHAR2(1),
  GENERATED      VARCHAR2(1),
  SECONDARY      VARCHAR2(1)
)
partition by range(OBJECT_ID)
(
  partition p1 values less than(10000), 
  partition p2 values less than(20000), 
  partition p3 values less than(30000), 
  partition p4 values less than(40000), 
  partition p5 values less than(maxvalue) 
);
insert into test select * from dba_objects where object_id is not null; 
Commit;  

CREATE index g_object_id ON test(object_id)
  GLOBAL PARTITION BY RANGE(object_id)
(
  partition p1 values less than(10000), 
  partition p2 values less than(20000), 
  partition p3 values less than(30000), 
  partition p4 values less than(40000), 
  partition p5 values less than(maxvalue)
);

select segment_name,partition_name,segment_type
from user_segments s
where s.segment_name = 'G_OBJECT_ID';
全局索引-表分区索引分区

 

  • LOCAL索引结构：

创建语法为：
CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL;
也可按照分区表的的分区结构给与一一定义，索引的分区将得到重命名。
分区上的位图索引只能为LOCAL索引，不能为GLOBAL全局索引。

表的DDL操作无需rebuild索引，可以非常方便的管理数据
create table TEST                               
(                                               
  OWNER          VARCHAR2(30) not null,         
  OBJECT_NAME    VARCHAR2(30) not null,         
  SUBOBJECT_NAME VARCHAR2(30),                  
  OBJECT_ID      NUMBER not null,               
  DATA_OBJECT_ID NUMBER,                        
  OBJECT_TYPE    VARCHAR2(19),                  
  CREATED        DATE not null,                 
  LAST_DDL_TIME  DATE not null,                 
  TIMESTAMP      VARCHAR2(19),                  
  STATUS         VARCHAR2(7),                   
  TEMPORARY      VARCHAR2(1),                   
  GENERATED      VARCHAR2(1),                   
  SECONDARY      VARCHAR2(1)                    
)                                               
partition by list (OWNER)                       
(                                               
  partition PART_OWNER1 values ('BI'),         
  partition PART_OWNER2 values ('CTXSYS'),      
  partition PART_OWNER3 values ('DBSNMP'),         
  partition PART_OWNER4 values ('DMSYS'), 
  partition PART_OWNER5 values ('EXFSYS'),   
  partition PART_OWNER6 values ('HR'),       
  partition PART_OWNER7 values ('IX'),      
  partition PART_OWNER8 values ('SYSMAN'),      
  partition PART_OWNER9 values ('SYSTEM'),       
  partition PART_OWNER10 values ('TSMSYS'),    
  partition PART_OTHER values (default)         
);
insert into test select * from dba_objects where object_id is not null; 
Commit;  
create index ind_l_object_id on test(object_id) local;
--执行下列语句前后执行计划没有变化
select * from test where object_id = 40855;
alter table test drop partition PART_OWNER5;
select * from test where object_id = 40855;
本地索引

 

　　一般使用LOCAL索引较为方便，而且维护代价较低，并且LOCAL索引是在分区的基础上去创建索引，类似于在一个子表内部去创建索引，这样开销主要是区分分区上，很规范的管理起来，在OLAP系统中应用很广泛；

　　而相对的GLOBAL索引是全局类型的索引，根据实际情况可以调整分区的类别，而并非按照分区结构一一定义，相对维护代价较高一些，在OLTP环境用得相对较多，这里所谓OLTP和OLAP也是相对的，不是特殊的项目，没有绝对的划分概念，在应用过程中依据实际情况而定，来提高整体的运行性能。

drop table test purge;
create table TEST                               
(                                               
  OWNER          VARCHAR2(30) not null,         
  OBJECT_NAME    VARCHAR2(30) not null,         
  SUBOBJECT_NAME VARCHAR2(30),                  
  OBJECT_ID      NUMBER not null,               
  DATA_OBJECT_ID NUMBER,                        
  OBJECT_TYPE    VARCHAR2(19),                  
  CREATED        DATE not null,                 
  LAST_DDL_TIME  DATE not null,                 
  TIMESTAMP      VARCHAR2(19),                  
  STATUS         VARCHAR2(7),                   
  TEMPORARY      VARCHAR2(1),                   
  GENERATED      VARCHAR2(1),                   
  SECONDARY      VARCHAR2(1)                    
)                                               
partition by list (OWNER)                       
(                                               
  partition PART_OWNER1 values ('BI'),         
  partition PART_OWNER2 values ('CTXSYS'),      
  partition PART_OWNER3 values ('DBSNMP'),         
  partition PART_OWNER4 values ('DMSYS'), 
  partition PART_OWNER5 values ('EXFSYS'),   
  partition PART_OWNER6 values ('HR'),       
  partition PART_OWNER7 values ('IX'),      
  partition PART_OWNER8 values ('SYSMAN'),      
  partition PART_OWNER9 values ('SYSTEM'),       
  partition PART_OWNER10 values ('TSMSYS'),    
  partition PART_OTHER values (default)         
);
insert into test select * from dba_objects where object_id is not null; 
Commit;
分区索引性能一定好于全局索引？
select *from test  partition(PART_OWNER1);
select *from test  partition(PART_OWNER2); 
select *from test  partition(PART_OWNER3);  
create index ind_g_object_id on test(object_id);
exec dbms_stats.gather_table_stats(user,'TEST',cascade => true);
select * from test where  object_id in(52525,41269,9615) ;

drop index ind_g_object_id;
create index ind_l_object_id on test(object_id) local;
select * from test where object_id in(52525,41269,9615);
全局索引优于全局分区索引的例子
drop index ind_l_object_id;
create index ind_g_object_id on test(object_id);
select /*+index(t)*/ object_id from test t where owner = 'SYSTEM';
drop index ind_g_object_id;
create index ind_l_object_id on test(object_id) local;
select /*+index(t)*/ object_id from test t where owner = 'SYSTEM';
全局分区索引优于全局索引的例子

　　一个误区：分区索引性能一定好于全局索引。原因是查询多个分区的数据，全局索引只需要在同一层级的叶子节点上查询，分区索引查询完第一个分区的索引后，需要切换到查第二分区索引的根节点，枝节点和叶子节点。相比之下全局索引少了切换的过程。

5.组合索引

 

　　组合索引，在一些场合可以避免回表

　　回表：在数据中，当查询数据的时候，在索引中查找索引后，获得该行的rowid，根据rowid再查询表中数据，就是回表。在数据库中，数据的存储都是以块为单位的，称为数据块，表中每一行数据都有唯一的地址标志ROWID。每次使用SQL进行查询的时候，都要扫描数据块，找到行所在的ROWID，再扫描该表的数据块。回表将会导致扫描更多的数据块。
Drop table test purge;
Create table test as select * from dba_objects;
Create index ind_id on test(object_id);
Set autotrace traceonly
select object_id,object_name from test where object_id<100;
Drop index ind_id;
Create index ind_id_name on test(object_id,object_name);
select object_id,object_name from test where object_id<100;
适当的场合能避免回表

5.1如何建立高效组合索引

　　•组合列返回越少越高效
　　在字段1上查询返回记录比较多，在字段2上查询返回记录也比较多，但联合字段1和字段2查询返回记录少，则适合建联合索引。这个要求我们对业务数据熟悉。
过多的字段建联合索引往往是不可取的，因为索引必然过大，不仅影响了定位数据，更影响了更新性能，一般不宜超过3个字段组合。
 
　　•组合两列谁在前更合适
   　在等值查询情况下，组合索引的列无论哪一列在前，性能都一样。
   　当一列是范围查询，一列是等值查询，这种情况下等值查询列在前，范围查询列在后，这样索引才最高效。
drop table test purge;
Create table test as select * from dba_objects;
create index IND_ID_TYPE on test(object_id,object_type);
create index IND_TYPE_ID on test(object_type,object_id);
set autotrace traceonly
select /*+index(t IND_ID_TYPE)*/* from test t where object_id=9 and object_type='INDEX';
select /*+index(t IND_TYPE_ID)*/* from test t where object_id=9 and object_type='INDEX';

select /*+index(t IND_ID_TYPE)*/ * from test t 
where object_id > 20  and object_id < 20000
       and object_type = 'TABLE';

select /*+index(t IND_TYPE_ID)*/ * from test t
     where object_id > 20  and object_id < 20000
       and object_type = 'TABLE';

--要想知道上面两条SQL快慢的原理，把下面的两条语句结果导出为excel
select  object_id,object_type from test t order by object_id,object_type;
select  object_type,object_id from test t order by object_type,object_id;
组合列谁在前更合适

 

　　•设计需考虑单列的查询

  　如果单列的查询列和联合索引的前置列一样，那单列可以不建索引，直接利用联合索引来进行检索数据。不过值得注意的是，当单列查询不在最前面，最前列的重复值多的情况下，可能会走跳跃索引。

Drop table test purge;
Create table test as select * from dba_objects;
create index IND_ID_TYPE on TEST (OBJECT_ID, OBJECT_TYPE);
Exec dbms_stats.gather_table_stats(user,'test',cascade=>true);
select * from test where object_id=20;

不过值得注意的是，当单列查询不在最前面，最前列的重复值多的情况下，可能会走跳跃索引。
drop index IND_NAME_ID;
create index IND_TYPE_ID on TEST (OBJECT_TYPE, OBJECT_ID);
select * from test where object_id=20;
单列索引

 

6.位图索引

　　目前大量使用的索引，主要是B*Tree索引,在索引结构中存储着键值和键值的rowid,并且是一一对应的。而位图索引存储为压缩的索引行，一个索引行中存储键值和起止rowid,以及这些键值的位置编码，一个位图段可能指向的是几十甚至成百上千行数据的位置，所以，位图索引主要针对大量相同值的列而创建。

　　位图索引优点: a.统计数据条数有优势,b.非常适合即席查询

 

drop table t purge;
create table t as select * from dba_objects;
begin
  for i in 1 .. 5 loop
    insert /*+append*/
    into t
      select * from dba_objects;
    commit;
  end loop;
end;
/
Select count(*) from t;
--建一个b*tree索引试试
Create index ind_object_id on t(object_id);
alter table t modify object_id not null;
Select count(*) from t;
--建一个bitmap索引试试
Create bitmap index ind_bitm_t_status on t(status);
Select count(*) from t;
统计数据条数
drop table t purge;

Create table t(name_id,gender not null,location not null,age_group not null,data)
As select rownum,
   decode(ceil(dbms_random.value(0,2)),1,'M',2,'F') gender,
   ceil(dbms_random.value(1,40)) location,
   decode(ceil(dbms_random.value(0,4)),1,'child',2,'young',3,'middle_age',4,'old'),
   rpad('*',20,'*') 
from dual connect by rownum <=1000000;

exec dbms_stats.gather_table_stats(user,'t');
--直接进行查询
select * from t
 where gender = 'M'
   and location in (10, 15, 30)
   and age_group = 'child';
--建B*treee索引
create index ind_gen_loc_age on t(gender,location,age_group);

select * from t
 where gender = 'M'
   and location in (10, 15, 30)
   and age_group = 'child';
   
select /*+index(t,ind_gen_loc_age)*/ * from t
 where gender = 'M'
   and location in (10, 15, 30)
   and age_group = 'child';

--建bitmap索引
drop index ind_gen_loc_age;
create bitmap index gender on t(gender);
create bitmap index location on t(location);
create bitmap index age_group on t(age_group);

select * from t
 where gender = 'M'
   and location in (10, 15, 30)
   and age_group = 'child';
即席查询

　　位图索引缺点:

       a.在位图索引中，同一个键值只有一个位图段，所以，相同键值的所有数据的dml操作都会被锁住。

       b. 重复率低的字段建效率差。

drop table t purge;
create table t (id number(10),sex varchar2(10));
begin
    for i in 1.. 10000
     loop
         insert into t values(i,'男');
     end loop;
     commit;
    end;

begin
    for i in 10001.. 20000
     loop
         insert into t values(i,'女');
     end loop;
     commit;
    end;

create bitmap index IDX_BT_SEX on T (sex);

insert into t values (100000,'男');---不提交

---换一个session，看以下dml语句是否能运行
update t set sex= '男' where id = 10001;
insert into t values (10001,'男');
delete t where id = 10000;
实验-锁表
drop table t purge;
create table t as select * from dba_objects;
begin
  for i in 1 .. 5 loop
    insert /*+append*/
    into t
      select * from dba_objects;
    commit;
  end loop;
end;
/
Atler table t nologging;
Update t set object_id=rownum;
Commit;
exec dbms_stats.gather_table_stats(user,'t');
Select count(*) from t;
Create bitmap index ind_bitm_t_id on t(object_id);
Select count(*) from t;
Select /*+index(t ind_bitm_t_id)*/count(*) from t;
重复率低的字段建了位图索引

6.1Bitmap索引的逻辑结构与物理结构

转摘：http://blog.csdn.net/stevendbaguo/article/details/8267789

row#0[8013] flag: ------, lock: 0, len=23
col 0; len 1; (1):  80    ------代表索引数字0
col 1; len 6; (6):  01 81 df 6e 00 00  ---rowid 起点的block和行号
col 2; len 6; (6):  01 81 df 6e 00 17 ---rowid 结束的block和行号,注意17 = 16+7 = 23
col 3; len 4; (4):  ca 10 42 08  
十六进制转换为二进制：select pkg_number_trans.f_hex_to_bin('ca104208')from dual;--来自于http://blog.csdn.net/guogang83/article/details/8002014
--- 1100 1010（首字节不表示rowid信息） 00010000，01000010，00001000凡是从起点到结束点内的1表示该值存在，这里有一个必须要注意的问题是，这样转化后的位置并不是真实的物理位置，在每个字节内部bit还要颠倒一下顺序（计算机写bit的时候是从低位到高位）,首字节不表示位置信息，也就是说上面的应该转换为 00001000，01000010，00010000，发现正好每5位中存在一个值为0的记录。
row#1[7990] flag: ------, lock: 0, len=23
col 0; len 2; (2):  c1 02    ------代表索引数字1
col 1; len 6; (6):  01 81 df 6e 00 00
col 2; len 6; (6):  01 81 df 6e 00 0f
col 3; len 3; (3):  c9 21 84
row#2[7966] flag: ------, lock: 0, len=24
col 0; len 2; (2):  c1 03  ------代表索引数字2
col 1; len 6; (6):  01 81 df 6e 00 00
col 2; len 6; (6):  01 81 df 6e 00 17
col 3; len 4; (4):  ca 42 08 01
位图物理结构

 

7.全文索引

 

　　•使用场景:b-tree,bitmap无法发挥作用的场景，like '%string%‘
 
set autotrace traceonlyset timing onselect DISTINCT(C.NAME) from customerdatafromccs C where C.Usaddr like  '%下梅林%';select DISTINCT( C.NAME) from customerdatafromccs C where instr(C.Usaddr,'下梅林')>0;
create index INDEX_USADDR on customerdatafromccs(Usaddr) indextype is ctxsys.context;
select DISTINCT( C.NAME) from customerdatafromccs c where contains( C.Usaddr,'下梅林')>0;
全文索引
select * from user_segments where segment_name ='INDEX_USADDR';
select table_name,index_name from user_indexes
全文索引的存储方式
select sum(bytes)/1024/1024 from user_segments where segment_name like '%INDEX_USADDR%';;
全文索引存储占用的空间
 
7.1全文索引缺点
•全文索引导致磁盘资源的大量占用。全文索引本身就是一个利用磁盘空间换取性能的方法。全文索引大的原因是，按照某种语言来进行分词。
•更新字段值，全文索引的索引不会自动更新，索引定期维护，以及表本身的维护操作使得这个表的管理成本大大的增加。
•使用全文索引并不是对应用透明的。如果要想利用全文索引，必须修改查询语句。原有的查询语句是不可能利用全文索引的，需要改成全文索引规定的语法。全文索引不会影响到其他的SQL语句。
•全文索引自身还有些缺陷
 
8.索引失效的情形
 
  • 索引列为空　
　　当索引列为空（Null）值或者当组合索引的所有列都为空时，此记录将不会包含在B*tree索引中。这是一个基本也是很重要的概念，它意味着无法使用B*tree索引来查找空值，只能查找非空值。因此，将可能在where子局中引用的列定义为非空，使得在这些列上建立的索引变得有效。
  • 关系字段类型不一致
　　表之间关联字段类型不一致；字段的类型和传入的查询条件不一致会发生隐式转换，导致不能用索引。
  • 统计信息缺失或错误
　　如有一段时间没有分析表了。这些表起初很小，过段时间他们已经增长到非常大。现在索引就很有意义（尽管原先并非如此）。此时分析这个表，就会使用索引。
如果没有正确的统计信息，CBO将无法做出正确的决定。 
  • 对列进行运算
drop table test purge;
create table test (id number not null PRIMARY KEY, test_date date) nologging;
insert /*+append */ into test  select rownum,
       to_date(to_char(sysdate - 18000, 'J') +
               trunc(dbms_random.value(0, 170)),
               'J')
  from dual
connect by rownum <= 10000;
commit;
create index ind_date on test(test_date);
exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true);

第一种情况：
select * from test where to_char(test_date,'yyyy-MM-dd')='2013-4-13';
在需要改程序的情况下
select * from test where test_date = to_date('2013-4-13','yyyy-MM-dd');

不需要改程序的情况下
drop index ind_date;
create index ind_date on test(to_char(test_date,'yyyy-MM-dd'));
select * from test where to_char(test_date,'yyyy-MM-dd')='2013-4-13';

第二种情况：
select * from test where id -10 < 30;
索引失效：列运算
 
9.思考题：表中的数据删除了，对应的索引会删除吗
　　索引块只有当全部数据被删除后才能被新的不同值数据插入,否则只有插入相同的值的时候才能利用原来删除的空间。记录删除的时候索引并没有被真正地从物理上删除，仅仅是在该索引叶子的记录上标记一个 [D] 表示该记录被删除.
SQL> create table test as select * from dba_objects;

表已创建。

SQL> create index ind_object_name on test(object_name);

索引已创建。

SQL> select index_name,s.blevel from user_indexes s where s.index_name='IND_OBJECT_NAME';

INDEX_NAME                         BLEVEL
------------------------------ ----------
IND_OBJECT_NAME                         1

SQL> select object_id from dba_objects s where s.object_name='IND_OBJECT_NAME';

 OBJECT_ID
----------
     99523

SQL> Alter session set tracefile_identifier='look for me';

会话已更改。

SQL> alter session set events 'immediate trace name treedump level 99523';

会话已更改。

SQL>
SQL> delete from test where object_type='INDEX';

已删除5535行。

SQL> COMMIT;

提交完成。

SQL> alter session set events 'immediate trace name treedump level 99523';

会话已更改。

SQL> select dbms_utility.data_block_address_file(17604247) "file",
  2  dbms_utility.data_block_address_block(17604247) "block" from dual;

      file      block
---------- ----------
         4     827031

SQL> alter system dump datafile 4 block 827031;

系统已更改。
生成dump文件

 Block header dump:  0x010c9e97
 Object id on Block? Y
 seg/obj: 0x184c3  csc: 0x95e.f296edd1  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10c9e89 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0005.01c.00002e07  0x00800b60.0f9d.27  --U-  107  fsc 0x118e.f296f035
 
Leaf block dump

row#116[3356] flag: ---D--, lock: 2, len=40
col 0; len 30; (30):
 42 49 4e 24 33 53 55 43 46 34 6d 43 52 4e 36 49 78 71 52 34 6d 62 65 64 5a
 51 3d 3d 24 30
col 1; len 6; (6):  01 0c 9e 7c 00 2a

-------以上为截取的一段Dump文件数据----
col 0 表示数据
col 1 表示rowid

col 1; len 6; (6):  01 0c 9e 7c 00 2a

16进制----》2进制 00000001  00001100  10011110  01111101  00000000  00101010

转换为2进制后                                    二进制                                            十进制        　　十六进制
前十位表示文件号                               0000000100          ---------------------->4 　　　　　　004
中间22位表示块号                               0011001001111001111101 ----------->827005　　　 0C9E7D　
最后16位表示行号                               0000000000101010            ----------->42                  002A
 
文件编号+块编号+行编号：=0040C9E7D002A
数据对象编号        文件编号        块编号           行编号
OOOOOO             FFF            BBBBBB            RRR
0x184c3　　　　　　　　004           0C9E7D          002A