[20120509]IOT索引組織表相關資訊的學習(三).txt

[20120509]IOT索引組織表相關資訊的學習(三).txt

上次連結:
http://space.itpub.net/267265/viewspace-719517
http://space.itpub.net/267265/viewspace-717272

IOT 是一種特殊的索引結構，使用它能夠解決特定場合的應用問題，但是在許多應用中很少使用，更多的是使用堆表。
我僅僅在生產系統中對3個表使用，我偏向使用靜態以及"只讀"的小表。

如果IOT表存在除主鍵外的第2索引，如果使用它存在物理猜"physical guess",今天測試一下move IOT表看看，第2索引的一些變化。


1.測試環境:

SQL> 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

SQL> create table t_iot ( a varchar2(10),b varchar2(10),vc varchar2(1200), constraint t_iot_pk PRIMARY KEY(a)) ORGANIZATION INDEX;

SQL> create index i_t_iot_b on t_iot(b);

insert into t_iot values ('1'        ,'a',lpad('a',1000,'a'));

insert into t_iot values ('22'       ,'b',lpad('a',1000,'a'));

insert into t_iot values ('333'      ,'c',lpad('a',1000,'a'));

insert into t_iot values ('4444'     ,'d',lpad('a',1000,'a'));

insert into t_iot values ('55555'    ,'e',lpad('a',1000,'a'));

insert into t_iot values ('666666'   ,'f',lpad('a',1000,'a'));

insert into t_iot values ('7777777'  ,'g',lpad('a',1000,'a'));

insert into t_iot values ('88888888' ,'h',lpad('a',1000,'a'));

insert into t_iot values ('999999999','i',lpad('a',1000,'a'));

commit ;

SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T_iot');

PL/SQL procedure successfully completed.

SQL> select index_name, index_type, pct_direct_access  from user_indexes where index_name = 'I_T_IOT_B';

INDEX_NAME                     INDEX_TYPE                  PCT_DIRECT_ACCESS

------------------------------ --------------------------- -----------------

I_T_IOT_B                      NORMAL                                     22

SQL> SELECT index_name, table_name, blevel, leaf_blocks FROM dba_indexes  WHERE table_name = 'T_IOT';

INDEX_NAME                     TABLE_NAME                         BLEVEL LEAF_BLOCKS

------------------------------ ------------------------------ ---------- -----------

T_IOT_PK                       T_IOT                                   1           2

I_T_IOT_B                      T_IOT                                   0           1

--PCT_DIRECT_ACCESS=22可以確定由於插入導致索引分裂，第2索引的物理猜都是失敗的多。

2.先修復看看。

SQL> ALTER INDEX I_T_IOT_B UPDATE BLOCK REFERENCES;

Index altered.

SQL> select index_name, index_type, pct_direct_access  from user_indexes where index_name = 'I_T_IOT_B';

INDEX_NAME                     INDEX_TYPE                  PCT_DIRECT_ACCESS

------------------------------ --------------------------- -----------------

I_T_IOT_B                      NORMAL                                     22

SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T_iot');

PL/SQL procedure successfully completed.

SQL> select index_name, index_type, pct_direct_access  from user_indexes where index_name = 'I_T_IOT_B';

INDEX_NAME                     INDEX_TYPE                  PCT_DIRECT_ACCESS

------------------------------ --------------------------- -----------------

I_T_IOT_B                      NORMAL                                    100

--PCT_DIRECT_ACCESS=100.

3.查詢看看：

SQL> set autot traceonly ;

SQL> select vc from t_iot where b='a';

Execution Plan

----------------------------------------------------------

Plan hash value: 1095339046

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

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

|*  1 |  INDEX UNIQUE SCAN| T_IOT_PK  |     1 |  1003 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN| I_T_IOT_B |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("B"='a')

   2 - access("B"='a')

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

       1523  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

--邏輯讀＝3

4.移動IOT表空間

SQL> alter table t_iot move tablespace users;

Table altered.

SQL> SELECT index_name, table_name, blevel, leaf_blocks,STATUS FROM dba_indexes  WHERE table_name = 'T_IOT';

INDEX_NAME                     TABLE_NAME                         BLEVEL LEAF_BLOCKS STATUS

------------------------------ ------------------------------ ---------- ----------- --------

T_IOT_PK                       T_IOT                                   1           2 VALID

I_T_IOT_B                      T_IOT                                   0           1 VALID

--可以發現第2索引依舊有效，不像堆表，如果move後，表記錄的rowid發生了變化，這樣其他的索引記錄的rowid與原來對不上，

--索引必然處於novalid的狀態，必須rebuild。

SQL>  select index_name, index_type, pct_direct_access  from user_indexes where index_name = 'I_T_IOT_B';

INDEX_NAME                     INDEX_TYPE                  PCT_DIRECT_ACCESS

------------------------------ --------------------------- -----------------

I_T_IOT_B                      NORMAL                                      0

--移動IOT後，第2索引的PCT_DIRECT_ACCESS＝0
--而IOT表的第2索引記錄的是主鍵以及對應IOT塊號，這樣IOT表移動後，第2索引記錄的主鍵依舊沒有變化，僅僅ioT表的塊號發生了變化。
--這樣使用第2索引查詢是物理猜失敗，僅僅透過主鍵能夠查詢到資訊，這樣邏輯讀會增加。

SQL> set autot traceonly

SQL> select vc from t_iot where b='a';

Execution Plan

----------------------------------------------------------

Plan hash value: 1095339046

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |     1 |  1003 |     2   (0)| 00:00:01 |

|*  1 |  INDEX UNIQUE SCAN| T_IOT_PK  |     1 |  1003 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN| I_T_IOT_B |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("B"='a')

   2 - access("B"='a')

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       1523  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

--邏輯讀=4.

5.重新rebuild第2索引或者修改索引塊的相關資訊

SQL> ALTER INDEX I_T_IOT_B UPDATE BLOCK REFERENCES; 

或者

SQL> alter index i_t_iot_b rebuild ;

--再次執行查詢：

SQL> set autot traceonly

SQL> select vc from t_iot where b='a';

Execution Plan

----------------------------------------------------------

Plan hash value: 1095339046

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

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

|*  1 |  INDEX UNIQUE SCAN| T_IOT_PK  |     1 |  1003 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN| I_T_IOT_B |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("B"='a')

   2 - access("B"='a')

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

       1523  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

--邏輯讀=3.

SQL> select index_name, index_type, pct_direct_access  from user_indexes where index_name = 'I_T_IOT_B';

INDEX_NAME                     INDEX_TYPE                  PCT_DIRECT_ACCESS

------------------------------ --------------------------- -----------------

I_T_IOT_B                      NORMAL                                    100

總結：在移動IOT表後，第2索引的物理猜應該都會失敗，如果想提高第2索引的訪問效率，應該reuild或者使用ALTER INDEX XXX UPDATE BLOCK REFERENCES來索引塊的相關資訊。