Re: IOT - cannot get valid consensus - bug or unexplained behavior

case is as follows:

CREATE TABLE "VISHNU".randomload

   ( "ROLL" NUMBER,
"NAME" VARCHAR2(40),
"MARK1" NUMBER,
"MARK2" NUMBER,
"MARK3" NUMBER,
"MARK4" NUMBER,
"MARK5" NUMBER,
"MARK6" 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,100)), round(dbms_random.value(0,100)), round(dbms_random.value(0,100)), round(dbms_random.value(0,100)), round(dbms_random.value(0,10000)) from dual connect by level < 1000000; commit;

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; commit;
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 statement). 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 query.

SELECT index_name, pct_direct_access, iot_redundant_pkey_elim FROM USER_INDEXES;

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 selected randomly

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 (7, 349) 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: 0x00000000011920f0  flg

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 indexes) we are 100% sure that the physical guesses are accurate due to 90-10 splits.

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 (database).

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 unique

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 segment.

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 served.

but it is only when the secondary indexes are used, it reads the overflow segment, which is typically not necessary row#114[7834] 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: [40]
 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_at_practice trace]$ cat noncdb_ora_31685.trc | grep "col 3" | wc -l 234
[oracle_at_practice trace]$ cat noncdb_ora_31685.trc | grep "col 0"| wc -l 234

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