Corresponding index table?

Question

I recently read about Oracle Index Organization Charts (IOT), but I'm not sure I fully 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 column idis a foreign key from another table entries, and my general 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 record table is correctly indexed.

Both of these tables have millions (16M currently) of rows, and currently this query really stinks (note: I wrapped it in a page request too, so I only return the first 20, but for simplicity I omitted this).

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

EDIT by popular request:

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

create index (group, time) compress 1;

, , . , (3 ), , IOT?

Answer 1

IOT , , ( ) - , - , , , , . id, , . (id, group, category). , (, , ).

:

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

, entry.id - , , . , , .

(.. ), ; , , , :

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)

, ID; IOT , .

, , , "", "".

Answer 2

dba-oracle.com, asktom.com, IOUG, asktom.com?

IOT - ,

?

, , .

Answer 3

IOT - . /. , . , , .

:

• - first_rows?
• , , ? , , .
• . addind index ( id).
• , ORDER BY, ?

Answer 4

Oracle ? , , ? WHERE "c.group = e.group"?

:

• "create unique index (group, category, id) "to" (id, group, category) "
• IOT (, , id)
• IOT (id, group, category)

In each of the above cases, use EXPLAIN PLAN to view the cost.