Re: Need help with indexes

Randy Harris 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.
>
>
>

Patch to level 4 if you aren't already there and build a unique constraint on either AB or BA depending on how you think the resulting index will be most-often used. Then, if required add an index on B or A though I would expect you wouldn't need to do so.

Information on cardinality and the percentage of rows your query is expected to return would be helpful too.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)