With update block references, the database is just scanning through the index to fix the stale guesses. Whereas a rebuild is essentially recreating it.
One side effect of this is a rebuild may make the index smaller. But updating references has no impact on the size:
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 Given that a rebuild recreates the index, unsurprisingly it does more work as the following tests show:
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
===============================================================================================Tests run on 12.2.0.1.
Run 1 is uses update block references and is faster, uses fewer latches and a lot less work on many stats (notably sorts and physicaly writes which are both zero).
So if all you want to do is fix stale guesses, update block references looks like the way to go.
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?Becuase there isn't a table to access! With an IOT the table
is an index. So "TABLE ACCESS BY INDEX ROWID" is an invalid access method.
Where can I see Oracle have read the quess in IOT, not primary key?Not sure what you mean by this. Could you clarify?
If you want to know more about this, I recommend reading Richard Foote's articles on this:
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/