ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120509]IOT索引组织表相关信息的学习(三).txt

[20120509]IOT索引组织表相关信息的学习(三).txt

原创 Linux操作系统 作者:lfree 时间:2012-05-09 11:34:24 0 删除 编辑
[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来索引块的相关信息。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-723115/,如需转载,请注明出处,否则将追究法律责任。

上一篇: [20120508]bad sql.txt
请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2287
  • 访问量
    6028060