jump to navigation

Index Organized Tables – the Basics. July 18, 2011

Posted by mwidlake in development, internals, performance.
Tags: , , , ,
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.

About these ads

Comments»

1. Erin Stellato - July 19, 2011

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

mwidlake - July 19, 2011

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

Erin Stellato - July 20, 2011

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

2. Log Buffer #230, A Carnival of the Vanities for DBAs | The Pythian Blog - July 22, 2011

[...] Martin Wildake thinks that the Index Organized Tables(IOTs) are a much under-used and yet very useful feature of Oracle. [...]

3. Dom Brooks - July 22, 2011

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.

mwidlake - July 22, 2011

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?

Dom Brooks - July 22, 2011

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.

4. BLEVEL and Height of Indexes « Martin Widlake's Yet Another Oracle Blog - July 27, 2011

[...] Posts Index Organized Tables – the Basics.IOT 2 – First examples and proofsWhy is my SYSTEM Tablespace so Big?What are Consistent Gets?Friday [...]

5. IOT part 3 – Significantly Reducing IO « Martin Widlake's Yet Another Oracle Blog - August 2, 2011

[...] <..IOT1 – the basics <….IOT2 – Examples and proofs [...]

6. IOT part 3 – Significantly Reducing IO « Ukrainian Oracle User Group - August 3, 2011

[...] ..IOT1 – the basics….IOT2 – Examples and proofs [...]

7. IOT Part 4 – Greatly Boosting Buffer Cache Efficiency « Martin Widlake's Yet Another Oracle Blog - August 8, 2011

[...] <..IOT1 – the basics <….IOT2 – Examples and proofs <……IOT3 – Significantly reducing IO [...]

8. IOT Part 5 – Primary Key Drawback – and Workaround « Martin Widlake's Yet Another Oracle Blog - August 17, 2011

[...] <..IOT1 – the basics <….IOT2 – Examples and proofs <……IOT3 – Significantly reducing IO [...]

9. IOT Part 5 – Primary Key Drawback – and Workaround « Ukrainian Oracle User Group - August 18, 2011

[...] ..IOT1 – the basics….IOT2 – Examples and proofs……IOT3 – Significantly reducing IO……..IOT4 – Boosting Buffer Cache efficiency [...]

10. Oracle ROWID and its Uniqueness « Amit's Tech Blog - September 21, 2011

[...] indexes on index-organized tables. To understand the basics of index organized tables, navigate here. LD_AddCustomAttr("AdOpt", "1"); LD_AddCustomAttr("Origin", "other"); [...]

11. IOT Part 6 – Inserts and Updates Slowed Down (part A) « Martin Widlake's Yet Another Oracle Blog - November 1, 2011

[...] <..IOT1 – the basics <….IOT2 – Examples and proofs <……IOT3 – Significantly reducing IO <……..IOT4 – Boosting Buffer Cache efficiency <……….IOT5 – Primary Key Drawback [...]

12. IOT Part 6 – Inserts and Updates Slowed Down (part A) « Ukrainian Oracle User Group - November 1, 2011

[...] ..IOT1 – the basics….IOT2 – Examples and proofs……IOT3 – Significantly reducing IO……..IOT4 – Boosting Buffer Cache efficiency……….IOT5 – Primary Key Drawback [...]

13. IOT Part 6(B) – OLTP Inserts into an IOT « Ukrainian Oracle User Group - November 12, 2011

[...] ..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 [...]

14. UKOUG Agenda at orawin.info - November 15, 2011

[...]  you’ve been following Martin’s excellent IOT blog series (start here if not) you’ll know what I’m [...]

15. IOTs « Oracle Scratchpad - November 22, 2011

[...] Part 1: The basics [...]

16. Quiz Night « Oracle Scratchpad - November 25, 2011

[...] 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 [...]

17. jcon.no: Oracle Blogg - Artikkel om IOT (Index Organized Table) - December 27, 2011

[...] 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 [...]

18. jcon.no: Oracle Blogg - Artikkel om indeks organiserte tabeller - December 27, 2011

[...] 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 [...]

19. IOTs by the Oracle Indexing Expert « Martin Widlake's Yet Another Oracle Blog - January 10, 2012

[...] 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. [...]

20. Lucian Lazar - January 23, 2012

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

Lucian Lazar - January 23, 2012

P.S. Both executions plans show index fast full scan on IOTA_PK and IOTB_PK.

21. Index Organized Table(s) — IOT(s) | Oracle Administrators Blog - by Aman Sood - January 27, 2012

[...] 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 [...]

22. Get Your Data in Order » SQLfail - February 11, 2013

[...] Widlake has an excellent series on IOTs explaining how they work and their benefits and caveats. I thoroughly recommend you read [...]

Khurram - April 22, 2013

hi Martin ,

good deep dive , i have got one question regarding the topic which you posted
http://mwidlake.wordpress.com/2011/07/26/iot-2-first-examples-and-proofs/ here,
why the IOT index blocks consumed more than normal heap table indexes?

I am posting it here cause couldnt get a chnace to post this question there.

Khurram

mwidlake - April 26, 2013

Hi Khurram,

The answer is that the PK for the heap table only holds the primary key columns. The IOT index holds the rest of the columns also, so it takes up more space. In the example in post 2 the IOT is in fact larger than the traditional heap table AND Primary Key. I now realise I did not come back to this point as I intended, so maybe I need to do a new post.
I am not exactly sure why the size of just the freshly created IOT is greater than the Heap-table-and-Primary-Key {and it not always the case} but if you SHRINK the IOT table it ends up smaller than the heap-table-and-Primary-key. My belief is that as the data is created, the block splits to populate the IOT cause the blocks to be partially empty, anything up to 50%. This will also be true of the PK of the heap table, but as the PK is smaller, the impact is less.The heap table should be very efficiently packed.

23. khurrampc - April 25, 2013

Hi Martin ,

First of all thanks for such a great insight IOT series , i am really enjoying it reading.

I have got stuck from one of yours statment here which is as follows

“Partitioning is important in this technique because a unique index must contain the partition key if the index is to be locally partitioned – otherwise the index must be global, ie the one index object references all the partitions across the table.”

What do you mean by Partioning is important in this technique (the technique which you defined how to overcome surrogate key issue within this post to maximize the usage of IOT using unique key) ,why a unique index must contain the partition key in this context , could you please elaborate a bit more.Sorry for posting it here cause i am unable to post with the relevant post “IOT5 Primary Key issue”

mwidlake - April 26, 2013

An IOT is in effect a unique index {the Primary Key is a special type of unique index} with the rest of the table data stored with the index value. If you partition a table, any unique keys must contain the partition key to be local indexes, ie to be partitioned in line with the table partitions. {This is because it would be a massive overhead for oracle to have to check all partitions to ensure uniqueness of a new record and that would be the only way it could be done if the partition key was not provided to identify the correct single partition} And if you are partitioning an IOT then you are by definition partitioning that unique index. So it has to contain the partition key.

24. khurrampc - April 27, 2013

Thanks Martin for yours clarification , regarding the IOT block consumption and heap table, it make sense that IOT has got further column other then Keys columns which cause more blocks in IOT , i have doubled check it by creating heap table only with PK columns and IOT only with PK columns which cause lesser block in IOT to heap.Thanks

regarding the second question i am still not clear from this statement of yours “Primary Key Issues” like “Partitioning is important in this technique” , what technique you are talking about? you mean to say whenver need to create unique key somehow go for IOT partition?

Khurram

25. Shah - December 30, 2013

Hi Martin,

First of all thanks for this great article. It is simple yet a very informative.
I have a question. Due to heavy DML’s and continuous purging there after makes my IOT fragmented. Hence full index scan is realized, Earlier I removed the histogram over few columns that helped me to get my RANGE SCAN back which was the original one.

Now I have to shrink the indexes being fragmented. Hope that will help me to bring back to original plan.

Q1. Instead of moving the table, Can I SHRINK the table online with SPACE option or I need to use Compact option?
Q2. If I schedule Shrink job every week(as per purging job) How far it is gonna help me to improve my performance when loads of data is purged every week?

Your reply will be appreciated.
Thanks,
S. Frdz


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 165 other followers

%d bloggers like this: