Is an Index Organized Table appropriate here?

Question

I recently was reading about Oracle Index Organized Tables (IOTs) but am not sure I quite understand WHEN to use them. So I have a small table:

create table categories 
(
   id        VARCHAR2(36),
   group     VARCHAR2(100),
   category  VARCHAR2(100
)
create unique index (group, category, id) COMPRESS 2;

The id column is a foreign key from another table entries and my common query is:

select e.id, e.time, e.title from entries e, categories c where e.id=c.id AND e.group=? AND c.category=? ORDER by e.time

The entries table is indexed properly.

Both of these tables have millions (16M currently) of rows and currently this query really stinks (note: I have it wrapped in a pagination query also so I only get back the first 20, but for simplicity I omitted that).

Since I am basically indexing the entire table, does it make sense to create this table as an IOT?

EDIT by popular demand:

create table entries
(
   id        VARCHAR2(36),
   time      TIMESTAMP,
   group     VARCHAR2(100),
   title     VARCHAR2(500),
   ....
)

create index (group, time) compress 1;

My real question I dont think depends on this though. Basically if you have a table with few columns (3 in this example) and you are planning on putting a composite index on all three rows is there any reason not to use an IOT?

Answer 1

Have you looked at dba-oracle.com, asktom.com, IOUG, another asktom.com?

There are penalties to pay for IOTs - e.g., poorer insert performance

Can you prototype it and compare performance?

Also, perhaps you might want to consider a hash cluster.

Answer 2

IOT's are a trade off. You are getting access performance for decreased insert/update performance. We typically use them for reference data that is batch loaded daily and not updated during the day. This is not to say it's the only way to use them, just how we use them.

Few things here:

1. You mention pagination - have you considered the first_rows hint?
2. Is that the order your index is in, with group as the first field? If so I'd consider moving ID to be the first column since that index will not be used.
3. foreign keys should have an index on the column. Consider addind an index on the foreign key (id column).
4. Are you sure it's not the ORDER BY causing slowness?

Answer 3

IOTs are great for a number of purposes, including this case where you're gonna have an index on all (or most) of the columns anyway - but the benefit only materialises if you don't have the extra index - the idea is that the table itself is an index, so put the columns in the order that you want the index to be in. In your case, you're accessing category by id, so it makes sense for that to be the first column. So effectively you've got an index on (id, group, category). I don't know why you'd want an additional index on (group, category, id).

Your query:

SELECT e.id, e.time, e.title
FROM entries e, categories c
WHERE e.id=c.id AND e.group=? AND c.category=?
ORDER by e.time

You're joining the tables by ID, but you have no index on entries.id - so the query is probably doing a hash or sort merge join. I wouldn't mind seeing a plan for what your system is doing now to confirm.

If you're doing a pagination query (i.e. only interested in a small number of rows) you want to get the first rows back as quick as possible; for this to happen you'll probably want a nested loop on entries, e.g.:

NESTED LOOPS
   ACCESS TABLE BY ROWID - ENTRIES
      INDEX RANGE SCAN - (index on ENTRIES.group,time)
   ACCESS TABLE BY ROWID - CATEGORIES
      INDEX RANGE SCAN - (index on CATEGORIES.ID)

Since the join to CATEGORIES is on ID, you'll want an index on ID; if you make it an IOT, and make ID the leading column, that might be sufficient.

The performance of the plan I've shown above will be dependent on how many rows match the given "group" - i.e. how selective an average "group" is.

Answer 4

What version of Oracle are you using? I ASSUME there is a primary key on table entries for field id, correct? Why the WHERE condition does not include "c.group = e.group" ?

Try to:

1. Remove the order by condition
2. Change the index definition from "create unique index (group, category, id)" to "create unique index (id, group, category)"
3. Reorganise table categories as an IOT on (group, category, id)
4. Reorganise table categories as an IOT on (id, group, category)

In each of the above case use EXPLAIN PLAN to review the cost