Re: How does Oracle use indexes?

In <ds6i7300005_at_novalink.com> markf_at_novalink.com (Mark Fetherolf) writes:

>Rick Writes...
 

>A vendor has supplied an application with the following three indexes
>on a table:-
>Index1 uses columns col1, col2, col3, col4, col5, col6.
>Index2 uses columns col1, col2, col3.
>Index3 uses columns col1, col2.
>I think index2 and index3 are redundant. The vendor says no.
>Surely Oracle can use index1 in any situation when only some of those
>leading columns are needed! Can anyone shed light on this problem?
 

>Exact match index searches use a somewhat different strategy from range
>searches. It is concievable that you will get substantially better
>retrieval performance for some queries by having all three indexes.
>Clearly, you will incur more storage and update overhead. Try dropping
>one at at time and check your performance.

I fail to follow your logic here. It does not make sense for Index2 or Index3 to be unique, as longer indexes *would* be redundant (contrary to a previous response suggesting that Index3 may be necessary for a Unique constraint).

Thus an exact match strategy is only relevant to Index1 (maybe) whilst a range search on col1 & col2 could be executed against any of the three. Therefore I see Index2 and Index3 as redundant.

If you are saying that Oracle can execute an exact match strategy against a duplicate index then I am intrigued. Please elaborate.

graeme

--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.