Re: Full table scan performance differences - why?

Comments embedded.
On Sep 18, 11:50 am, steph <stepha..._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.

You're calling a function for each and every row you process. Have you taken the time to trace what that function is doing? Probably not ...

> Probably the reason is that the date-value is a constant
> in the first query but not in the second.

Partly.

> But in reality the second
> value is also a constant - it does not change.

But you're now going through any number of additional 'gyrations' to get that 'constant'.

> 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.

Why should it?

> 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?
>

Discover what, exactly, this function is doing and make THAT more efficient. Or re-write your query to call the function ONCE for the duration of the query:

     WITH PKGDT as (
              SELECT ret_pkg.get_date proc_dt
              from dual
     )
     SELECT *
       FROM VERM, pkgdt
      WHERE 1=1
        AND VER_DAT >= proc_dt;

> Thanks,
> Stephan

David Fitzjarrell Received on Thu Sep 18 2008 - 14:29:32 CDT