Oracle 索引组织表、二级索引和陈旧猜测

嗨，汤姆

物联网的二级索引有物理猜测。然而，在插入多次后，猜测可能会变得陈旧。

这是获得新鲜猜测的两种方法:

1. Alter index...重新定义和
2. ALTER INDEX...更新块引用。

3.什么更好或更快？为什么？

如果索引的所有猜测都是新鲜的，为什么查询计划是索引唯一扫描索引范围扫描IX_ONAME，而不是索引ROWID的表访问？

4.在哪里可以看到Oracle在IOT中读取了quess，而不是主键？


谢谢

使用更新块引用，数据库只是扫描索引以修复陈旧的猜测。而重建本质上是在重建它。

这样做的一个副作用是重建可能会使索引变小。但是更新引用对大小没有影响:

create table t (
  x int, y int, z int,
  constraint pk primary key (x, y)
) organization index;

create index i on t (z);

exec dbms_random.seed(0);
insert into t 
  select x.r, y.r, dbms_random.value(1, 200)
  from  (select level r from dual connect by level <= 10000) x,
        (select level r from dual connect by level <= 5) y;
        
commit;

exec dbms_stats.gather_table_stats(user, 't');

select blevel, leaf_blocks
from   user_indexes
where  index_name = 'I';

BLEVEL   LEAF_BLOCKS   
       1           236 

alter index i update block references;

exec dbms_stats.gather_table_stats(user, 't');

select blevel, leaf_blocks
from   user_indexes
where  index_name = 'I';

BLEVEL   LEAF_BLOCKS   
       1           236 

truncate table t;
exec dbms_random.seed(0);
insert into t 
  select x.r, y.r, dbms_random.value(1, 200)
  from  (select level r from dual connect by level <= 10000) x,
        (select level r from dual connect by level <= 5) y;
        
commit;

exec dbms_stats.gather_table_stats(user, 't');

select blevel, leaf_blocks
from   user_indexes
where  index_name = 'I';

BLEVEL   LEAF_BLOCKS   
       1           236 

alter index i rebuild;

exec dbms_stats.gather_table_stats(user, 't');

select blevel, leaf_blocks
from   user_indexes
where  index_name = 'I';

BLEVEL   LEAF_BLOCKS   
       1           157 
复制

鉴于重建会重新创建索引，毫不奇怪，它会做更多的工作，如以下测试所示:

truncate table t;
exec dbms_random.seed(0);
insert into t 
  select x.r, y.r, dbms_random.value(1, 200)
  from  (select level r from dual connect by level <= 10000) x,
        (select level r from dual connect by level <= 5) y;
        
commit;

exec runstats_pkg.rs_start;
alter index i update block references;
exec runstats_pkg.rs_pause;

exec dbms_random.seed(0);
truncate table t;
insert into t 
  select x.r, y.r, dbms_random.value(1, 200)
  from  (select level r from dual connect by level <= 10000) x,
        (select level r from dual connect by level <= 5) y;
        
commit;

exec runstats_pkg.rs_resume;
alter index i rebuild;
exec runstats_pkg.rs_stop(1000, false);

===============================================================================================
RunStats report : 01-NOV-2017 04:53:24
===============================================================================================


-----------------------------------------------------------------------------------------------
1. Summary timings
-----------------------------------------------------------------------------------------------

Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
TIMER cpu time (hsecs)                                             20           26            6
TIMER elapsed time (hsecs)                                         68           91           23

Comments:
1) Run1 was 25.3% quicker than Run2
2) Run1 used 25.3% less CPU time than Run2


-----------------------------------------------------------------------------------------------
2. Statistics report
-----------------------------------------------------------------------------------------------

Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
LATCH KTU in-memory txn table latch                             1,186          116       -1,070
STAT  txn cache local writes                                    1,185          115       -1,070
LATCH object queue header operation                                86        1,243        1,157
STAT  db block changes                                          2,154          550       -1,604
LATCH simulator hash latch                                         22        2,805        2,783
STAT  redo size for direct writes                                   0        3,060        3,060
STAT  KTFB alloc time (ms)                                          0        3,273        3,273
LATCH cache buffers chains                                    106,462      116,875       10,413
STAT  file io wait time                                           235       41,699       41,464
STAT  no buffer to keep pinned count                                0       50,000       50,000
STAT  sorts (rows)                                                  0       50,000       50,000
STAT  session uga memory                                            0       51,824       51,824
STAT  undo change vector size                                  79,628        8,116      -71,512
STAT  redo size                                               260,532       62,632     -197,900
STAT  physical read bytes                                     245,760      819,200      573,440
STAT  physical read total bytes                               245,760      819,200      573,440
STAT  logical read bytes from cache                       417,611,776  416,915,456     -696,320
STAT  physical write bytes                                          0    1,286,144    1,286,144
STAT  physical write total bytes                                    0    1,286,144    1,286,144
STAT  cell physical IO interconnect bytes                     245,760    2,105,344    1,859,584
STAT  KTFB alloc space (block)                                      0    2,097,152    2,097,152
STAT  session pga memory                                   -3,932,160      131,072    4,063,232


-----------------------------------------------------------------------------------------------
3. Latching report
-----------------------------------------------------------------------------------------------

Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
LATCH total latches used                                      112,421      125,178       12,757

Comments:
1) Run1 used 10.2% fewer latches than Run2


-----------------------------------------------------------------------------------------------
4. About
-----------------------------------------------------------------------------------------------
- RunStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
- Based on the original RUNSTATS utility by Tom Kyte

===============================================================================================
End of report
===============================================================================================
复制

测试在12.2.0.1上运行。

运行1使用更新块引用，并且速度更快，使用更少的锁存器，并且在许多统计信息上的工作更少 (尤其是排序和物理写入，它们都是零)。

因此，如果您要做的只是修复陈旧的猜测，更新块引用看起来像是要走的路。

If all guesses of index is fresh, why query plan is INDEX UNIQUE SCAN INDEX RANGE SCAN IX_ONAME, but not TABLE ACCESS BY INDEX ROWID?

因为没有表可以访问!有了物联网，表is指数。所以 “按索引ROWID进行表访问” 是一种无效的访问方法。

Where can I see Oracle have read the quess in IOT, not primary key?

不知道你说的是什么意思。你能澄清一下吗？

如果您想了解更多信息，建议阅读Richard Foote的文章:

https://richardfoote.wordpress.com/2012/04/26/iot-secondary-indexes-the-logical-rowid-guess-component-part-i-lucky/
https://richardfoote.wordpress.com/2012/05/08/iot-secondary-indexes-the-logical-rowid-guess-component-part-ii-move-on/