Shadi AL BARHOUCH wrote:

> Hi all :
> I have an oracle 9i table which has the fields a,b,c, and d . I made an =
> index on the group (a,b) and (c,d)
> if I user the simple select :
> select * from test where (b < 10) what is the strategy of oracle to take =
> the indexes ? did it search for the index b within the group indexes =
>
The correct answer is: "that depends"

But to be more precise:

If you are using the rule based optimizer (RBO) then no index will be used.

If you are using the cost based optimizer (CBO) AND the number of
distinct values in the a column are sufficiently low - the optimizer can calculate
that it is cheaper to "skip scan" the a,b index.
In that case the a,b index will be used.

This can be verified in V$SQL_PLAN, with explain plan, SQL trace or
tools using these sources for information (like Tora) or Oracle Enterprise
Manager (the tuning pack).

> (a,b) ? or it doesn't deal with the field b as an indexed fielkd ?
> and if I added an additional condition like (b < 10 and name !=3D =
> 'Name') does oracle search for the indexed condition fields ? or it =
> apply the condition during its order ?
>
If the conditions mentioned above for using an index are met - then in
*most* cases the index will be used first and then the second predicate
will be evaluated after retrieving the rows from the table.

There are cases though where the whole query is solved only using the index
and in those cases the extra predicate (requiring data from the table) can be
calculated as being more expensive in terms of IO's than doing a full table scan
and thus not use an index.

> I need full description for the oracle index strategy .
>  
>
Probably you are best left at reading the docs (only a few 1000' pages)
- plus browsing through metalink
- plus attending a SQL tuning course

:-) :-)

> Best regrards
> Shadi Al Barhouch
>  
>
Martin Berg



-- 
To unsubscribe, email: suse-oracle-unsubscribe@suse.com
For additional commands, email: suse-oracle-help@suse.com
Please see http://www.suse.com/oracle/ before posting

.