Re: Yet another "why is my index not used" question

"Radoulov, Dimitre" <cichomitiko_at_gmail.com> wrote in message news:glrs1c$k3c$1_at_reader.motzarella.org...
>
> Hi Michael,
> I confirm that the optimizer picks a different access path
> for the modified version you suggest:
>
>
> 09:57:27 SQL> SELECT * FROM t WHERE
> 10:03:32 2 vmenmag=18 AND vmemois=200810 and
> 10:03:37 3 vmenmag=18 AND vmemois=200810;
>
> 53710 rows selected.
>
> Elapsed: 00:00:32.28
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=588 Card=55964
> Bytes=9290024)
> 1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T' (Cost=588
> Card=55964 Bytes=9290024)
> 2 1 INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE) (Cost=357
> Card=1142)
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 28025 consistent gets
> 1015 physical reads
> 0 redo size
> 4154910 bytes sent via SQL*Net to client
> 40036 bytes received via SQL*Net from client
> 3582 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 53710 rows processed
>
>

That's a little surprising, as it really shouldn't make a difference. The optimizer should be able to spot the duplicated predicates in such an easy case and eliminate them rather than doublecounting.  (This trick used to work in earlier versions of Oracle, I had thought it was out by 9.2) This behaviour probably won't (or, at least, shouldn't appear in 9.2.0.8 or 10.2.

It would be useful to generate a full execution plan using explain plan and dbms_xplan.display so that we can see what the final predicates look like.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html