Re: Advice on Indexes please?

"harry" <a_at_abc.com> wrote in message
news:dj3iuto2j3uucoa7ms9li9o3vkmdhvdt3t_at_4ax.com...
> In general should any columns that appear in a WHERE clause have a index
created for them?
>
> Also should the same rule apply if they appear in the ORDER BY part
aswell?
>
> Any other tips?
>
> many thanks
>
> harry

Question 1:
No. This depends on whether the affected columns are making up a primary, unique of foreign key constraint and on the cardinality. One column with 4 distinct values, which are evenly distributed, it makes very little sense to index that one, or it must be bitmap indexes (remember however bitmap indexes result in an insert/update performance hit) Question 2:
No. In most cases you are better off with a sort/merge.

Creating histograms using analyze or dbms_stats will show you the distribution of an index.
Also it will show you the clustering factor of an index. This factor determines distribution of records according to the index in a table. Clustering factor nearing the number of records means: the physical sequence of records corresponds with the sequence in the index. Clustering factor nearing the number of blocks means: records are completely unorganized.
When CBO has to choose between 2 valid indexes, it will always choose the index with the lowest clustering factor. In extreme cases this may force you to reorganize your table.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address