[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索引.