Your belief that this should not be necessary is correct based on your assumptions. Normally this means the assumptions should be rechecked (unless there is data corruption or a bug). If you run the following you should get the same results from the first query as the second:
DROP TABLE t1;
CREATE TABLE t1 AS (
SELECT to_date('01-FEB-2011','DD-MON-YYYY')+level myDateColumn
FROM dual CONNECT BY level <=120);
select * from t1
where myDateColumn
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');
select myDateColumn from t1
where to_date(myDateColumn)
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');
To check some of the assumptions, can you show the results of the following query?
SELECT myDateColumn, to_date(myDateColumn)
, to_char(myDateColumn,'DD-MON-YY HH.MI.SS PM'), to_char(myDateColumn,'YYYY')
FROM myTable
WHERE to_date(myDateColumn)
BETWEEN to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');
The first three columns should all show identical information and the last should verify that the year is correct.
Oracle is unable to do partition pruning when a function is applied to the partitioned column. From the docs:
There are several cases when the optimizer cannot perform pruning. One
common reasons is when an operator is used on top of a partitioning
column. This could be an explicit operator (for example, a function)
or even an implicit operator introduced by Oracle as part of the
necessary data type conversion for executing the statement.
Your view has to apply some form of function to start and end dates to figure out if they're the same year or not, so I believe you're out of luck with this approach.
Our solution to a similar problem was to create materialized views over the base table, specifying different partition keys on the materialized views.
We've tailored ours to match common base queries so that we get query rewrite benefits as well. You may need to get users to use the MVs directly to ensure you get the partition pruning working as you need, rather than relying on query rewrite.
(Updated to remove incorrect example and add info regarding applying functions to partition columns)
Best Answer
MIN
will return the smallest non-NULL value. The query won't throw an error, it will simply ignore the NULL values