oracle 索引详解

博主： Tehran
发布时间：2022 年 03 月 02 日
437 次浏览
暂无评论
19603字数
分类： Oracle

第一章索引的定义

在中，索引是一种与表有关的数据库结构，它可以使对应于表的语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引。

第二章索引的分类

1 索引逻辑分类

单列索引：基于一列的操作
多列索引：组合索引，最多为32列。组合索引的列不一定与表中列顺序相同。
惟一索引：列的值各不相同。
非惟一索引：列的值允许相同。
基于函数的惟一索引：利用表中一列或多列基于函数表达式所创建的索引。既可以是B-树，也可以是位图索引。

2 索引物理分类

分区索引
非分区索引：非分区既可以是B－树，也可以是位图索引。
B-树索引：包括正常索引或反转关键字索引
位图索引

第三章 B-Tree 索引详解

1 结构

B-Treeindex也是我们传统上常见所理解的索引。（balancetree）即，左右两个分支相对平衡。

Root为根节点，branch为分支节点，leaf到最下面一层称为。每个节点表示一层，当查找某一数据时先读根节点，再读支节点，最后找到叶子节点。叶子节点会存放indexentry（索引入口），每个索引入口对应一条记录。
Indexentry的组成部分：
Indexentryentryheader　　存放一些控制信息。
Keycolumnlength　　某一key的长度
Keycolumnvalue　　某一个key的值
ROWID　　指针，具体指向于某一个数据

2 场合：非常适合数据重复度低的字段例如身份证号码手机号码 QQ号等字段，常用于主键唯一约束，一般在在线交易的项目中用到的多些。

3 原理：一个键值对应一行（rowid）格式：【索引头|键值|rowid】

4 优点：当没有索引的时候，oracle只能全表扫描where qq=40354446 这个条件那么这样是灰常灰常耗时的，当数据量很大的时候简直会让人崩溃，那么有个B-tree索引我们就像翻书目录一样，直接定位rowid立刻就找到了我们想要的数据，实质减少了I/O操作就提高速度，它有一个显著特点查询性能与表中数据量无关，例如查2万行的数据用了3 consistent get,当查询1200万行的数据时才用了4 consistent gets。

当我们的字段中使用了主键or唯一约束时，不用想直接可以用B-tree索引

5 缺点：不适合键值重复率较高的字段上使用，例如第一章 1-500page 第二章 501-1000page

实验：

1 清空共享池，数据库缓冲区
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;

2 创建表t1,t2
T1表中没有重复值
SQL> create table t1 as select object_id,object_name from dba_objects;

Table created.

SQL> select count(*) from t1;

COUNT(*)
----------
72476

SQL> select * from t1 where rownum<=5;
OBJECT_ID OBJECT_NAME
---------- --------------------
20 ICOL$
46 I_USER1
28 CON$
15 UNDO$
29 C_COBJ#

t2 表的object_id列我们是做了取余操作，值就只有0,1两种，因此重复率较高，如此设置为了说明重复率对B树索引的影响；
SQL> create table t2 as select mod(object_id,2) object_id,object_name from dba_objects;

SQL> select * from t2 where rownum<=5;

OBJECT_ID OBJECT_NAME
---------- --------------------
0 ICOL$
0 I_USER1
0 CON$
1 UNDO$
1 C_COBJ#

3 创建索引
SQL> create index t1_index on t1(object_id);
SQL> create index t2_index on t2(object_id);

4 查看t1,t2的重复率
SQL> select count(distinct(object_id)) from t1;

COUNT(DISTINCT(OBJECT_ID))
--------------------------
72476

SQL> select count(distinct(object_id)) from t2;

COUNT(DISTINCT(OBJECT_ID))

5 收集表的统计信息
SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T1',method_opt=>'for all indexed columns size 2',cascade=>TRUE);

SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T2',method_opt=>'for all indexed columns size 2',cascade=>TRUE);

显示执行计划和统计信息+设置autotrace简介
序号命令解释
1 SET AUTOTRACE OFF 此为默认值，即关闭Autotrace
2 SET AUTOTRACE ON EXPLAIN 只显示执行计划
3 SET AUTOTRACE ON STATISTICS 只显示执行的统计信息
4 SET AUTOTRACE ON 包含2,3两项内容
5 SET AUTOTRACE TRACEONLY 与ON相似，但不显示语句的执行结果

T1（重复率低的表）
SQL> set autotrace on explain
SQL> select * from t1 where object_id=1;

Execution Plan

Plan hash value: 1186333541

TABLE ACCESS BY INDEX ROWID| T1 -------------走it_index索引了

INDEX RANGE SCAN | T1_INDEX

Statistics

2 consistent gets
t1表的object_id没有重复值，因此使用B-tree索引扫描只有2次一致性读

T2(重复率高的表)

SQL> select * from t2 where object_id=1;
Execution Plan

Plan hash value: 1513984157

| 0 | SELECT STATEMENT | | 36199 | 954K| 90 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T2 | 36199 | 954K| 90 (2)| 00:00:02 |
2739 consistent gets
T2为什么要用全表扫描而不用B-tree索引呢？这是因为oracle基于成本优化器CBO认为使用全表扫描要比使用B-tree索引性能更好更快，由于我们结果重复率很高，导致有2739次一致性读，从cup使用率90%上看也说明了B-tree索引不适合键值重复率较高的列

我们在看一下 强制使用B-tree索引时，效率是不是没有全表扫描高呢？
SQL> select /+index(t2 t2_index) / * from t2 where object_ID=1;
Execution Plan

Plan hash value: 1666064008

| 0 | SELECT STATEMENT | | 36199 | 954K| 391 (1)| 00
:00:05 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 36199 | 954K| 391 (1)| 00
:00:05 |

|* 2 | INDEX RANGE SCAN | T2_INDEX | 36199 | | 69 (0)| 00
:00:01 |

5220 consistent gets
使用B-tree索引5220次一致性读 > 全表扫描2739次一致性读，而且cpu使用率也非常高，显然效果没有全表扫描高

小结：从以上的测试我们可以了解到，B-tree索引在什么情况下使用跟键值重复率高低有很大关系的，之间没有一个明确的分水岭，只能多测试分析执行计划后来决定。
第四章位图索引详解
1结构位图索引主要针对大量相同值的列而创建。拿全国居民登录一第表来说，假设有四个字段：姓名、性别、年龄、和，年龄和性别两个字段会产生许多相同的值，性别只有男女两种值，年龄，1到120（假设最大年龄120岁）个值。那么不管一张表有几亿条记录，但根据性别字段来区分的话，只有两种取值（男、女）。那么位图索引就是根据字段的这个特性所建立的一种索引。

BitmapIndex

2 场合：列的基数很少，可枚举，重复值很多，数据不会被经常更新
3 原理：一个键值对应很多行（rowid），格式：键值 start_rowid end_rowid 位图
4 优点：OLAP 例如报表类数据库重复率高的数据特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果
位图索引的特点：
1.Bitmap索引的存储空间节省
2.Bitmap索引创建的速度快
3.Bitmap索引允许键值为空
4.Bitmap索引对表记录的高效访问

5 缺点：不适合重复率低的字段，还有经常DML操作（insert，update，delete），因为位图索引的锁代价极高，修改一个位图索引段影响整个位图段，例如修改
一个键值，会影响同键值的多行，所以对于OLTP 系统位图索引基本上是不适用的
举例讲解：
假设存在数据表T，有两个数据列A和B，取值如下，我们看到A和B列中存在相同的数据。
对两个数据列A、B分别建立位图索引：idx_t_bita和idx_t_bitb。两个索引对应的存储逻辑结构如下：
Idx_t_bita索引结构，对应的是叶子节点：
Idx_t_bitb索引结构，对应的是叶子节点：
实验：位图索引与B-tree索引性能比较

利用dba_objects创建两个相同行数据的表t3,t4
SQL> create table t3 as select * from dba_objects;
SQL> insert into t3 select * from t3;
72480 rows created.
/
144960 rows created.

因object_type字段重复值较高，顾在此字段上创建bitmap索引
SQL> create bitmap index t3_index on t3(object_type);

创建一个和t3表结构一模一样的表t4，并在object_type列上创建一个B-tree索引
SQL> create table t4 as select * from t3;

在t4表object_type创建B-tree索引
SQL> create index t4_index on t4(object_type);

对比位图索引和B-tree 索引所占空间大小,很明显位图要远远小于B-tree索引所占用的空间，节约空间特性也是我们选择位图的理由之一
SQL> select segment_name,bytes/1024/1024||'M' bytes from user_segments where segment_type='INDEX';

SEGMENT_NAME BYTES

T1_INDEX 2M
T2_INDEX 2M
T3_INDEX .1875M
T4_INDEX 7M
显示执行计划
SQL> set autotrace trace exp stat;
SQL> select count(*) from t3 where object_type='TABLE';

Execution Plan

| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00
:00:01 |

| 1 | SORT AGGREGATE | | 1 | 11 | |
|

| 2 | BITMAP CONVERSION COUNT | | 6401 | 70411 | 1 (0)| 00
:00:01 |

Predicate Information (identified by operation id):

3 - access("OBJECT_TYPE"='TABLE')

Note

dynamic sampling used for this statement (level=2)

Statistics

9 recursive calls
0 db block gets
83 consistent gets
1 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

在创建有B-tree索引的表上做count操作对比执行计划
SQL> select count(*) from t4 where object_type='TABLE';
Execution Plan

Plan hash value: 1074473071

| 0 | SELECT STATEMENT | | 1 | 11 | 35 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | INDEX RANGE SCAN| T4_INDEX | 12431 | 133K| 35 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - access("OBJECT_TYPE"='TABLE')

Note

dynamic sampling used for this statement (level=2)

Statistics

9 recursive calls
0 db block gets
114 consistent gets 一致性读的次数高于位图索引表的次数，位图索引效率高
29 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

我们再看看等值查找where object_type='TABLE'情况下位图索引和B-tree索引的性能对比
SQL> select * from t3 where object_type='TABLE' ;

11424 rows selected.

Execution Plan

Plan hash value: 3749216620

| 0 | SELECT STATEMENT | | 6401 | 1293K| 508 (0)| 0
0:00:07 |

| 1 | TABLE ACCESS BY INDEX ROWID | T3 | 6401 | 1293K| 508 (0)| 0
0:00:07 |

| 2 | BITMAP CONVERSION TO ROWIDS| | | | |
|

Predicate Information (identified by operation id):

3 - access("OBJECT_TYPE"='TABLE')

Note

dynamic sampling used for this statement (level=2)

Statistics

7 recursive calls
0 db block gets
1542 consistent gets
0 physical reads
0 redo size
1264161 bytes sent via SQL*Net to client
8894 bytes received via SQL*Net from client
763 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11424 rows processed

t4 表上使用B-tree索引得到执行计划
SQL> select /+index(t4 t4_index) / * from t4 where object_type='TABLE' ;

11424 rows selected.

Execution Plan

Plan hash value: 81425413

| 0 | SELECT STATEMENT | | 12431 | 2512K| 529 (0)| 00
:00:07 |

| 1 | TABLE ACCESS BY INDEX ROWID| T4 | 12431 | 2512K| 529 (0)| 00
:00:07 |

|* 2 | INDEX RANGE SCAN | T4_INDEX | 12431 | | 35 (0)| 00
:00:01 |

Predicate Information (identified by operation id):

2 - access("OBJECT_TYPE"='TABLE')

Note

dynamic sampling used for this statement (level=2)

Statistics

290 recursive calls
0 db block gets
2363 consistent gets
0 physical reads
0 redo size
1264161 bytes sent via SQL*Net to client
8894 bytes received via SQL*Net from client
763 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11424 rows processed
小结：在等值查找中我们可以看出位图索引的效率依言高于B-tree索引

第五章创建索引的规则

1、权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。
这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也要跟着修改。这里需要权衡我们的操作是查询多还是修改多。
2、把索引与对应的表放在不同的。
当读取一个表时表与索引是同时进行的。如果表与索引和在一个表空间里就会产生资源竞争，放在两个表这空就可。
3、最好使用一样大小是块。
Oracle默认五块，读一次，如果你定义6个块或10个块都需要读取两次I/O。最好是5的整数倍更能提高效率。
4、如果一个表很大，建立索引的时间很长，因为建立索引也会产生大量的redo信息，所以在创建索引时可以设置不产生或少产生redo信息。只要表数据存在，索引失败了大不了再建，所以可以不需要产生redo信息。

5、建索引的时候应该根据具体的业务SQL来创建，特别是where条件，还有where条件的顺序，尽量将过滤大范围的放在后面，因为SQL执行是从后往前的。（小李飛菜刀）

第六章创建索引时的可选项

oracle中建索引可能大家都会，但是建索引是有几个选项参数却很少有人关注，在某些特殊环境下，可能会非常有用，下面一一说明：
1.NOSORT，记录排序可选项。
默认情况下，在表中创建索引的时候，会对表中的记录进行排序，排序成功后再创建索引。但是当记录比较多的是，这个排序作业会占用比较多的时间。
特殊情况下，我们就可以使用该参数加快建索引的速度。
例如：
create index idx_scm_salaud_bill_hdr_relid
on SCM_SALAUD_BILL_HDR (audrelateid)nosort;

2.online ,在线建索引
在生产环境中建索引时，经常碰到：“数据表处于busy状态，需要加nowait参数”的提示。
因为：默认情况下，数据库系统是不允许DML操作与创建索引的操作同时进行的，例如上文的索引，在建的同时，不允许有人在SCM_SALAUD_BILL_HDR 上执行update、insert等操作，这个在生产环境中几乎是不可能。
那online选项就派上用场，可以 DML操作与创建索引操作是否可以同时进行，但是代价就是建索引的时间会延长。
create index idx_scm_salaud_bill_hdr_relid
on SCM_SALAUD_BILL_HDR (audrelateid)online;

3.NOLOGGING，是否需要记录日志信息
这个好理解，就是建索引时记不记日志，一般用在在大型表上建索引，使用该参数，默认是记日志。

4.COMPUTE STATISTICS，是否更新统计信息。
数据库在选择执行计划时，依据就是表、索引的统计信息，该参数会提示数据库建索引的同时，更新对应的统计信息。但是在一个数据修改量比较大的环境中，使用该选项有可能导致执行计划的不稳定。

5.PARALLEL，多服务进程创建索引。
数据库服务器若是多CPU情况下，使用该参数会增加并发，提高效率

第七章 Oracle 索引的维护

7.1 查看系统表中的用户索引
在Oracle中，SYSTEM表是安装数据库时自动建立的，它包含数据库的全部数据字典，存储过程、包、函数和触发器的定义以及系统回滚段。
一般来说，应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象。因为这样会带来数据库维护和管理的很多问题。一旦SYSTEM表损坏了，只能重新生成数据库。我们可以用下面的语句来检查在SYSTEM表内有没有其他用户的索引存在。
SQL> select owner from dba_indexes where tablespace_name='SYSTEM' and owner not in('SYS','SYSTEM');

OWNER

OUTLN
OUTLN
OUTLN
OUTLN
OUTLN
ORDDATA
ORDDATA
ORDDATA
OLAPSYS
OLAPSYS
MDSYS
MDSYS
MDSYS
MDSYS
MDSYS
MDSYS
MDSYS

17 rows selected.

7.2 索引的存储情况检查
Oracle为数据库中的所有数据分配逻辑结构空间。数据库空间的单位是block、extent和segment。
Block ：是Oracle使用和分配的最小存储单位。它是由数据库建立时设置的DB_BLOCK_SIZE决定的。一旦数据库生成了，数据块的大小不能改变。要想改变只能重新建立数据库。
Extent ：是由一组连续的block组成的。一个或多个extent组成一个segment。当一个segment中的所有空间被用完时，Oracle为它分配一个新的extent。
Segment ：是由一个或多个extent组成的。它包含某表空间中特定逻辑存储结构的所有数据。一个段中的extent可以是不连续的，甚至可以在不同的数据文件中。
一个object只能对应于一个逻辑存储的segment，我们通过查看该segment中的extent，可以看出相应object的存储情况。
（1）查看索引段中extent的数量：
SQL> SELECT segment_name,COUNT(*) FROM dba_extents WHERE segment_type = 'INDEX' AND owner = UPPER ('SYS') AND SEGMENT_NAME LIKE 'T%' GROUP BY segment_name;

SEGMENT_NAME COUNT(*)

T3_INDEX 3
T1_INDEX 17
T2_INDEX 17
T4_INDEX 22
（2）查看表空间内的索引的扩展情况：
SQL> select substr(segment_name,1,20) "segment name",bytes,count(bytes) from dba_extents
where segment_name in (select index_name from dba_indexes where tablespace_name=UPPER('SYS'))
group by segment_name,bytes order by segment_name;

no rows selected

7.3索引的选择性
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

如果是使用基于cost的最优化，优化器不应该使用选择性不好的索引。如果是使用基于rule的最优化，优化器在确定执行路径时不会考虑索引的选择性（除非是唯一性索引），并且不得不手工优化查询以避免使用非选择性的索引。
确定索引的选择性，可以有两种方法：手工测量和自动测量。

（1）手工测量索引的选择性
如果要根据一个表的两列创建两列并置索引，可以用以下方法测量索引的选择性：
　　列的选择性=不同值的数目/行的总数/越接近1越好/
selectcount(distinct第一列||"%"||第二列)/count(*)from表名
如果我们知道其中一列索引的选择性（例如其中一列是主键），那么我们就可以知道另一列索引的选择性。
手工方法的优点是在创建索引前就能评估索引的选择性。

（2）自动测量索引的选择性
如果分析一个表，也会自动分析所有表的索引。
第一，为了确定一个表的确定性，就要分析表。
analyzetable表名computestatistics
第二，确定索引里不同关键字的数目：
selectdistinct_keysfromuser_indexeswheretable_name="表名"andindex_name="索引名"
第三，确定表中行的总数：
selectnum_rowsfromuser_tableswheretable_name="表名"
第四，索引的选择性=索引里不同关键字的数目/表中行的总数：
selecti.distinct_keys/t.num_rowsfromuser_indexesi,user_tablest
wherei.table_name="表名"andi.index_name="索引名"andi.table_name=t.table_name
第五，可以查询USER_TAB_COLUMNS以了解每个列的选择性。
表中所有行在该列的不同值的数目：
selectcolumn_name,num_distinctfromuser_tab_columnswheretable_name="表名"
列的选择性=NUM_DISTINCT/表中所有行的总数，查询USER_TAB_COLUMNS有助测量每个列的选择性，但它并不能精确地测量列的并置组合的选择性。要想测量一组列的选择性，需要采用手工方法或者根据这组列创建一个索引并重新分析表。

7.4确定索引的实际碎片
随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的程度也越高。碎片的产生使访问和使用该索引的I/O成本增加。碎片较高的索引必须重建以保持最佳性能。

（1）利用验证索引命令对索引进行验证。
这将有价值的索引信息填入index_stats表。
validateindex用户名.索引名
或者：
analyzeindexindex_namevalidatestructure;
注意：index_stats只保存最近一次分析的结果

（2）查询index_stats表以确定索引中删除的、未填满的叶子（Leaf）行的百分比和height字段。
selectname,height,del_lf_rows,lf_rows,round((del_lf_rows/(lf_rows+0.0000000001))*100)"FragPercent"fromindex_stats

（3）如果索引的叶子行的碎片超过10%，或者index_stats中height>=4,可以考虑对索引进行重建。
alterindex用户名.索引名rebuildtablespace表空间名storage(initial初始值next扩展值)nologging
/Formattedon2010/6/1921:58:45(QP5v5.115.810.9015)/
SELECT'alterindex'||owner||'.'||segment_name||'rebuild;'
FROM(SELECTCOUNT(*),
owner,
segment_name,
t.tablespace_name
FROMdba_extentst
WHEREt.segment_type='INDEX'
ANDt.ownerNOTIN('SYS','SYSTEM')
GROUPBYowner,segment_name,t.tablespace_name
HAVINGCOUNT(*)>10
ORDERBYCOUNT(*)DESC);

（4）如果出于空间或其他考虑，不能重建索引，可以整理索引。
alterindex用户名.索引名coalesce

（5）清除分析信息
analyzeindex用户名.索引名deletestatistics

7.5重建索引
（1）检查需要重建的索引
根据以下几方面进行检查，确定需要重建的索引。
第一，查看SYSTEM表空间中的用户索引
为了避免数据字典的碎片出现，要尽量避免在SYSTEM表空间出现用户的表和索引。
selectindex_namefromdba_indexeswheretablespace_name="SYSTEM"andownernotin("SYS","SYSTEM")

第二，确保用户的表和索引不在同一表空间内
表和索引对象的第一个规则是把表和索引分离。把表和相应的索引建立在不同的表空间中，最好在不同的磁盘上。这样可以避免在数据管理和查询时出现的许多I/O冲突。
SELECTi.owner"OWNER",
i.index_name"INDEX",
t.table_name"TABLE",
i.tablespace_name"TABLESPACE"
FROMdba_indexesi,dba_tablest
WHEREi.owner=t.owner
ANDi.table_name=t.table_name
ANDi.tablespace_name=t.tablespace_name
ANDi.ownerNOTIN('SYS','SYSTEM')

第三，查看数据表空间里有哪些索引
用户的默认表空间应该不是SYSTEM表空间，而是数据表空间。在建立索引时，如果不指定相应的索引表空间名，那么，该索引就会建立在数据表空间中。这是程序员经常忽略的一个问题。应该在建索引时，明确的指明相应的索引表空间。
SELECTowner,segment_name,SUM(bytes)
FROMdba_segments
WHEREtablespace_name='SYSTEM'ANDsegment_type='INDEX'
GROUPBYowner,segment_name

第四，查看哪个索引被扩展了超过10次
随着表记录的增加，相应的索引也要增加。如果一个索引的nextextent值设置不合理（太小），索引段的扩展变得很频繁。索引的extent太多，检索时的速度和效率就会降低。
（1）查看索引扩展次数
SELECTCOUNT(*),
owner,
segment_name,
tablespace_name
FROMdba_extents
WHEREsegment_type='INDEX'ANDownerNOTIN('SYS','SYSTEM')
GROUPBYowner,segment_name,tablespace_name
HAVINGCOUNT( * )>10
ORDERBYCOUNT( * )DESC

（2）找出需要重建的索引后，需要确定索引的大小，以设置合理的索引存储参数。
SELECTowner"OWNER",
segment_name"INDEX",
tablespace_name"TABLESPACE",
bytes"BYTES/COUNT",
SUM(bytes)"TOTALBYTES",
ROUND(SUM(bytes)/(1024*1024),0)"TOTALM",
COUNT(bytes)"TOTALCOUNT"
FROMdba_extents
WHEREsegment_type='INDEX'
ANDsegment_nameIN('INDEX_NAME1','INDEX_NAME2')
GROUPBYowner,
segment_name,
segment_type,
tablespace_name,
bytes
ORDERBYowner,segment_name
（3）确定索引表空间还有足够的剩余空间
确定要把索引重建到哪个索引表空间中。要保证相应的索引表空间有足够的剩余空间。
SELECTROUND(bytes/(1024*1024),2)"free(M)"
FROMsm$ts_free
WHEREtablespace_name='表空间名'

（4）重建索引
重建索引时要注意以下几点：
a.如果不指定tablespace名，索引将建在用户的默认表空间。
b.如果不指定nologging，将会写日志，导致速度变慢。由于索引的重建没有恢复的必要，所以，可以不写日志。
c.如果出现资源忙，表明有进程正在使用该索引，等待一会再提交。
alterindex索引名rebuildtablespace索引表空间名storage(initial初始值next扩展值)nologging

（5）检查索引
对重建好的索引进行检查。
select * fromdba_extentswheresegment_name="索引名"
（6）根据索引进行查询，检查索引是否有效
使用相应的where条件进行查询，确保使用该索引。看看使用索引后的效果如何。
select * fromdba_ind_columnswhereindex_name='索引名'
然后，根据相应的索引项进行查询。
select * from"表名"where...

（7）找出有碎片的表空间，并收集其碎片。
重建索引后，原有的索引被删除，这样会造成表空间的碎片。
SELECT'altertablespace'||tablespace_name||'coalesce;'
FROMdba_free_space_coalesced
WHEREpercent_blocks_coalesced!=100

查看索引占用空间大小：
select(sum(bytes)/1024/1024)||'MB'fromdba_segmentswheresegment_name='INDBILLLOG5_CALLEND';
查看表占用空间大小
select(sum(bytes)/1024/1024)||'MB'fromdba_segmentswheresegment_name='TBILLLOG5';

整理表空间的碎片。
altertablespace表空间名coalesce

第八章索引常见操作

改变索引：
SQL> alter index employees_last _name_idx storage(next 400K maxextents 100);
索引创建后，感觉不合理，也可以对其参数进行修改。详情查看相关文档

调整索引的空间：
新增加空间
SQL> alter index orders_region_id_idx allocate extent (size 200K datafile '/disk6/index01.dbf');

释放空间
SQL> alter index oraers_id_idx deallocate unused;
索引在使用的过程中可能会出现空间不足或空间浪费的情况，这个时候需要新增或释放空间。上面两条命令完成新增与释放操作。关于空间的新增oracle可以自动帮助，如果了解数据库的情况下手动增加可以提高性能。

重新创建索引：
索引是由oracle自动完成，当我们对数据库频繁的操作时，索引也会跟着进行修改，当我们在数据库中删除一条记录时，对应的索引中并没有把相应的索引只是做一个删除标记，但它依然占据着空间。除非一个块中所有的标记全被删除的时，整个块的空间才会被释放。这样时间久了，索引的性能就会下降。这个时候可以重新建立一个干净的索引来提高效率。
SQL> alter index orders_region_id_idx rebuild tablespace index02;
通过上面的命令就可以重现建立一个索引，oracle重建立索引的过程：
1、锁表，锁表之后其他人就不能对表做任何操作。
2、创建新的（干净的）临时索引。
3、把老的索引删除掉
4、把新的索引重新命名为老索引的名字
5、对表进行解锁。

移动所引：
其实，我们移动索引到其它表空间也同样使用上面的命令，在指定表空间时指定不同的表空间。新的索引创建在别位置，把老的干掉，就相当于移动了。
SQL> alter index orders_region_id_idx rebuild tablespace index03;
在线重新创建索引：
上面介绍，在创建索引的时候，表是被锁定，不能被使用。对于一个大表，重新创建索引所需要的时间较长，为了满足用户对表操作的需求，就产生的这种在线重新创建索引。
SQL> alter index orders_id_idx rebuild online;
创建过程：
1、锁住表
2、创建立临时的和空的索引和IOT表用来存在on-goingDML。普通表存放的键值，IOT所引表直接存放的表中数据；on-gongDML也就是用户所做的一些增删改的操作。
3、对表进行解锁
4、从老的索引创建一个新的索引。
5、IOT表里存放的是on-goingDML信息，IOT表的内容与新创建的索引合并。
6、锁住表
7、再次将IOT表的内容更新到新索引中，把老的索引干掉。
8、把新的索引重新命名为老索引的名字
9、对表进行解锁

整合索引碎片：
如上图，在很多索引中有剩余的空间，可以通过一个命令把剩余空间整合到一起。　　
SQL> alter index orders_id_idx coalesce;

删除索引：
SQL> drop index hr.departments_name_idx;

第九章分析索引

　　检查所引的有效果，前面介绍，索引用的时间久了会产生大量的碎片、垃圾信息与浪费的剩余空间了。可以通过重新创建索引来提高所引的性能。
可以通过一条命令来完成分析索引，分析的结果会存放在在index_stats表中。
查看存放分析数据的表：

SQL> select count(*) from index_stats;

COUNT(*)
--------

**0**

执行分析索引命令：

SQL> analyze index my_bit_idx validate structure;

Index analyzed.

再次查看 index_stats 已经有了一条数据

SQL> select count(*) from index_stats;

COUNT(*)
--------

**1**
```
把数据查询出来：
SQL> select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;

HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS

---

**2** MY_BIT_IDX **1000** **3** **100**
**分析数据分析**：
（HEIGHT）这个所引高度是2，（NAME）索引名为MY_BIT_IDX，（LF_ROWS）所引表有1000行数据，（LF_BLKS）占用3个块，（DEL_LF_ROWS）删除100条记录。
　　这里也验证了前面所说的一个问题，删除的100条数据只是标记为删除，因为总的数据条数依然为1000条，占用3个块，那么每个块大于333条记录，只有删除的数据大于333条记录，这时一个块被清空，总的数据条数才会减少。

**转载自**[chenoracle](http://blog.itpub.net/29785807/viewspace-1269118/)

最后修改：2022 年 04 月 02 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

oracle 索引详解

Tehran • 2022 年 03 月 02 日

<div class="tip share">请注意，本文编写于 780 天前，最后修改于 748 天前，其中某些信息可能已经过时。</div>

<h3>第一章 索引的定义</h3>在 中，索引是一种与表有关的数据库结构，它可以使对应于表的 语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是一个独立于表的对象，可以存放在与表不同的表空间中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引。<h3>第二章 索引的分类</h3><h4>1 索引逻辑分类</h4>单列索引：基于一列的操作 多列索引：组合索引，最多为32列。组合索引的列不一定与表中列顺序相同。 惟一索引：列的值各不相同。 非惟一索引：列的值允许相同。 基于函数的惟一索引：利用表中一列或多列基于函数表达式所创建的索引。既可以是B-树，也可以是位图索引。<h4>2 索引物理分类</h4>分区索引 非分区索引：非分区既可以是B－树，也可以是位图索引。 B-树索引：包括正常索引或反转关键字索引 位图索引<h3>第三章 B-Tree 索引详解</h3><h4>1 结构</h4>B-Treeindex也是我们传统上常见所理解的索引。 （balancetree）即 ，左右两个分支相对平衡。Root为根节点，branch为分支节点，leaf到最下面一层称为 。每个节点表示一层，当查找某一数据时先读根节点，再读支节点，最后找到叶子节点。叶子节点会存放indexentry（索引入口），每个索引入口对应一条记录。 Indexentry的组成部分： Indexentryentryheader　　存放一些控制信息。 Keycolumnlength　　某一key的长度 Keycolumnvalue　　某一个key的值 ROWID　　指针，具体指向于某一个数据<h4>2 场合：非常适合数据重复度低的字段 例如 身份证号码 手机号码 QQ号等字段，常用于主键 唯一约束，一般在在线交易的项目中用到的多些。</h4><h4>3 原理：一个键值对应一行（rowid） 格式： 【索引头|键值|rowid】</h4><h4>4 优点：当没有索引的时候，oracle只能全表扫描where qq=40354446 这个条件那么这样是灰常灰常耗时的，当数据量很大的时候简直会让人崩溃，那么有个B-tree索引我们就像翻书目录一样，直接定位rowid立刻就找到了我们想要的数据，实质减少了I/O操作就提高速度，它有一个显著特点查询性能与表中数据量无关，例如 查2万行的数据用了3 consistent get,当查询1200万行的数据时才用了4 consistent gets。</h4>当我们的字段中使用了主键or唯一约束时，不用想直接可以用B-tree索引<h4>5 缺点：不适合键值重复率较高的字段上使用，例如 第一章 1-500page 第二章 501-1000page</h4><h4>实验：</h4>1 清空共享池，数据库缓冲区 SQL&gt; alter system flush shared_pool; SQL&gt; alter system flush buffer_cache;2 创建表t1,t2 T1表中没有重复值 SQL&gt; create table t1 as select object_id,object_name from dba_objects;Table created.<pre><code>SQL&gt; select count(*) from t1;

COUNT(*)
----------
72476

SQL&gt; select * from t1 where rownum&lt;=5;
OBJECT_ID OBJECT_NAME
---------- --------------------
20 ICOL$
46 I_USER1
28 CON$
15 UNDO$
29 C_COBJ#</code></pre>t2 表的object_id列我们是做了取余操作，值就只有0,1两种，因此重复率较高，如此设置为了说明重复率对B树索引的影响； SQL&gt; create table t2 as select mod(object_id,2) object_id,object_name from dba_objects;<pre><code>SQL&gt; select * from t2 where rownum&lt;=5;

OBJECT_ID OBJECT_NAME
---------- --------------------
0 ICOL$
0 I_USER1
0 CON$
1 UNDO$
1 C_COBJ#</code></pre>3 创建索引 SQL&gt; create index t1_index on t1(object_id); SQL&gt; create index t2_index on t2(object_id);4 查看t1,t2的重复率 SQL&gt; select count(distinct(object_id)) from t1;<pre><code>COUNT(DISTINCT(OBJECT_ID))
--------------------------
72476</code></pre>SQL&gt; select count(distinct(object_id)) from t2;COUNT(DISTINCT(OBJECT_ID))<hr>25 收集表的统计信息 SQL&gt; execute dbms_stats.gather_table_stats(ownname=&gt;'SYS',tabname=&gt;'T1',method_opt=&gt;'for all indexed columns size 2',cascade=&gt;TRUE);SQL&gt; execute dbms_stats.gather_table_stats(ownname=&gt;'SYS',tabname=&gt;'T2',method_opt=&gt;'for all indexed columns size 2',cascade=&gt;TRUE);显示执行计划和统计信息+设置autotrace简介 序号 命令 解释 1 SET AUTOTRACE OFF 此为默认值，即关闭Autotrace 2 SET AUTOTRACE ON EXPLAIN 只显示执行计划 3 SET AUTOTRACE ON STATISTICS 只显示执行的统计信息 4 SET AUTOTRACE ON 包含2,3两项内容 5 SET AUTOTRACE TRACEONLY 与ON相似，但不显示语句的执行结果T1（重复率低的表） SQL&gt; set autotrace on explain SQL&gt; select * from t1 where object_id=1;Execution Plan<hr>Plan hash value: 1186333541TABLE ACCESS BY INDEX ROWID| T1 -------------走it_index索引了INDEX RANGE SCAN | T1_INDEXStatistics<hr>2 consistent gets t1表的object_id没有重复值，因此使用B-tree索引扫描只有2次一致性读T2(重复率高的表)SQL&gt; select * from t2 where object_id=1; Execution Plan<hr>Plan hash value: 1513984157<hr>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<hr>| 0 | SELECT STATEMENT | | 36199 | 954K| 90 (2)| 00:00:02 | |* 1 | TABLE ACCESS FULL| T2 | 36199 | 954K| 90 (2)| 00:00:02 | 2739 consistent gets T2为什么要用全表扫描而不用B-tree索引呢？这是因为oracle基于成本优化器CBO认为使用全表扫描要比使用B-tree索引性能更好更快，由于我们结果重复率很高，导致有2739次一致性读，从cup使用率90%上看也说明了B-tree索引不适合键值重复率较高的列我们在看一下 强制使用B-tree索引时，效率是不是没有全表扫描高呢？ SQL&gt; select /+index(t2 t2_index) / * from t2 where object_ID=1; Execution Plan<hr>Plan hash value: 1666064008<hr><hr>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti me |<hr><hr>| 0 | SELECT STATEMENT | | 36199 | 954K| 391 (1)| 00 :00:05 || 1 | TABLE ACCESS BY INDEX ROWID| T2 | 36199 | 954K| 391 (1)| 00 :00:05 ||* 2 | INDEX RANGE SCAN | T2_INDEX | 36199 | | 69 (0)| 00 :00:01 |5220 consistent gets 使用B-tree索引5220次一致性读 &gt; 全表扫描2739次一致性读，而且cpu使用率也非常高，显然效果没有全表扫描高小结：从以上的测试我们可以了解到，B-tree索引在什么情况下使用跟键值重复率高低有很大关系的，之间没有一个明确的分水岭，只能多测试分析执行计划后来决定。 第四章 位图索引详解 1结构位图索引主要针对大量相同值的列而创建。拿全国居民登录一第表来说，假设有四个字段：姓名、性别、年龄、和 ，年龄和性别两个字段会产生许多相同的值，性别只有男女两种值，年龄，1到120（假设最大年龄120岁）个值。那么不管一张表有几亿条记录，但根据性别字段来区分的话，只有两种取值（男、女）。那么位图索引就是根据字段的这个特性所建立的一种索引。BitmapIndex2 场合：列的基数很少，可枚举，重复值很多，数据不会被经常更新 3 原理：一个键值对应很多行（rowid）， 格式：键值 start_rowid end_rowid 位图 4 优点：OLAP 例如报表类数据库 重复率高的数据 特定类型的查询例如count、or、and等逻辑操作因为只需要进行位运算即可得到我们需要的结果 位图索引的特点： 1.Bitmap索引的存储空间节省 2.Bitmap索引创建的速度快 3.Bitmap索引允许键值为空 4.Bitmap索引对表记录的高效访问5 缺点：不适合重复率低的字段，还有经常DML操作（insert，update，delete），因为位图索引的锁代价极高，修改一个位图索引段影响整个位图段，例如修改 一个键值，会影响同键值的多行，所以对于OLTP 系统位图索引基本上是不适用的 举例讲解： 假设存在数据表T，有两个数据列A和B，取值如下，我们看到A和B列中存在相同的数据。 对两个数据列A、B分别建立位图索引：idx_t_bita和idx_t_bitb。两个索引对应的存储逻辑结构如下： Idx_t_bita索引结构，对应的是叶子节点： Idx_t_bitb索引结构，对应的是叶子节点： 实验：位图索引与B-tree索引性能比较利用dba_objects创建两个相同行数据的表t3,t4 SQL&gt; create table t3 as select * from dba_objects; SQL&gt; insert into t3 select * from t3; 72480 rows created. / 144960 rows created.因object_type字段重复值较高，顾在此字段上创建bitmap索引 SQL&gt; create bitmap index t3_index on t3(object_type);创建一个和t3表结构一模一样的表t4，并在object_type列上创建一个B-tree索引 SQL&gt; create table t4 as select * from t3;在t4表object_type创建B-tree索引 SQL&gt; create index t4_index on t4(object_type);对比位图索引和B-tree 索引所占空间大小,很明显位图要远远小于B-tree索引所占用的空间，节约空间特性也是我们选择位图的理由之一 SQL&gt; select segment_name,bytes/1024/1024||'M' bytes from user_segments where segment_type='INDEX';SEGMENT_NAME BYTES<hr>T1_INDEX 2M T2_INDEX 2M T3_INDEX .1875M T4_INDEX 7M 显示执行计划 SQL&gt; set autotrace trace exp stat; SQL&gt; select count(*) from t3 where object_type='TABLE';Execution Plan<hr>Plan hash value: 3466593685 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti me |<hr><hr>| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00 :00:01 || 1 | SORT AGGREGATE | | 1 | 11 | | || 2 | BITMAP CONVERSION COUNT | | 6401 | 70411 | 1 (0)| 00 :00:01 ||* 3 | BITMAP INDEX SINGLE VALUE| T3_INDEX | | | | |<hr><hr>Predicate Information (identified by operation id):<hr>3 - access("OBJECT_TYPE"='TABLE')Note<hr><ul><li>dynamic sampling used for this statement (level=2)</li></ul>Statistics<hr>9 recursive calls 0 db block gets 83 consistent gets 1 physical reads 0 redo size 528 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed在创建有B-tree索引的表上做count操作对比执行计划 SQL&gt; select count(*) from t4 where object_type='TABLE'; Execution Plan<hr>Plan hash value: 1074473071<hr>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<hr>| 0 | SELECT STATEMENT | | 1 | 11 | 35 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | | 2 | INDEX RANGE SCAN| T4_INDEX | 12431 | 133K| 35 (0)| 00:00:01 |<hr>Predicate Information (identified by operation id):<hr>2 - access("OBJECT_TYPE"='TABLE')Note<hr><ul><li>dynamic sampling used for this statement (level=2)</li></ul>Statistics<hr>9 recursive calls 0 db block gets 114 consistent gets 一致性读的次数高于位图索引表的次数，位图索引效率高 29 physical reads 0 redo size 528 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed我们再看看等值查找where object_type='TABLE'情况下位图索引和B-tree索引的性能对比 SQL&gt; select * from t3 where object_type='TABLE' ;11424 rows selected.Execution Plan<hr>Plan hash value: 3749216620<hr><hr>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T ime |<hr><hr>| 0 | SELECT STATEMENT | | 6401 | 1293K| 508 (0)| 0 0:00:07 || 1 | TABLE ACCESS BY INDEX ROWID | T3 | 6401 | 1293K| 508 (0)| 0 0:00:07 || 2 | BITMAP CONVERSION TO ROWIDS| | | | | ||* 3 | BITMAP INDEX SINGLE VALUE | T3_INDEX | | | | |<hr><hr>Predicate Information (identified by operation id):<hr>3 - access("OBJECT_TYPE"='TABLE')Note<hr><ul><li>dynamic sampling used for this statement (level=2)</li></ul>Statistics<hr>7 recursive calls 0 db block gets 1542 consistent gets 0 physical reads 0 redo size 1264161 bytes sent via SQL*Net to client 8894 bytes received via SQL*Net from client 763 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11424 rows processedt4 表上使用B-tree索引得到执行计划 SQL&gt; select /+index(t4 t4_index) / * from t4 where object_type='TABLE' ;11424 rows selected.Execution Plan<hr>Plan hash value: 81425413<hr><hr>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti me |<hr><hr>| 0 | SELECT STATEMENT | | 12431 | 2512K| 529 (0)| 00 :00:07 || 1 | TABLE ACCESS BY INDEX ROWID| T4 | 12431 | 2512K| 529 (0)| 00 :00:07 ||* 2 | INDEX RANGE SCAN | T4_INDEX | 12431 | | 35 (0)| 00 :00:01 |<hr><hr>Predicate Information (identified by operation id):<hr>2 - access("OBJECT_TYPE"='TABLE')Note<hr><ul><li>dynamic sampling used for this statement (level=2)</li></ul>Statistics<hr>290 recursive calls 0 db block gets 2363 consistent gets 0 physical reads 0 redo size 1264161 bytes sent via SQL*Net to client 8894 bytes received via SQL*Net from client 763 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11424 rows processed 小结：在等值查找中我们可以看出位图索引的效率依言高于B-tree索引<h3>第五章 创建索引的规则</h3>1、权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。 这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也要跟着修改。这里需要权衡我们的操作是查询多还是修改多。 2、把索引与对应的表放在不同的 。 当读取一个表时表与索引是同时进行的。如果表与索引和在一个表空间里就会产生资源竞争，放在两个表这空就可 。 3、最好使用一样大小是块。 Oracle默认五块，读一次 ，如果你定义6个块或10个块都需要读取两次I/O。最好是5的整数倍更能提高效率。 4、如果一个表很大，建立索引的时间很长，因为建立索引也会产生大量的redo信息，所以在创建索引时可以设置不产生或少产生redo信息。只要表数据存在，索引失败了大不了再建，所以可以不需要产生redo信息。5、建索引的时候应该根据具体的业务SQL来创建，特别是where条件，还有where条件的顺序，尽量将过滤大范围的放在后面，因为SQL执行是从后往前的。（小李飛菜刀）<h3>第六章 创建索引时的可选项</h3>oracle中建索引可能大家都会，但是建索引是有几个选项参数却很少有人关注，在某些特殊环境下，可能会非常有用，下面一一说明： 1.NOSORT，记录排序可选项。 默认情况下，在表中创建索引的时候，会对表中的记录进行排序，排序成功后再创建索引。但是当记录比较多的是，这个排序作业会占用比较多的时间。 特殊情况下，我们就可以使用该参数加快建索引的速度。 例如： create index idx_scm_salaud_bill_hdr_relid on SCM_SALAUD_BILL_HDR (audrelateid)nosort;2.online ,在线建索引 在生产环境中建索引时，经常碰到：“数据表处于busy状态，需要加nowait参数”的提示。 因为：默认情况下，数据库系统是不允许DML操作与创建索引的操作同时进行的，例如上文的索引，在建的同时，不允许有人在SCM_SALAUD_BILL_HDR 上执行update、insert等操作，这个在生产环境中几乎是不可能。 那online选项就派上用场，可以 DML操作与创建索引操作是否可以同时进行，但是代价就是建索引的时间会延长。 create index idx_scm_salaud_bill_hdr_relid on SCM_SALAUD_BILL_HDR (audrelateid)online;3.NOLOGGING，是否需要记录日志信息 这个好理解，就是建索引时记不记日志，一般用在在大型表上建索引，使用该参数，默认是记日志。4.COMPUTE STATISTICS，是否更新统计信息。 数据库在选择执行计划时，依据就是表、索引的统计信息，该参数会提示数据库建索引的同时，更新对应的统计信息。但是在一个数据修改量比较大的环境中，使用该选项有可能导致执行计划的不稳定。5.PARALLEL，多服务进程创建索引。 数据库服务器若是多CPU情况下，使用该参数会增加并发，提高效率<h3>第七章 <a href="https://uuks.cn/go/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdGlhbmxlc29mdHdhcmUvYXJ0aWNsZS9kZXRhaWxzLzU2ODA3MDY" target="_blank" >Oracle</a> 索引的维护</h3>7.1 查看系统表中的用户索引 在Oracle中，SYSTEM表是安装数据库时自动建立的，它包含数据库的全部数据字典，存储过程、包、函数和触发器的定义以及系统回滚段。 一般来说，应该尽量避免在SYSTEM表中存储非SYSTEM用户的对象。因为这样会带来数据库维护和管理的很多问题。一旦SYSTEM表损坏了，只能重新生成数据库。我们可以用下面的语句来检查在SYSTEM表内有没有其他用户的索引存在。 SQL&gt; select owner from dba_indexes where tablespace_name='SYSTEM' and owner not in('SYS','SYSTEM');OWNER<hr>OUTLN OUTLN OUTLN OUTLN OUTLN ORDDATA ORDDATA ORDDATA OLAPSYS OLAPSYS MDSYS MDSYS MDSYS MDSYS MDSYS MDSYS MDSYS17 rows selected.7.2 索引的存储情况检查 Oracle为数据库中的所有数据分配逻辑结构空间。数据库空间的单位是block、extent和segment。 Block ：是Oracle使用和分配的最小存储单位。它是由数据库建立时设置的DB_BLOCK_SIZE决定的。一旦数据库生成了，数据块的大小不能改变。要想改变只能重新建立数据库。 Extent ：是由一组连续的block组成的。一个或多个extent组成一个segment。当一个segment中的所有空间被用完时，Oracle为它分配一个新的extent。 Segment ：是由一个或多个extent组成的。它包含某表空间中特定逻辑存储结构的所有数据。一个段中的extent可以是不连续的，甚至可以在不同的数据文件中。 一个object只能对应于一个逻辑存储的segment，我们通过查看该segment中的extent，可以看出相应object的存储情况。 （1）查看索引段中extent的数量： SQL&gt; SELECT segment_name,COUNT(*) FROM dba_extents WHERE segment_type = 'INDEX' AND owner = UPPER ('SYS') AND SEGMENT_NAME LIKE 'T%' GROUP BY segment_name;SEGMENT_NAME COUNT(*)<hr>T3_INDEX 3 T1_INDEX 17 T2_INDEX 17 T4_INDEX 22 （2）查看表空间内的索引的扩展情况： SQL&gt; select substr(segment_name,1,20) "segment name",bytes,count(bytes) from dba_extents where segment_name in (select index_name from dba_indexes where tablespace_name=UPPER('SYS')) group by segment_name,bytes order by segment_name;no rows selected7.3索引的选择性 索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。如果是使用基于cost的最优化，优化器不应该使用选择性不好的索引。如果是使用基于rule的最优化，优化器在确定执行路径时不会考虑索引的选择性（除非是唯一性索引），并且不得不手工优化查询以避免使用非选择性的索引。 确定索引的选择性，可以有两种方法：手工测量和自动测量。（1）手工测量索引的选择性 如果要根据一个表的两列创建两列并置索引，可以用以下方法测量索引的选择性： 　　列的选择性=不同值的数目/行的总数/越接近1越好/ selectcount(distinct第一列||"%"||第二列)/count(*)from表名 如果我们知道其中一列索引的选择性（例如其中一列是主键），那么我们就可以知道另一列索引的选择性。 手工方法的优点是在创建索引前就能评估索引的选择性。（2）自动测量索引的选择性 如果分析一个表，也会自动分析所有表的索引。 第一，为了确定一个表的确定性，就要分析表。 analyzetable表名computestatistics 第二，确定索引里不同关键字的数目： selectdistinct_keysfromuser_indexeswheretable_name="表名"andindex_name="索引名" 第三，确定表中行的总数： selectnum_rowsfromuser_tableswheretable_name="表名" 第四，索引的选择性=索引里不同关键字的数目/表中行的总数： selecti.distinct_keys/t.num_rowsfromuser_indexesi,user_tablest wherei.table_name="表名"andi.index_name="索引名"andi.table_name=t.table_name 第五，可以查询USER_TAB_COLUMNS以了解每个列的选择性。 表中所有行在该列的不同值的数目： selectcolumn_name,num_distinctfromuser_tab_columnswheretable_name="表名" 列的选择性=NUM_DISTINCT/表中所有行的总数，查询USER_TAB_COLUMNS有助测量每个列的选择性，但它并不能精确地测量列的并置组合的选择性。要想测量一组列的选择性，需要采用手工方法或者根据这组列创建一个索引并重新分析表。7.4确定索引的实际碎片 随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的程度也越高。碎片的产生使访问和使用该索引的I/O成本增加。碎片较高的索引必须重建以保持最佳性能。（1）利用验证索引命令对索引进行验证。 这将有价值的索引信息填入index_stats表。 validateindex用户名.索引名 或者： analyzeindexindex_namevalidatestructure; 注意：index_stats只保存最近一次分析的结果（2）查询index_stats表以确定索引中删除的、未填满的叶子（Leaf）行的百分比和height字段。 selectname,height,del_lf_rows,lf_rows,round((del_lf_rows/(lf_rows+0.0000000001))*100)"FragPercent"fromindex_stats（3）如果索引的叶子行的碎片超过10%，或者index_stats中height&gt;=4,可以考虑对索引进行重建。 alterindex用户名.索引名rebuildtablespace表空间名storage(initial初始值next扩展值)nologging /Formattedon2010/6/1921:58:45(QP5v5.115.810.9015)/ SELECT'alterindex'||owner||'.'||segment_name||'rebuild;' FROM(SELECTCOUNT(*), owner, segment_name, t.tablespace_name FROMdba_extentst WHEREt.segment_type='INDEX' ANDt.ownerNOTIN('SYS','SYSTEM') GROUPBYowner,segment_name,t.tablespace_name HAVINGCOUNT(*)&gt;10 ORDERBYCOUNT(*)DESC);（4）如果出于空间或其他考虑，不能重建索引，可以整理索引。 alterindex用户名.索引名coalesce（5）清除分析信息 analyzeindex用户名.索引名deletestatistics7.5重建索引 （1）检查需要重建的索引 根据以下几方面进行检查，确定需要重建的索引。 第一，查看SYSTEM表空间中的用户索引 为了避免数据字典的碎片出现，要尽量避免在SYSTEM表空间出现用户的表和索引。 selectindex_namefromdba_indexeswheretablespace_name="SYSTEM"andownernotin("SYS","SYSTEM")第二，确保用户的表和索引不在同一表空间内 表和索引对象的第一个规则是把表和索引分离。把表和相应的索引建立在不同的表空间中，最好在不同的磁盘上。这样可以避免在数据管理和查询时出现的许多I/O冲突。 SELECTi.owner"OWNER", i.index_name"INDEX", t.table_name"TABLE", i.tablespace_name"TABLESPACE" FROMdba_indexesi,dba_tablest WHEREi.owner=t.owner ANDi.table_name=t.table_name ANDi.tablespace_name=t.tablespace_name ANDi.ownerNOTIN('SYS','SYSTEM')第三，查看数据表空间里有哪些索引 用户的默认表空间应该不是SYSTEM表空间，而是数据表空间。在建立索引时，如果不指定相应的索引表空间名，那么，该索引就会建立在数据表空间中。这是程序员经常忽略的一个问题。应该在建索引时，明确的指明相应的索引表空间。 SELECTowner,segment_name,SUM(bytes) FROMdba_segments WHEREtablespace_name='SYSTEM'ANDsegment_type='INDEX' GROUPBYowner,segment_name第四，查看哪个索引被扩展了超过10次 随着表记录的增加，相应的索引也要增加。如果一个索引的nextextent值设置不合理（太小），索引段的扩展变得很频繁。索引的extent太多，检索时的速度和效率就会降低。 （1）查看索引扩展次数 SELECTCOUNT(*), owner, segment_name, tablespace_name FROMdba_extents WHEREsegment_type='INDEX'ANDownerNOTIN('SYS','SYSTEM') GROUPBYowner,segment_name,tablespace_name HAVINGCOUNT( * )&gt;10 ORDERBYCOUNT( * )DESC（2）找出需要重建的索引后，需要确定索引的大小，以设置合理的索引存储参数。 SELECTowner"OWNER", segment_name"INDEX", tablespace_name"TABLESPACE", bytes"BYTES/COUNT", SUM(bytes)"TOTALBYTES", ROUND(SUM(bytes)/(1024*1024),0)"TOTALM", COUNT(bytes)"TOTALCOUNT" FROMdba_extents WHEREsegment_type='INDEX' ANDsegment_nameIN('INDEX_NAME1','INDEX_NAME2') GROUPBYowner, segment_name, segment_type, tablespace_name, bytes ORDERBYowner,segment_name （3）确定索引表空间还有足够的剩余空间 确定要把索引重建到哪个索引表空间中。要保证相应的索引表空间有足够的剩余空间。 SELECTROUND(bytes/(1024*1024),2)"free(M)" FROMsm$ts_free WHEREtablespace_name='表空间名'（4）重建索引 重建索引时要注意以下几点： a.如果不指定tablespace名，索引将建在用户的默认表空间。 b.如果不指定nologging，将会写日志，导致速度变慢。由于索引的重建没有恢复的必要，所以，可以不写日志。 c.如果出现资源忙，表明有进程正在使用该索引，等待一会再提交。 alterindex索引名rebuildtablespace索引表空间名storage(initial初始值next扩展值)nologging（5）检查索引 对重建好的索引进行检查。 select * fromdba_extentswheresegment_name="索引名" （6）根据索引进行查询，检查索引是否有效 使用相应的where条件进行查询，确保使用该索引。看看使用索引后的效果如何。 select * fromdba_ind_columnswhereindex_name='索引名' 然后，根据相应的索引项进行查询。 select * from"表名"where...（7）找出有碎片的表空间，并收集其碎片。 重建索引后，原有的索引被删除，这样会造成表空间的碎片。 SELECT'altertablespace'||tablespace_name||'coalesce;' FROMdba_free_space_coalesced WHEREpercent_blocks_coalesced!=100查看索引占用空间大小： select(sum(bytes)/1024/1024)||'MB'fromdba_segmentswheresegment_name='INDBILLLOG5_CALLEND'; 查看表占用空间大小 select(sum(bytes)/1024/1024)||'MB'fromdba_segmentswheresegment_name='TBILLLOG5';整理表空间的碎片。 altertablespace表空间名coalesce<h3>第八章 索引常见操作</h3>改变索引： SQL&gt; alter index employees_last _name_idx storage(next 400K maxextents 100); 索引创建后，感觉不合理，也可以对其参数进行修改。详情查看相关文档调整索引的空间： 新增加空间 SQL&gt; alter index orders_region_id_idx allocate extent (size 200K datafile '/disk6/index01.dbf');释放空间 SQL&gt; alter index oraers_id_idx deallocate unused; 索引在使用的过程中可能会出现空间不足或空间浪费的情况，这个时候需要新增或释放空间。上面两条命令完成新增与释放操作。关于空间的新增oracle可以自动帮助，如果了解数据库的情况下手动增加可以提高性能。重新创建索引： 索引是由oracle自动完成，当我们对数据库频繁的操作时，索引也会跟着进行修改，当我们在数据库中删除一条记录时，对应的索引中并没有把相应的索引只是做一个删除标记，但它依然占据着空间。除非一个块中所有的标记全被删除的时，整个块的空间才会被释放。这样时间久了，索引的性能就会下降。这个时候可以重新建立一个干净的索引来提高效率。 SQL&gt; alter index orders_region_id_idx rebuild tablespace index02; 通过上面的命令就可以重现建立一个索引，oracle重建立索引的过程： 1、锁表，锁表之后其他人就不能对表做任何操作。 2、创建新的（干净的）临时索引。 3、把老的索引删除掉 4、把新的索引重新命名为老索引的名字 5、对表进行解锁。移动所引： 其实，我们移动索引到其它表空间也同样使用上面的命令，在指定表空间时指定不同的表空间。新的索引创建在别位置，把老的干掉，就相当于移动了。 SQL&gt; alter index orders_region_id_idx rebuild tablespace index03; 在线重新创建索引： 上面介绍，在创建索引的时候，表是被锁定，不能被使用。对于一个大表，重新创建索引所需要的时间较长，为了满足用户对表操作的需求，就产生的这种在线重新创建索引。 SQL&gt; alter index orders_id_idx rebuild online; 创建过程： 1、锁住表 2、创建立临时的和空的索引和IOT表用来存在on-goingDML。普通表存放的键值，IOT所引表直接存放的表中数据；on-gongDML也就是用户所做的一些增删改的操作。 3、对表进行解锁 4、从老的索引创建一个新的索引。 5、IOT表里存放的是on-goingDML信息，IOT表的内容与新创建的索引合并。 6、锁住表 7、再次将IOT表的内容更新到新索引中，把老的索引干掉。 8、把新的索引重新命名为老索引的名字 9、对表进行解锁整合索引碎片： 如上图，在很多索引中有剩余的空间，可以通过一个命令把剩余空间整合到一起。　　 SQL&gt; alter index orders_id_idx coalesce;删除索引： SQL&gt; drop index hr.departments_name_idx;<h3>第九章 分析索引</h3>　　检查所引的有效果，前面介绍，索引用的时间久了会产生大量的碎片、垃圾信息与浪费的剩余空间了。可以通过重新创建索引来提高所引的性能。 可以通过一条命令来完成分析索引，分析的结果会存放在在index_stats表中。 查看存放分析数据的表：<pre><code>SQL&gt; select count(*) from index_stats;

COUNT(*)
--------

**0**</code></pre>执行分析索引命令：<pre><code>SQL&gt; analyze index my_bit_idx validate structure;

Index analyzed.</code></pre>再次查看 index_stats 已经有了一条数据<pre><code>SQL&gt; select count(*) from index_stats;

COUNT(*)
--------

**1**
```
把数据查询出来：
SQL&gt; select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;

HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS

---

**2** MY_BIT_IDX **1000** **3** **100**
**分析数据分析**：
（HEIGHT）这个所引高度是2，（NAME）索引名为MY_BIT_IDX，（LF_ROWS）所引表有1000行数据，（LF_BLKS）占用3个块，（DEL_LF_ROWS）删除100条记录。
　　这里也验证了前面所说的一个问题，删除的100条数据只是标记为删除，因为总的数据条数依然为1000条，占用3个块，那么每个块大于333条记录，只有删除的数据大于333条记录，这时一个块被清空，总的数据条数才会减少。

**转载自**[chenoracle](http://blog.itpub.net/29785807/viewspace-1269118/)
</code></pre>

oracle 索引详解

第一章索引的定义

第二章索引的分类

1 索引逻辑分类

2 索引物理分类

第三章 B-Tree 索引详解

1 结构

2 场合：非常适合数据重复度低的字段例如身份证号码手机号码 QQ号等字段，常用于主键唯一约束，一般在在线交易的项目中用到的多些。

3 原理：一个键值对应一行（rowid）格式：【索引头|键值|rowid】

5 缺点：不适合键值重复率较高的字段上使用，例如第一章 1-500page 第二章 501-1000page

实验：

第五章创建索引的规则

第六章创建索引时的可选项

第七章 Oracle 索引的维护

第八章索引常见操作

第九章分析索引

相关文章

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

handsome 换字体

Oracle 数据库常用安装包合集（包含基础版和最新补丁集202107）

超全 Linux 安装包合集(Centos Redhat Oracle Linux)

本站typecho 主题版本修改的东西(8.40版本和typecho开发版1.2)

影视资源站接口

oracle用户权限常用查询

oracle 分析函数（窗口函数 over）

Oracle Resize

mysql查询语句优化

ORACLE V$视图解析

oracle 索引详解

第一章 索引的定义

第二章 索引的分类

1 索引逻辑分类

2 索引物理分类

第三章 B-Tree 索引详解

1 结构

2 场合：非常适合数据重复度低的字段 例如 身份证号码 手机号码 QQ号等字段，常用于主键 唯一约束，一般在在线交易的项目中用到的多些。

3 原理：一个键值对应一行（rowid） 格式： 【索引头|键值|rowid】

5 缺点：不适合键值重复率较高的字段上使用，例如 第一章 1-500page 第二章 501-1000page

实验：

第五章 创建索引的规则

第六章 创建索引时的可选项

第七章 Oracle 索引的维护

第八章 索引常见操作

第九章 分析索引

相关文章

发表评论 取消回复 # 请输入真实的邮箱地址便于接收回复

oracle 索引详解

第一章索引的定义

第二章索引的分类

2 场合：非常适合数据重复度低的字段例如身份证号码手机号码 QQ号等字段，常用于主键唯一约束，一般在在线交易的项目中用到的多些。

3 原理：一个键值对应一行（rowid）格式：【索引头|键值|rowid】

5 缺点：不适合键值重复率较高的字段上使用，例如第一章 1-500page 第二章 501-1000page

第五章创建索引的规则

第六章创建索引时的可选项

第八章索引常见操作

第九章分析索引

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复