[20120608]IOT的第2索引重建.txt
参考链接: 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索引.
