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