Full table scan performance differences - why?

Hello group,

I've got a table with roughly 7 million rows in a 10G database.

When I query it like that it returns results in about a second:

    SELECT *

      FROM VERM
     WHERE 1=1
       AND VER_DAT >= to_date('20.07.2008','dd.mm.yyyy');

The plan looks like this:
SELECT STATEMENT ALL_ROWSCost: 11,111 Bytes: 210,924 Cardinality: 5,022

        1 TABLE ACCESS FULL TABLE VERM Cost: 11,111 Bytes: 210,924 Cardinality: 5,022

When I change the query so that the date value (i.e. the SAME date value) is returned from a package function (which in turn just returns a global package variable) all is different:

    SELECT *

      FROM VERM
     WHERE 1=1
       AND VER_DAT >= ret_pkg.get_date;

It takes one minute to return results and the plan looks like this: SELECT STATEMENT ALL_ROWSCost: 12,667 Bytes: 14.839.356 Cardinality: 353,318

        1 TABLE ACCESS FULL TABLE VERM Cost: 12,667 Bytes: 14.839.356 Cardinality: 353,318

I wonder why. Probably the reason is that the date-value is a constant in the first query but not in the second. But in reality the second value is also a constant - it does not change. I've tried to improve performance of the second query by using a restrict_references pragma for the package function - but this didn't help. Somehow I need to communicate to Oracle that the first and second queries are to be executed the same way - through an optimizer hint maybe. How can I solve this?

Thanks,
Stephan Received on Thu Sep 18 2008 - 11:50:19 CDT