Index Issues

[ckwan]

Hi everyone,

I come across an Oracle Reference Book with following statement :

Simple Index contains only one column of the corresponding row in the table.
Composite Indexes store more than one column of data for you to search, plus the ROWID of the corresponding row in the table. You can up to 32 columns in a composite index, but you may be restricted from including that many if the total size of all the columns you want in the index exceeds DB_BLOCK_SIZE / 3.

How to calculate the total size of all the columns ?
Why the db_block_size must divide by 3 ?

Thanks

[sreddy]

Why it should be divided by 3, I dunno ! Oracle better answer you Q as they fix the limitations on the parameters,values etc.,

AFAI, calculating size of the columns, see the datatypes of columns and length of the columns and add all of the size of the columns you will arriving at what you wanted. Remember if its date column its 7 bytes. Refer documentation for the rest of the datatypes.

[Halo]

Refer the DBA Handbook for details on index sizing.