Re: Individual Indexes v.s. One "combinded" Index

In article <2aa.456.846%mpcbbs_at_ibase.org.br>, Carlos.Netto_at_ibase.org.br (Carlos Netto) writes:
>

 ...stuff deleted ...
>
> a) search for value 1 -> 10,000 rows scan (in avarange, of course)
> b) search for value 6 -> 10,000 rows scan (in avarange, of course)
> c) search for value 3 -> 10,000 rows scan (in avarange, of course)
> d) search for value 9 -> 10,000 rows scan (in avarange, of course)
> e) search for value 2 -> 10,000 rows scan (in avarange, of course)
> f) search for value 0 -> 10,000 rows scan (in avarange, of course)

I may have miss-understood what you were trying to say but ...

Oracle will pick ONE index and retrieve all rows that satisfy THAT single criteria AND then examine each identified row to exclude those that fail the other column criteria.

This is the reason why it is best to have a concatenated index (or as unique as possible) that narrows down the candidate search rows very quickly.  

> to make a intersect of the six above results (AND-EQUAL operation). Of course,
> Oracle can make something better (I didn't execute EXPLAIN for this statement),
> but be sure nothing as good as a unique scan.

I have never seen Oracle do the multiple index scanning (on one table) and then sort-merge that you have detailed above.

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras