Over the last few days I came across this term IOT lot of times. I tried to memorize the abbreviation for it. Index Organized Table, Index Organized Table, Index Organized Table..... But it didn't sink in. Few days passed and today I was reading an article on asktom and came across a question on IOTs. And then I was trying to recollect what an IOT stood for. Well that is when I decided I need to learn about it in a better way than memorizing what IOT's full form was.
Then as anyone would do I goggled and found this wonderful white paper on Index Organized Tables and all the nitty gritty details of it. To summarize an Index Organized Table is a table which is organized based on the primary key Index of the table. This reduces storage requirements and the additional IO required during querying to access the rowid from the index and then the table. There are many added advantages to the Index Organized Tables like enabling parallel processing even tough the table is not partitioned. An IOT is no different from a conventional table in terms of the data types that can be stored except that we can not create object tables as IOTs. All the object features like Object type, VARRAYs, Nested Tables, REF cursors etc. are supported.
I could find only the advantages of using IOTs in the previous article but no disadvantages are listed there. But I found another good article which explained clearly how IOTs are physically organized and in what scenarios a IOT can be a performance drainer. It turns out that when a IOT is created physically the data will be stored in the index as well as the overflow table which contains the data of the overflowed columns. The actual table is just a logical entity which fetches data from the index and the overflow table. The only disadvantage I found was that when we need to do a full table scan on non key tables then we might have to do more physical reads than in the case where the table was a conventional table.
Having read all the articles I am left with the questions:
1) Can I convert all my existing tables into index organized table?
2) Do I have a criteria in selecting the tables which are a good fit to be defined as Index Organized Tables?
No comments:
Post a Comment