Re: Need help with indexes

"Randy Harris" <randy_at_SpamFree.com> wrote:

>I'm using 8.1.7 and am looking for some advice on creating indexes.
>
>A table includes columns A and B. I expect to use both fields in WHERE
>clauses and ORDER BY clauses so I would like indexes on both of them. The
>SELECT statements will likely use one or the other but not both, however I
>need the (A,B) combination to be UNIQUE.
>
>I thought:
>
>Create INDEX on A (not unique)
>Create INDEX on B (not unique)
>Create UNIQUE CONSTRAINT on (A,B)
>
>It won't let me do that.
>
>Will simply creating a unique index on (A,B) do what I want if I only use
>one field or the other in SELECT statements?
>
>Thanks in advance for any suggestions.
>

IIRC,
Prior to v9, a compound index is only used if the fields are used in the order that they appear in the index.. So if you created a unique index (A,B),
any selection criteria would need to use A (or A AND B) for that index to come into play..Using Just B would not work. Oracle ( until v9) will not use columns in a compound index if you do not, when attempting to use one of the columns after the first one in the compound index column list, specify all the columns that precede it .

You could, I imagine, insure that both columns are used even if only B contains the real selection criteria, like:

Where A >' 0' and B = 'real selection criteria'; ( assumes A is Varchar type) Received on Fri Sep 19 2003 - 10:42:15 CDT