Re: Index Organized Tables

Your understanding appears to be correct. You might want to make your blocks fairly large and the PCTTHRESHOLD fairly large though (even up to 50%) to make sure that bits of rows to not accidentally fall into the overflow.

I would try creating the table without an OVERFLOW at all to start with (will fail if Oracle determines that block size and max row requirements may lead to the need for an overflow)

Since all your queries use all your columns, you do not want an overflow. If almost all your queries use only the first few columns then you would consider an explicit INCLUDING column.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

mikestreeton_at_my-deja.com wrote in message <7o8ubn$qj$1_at_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 - 04:39:47 CDT