Live SQL

Wrong Results with IOT, Added Column and Secondary Index

View All Scripts Login to Run Script

Script Name Wrong Results with IOT, Added Column and Secondary Index
Description This is a reproducible example of a bug. For more details see http://db-oriented.com/2018/06/05/wrong-results-with-iot-added-column-and-secondary-index/
Area SQL General
Contributor Oren Nakdimon (DBoriented)
Created Tuesday June 05, 2018

Statement 1
We create an index-organized table named iot, including the columns x and y. The primary key is comprised of both columns.
```
create table iot ( 
  x number,  
  y number,  
  constraint iot_pk primary key (x,y) 
) organization index
```
```
Table created.
```

Statement 2

We create a secondary index on y.

create index secondary_idx on iot (y)

Index created.

Statement 3

We populate the table with some rows.

insert into iot select rownum,mod(rownum,3) from dual connect by level<=7

7 row(s) inserted.

Statement 4
```
commit
```
```
Statement processed.
```
Statement 5
Now we add another column, z, to the table,
```
alter table iot add z number
```
```
Table altered.
```
Statement 6
and we set some value in z in one row.
```
update iot set z=42 where x=1
```
```
1 row(s) updated.
```
Statement 7
```
commit
```
```
Statement processed.
```
Statement 8
So currently this is the content of the table. Note that z contains the value 42 in the first row and null in all the other rows.
```
select * from iot
```
```
X Y Z
1 1 42
2 2  - 
3 0  - 
4 1  - 
5 2  - 
6 0  - 
7 1  - 

7 rows selected.
```
Statement 9
But if we access the table via the secondary index – for example getting all the records where y=1 – the value of z from the first row “spills over” into the other rows, and we get wrong results.
```
select * from iot where y=1
```
```
X Y Z
1 1 42
4 1 42
7 1 42

3 rows selected.
```
Statement 10
It seems that the problem is not with the secondary index itself, as we get the correct rows, but something with accessing the table via the secondary index causes the wrong results. If we hint the query to access the table directly we get the correct results.
```
select /*+ index_ffs (iot) */ * from iot where y=1
```
```
X Y Z
1 1 42
4 1  - 
7 1  - 

3 rows selected.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database