数据库重建索引 oracle数据库如何重建索引
其实数据库重建索引的问题并不复杂，但是又很多的朋友都不太了解oracle数据库如何重建索引，因此呢，今天小编就来为大家分享数据库重建索引的一些知识，希望可以帮助到大家，下面我们一起来看看这个问题的分析吧！
oracle数据库如何重建索引
当索引的碎片过多时会影响执行查询的速度从而影响到我们的工作效率这时候采取的最有利的措施莫过于重建索引了本文主要介绍了Oracle数据库中检查索引碎片并重建索引的过程接下来我们就开始介绍这一过程
重建索引的步骤如下
确认基本信息
登入数据库找到专门存放index的tablespace并且这个tablespace下所有index的owner都是tax将index专门存放在一个独立的tablespace与数据表的tablespace分离是常用的数据库设计方法
查找哪些index需要重建
通过anlyze index validate structure命令可以分析单个指定的index并且将单个index分析的结果存放到 index_stats试图下一般判断的依据是
height> pct_used<% del_lf_rows/ lf_rows+> g)
google上下载了遍历所有index脚本
发现anlyze index validate structure只能填充单个index分析信息于是google了下从网上下了个Loop脚本遍历索引空间下所有的索引名字并且可以把所有index的分析信息存放到自己建立的一个用户表中
anlyze index锁定index
发现下载的脚本不好用应为anlyze index在分析索引前要争取独占锁锁住index很明显有些index正在被应用系统的使用所以运行anlyze失败这里吸取的教训是尽量晚上做这种事但是本人比较喜欢准时回家所以在语句中添加Exception Handler抛出anlyze index执行失败的那些index名称使脚本正常运行完毕并且根据打印到前台的index name手动执行那些index分析
虽然发现个index中有个符合上面的判断的依据但是发现索引都不大而那些拥有百万leaf的索引又没有符合上面的判断条件所以结论是无需index rebuild online没有啥碎片
什么时候可以rebuild index呢?
rebuild index online对那些有大量DML操作的大索引是有益的可以每个月季度做一次针对较大索引的rebuild通常哪怕rebuild index online也会造成I/O争用所以有无online意义不大可以放到个晚上分批执行rebuild index锁定index不让用户用(没有用户等入的时候)并且加上paralle关键字应为发现数据库服务器有个cpu processors
lishixinzhi/Article/program/Oracle/201311/19014重建索引是什么意思
什么时候需要重建索引
索引在普遍意义上能够给数据库带来带来提升，但索引的额外开销也是不容小视的，而索引的重建也是维护索引的重要工作之一。经过维护的索引可带来以下好处：
1、CBO对于索引的使用可能会产生一个较小的成本值，从而在执行计划中选择使用索引。
2、使用索引扫描的查询扫描的物理索引块会减少，从而提高效率。
3、于需要缓存的索引块减少了，从而让出了内存以供其他组件使用。
重建索引的原因主要包括：
1、删除的空间没有重用，导致索引出现碎片
2、删除大量的表数据后，空间没有重用，导致索引"虚高"
3、索引的 clustering_facto和表不一致
也有人认为当索引树高度超过4的时候需要进行重建，但是如果表数量级较大，自然就不会有较高的树，而且重建不会改变索引树高度，除非是由于大量引起的索引树“虚高”，重建才会改善性能，当然这又回到了索引碎片的问题上了。
在PL-SQL中如何给oracle数据库重建索引
重建索引有多种方式，如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点：
首先建立测试表及数据：
SQL> CREATE TABLE TEST AS SELECT CITYCODE C1 FROM CITIZENINFO2;
Table created
SQL> ALTER TABLE TEST MODIFY C1 NOT NULL;
Table altered
SQL> SELECT COUNT(1) FROM TEST;
COUNT(1)
----------
16000000
一、drop and re-create和rebuild
首先看看正常建立索引时，对表的加锁情况。
suk@ORACLE9I>@show_sid
SID
----------
14
suk@ORACLE9I> CREATE INDEX IDX_TEST_C1 ON TEST(C1);
索引已创建。
SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;
OBJECT_NAME LMODE
----------------------------------------
OBJ$ 3
TEST 4
可见，普通情况下建立索引时，oracle会对基表加share锁，由于share锁和 row-X是不兼容的，也就是说，在建立索引期间，无法对基表进行DML操作。
对于删除重建索引的方法就不介绍了，它与上面的描述是一样的，下面我们看看用rebuild的方式建立索引有什么特别。
suk@ORACLE9I> ALTER INDEX IDX_TEST_C1 REBUILD;
索引已更改。
另开一个会话，查询此时test的加锁情况：
SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;
OBJECT_NAME LMODE
----------------------------------------
TEST 4
可见，rebuild的方式对基表的加锁方式与CREATE时是一样的。
另开一个会话，在索引正在rebuild时，执行如下SQL：
suk@ORACLE9I> SET AUTOTRACE TRACE
suk@ORACLE9I> SELECT/*+ INDEX(TEST)*/ COUNT(1) FROM TEST WHERE ROWNUM<10;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE(Cost=26 Card=1)
1 0 SORT(AGGREGATE)
2 1 COUNT(STOPKEY)
3 2 INDEX(FULL SCAN) OF'IDX_TEST_C1'(NON-UNIQUE)(Cost=
26 Card=1986621)
可以看到索引在重建时，查询仍然可以使用旧索引。实际上，oracle在rebuild时，在创建新索引过程中，并不会删除旧索引，直到新索引rebuild成功。
从这点可以知道rebuild比删除重建的一个好处是不会影响原有的SQL查询，但也正由于此，用rebuild方式建立索引需要相应表空间的空闲空间是删除重建方式的2倍。
重建索引有多种方式，如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点：
相关文章：
oracle重建索引（一）
二、rebuild和rebuild online
首先我们跟踪一下rebuild online的过程。
另开一个会话查看锁的信息：
SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14;
OBJECT_NAME LMODE
----------------------------------------
SYS_JOURNAL_10499 4
TEST 2
SQL> INSERT INTO TEST VALUES(11);
1 row inserted
SQL> COMMIT;
Commit complete
可以看到，在rebuild online期间，oracle对基表加的是RS所，此时我们可以对基表进行DML操作。但奇怪的话在相同的session中有一个SYS_JOURNAL_10499表被加SHARE锁，这个表是干什么用的呢？
我们看看trace文件，有这样的信息：
create table"SUK"."SYS_JOURNAL_10499"(C0 NUMBER(6,0), opcode char(1),
partno number, rid rowid, primary key( C0, rid)) organization index
TABLESPACE"TEST"
CREATE UNIQUE INDEX"SUK"."SYS_IOT_TOP_10605" on
"SUK"."SYS_JOURNAL_10499"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE"TEST"
NOPARALLEL
drop table"SUK"."SYS_JOURNAL_10499"
我们在查查10499是什么东西：
SQL> SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID=10499;
OBJECT_NAME OBJECT_TYPE
------------------------------------------------
IDX_TEST_C1 INDEX
从这些信息可以推测：表SYS_JOURNAL_10499就是实现在重建索引时不阻塞DML操作而设计的，它存储的是在索引重建期间发生在基表的数据变化。可以推测，CREATE INDEX.... ONLINE应该也有一张类似的表。
实际上，oracle之所以在创建索引时锁表阻止DML操作就是为了防止不能索引新变化的数据，在online方式重建时，有了临时表SYS_JOURNAL_XXXX，oracle就可以放心大胆地让用户操作了，因为所有重建索引期间的数据变化信息都会保留在SYS_JOURNAL_XXX表中，当索引重建完后再加上SYS_JOURNAL_XXX记录的数据，就不会漏索引数据了。（XXX是被重建的索引对应的OBJECT_ID)
导读：
重建索引有多种方式，如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点：
相关文章：
oracle重建索引（一）
oracle重建索引（二）
三、rebuild和rebuild online的数据源
网上一直有这样一个说法：重建索引是以原索引作为数据源的。那么，这种说法是否准确呢？我们做实验来验证一下：
suk@ORACLE9I> COL SEGMENT_NAME FORMAT A30
--首先看看表和索引的大小
suk@ORACLE9I> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN('TEST','IDX_TEST_C1');
SEGMENT_NAME BYTES
----------------------------------------
TEST 201326592
IDX_TEST_C1 293601280
suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD;
已解释。
suk@ORACLE9I> SELECT* FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id| Operation| Name| Rows| Bytes| Cost|
-----------------------------------------------------------------------
| 0| ALTER INDEX STATEMENT|||||
| 1| INDEX BUILD NON UNIQUE| IDX_TEST_C1||||
| 2| SORT CREATE INDEX|||||
| 3| TABLE ACCESS FULL| TEST||||
-----------------------------------------------------------------------
Note: rule based optimization
已选择11行。
--从执行计划可以看出，当索引比表大时，rebuild索引用的数据源是基表。
suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE;
已解释。
suk@ORACLE9I> SELECT* FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id| Operation| Name| Rows| Bytes| Cost|
-----------------------------------------------------------------------
| 0| ALTER INDEX STATEMENT|||||
| 1| INDEX BUILD NON UNIQUE| IDX_TEST_C1||||
| 2| SORT CREATE INDEX|||||
| 3| TABLE ACCESS FULL| TEST||||
-----------------------------------------------------------------------
Note: rule based optimization
已选择11行。
--从执行计划可以看出，当索引比表大时，rebuild online索引用的数据源是基表。
--我们为TEST添加一列，使得表比索引大
suk@ORACLE9I> ALTER TABLE TEST ADD(C2 CHAR(30) DEFAULT'1');
表已更改。
suk@ORACLE9I> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN('TEST','IDX_TEST_C
1');
SEGMENT_NAME BYTES
----------------------------------------
TEST 1476395008
IDX_TEST_C1 293601280
suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD;
已解释。
suk@ORACLE9I> SELECT* FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id| Operation| Name| Rows| Bytes| Cost|
-----------------------------------------------------------------------
| 0| ALTER INDEX STATEMENT|||||
| 1| INDEX BUILD NON UNIQUE| IDX_TEST_C1||||
| 2| SORT CREATE INDEX|||||
| 3| INDEX FAST FULL SCAN| IDX_TEST_C1||||
-----------------------------------------------------------------------
Note: rule based optimization
已选择11行。
--从执行计划可以看出，当表比索引大时，执行计划已经改变，rebuild索引是以索引作为数据源的。
suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE;
已解释。
suk@ORACLE9I> SELECT* FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------
| Id| Operation| Name| Rows| Bytes| Cost|
-----------------------------------------------------------------------
| 0| ALTER INDEX STATEMENT|||||
| 1| INDEX BUILD NON UNIQUE| IDX_TEST_C1||||
| 2| SORT CREATE INDEX|||||
| 3| TABLE ACCESS FULL| TEST||||
-----------------------------------------------------------------------
Note: rule based optimization
已选择11行。
--从执行计划可以看出，当表比索引大时，rebuild online仍然以基表作为数据源。
rebuild模式下，因为表数据不会产生变化，oracle主要考虑性能问题，把更快扫描完成的段作为数据源。在上面的例子中，我们并没有对表进行分析，故oracle应该根据数据段的大小来决定那个作为数据源的。一般索引字段比较多，或者对索引字段的DML操作较多，可能会导致索引比表大，这时oracle就会使用基表作为新索引的数据源进行rebuild了。
而在rebuild online模式下，因为允许DML操作，而表数据变化的同时索引也会跟着变化，为了索引与基表数据的一致性，比如采用基表数据作为数据源，而不能用原索引数据作为数据源。
我们用反证法证明不能用原索引作为新索引的数据源。
例如：
T1发出rebuild online命令
T2删除某条数据，删数据的同时，oracle会自动维护了旧索引
T3扫描经过T2数据所在索引节点
T4插入一条记录，新记录对应的索引节点刚好重用了T2删除的数据对应的索引节点空间
如果是这样的话，新建的索引将不包含T4插入的记录的信息。所以，rebuild online情况下新索引的数据源不能是原索引。
rebuild online情况下，如果非用原索引作为新索引的数据源的话，用中间表记录索引变化的方法应该是可以实现的，但由于数据变化会同时引起索引变化的特定决定了这种方法将异常复杂及效率底下，所以oracle不考虑旧索引作为新索引的数据源是有道理的。
结论：
1、rebuild会阻塞对基表的DML操作，但不会影响rebuild期间查询对原有索引的使用。
2、rebuild的数据源可能是基表，也可能是原索引。取决于基表和原索引的大小，那个小，rebuild时就会用那个作为数据源。这也说明了网上盛传的rebuild以原索引作为数据库的说法是不完全正确的。
3、rebuild online运行用户在索引重建期间执行DML操作。
4、rebuild online的数据源是基表
关于数据库重建索引的内容到此结束，希望对大家有所帮助。