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

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Thu, 29 Jan 2009 20:20:04 +0100
Message-ID: <glsvh5$imh$1_at_reader.motzarella.org>

Jonathan Lewis wrote:

> "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 double-
> counting. (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.

This is the full execution plan:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---

---

| Id  | Operation                         |  Name       | Rows  | Bytes 

| Cost | Pstart| Pstop |

---

|   0 | SELECT STATEMENT                  |             | 55964 | 
9072K|   588 |       |       |
|   1 |  TABLE ACCESS BY LOCAL INDEX ROWID| T           | 55964 | 
9072K|   588 |    18 |    18 |
|*  2 |   INDEX RANGE SCAN                | IDX_2       |  1142 | 

| 357 | 18 | 18 |

---

Predicate Information (identified by operation id):

---

    2 - access("T"."VMENMAG"=18 AND "T"."VMEMOIS"=200810)

        filter("T"."VMENMAG"=18 AND "T"."VMEMOIS"=200810)

Note: cpu costing is off

I'm reporting the original version too, there may be a sligth differences between my original post and the following two because of data changes and the automated statistics gathering with estimate percent 10:

PLAN_TABLE_OUTPUT

---

---

| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |

---

|   0 | SELECT STATEMENT     |             | 55964 |  9072K|  5947 | 
    |       |
|*  1 |  TABLE ACCESS FULL   | T           | 55964 |  9072K|  5947 | 

18 | 18 |

---

Predicate Information (identified by operation id):

---

    1 - filter("T"."VMEMOIS"=200810)

Note: cpu costing is off

Best regards
Dimitre Received on Thu Jan 29 2009 - 13:20:04 CST