Index Organized Tables

From: <mikestreeton_at_my-deja.com>
Date: Wed, 04 Aug 1999 08:41:27 GMT
Message-ID: <7o8ubn$qj$1@nnrp1.deja.com>

Can anybody enlighten me as to the best uses for index organized tables, I have a set of data that I think will benefit from being stored in a table, i.e. it is always accessed by the PK index, either a single row or range scan. What is not made clear in the manual is which columns are put in the index and which go in the overflow blocks, is this correct (this is a quick example not synatically correct)

create table x (

a number,
b number,
c number,
d number,
e number,
f number,

constraint pk_x primary key (a,b))
organization index tablespace my_tabspace threshold 20 including e
overflow tablespace my_overtabspace
/
This will create an index organized table with an index containing the columns a,b and also holding the columns c,d and e. The column f will be stored seperately in blocks in the the tablespace my_overtabspace. Therefore access to columns a thru e will only be via the index and access to column f with require further reading from the over flow blocks as would table columns that do not appear in a normal index.

If this is correct is it a good idea to store all the columns in the index since all my queries use all the columns (10 numbers and 2 varchars).

Many Thanks

Mike

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Aug 04 1999 - 03:41:27 CDT