case is as follows:
CREATE TABLE "VISHNU".randomload
( "ROLL" NUMBER,
primary key (roll)
) organization index including mark3 overflow;
create index randomload_idx on randomload(mark6);
insert into randomload select rownum, dbms_random.string(0,40) name,
round(dbms_random.value(0,100)), round(dbms_random.value(0,10000)) from
dual connect by level < 1000000;
exec dbms_stats.gather_table_statS('VISHNU','RANDOMLOAD', CASCADE=>TRUE);
There are so many things here:
create table test (roll number primary key);
insert into test select rownum from dual connect by level < 10000;
here we are 100% sure that there are only 90-10 (100-0) block splits for
the index all the time for the index. .
similarly for the above IOT, the primary data structure (index) will have
100% 90-10 (100-10) block splits in its entirely during the insert ( above
so no question of row movement as part of 50-50 block splits and the
eventual location changes which requires secondary index block references
to be updated.
this is accurate with the pct_direct_access being 100 using the following
SELECT index_name, pct_direct_access, iot_redundant_pkey_elim FROM
select any random value for mark6 column by issuing the following query.
SELECT MARK6,COUNT(*) FROM RANDOMLOAD GROUP BY MARK6 order by 2 fetch first
5 rows only;
now restart the database
login as the same user who created the table and issue the query as follows:
select avG(mark3) from randomload where mark6= 123; // this value 123
we will get the result in inturn... but things get wierd from here: if we
go to the trace directory.
a huge trace file is generated with the entries and dumps of nearly every
block that this query touch.
kcbzib: encounter logical error ORA-1410, try re-reading from other mirror..
cursor valid? 1 warm_up abort 0 makecr 0 line 18694 ds_blk (7, 349) bh_blk
kcbds 0x7fbec8e93b70: pdb 0, tsn 4, rdba 0x01c0015d, afn 7, objd 75861, cls
1, tidflg 0x8 0x80 0x0
dsflg 0x108000, dsflg2 0x0, lobid 0x0:0, cnt 0, addr 0x0, exf
0x12360b10, hdl 0x79cebb38 seq 0x0, dx 0x0, ctx 0 noncontig 0
whr: 'qeilwh03: qeilbk'
env [0x7fbec8e8fef0]: (scn: 0x00000000011923c9 xid: 0x0000.000.00000000
uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000
st-scn: 0x0000000000000000 hi-scn: 0x0000000000000000 ma-scn:
BH (0xb6f70cf8) file#: 7 rdba: 0x01c0015d (7/349) class: 1 ba: 0xb631a000
set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
dbwrid: 0 obj: 75861 objn: 75861 tsn: [0/4] afn: 7 hint: f
hash: [0x780870c8,0x780870c8] lru: [0xb6f70f48,0x7826d408]
ckptq: [NULL] fileq: [NULL]
objq: [0x6c08eea0,0x6c08eea0] objaq: [0x6c08ee90,0x6c08ee90]
use: [0x79cebb78,0x79cebb78] wait: [NULL]
the error ORA-1410 is raised when an operation refers to a ROWID in a table
for which there is no such row... ROWIDs in IOT doesn't make sense.. (its
basically guesses and through primary key columns stored in secondary
we are 100% sure that the physical guesses are accurate due to 90-10
we get this error each time when oracle tries reading blocks from disk and
generate huge traces. possible bug?
the following appears to fix the issue.
alter index randomload_idx update block references;
and the trace files are generated after subsequent selects and restart
Things get even wierd starting from here, we know that mark3 column is
included as part of B-Tree structure using including clause and roll is
now i issue a query select avg(mark3) from randomload where mark6= 123;
we get the result as expected, but if we look at 10046 traces, session
reads the overflow segment and there are many block reads to the overflow
i created a table as follows:
create table temp as select roll from randomload where mark6 = 123;
and issue the query
select avg(mark3) from randomload where roll in (select roll from temp);
//nested loops unique scan.
if we trace the above statement no reads to overflow segments are
performed, and things work as expected, and the intended purpose of IOT is
but it is only when the secondary indexes are used, it reads the overflow
segment, which is typically not necessary
row#114 flag: K---S--, lock: 2, len=66
col 0; len 4; (4): c3 37 09 19
tl: 59 fb: --H-F--- lb: 0x0 cc: 4
nrid: 0x01c0213b.126 //overflow segment rdba
col 0: 
49 41 4d 4b 47 41 45 42 55 4e 58 52 46 56 59 51 51 59 57 42 5a 55 57 4e 4b
59 51 4b 5a 59 41 53 4d 4f 55 56 55 4c 4b 58
col 1: [ 2] c1 0a
col 2: [ 2] c1 59
col 3: [ 2] c1 1f // mark3 is present
as clearly col 3 ---> mark3 is stored.
[oracle@practice trace]$ cat noncdb_ora_31685.trc | grep "col 3" | wc -l
[oracle@practice trace]$ cat noncdb_ora_31685.trc | grep "col 0"| wc -l
I did dump the IOT leaf block to see if there are any mismatches where it
read the overflow segment, in this case there is none.
can someone please tell me why an extra IO to the overflow segment is
necessary when the data is already there in the index leaf block and this
doesn't happen when we lookup with primary key.
Wondered if it is basically due to uniqueness and created the same table as
follows this time including a low NDV column in the primary key
CREATE TABLE "VISHNU"."TEMP"
( "ROLL" NUMBER,
PRIMARY KEY ("MARK1", "ROLL") ENABLE
) SEGMENT CREATION IMMEDIATE
ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
PCTTHRESHOLD 50 INCLUDING "MARK3" OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "USERS" ;
and populated the table with the content from randomload and issued the
select avg(mark3) from randomload where mark1=98;
clearly the trace file indicated that it never looked at the overflow
segment or read any data from the overflow segment.
This unnecessary extra IO to the overflow segments happen only when the
secondary indexes are involved..... is this another bug or something I am
missing here. tried disabling index prefetching and table prefetching
(various combinations) even though these are not even related just to see
even then the extra IO is performed.
i can't get a valid consensus why the read to overflow segment is required
at all? does Oracle simply sees nrid and reads the overflow segment every
time especially with secondary indexes?
Database 19.5 (OCT19 PSU)