Index Organized Tables – the Basics. July 18, 2011
Posted by mwidlake in development, internals, performance.Tags: Database Design, index organized tables, IOT, performance, system development
trackback
..>IOT2 – Examples and proofs
….>IOT3 – Greatly reducing IO with IOTs
……>IOT4 – Boosting Buffer Cache Efficiency
……..>IOT5 – Primary Key issues
……….>IOT6a – Slowing Down Insert
…………>IOT6(B) – OLTP Inserts
I think Index Organized Tables(IOTs) are a much under-used and yet very useful feature of Oracle. Over the next few postings I’m going to cover some aspect of Index Organised Tables, both good and not-so-good. I am going to cover some benefits of IOTs that I think many people are unaware of. In this first post I am just going to run through the basics of IOTs.
The idea behind an IOT is simple. You hold all the data for the table in the ordered structure of an index. Why would you want to do that? Let us consider a very common requirement, accessing a row in a “large” table via a known, unique key.
Traditionally you have a heap table holding the data you want to access and a standard index to support access to that table. See the first diagram below. The 4-layer triangle represents the index, with a root block, two levels of branch blocks and then the leaf blocks at the “bottom”. The blue rectangle represents the table with the squares being individual rows. Of course, in a large table there would be thousands or millions of “squares”, this is just a simple diagram to show the idea.
When you issue a SQL statement to select the row via the indexed column(s) then oracle will read the root block (1), find the relevent block in the first level of branch blocks (2), then the relevant block in the second level of branch blocks (3) and finally (as far as the index is concerned) the relevant Leaf Block for the unique key. The leaf block holds the indexed column(s) and also the rowid. The rowid is the fastest way to look up a record, it states the file, block and row offset for the row. This allows oracle to go straight to the block and get the row. That is read number (5).
The number of branch blocks {and thus the number of blocks that need to be read to find a row} will vary depending on how much data is indexed, the number and size of the columns in the index, how efficiently the space has been used in the blocks and one or two other factors. In my experience most indexes for tables with thousands or millions of rows have one, two or three levels of branch blocks.
The second diagram shows a representation of the Index Organized Table. The table has in effect disappeared as a distinct object and the information has been moved into the leaf blocks of the index {part of me feels Index Organized Tables should really be called Table Organized Indexes or Table Containing Indexes as that would better indicate what is physically done}:
So with the IOT oracle reads the root block (1), the two branch level blocks (2 and 3) and finally the leaf block (4). The leaf block does not hold the rowid but rather the rest of the columns for the table {this can be changed, a more advanced feature allows you to store some or all the extra columns in an overflow segment}. Thus to access the same data, Oracle has to read only 4 blocks, not 5. Using an IOT saves one block read per unique lookup.
This saving of block reads is probably the main feature that IOTs are known for, but there are others which I will cover in later posts. Two things I will mention now is that, firstly, the use of IOTs is potentially saving disc space. An index is in effect duplication of data held in the table. When you create an index no new information is created but space is used up holding some of the table information in a structure suitable for fast lookup. Secondly, the index and table have to be maintained whenever a change is made to the columns that are indexed. IOTs reduce this maintenance overhead as there is only one thing to maintain.
Now for some drawbacks.
- The IOT has to be indexed on the primary key. There is no option to create an IOT based on other indexes. As such you have to either be accessing the table via the primary key to get the benefit – or you have to be a little cunning.
- The index is going to be larger than it was and very often larger than the original table. This can slow down range scans or full scans of the index and a “full table scan” will now be a full index scan on this large object, so that can also negatively impact performance. However, if a range scan would then have resulted in access to the table to get extra columns, the IOT gives a similar benefit in reducing IO to that for single row lookups.
- I just want to highlight that you now have no rowid for the rows.
- Secondary indexes are supported but will potentially be less efficient due to this lack of rowid.
So, a brief summary is that Index Organised Tables effectively move the table data into the Primary Key index, reduce the number of block lookups needed to select one row, can save some disc space. But you can only organize the table via the Primary Key and it can make full or partial table scans and lookups via other indexes slower.
There are several more important benefits to IOTs {in my opinion} which I will come to over the next week or two.
Martin,
Great post! I love the pictures – visuals are a huge help to me
But of course I have two questions…
First, you state: “…a “full table scan” will now be a full index scan on this large object, so that can also negatively impact performance.”
How would this be worse than a scan of the original heap table? When doing a full index scan of an IOT, wouldn’t it just read the leaf level? I assume the leaf level pages have forward and backward pointers to each other. Hopefully the leaf level of the IOT would be similar in size to the heap table.
Second, you state: “Secondary indexes are supported but will potentially be less efficient due to this lack of rowid.”
Do you mean that in secondary indexes, instead of a rowid there is the primary key (defined for the IOT) and once you find, via the secondary index, the rows you want, you must traverse the IOT for each primary key?
Thanks!
Erin
Hi Erin,
Thanks for those kind words, especially about the pictures. There is a bit of a story to those pictures, which I’ll save for my next Friday Philosophy…
To answer your questions:
The full index scan is slower for the reason you touch on at the end of your question – “hopefully the leaf level of the IOT would be similar size…”. Well, it will be similar but it will almost certainly be larger. You have the overhead of the structure of the index and also there is a tendancy towards less efficient storage of rows into the index structure. ie fewer rows per block than you would get in a normal table, so the whole segment is larger and thus takes longer to full scan. I’ll cover some more aspects of this in later posts.
The secondary indexes are less efficient for the reason you suggest – they do not hold a rowid. They can’t hold a rowid as there is no rowid to hold. The secondary index actually holds the primary key and also a row-guess. Again, I’ll cover in more detail in a later post as to the impact on finding the correct rows via a secondary index.
Regards,
Martin
Thanks for the explanations Martin! I look forward to your future posts – it’s very interesting to see how this differs from SQL Server
Erin
[...] Martin Wildake thinks that the Index Organized Tables(IOTs) are a much under-used and yet very useful feature of Oracle. [...]
I had a conversation yesterday with someone on an internal forum who had an error trying to add a VARCHAR2(2000) column to an IOT without an overflow segment . Thank goodness for the error I say. But the point I raised was that once you add an overflow, you’re losing much of the benefit unless you frequently don’t ask for the overflow data. But then isn’t it much like a heap table with an index and just using the index, not going to the table?
I look forward to the series.
I’ll touch on that aspect of IOTs. I’m guessing that the person with the problem had a 4k block size? Or lots of other columns on the table too?
That would be my guess as well but to be honest I didn’t probe too deeply.
In fact, I wouldn’t be surprised if it was an 8k blocksize and this was not the first VARCHAR2(2000) that they’d added.
[...] Posts Index Organized Tables – the Basics.IOT 2 – First examples and proofsWhy is my SYSTEM Tablespace so Big?What are Consistent Gets?Friday [...]
[...] <..IOT1 – the basics <….IOT2 – Examples and proofs [...]
[...] ..IOT1 – the basics….IOT2 – Examples and proofs [...]
[...] <..IOT1 – the basics <….IOT2 – Examples and proofs <……IOT3 – Significantly reducing IO [...]
[...] <..IOT1 – the basics <….IOT2 – Examples and proofs <……IOT3 – Significantly reducing IO [...]
[...] ..IOT1 – the basics….IOT2 – Examples and proofs……IOT3 – Significantly reducing IO……..IOT4 – Boosting Buffer Cache efficiency [...]
[...] indexes on index-organized tables. To understand the basics of index organized tables, navigate here. LD_AddCustomAttr("AdOpt", "1"); LD_AddCustomAttr("Origin", "other"); [...]
[...] <..IOT1 – the basics <….IOT2 – Examples and proofs <……IOT3 – Significantly reducing IO <……..IOT4 – Boosting Buffer Cache efficiency <……….IOT5 – Primary Key Drawback [...]
[...] ..IOT1 – the basics….IOT2 – Examples and proofs……IOT3 – Significantly reducing IO……..IOT4 – Boosting Buffer Cache efficiency……….IOT5 – Primary Key Drawback [...]
[...] ..IOT1 – the basics….IOT2 – Examples and proofs……IOT3 – Significantly reducing IO……..IOT4 – Boosting Buffer Cache efficiency……….IOT5 – Primary Key Drawback…………IOT6(A) – Bulk Insert slowed down [...]
[...] you’ve been following Martin’s excellent IOT blog series (start here if not) you’ll know what I’m [...]
[...] Part 1: The basics [...]
[...] by Martin Widlake’s series on IOTs, I thought I’d throw out this little item. In the following, run against 10.2.0.3, tables t3 [...]
[...] DBA-er har for liten kunnskap og erfaring med denne tabelltypen. Jonathan Lewis henviste til denne lenken her om dagen i sin Oracle blogg. Hvis du ikke har IOT-er i din Oracle database er det kanskje verdt [...]
[...] DBA-er har for liten kunnskap og erfaring med denne tabelltypen. Jonathan Lewis henviste til denne lenken her om dagen i sin Oracle blogg. Hvis du ikke har IOT-er i din Oracle database er det kanskje verdt [...]
[...] I put in occasional pictures and maybe write more about how the example matches real world situations. If you want, you can look back at my own introduction to the topic. [...]
Great articles on IOT, Martin! Very good explanations from your side, I enjoyed very much reading them.
I have one question: does a leaf store only the key columns or the non-key columns as well? My guess is that the second answer is correct.
I have a small script below with a test case: the two queries take the same time to execute although one IOT has the third column included in the PK and the second IOT does not (I have flushed the buffer cache and shared pool before each run).
Regards,
Lucian
create table iota(a1 integer, a2 integer, a3 integer, constraint iota_pk primary key(a1,a2,a3)) organization index;
create table iotb(b1 integer, b2 integer, b3 integer, constraint iotb_pk primary key(b1,b2)) organization index;
begin
for i in 1..10000000 loop
insert into iota values(i,i,i);
end loop;
commit;
end;
begin
for i in 1..10000000 loop
insert into iotb values(i,i,i);
end loop;
commit;
end;
select * from iota where mod(a1,3) = 0 and a2 > 1000000 and mod(a3,5) = 0 — 0,75 sec, 0,56 sec, 0,54 sec
select * from iotb where mod(b1,3) = 0 and b2 > 1000000 and mod(b3,5) = 0 — 0,58 sec, 0,59 sec, 0,53 sec
P.S. Both executions plans show index fast full scan on IOTA_PK and IOTB_PK.
[...] Organized Table(s) — IOT(s) Here is a very useful series of write-ups on Index Organized Table(s) by Martin Widlake…. Author: Aman on November 4, 2011 Category: Hemant Newer: CTAS in a [...]