Skip to Main Content
  • Questions

  • Index Organized Tables - when not to use it

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, KC.

Asked: August 31, 2009 - 2:33 pm UTC

Last updated: August 31, 2009 - 4:37 pm UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Dear Tom,

I have a question about Index Organized Tables while reading your book - "Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions".

I understand there are situations where IOT has its advantage over traditional heap tables - e.g. the daily stock quote example.

What I want to ask you is - what are the situations / pattern where IOT would break. When should I not using IOT?

What if data arrives randomly (not in sort order)? Would IOT takes an enormous amount of time managing indexes and moving data block around? I would tend to say yes but when I read on to your book's Sorted Hash Clustered Tables section you have the below comment "If the information arrives randomly (not in sort order), this data structure [Sorted Hash Clustered Tables] quickly breaks down during the insert process, as much data has to be moved to put the rows physically in order on disk. A sorted hash cluster is not recommended in that case (an IOT, on the other hand, could well be useful for that data)."

So you are saying that random INSERT is not a problem for IOT and this is what confuses me. Can you explain why random INSERT is fine, together with a description of situations where we don't want to use IOT? Thanks.









and Tom said...

... What I want to ask you is - what are the situations / pattern where IOT would break. When should I not using IOT? ...

whenever you have a situation that would not benefit from them.

It is a structure, that unless you have identified a BENEFIT from it - you will almost certainly suffer negative side effects.

It takes longer to insert into
You don't have good secondary index capabilities



You cannot mix questions about two entirely different structures - IOTs and sorted hash clusters *cannot* be compared at all - not even remotely.


If you look at my example for IOTs - the data arrives out of order - that is the nature of the examples in fact - a document management system with a primary key of (username,doc_name) - data arrives OUT of order, a stock quote system with a primary key of (stock_symbol,stock_date) - the data arrives OUT OF ORDER - and we don't care for the IOT.

It takes longer to put the data away in the IOT (mostly regardless of the arrival order) but it is retrieved much faster with less resources (assuming you have something like the stock quotes or document management system examples)


... So you are saying that random INSERT is not a problem for IOT and this is what confuses me. ...

most data in most indexes is inserted randomly - it is the nature of the index. Think of an index on LAST_NAME in an EMP table. You do not insert the data sorted (all of the A's, then B's then C's and so on). You insert RANDOMLY, an index expects that.


So, in short, use them only when they provide some measurable benefit - you understand now what they do (store data sorted in the leaf blocks of an index - if you pull up many rows based on part of the primary key frequently, the IOT can be useful).

Do not use them if that is not a benefit to you (as it is NOT a benefit in most cases - most tables should not be an IOT, there would be no reason to make them an IOT)

Rating

  (1 rating)

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

Comments

Daniel Stolf, August 31, 2009 - 4:17 pm UTC

"whenever you have a situation that would not benefit from them"

I'm sorry, but this answer could very well fit most of the questions you receive on your site :)

I think that what the original poster wanted to know is: What is it that makes IOTs good on some specific situations? And what is it that makes it so bad most of the time?

Would it be a problem when there's too much concurrent DML? Would it be a problem when there's too much data in a row besides de primary key?

Personally, I like to use IOTs in small, fixed values, parametrization tables...

Best regards,
Daniel Stolf
Tom Kyte
August 31, 2009 - 4:37 pm UTC

... I'm sorry, but this answer could very well fit most of the questions you
receive on your site :)
...

that is correct.

"when should i use a hammer" - that answer fits there too.


... What is it that makes
IOTs good on some specific situations? ...

but - he has the book - he quoted from the chaper, that ENTIRE chapter is "what they do, what they provide" with case studies.

... And what is it that makes it so bad most
of the time?
...

There is cost to organizing data - if you do not need data organized, do not pay that cost. That is what it comes down to. If the salient features of the IOT (of which the poster had total access to, they were written about extensively in that chapter he is referring to) do not apply to you - do not use them - there is a cost of holding everything in an index (that is actually discussed in that chapter as well).

The bottom line is - unless you can tell us why you chose the IOT, you probably shouldn't have chosen it.

I'd say the same about partitioning, unless you can enumerate a couple of measurable reasons you chose to partition - you probably should not have.

Same with clusters (hash and b*tree)

These structures all impose penalties to put data away - and can for certain access paths reduce the work needed to retrieve. If you do not have the case whereby that is true - do not utilize them (partitions can be used for things other than just retrieval like administrative things - dropping/truncating/adding partitions and so on)



for IOTs the major downsides are (cut and pasted from above)

It takes longer to insert into
You don't have good secondary index capabilities
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.