Re: index usage question

Hi,

generally, an index is used only when the first columns have a value in the where clause.

this is true in a rule based instance but this is disappearing with the new versions (V8) of Oracle.

if you use statistics mode, you can check :

1. seletivity of all columns having a value in where clause
2. collecting or not the statistics values (see ANALYZE command)
3. number of rows in your table

HTH.
Yang

usmrall wrote in message <8216mu$rqa$1_at_nnrp1.deja.com>...
>Hello all,
>How does Oracle decide which index to use, in a rule based world ?
>I did read up on the tuning guid for RBO, but I have a wierd case
>so here goes...
>my where clause is as follows
>where client = 50 and document_num = 100 and rownum < 2
>there are two indexes
>index a: client,company_code,posting_date,document_num (PK index)
>index b: client,company_code,posting_docu,document_num
>
>Oracle ends up using the primary key index. Now, I was under the
>impression that in RBO an index matching the WHERE predicate would
>be used ! Was I wrong or is there some logic to this?
>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Tue Nov 30 1999 - 14:07:28 CST