Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Paul.

Asked: March 15, 2011 - 11:24 am UTC

Last updated: March 17, 2011 - 8:19 am UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Tom - in your experience, what are the common errors where people have used IOTs in the wrong situation?

Thanks.

and Tom said...

When they have lots of secondary indexes on them in an environment where the IOT is constantly in a state of flux. The secondary indexes (indexes on non-primary key columns) have rowid 'guesses' in them and if the guess is correct - the efficiency of the secondary index is about as good as the efficiency of a normal index on a normal table. But if the row has moved from the leaf block it was inserted into - the rowid guess in the secondary index goes stale and we end up having to do a range scan on the secondary index to find the guess, discover the guess is bad, and then it will have to find the row by scanning for the primary key. So a simple index range scan + table access by index rowid becomes

index range scan + table access by index rowid + index range scan (to find the row in the IOT by primary key)



Another case would be when.... You didn't need what an IOT provides. I've seen at least one implementation where all the tables where IOTs. The reason: they ported from SQL Server and discovered that an IOT was the closest thing to a clustered table in SQL Server and since all of their tables were clustered in SQL Server....

If you don't need your rows stored by primary key in an index structure, it would be wrong to use an IOT ;)

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

IOTs

Paul Sweeney, March 16, 2011 - 3:46 am UTC

Thanks Tom.

Data Warehouse and IOT

snehasish das, March 16, 2011 - 11:10 am UTC

Hi Tom,

I hear a lot of times that IOT has limited use in Datawarehouse environment.

Can you please let me know why is IOT of less use in DWH what are limitation of IOT in DWH,or its only a myth.


Regards,
Snehasish Das.
Tom Kyte
March 16, 2011 - 12:33 pm UTC

I hear a lot of times that IOT has limited use in Datawarehouse environment.


that is where they rock and roll - in a warehouse you don't have the OLTP issue so you can do the load in a way that doesn't make secondary indexes a problem.

I guess I'd have to hear their arguments - maybe they are arguing my second point above - the IOT was the wrong structure for the needs. But that doesn't rule out IOTs in a warehouse by any means.

Question about secondary index on IOT

Michael, March 17, 2011 - 2:37 am UTC

Hi Tom,

you said:

When they have lots of secondary indexes on them in an environment where the IOT is constantly in a state of flux. The secondary indexes (indexes on non-primary key columns) have rowid 'guesses' in them and if the guess is correct - the efficiency of the secondary index is about as good as the efficiency of a normal index on a normal table. But if the row has moved from the leaf block it was inserted into - the rowid guess in the secondary index goes stale and we end up having to do a range scan on the secondary index to find the guess, discover the guess is bad, and then it will have to find the row by scanning for the primary key.

Now my question in this context:

If i have a mapping table t (n:m mapping table) between two tables a and b, for example. This table t is created as an IOT. The primary key is on (a_id, b_id) where a_id is the foreign key to table a, while b_id is the foreign key to table b. This table/index is useful when i want to "go" from a to b. To "go" efficiently from b to a, i create a secondary index on t with (b_id, a_id).

Does this secondary suffer from the above problem? (I think there is no need to go to the IOT, because the secondary index contains everything necessary.)
And: Does this secondary index even contains any rowid 'guesses'? (If yes, why?)

Thanks.
Tom Kyte
March 17, 2011 - 8:19 am UTC

Does this secondary suffer from the above problem?

check the plan, if it just uses the secondary index and never goes to the table, it would not 'suffer' from the above problem.

ops$tkyte%ORA11GR2> create table a ( aid int primary key, data varchar2(1) );

Table created.

ops$tkyte%ORA11GR2> create table b ( bid int primary key, data varchar2(1) );

Table created.

ops$tkyte%ORA11GR2> create table a_b ( aid references a, bid references b, constraint a_b_pk primary key(aid,bid) ) organization index;

Table created.

ops$tkyte%ORA11GR2> create unique index a_b_idx on a_b(bid,aid);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from a, a_b, b where a.aid = 5 and a.aid = a_b.aid and a_b.bid = b.bid;

Execution Plan
----------------------------------------------------------
Plan hash value: 1908560623

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |    56 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |              |     1 |    56 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |              |     1 |    30 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| A            |     1 |    15 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | SYS_C0016268 |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | B            |     1 |    15 |     2   (0)| 00:00:01 |
|*  6 |   INDEX RANGE SCAN            | A_B_IDX      |     1 |    26 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."AID"=5)
   6 - access("A_B"."BID"="B"."BID" AND "A_B"."AID"=5)

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> select * from a, a_b, b where b.bid = 5 and a.aid = a_b.aid and a_b.bid = b.bid;

Execution Plan
----------------------------------------------------------
Plan hash value: 2364633749

-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     1 |    56 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |              |       |       |            |          |
|   2 |   NESTED LOOPS                 |              |     1 |    56 |     2   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |              |     1 |    41 |     1   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| B            |     1 |    15 |     1   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | SYS_C0016269 |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | A_B_IDX      |     1 |    26 |     0   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN           | SYS_C0016268 |     1 |       |     0   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID  | A            |     1 |    15 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("B"."BID"=5)
   6 - access("A_B"."BID"=5)
   7 - access("A"."AID"="A_B"."AID")

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off


No reference to the table A_B, so the rowid hint is not being used.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.