Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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)Received on Fri Sep 19 2003 - 10:44:40 CDT