Home » RDBMS Server » Performance Tuning » IOT - what is that?
icon5.gif  IOT - what is that? [message #405803] Fri, 29 May 2009 10:27 Go to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Hello,

I have some questions about Index Organized Tables (IOT).
I tried to search in the internet but I cound not find the answers.

  1. Are there any extra problems when using IOT instead of heap organized tables?
  2. When should I use IOT?

    • I have a TINY table with non-unique columns and few keys.
      Answer seems to be that I should not use the IOT in that case
    • I have a HUGE table with 5 columns where 4 of them are unique keys and 5th of them is the value (some number).
      Primary key is set on (Col1,Col2,Col3,Col4).
      Key size is more or less equal to the table size.
    • Table is partitioned (with prefixed partitions or not) or unpartitioned
    • Table is never/rarely/often updated
    • Table is rarely/often queried for selective data
    • Table is rarely/often queried for all the data
      Answer seems to be that I should not use the IOT when all the data are queried and full-scan would be performed
    • Heap organized table has low/high fragmentation (number of blocks per key in PK)

  3. Should I always try to use index organized tables?
  4. Should I try to use index organized tables whenever a small subset of data (let say 1% or less) is retrieved per query?


Re: IOT - what is that? [message #405808 is a reply to message #405803] Fri, 29 May 2009 11:11 Go to previous messageGo to next message
ahudspith
Messages: 26
Registered: January 2009
Location: Avoiding the tax man.
Junior Member

# Are there any extra problems when using IOT instead of heap organized tables?
YES - they are very slow when accessed by non key columns.

# When should I use IOT?


* I have a TINY table with non-unique columns and few keys.
Answer seems to be that I should not use the IOT in that case.
I agree.

* I have a HUGE table with 5 columns where 4 of them are unique keys and 5th of them is the value (some number).
Primary key is set on (Col1,Col2,Col3,Col4).
Key size is more or less equal to the table size.
* Table is partitioned (with prefixed partitions or not) or unpartitioned
* Table is never/rarely/often updated
* Table is rarely/often queried for selective data
* Table is rarely/often queried for all the data
Answer seems to be that I should not use the IOT when all the data are queried and full-scan would be performed.
Seems reasonable - but it requires testing.
How is the data assessed?
You may have a PK on C1, C2, C3, C4 ... but the largest queries may be on C3 alone. Are there additional indexes on this table?

* Heap organized table has low/high fragmentation (number of blocks per key in PK)
You will need to explain what you are asking here. It's a generic statement - not a question.

# Should I always try to use index organized tables?
No - they are best avoided IMO.
They can dramaticly improve the execution speed of a query... to the detriment of others. Someday - someone will make a change to your system altering the way the data is accessed - and it's headache time at that point...

# Should I try to use index organized tables whenever a small subset of data (let say 1% or less) is retrieved per query?
Why would that make much difference unless you were doing a FTS?
If you are performing a FTS then it would be easier to correctly index the table (as heap) in the first place.
Re: IOT - what is that? [message #405872 is a reply to message #405808] Sat, 30 May 2009 16:03 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
I was trying to get an overview on the IOT - thus I have listed several situations for which I would like to get some pointers about the impact of using IOT there - since Oracle documentation is not giving such details. I can only test the performance on my own and one day hit a wall because the performance is not enough.

As for my schema - I have a table that is ~3GB large. It has 5 columns where 4 are the keys (first one is a date and the table is partitioned over range by that date). The keys are never null and must be unique. 5th column contain a single number that is queried (3 or 4 columns are always provided thus the last key column might be omitted).
That table has 1 local index set as primary key. Size of that key is about 3GB - and that is my concern.
Old data are dropped from time to time - this is why we have a local index used as PK.
Data are inserted every day by "insert into ... SELECT ... FROM", and are queried during weekend only.
Re: IOT - what is that? [message #405874 is a reply to message #405803] Sat, 30 May 2009 16:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
What problem are you trying to solve?
How would an independent observer conclude your problem has been solved?
Re: IOT - what is that? [message #405876 is a reply to message #405874] Sat, 30 May 2009 16:42 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Problem: Lack of the knowledge over the internet.
Solution: Any hints about using the IOT (what is it? when to use it? why it shouldn't be used? what are the consequences of using it?)
Re: IOT - what is that? [message #405877 is a reply to message #405803] Sat, 30 May 2009 16:54 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=oracle+iot

http://www.lmgtfy.com/?q=oracle+iot+performance

http://www.lmgtfy.com/?q=oracle+iot+disadvantages

[Updated on: Sat, 30 May 2009 16:55]

Report message to a moderator

Previous Topic: JDBC connection performance
Next Topic: How to judge the slow query?
Goto Forum:
  


Current Time: Sun Dec 11 07:55:51 CST 2016

Total time taken to generate the page: 0.08273 seconds