search on front of compound key

[robert]

i can't find a thread which answers the question:
does oracle (8.1.x) benefit from having multiple compound
keys?

NonUniqueKey -> col1 + col2 + col3
OtherNonUniqueKey -> col1 + col2

a where clause -> where col1 = 'A' and col2 = 'B'

other databases i've used would retrieve (based on the
physical architecture of their index stores) just as fast
on NonUniqueKey; OtherNonUniqueKey is completely redundant.

thanks,
robert

[Tim Cuthbertson]

You are correct, the OtherNonUniqueKey index is redundant and can safely be
dropped. Actually, it reduces performance on inserts, deletes, and some
updates, as both indexes must be maintained.

Tim

"robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...

i can't find a thread which answers the question:
does oracle (8.1.x) benefit from having multiple compound
keys?

NonUniqueKey -> col1 + col2 + col3
OtherNonUniqueKey -> col1 + col2

a where clause -> where col1 = 'A' and col2 = 'B'

other databases i've used would retrieve (based on the
physical architecture of their index stores) just as fast
on NonUniqueKey; OtherNonUniqueKey is completely redundant.

thanks,
robert