Re: Full table scan performance differences - why?

On Thu, 18 Sep 2008 09:50:19 -0700 (PDT), steph <stephan0h_at_yahoo.de> wrote:

>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

Please read the execution plans carefully. They are *identical*!!! No 'improvement' is possible. The Cost Bytes and Cardinality data have always been an *estimate*. In the former case CBO can access histograms, because it has the constant.
Functions are not being executed when determining the plan. But anyway: table access full is table access full, which ever way you are going to hint it.

-- 
Sybrand Bakker
Senior Oracle DBA