By Franck Pachot
.
When can we use IOT? That’s a question I had when giving recently the Oracle DBA essentials workshop. the DBA Essential is very dense and there is only half a day about performance. We have the Oracle Performance Tuning workshop to go into those details. But IOTs are under used in my opinion, so I’ll post a use case for them where they are a good idea.
Basically:
- Index access is very fast to get all indexed columns, but can be longer when having to get the non-indexed columns
- IOT are fast to retreive all columns (except overflow) by primary key
- IOT are long when retreiving by secondary index when we get columns that are not in the index and that are not the primary key
- USER_PRIVS(USER_ID,PRIV_ID,GRANT_OPTION)
- USER_PRIVS(PRIV_ID,USER_ID,GRANT_OPTION)
CREATE TABLE USER_PRIVS ( USER_ID,PRIV_ID,GRANT_OPTION , PRIMARY KEY(USER_ID,PRIV_ID) ) ORGANISATION INDEX;
CREATE INDEX PRIV_USERS (PRIV_ID,USER_ID,GRANT_OPTION)
So, if you navigate from USERS you access directly to the index leaf that has all information.
And if you navigate from PRIVILEGES, you have also all information. Someone said that access via secondary index is long? Yes but that’s only when we have to get to the other columns because the secondary index don’t have a rowid. It stores the primary key instead and must go through the primary index to get the other columns. But there are two point in my example:
- I already have all the primary key columns in my secondary index, and Oracle is clever enough to not store them in double
- I don’t have to get to the primary index because I have evrey columns in my secondary index
- we access mainly through the primary key and accept more expensive queries when accessing otherwise. Note that the secondary index can be optimized is we ALTER INDEX … UPDATE BLOCK REFERENCES regularly
- the secondary indexes have all the columns needed for our query