Re: 32 indexes in a table ??

premjhere_at_yahoo.co.in (Prem J Khanna) wrote in message news:<11af2de9.0207090026.3cbe0b35_at_posting.google.com>...
> guys,
>
> in my client place (8.1.6 on win2k),
>
> i have a table MSG which has about 9 lac records and 45 columns.
> it has about 32 b-tree non-unique indexes.
> no primary key is found , although a column "POSTNO" can be a
> PK.
>
I've never seen any table with so many indexes.

> of these 32,
>
> 18 columns have no values at all.i mean NULL.
> 8 columns have 30 records .
> 3 have some 5000 records.
> 2 columns have 8 lac records
> 2 has 2 lac records
>
> is the indexes on the first 26 columns which have negligible
> records necessary ? can they be dropped ?
> will not the huge no. of NULL in an indexed column bring down the
> performance ?
>
If you have indexes which are created only on null columns (one or more of the 18) they are useless and can be dropped. Whether you need the other indexes depends on how you query the table. But I suspect you won't need more than a few for a 5000 record table. Oracle concepts mannual has good explanations on how indexes are created and used.

> and can the index on the column ( which is of low cardinality )
> which has 2 lac records be changed to bitmap index ?
>
> is too many indexes advisable in a table ?
>
> It's urgent.
> kindly guide me.
>
> TIA.
Received on Wed Jul 10 2002 - 00:52:04 CEST