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:
>> 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
>>
>>
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
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