 IOT - what is that? [message #405803] |
Fri, 29 May 2009 10:27  |
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.
- Are there any extra problems when using IOT instead of heap organized tables?
- 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)
- Should I always try to use index organized tables?
- 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  |
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 |
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 #405876 is a reply to message #405874] |
Sat, 30 May 2009 16:42 |
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?)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
