[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来索引块的相关信息。