Oracle Partition Pruning with bind variables

Question

I have a large (150m+ row) table, which is partitioned into quarters using a DATE partition key.

When I query the table using something like...

SELECT *
FROM   LARGE_TABLE
WHERE  THE_PARTITION_DATE >= TO_DATE('1/1/2009', 'DD/MM/YYYY')
AND    THE_PARTITION_DATE < TO_DATE('1/4/2009', 'DD/MM/YYYY');

... partition pruning works correctly... the optomiser is able to realise that it only needs to look at a single partition (in this case Q1 2009). EXPLAIN PLAN shows "PARTITION RANGE SINGLE"

However, when I move this query to PL/SQL and pass in the same dates as variables, the plan is showing as "PARTITION RANGE (ITERATOR)"... the optomiser is unable to understand that it only needs to look at the single partiiton (presumably because it doesn't have the actual values when it's evaluating the plan).

The only workaround I've found round so far is to code an EXECUTE IMMEDIATE including the dates in the SQL string so that the partition pruning works correctly.

Is there a better way?

Answer 1

I don't think you should see an actual performance difference with the bind variables - you should see an execution plan step of "PARTITION RANGE ITERATOR PARTITION: KEY KEY..." which means Oracle will determine the start and stop partitions at execution time.