0

i have two queries

select *
 from T
 where DATE_OF between to_date ('01.02.2012 00:00:00', 'DD.MM.YYYY hh24:mi:ss')
                   and to_date ('01.02.2012 23:59:59','DD.MM.YYYY hh24:mi:ss')

explain plan

SELECT STATEMENT  ALL_ROWSCost: 146,313  Bytes: 55,799,142  Cardinality: 150,402        
2 MAT_VIEW ACCESS BY INDEX ROWID MAT_VIEW T Cost: 146,313  Bytes: 55,799,142  Cardinality: 150,402      
    1 INDEX RANGE SCAN INDEX IND$T_05 Cost: 464  Cardinality: 150,402  

Second query

select *
from T
where DATE_OF >= to_date('201202','yyyyMM') 
  and DATE_OF <  to_date('201203','yyyyMM')

explain plan

SELECT STATEMENT  ALL_ROWSCost: 4,242,836  Bytes: 1,618,120,952  Cardinality: 4,361,512         
2 MAT_VIEW ACCESS BY INDEX ROWID MAT_VIEW T Cost: 4,242,836  Bytes: 1,618,120,952  Cardinality: 4,361,512   
    1 INDEX RANGE SCAN INDEX IND$T_05 Cost: 13,368  Cardinality: 4,361,512  

index ddl

CREATE INDEX IND$T_05 ON T(DATE_OF, TP)

DATE_OF is date typed column. MVIEW is not partitioned.

Why such a big difference in the cost of queries?

turbanoff
  • 2,439
  • 6
  • 42
  • 99
  • Had similar issue. Had table with a DATE column TDATE, with index on that column. Issued a select query which in the where clause I had "where TDATE >= TO_TIMESTAMP('12-MAR-12')". Execution plan cost was high. Once I changed to "where TDATE >= TO_DATE('12-MAR-12')", the execution plan cost was significantly lower. – Jose Mar 13 '12 at 14:02

1 Answers1

3

This is because first query scans for one day(1 Feb 2012) and the second scans for one month(Feb 2012).

You want one day or one month?

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76