Full Partition Scan

[haste]

Hi,
I have written this query for a partitioned table and what I get is that it scans all the partitions, why is this so?

SELECT COUNT(trn_no)
FROM TRANSACTIONS
WHERE ORG_CODE = :P_ORG_CODE
AND DOC_DT BETWEEN ADD_MONTHS(TO_DATE(:P_YEAR,'YYYY'), -11) AND ADD_MONTHS(TO_DATE(:P_YEAR,'YYYY'), 1)-1
AND REGION_CODE = :REGION_CODE

The table is partition by range:

PARTITION BY RANGE (CREATED_ON) 
(  
  PARTITION TRANS_YR_2005_AND_LESS VALUES LESS THAN (TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING,  
  PARTITION TRANS_YR_2006_Q1 VALUES LESS THAN (TO_DATE(' 2006-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING,  
  PARTITION TRANS_YR_2006_Q2 VALUES LESS THAN (TO_DATE(' 2006-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING,  
  PARTITION TRANS_YR_2006_Q3 VALUES LESS THAN (TO_DATE(' 2006-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING,  
  PARTITION TRANS_YR_2006_Q4 VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING,  
  PARTITION TRANS_YR_2007_Q1 VALUES LESS THAN (TO_DATE(' 2007-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING,  
  PARTITION TRANS_YR_2007_Q2 VALUES LESS THAN (TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING,  
...

 

[haste]

Hi,
I changed the condition to use CREATED_ON col which is the partition key rather than DOC_DT and the query didn't do a scan on all partitions.

AND CREATED_ON  BETWEEN ADD_MONTHS(TO_DATE(:P_YEAR,'YYYY'), -11) AND ADD_MONTHS(TO_DATE(:P_YEAR,'YYYY'), 1)-1