参考链接:
http://richardfoote.wordpress.com/2012/05/15/index-rebuild-does-it-use-the-index-or-the-table-nothing-touches-me/
IOT表是特殊的索引结构,如果第2索引的物理猜失败很多,可以通过rebuild来重建索引,修复物理猜失败.
很明显第2索引重建的一个目的就是修复物理猜失败,这样要获得正确的UROWID,必须扫描IOT组织表,获得正确的逻辑rowid.
但是普通的堆表索引呢?下面看几个例子:
测试环境:
select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
1.测试head表的情况:
create table t as select rownum id, cast(dbms_random.string('x',10) as varchar2(10)) name,lpad('x',100,0) other from dual connect by level
create index i_t_name on t(name);
exec dbms_stats.gather_table_stats(user,'T');
2.做一个10046跟踪看看:
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 12';
alter index i_t_name rebuild ;
alter session set events '10046 trace name context off';
SQL ID: 8y4va6xg7905s
Plan Hash: 294279316
alter index i_t_name rebuild
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 2 2 0 0
Execute 1 0.35 0.87 315 100047 972 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.36 0.87 317 100049 972 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE I_T_NAME (cr=100172 pr=315 pw=307 time=0 us)(object id 0)
100000 SORT CREATE INDEX (cr=100008 pr=308 pw=0 time=124167 us)
100000 INDEX FAST FULL SCAN I_T_NAME (cr=100008 pr=308 pw=0 time=428512 us)(object id 96623)
--可以发现要rebuild index,选择的是INDEX FAST FULL SCAN I_T_NAME,既扫描i_t_name索引.
--应该表比较大,而索引相对小,执行计划选择扫描索引.
3.如果加入online参数呢?
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 12';
alter index i_t_name rebuild online ;
alter session set events '10046 trace name context off';
SQL ID: 7974hk0xzpwx8
Plan Hash: 2403602364
alter index i_t_name rebuild online
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 1 1 0 0
Execute 1 0.27 0.87 1724 1809 1162 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.27 0.87 1725 1810 1162 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE I_T_NAME (cr=1929 pr=1718 pw=307 time=0 us)(object id 0)
100000 SORT CREATE INDEX (cr=1700 pr=1716 pw=0 time=155625 us)
100000 TABLE ACCESS FULL T (cr=1700 pr=1716 pw=0 time=123272 us cost=472 size=1100000 card=100000)
--可以发现如果是rebuild online,对于对表选择的是全表扫描.
4.如果表相对索引很小,在rebuild的时候,会选择cost很低的全表扫描.
drop table t purge;
create table t as select rownum id, cast(dbms_random.string('x',10) as varchar2(10)) name,lpad('x',100,0) other from dual connect by level
create index i_t_name on t(name) pctfree 90;
exec dbms_stats.gather_table_stats(user,'T');
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 12';
alter index i_t_name rebuild ;
alter session set events '10046 trace name context off';
SQL ID: 8y4va6xg7905s
Plan Hash: 2403602364
alter index i_t_name rebuild
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 2 2 0 0
Execute 1 0.45 6.92 1702 1814 5164 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.45 6.92 1704 1816 5164 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE I_T_NAME (cr=2114 pr=1702 pw=3454 time=0 us)(object id 0)
100000 SORT CREATE INDEX (cr=1700 pr=1695 pw=0 time=179026 us)
100000 TABLE ACCESS FULL T (cr=1700 pr=1695 pw=0 time=196289 us cost=472 size=1100000 card=100000)
5.而对于IOT表呢?
很明显前面已经提高rebuild一定要扫描IOT表,这样才能修复物理猜的失败.
create table t_iot (id number constraint t_iot_pk primary key, name varchar2(10), other varchar2(100)) organization index;
insert into t_iot select rownum id, cast(dbms_random.string('x',10) as varchar2(10)) name,lpad('x',100,0) other from dual connect by level
create index i_t_iot_name on t_iot(name) ;
exec dbms_stats.gather_table_stats(user,'t_iot');
alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 12';
alter index i_t_iot_name rebuild ;
alter session set events '10046 trace name context off';
SQL ID: 7cysaqqva4gy5
Plan Hash: 3369793897
alter index i_t_iot_name rebuild
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.82 1.43 1594 100078 1090 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.82 1.43 1594 100078 1090 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 INDEX BUILD NON UNIQUE I_T_IOT_NAME (cr=100210 pr=1593 pw=390 time=0 us)(object id 0)
100000 SORT CREATE INDEX (cr=100036 pr=1586 pw=0 time=138489 us)
100000 INDEX FAST FULL SCAN T_IOT_PK (cr=100036 pr=1586 pw=0 time=479279 us cost=426 size=1100000 card=100000)(object id 96642)
总结:
对比再次看出heap表与IOT的不同,iot的第2索引记录的逻辑rowid,如果iot数据发生变化时,里面记录的逻辑rowid不对,这样物理才失败,在rebuild的时候,要
修复这个问题,仅仅要扫描IOT表.而堆表,索引里面除了保存索引键值外,好保存rowid,这样在重建的时候不需要扫描扫描表.而选择online reuild,这个是一种
特殊情况,允许DML操作,不锁表,要选择全表扫描来rebuild索引.
