Re: Full table scan performance differences - why?

On Sep 19, 1:38 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> <sriv..._at_hotmail.com> schreef in berichtnews:b3eb4077-78e5-44e4-be2a-76c00cb816e7_at_l42g2000hsc.googlegroups.com...
>
>
>
> > This is being caused due to the package call for every row.
>
> > You can change this situation by defining the function as
> > deterministic
>
> > I tested it out
>
> > create or replace package px is
> > g_date date;
> > function get_date return date;
> > function get_date_det return date deterministic ;
> > end;
> > /
>
> > create or replace package body px is
> > function get_date return date is
> > begin
> >  return px.g_date;
> > end;
> > function get_date_det return date deterministic is
> > begin
> >  return px.g_date;
> > end;
> > end;
> > /
>
> > create table x as select * from dba_objects
> > /
>
> > you can check the performance of each of these statements
>
> > select count(*) from x where
> > created>to_date('20.07.2008','dd.mm.yyyy')
> > /
>
> > exec px.g_date := to_date('20.07.2008','dd.mm.yyyy')
>
> > select count(*) from x where created>px.get_date
> > /
>
> > select count(*) from x where created>px.get_date_det
> > /
>
> > But there are somethings you need to be know of deterministic
> > functions.
> > check pl/sql doc
>
> > regards
> > srivenu
>
> Only problem is that the function is not really deterministic. Its return
> value is determined by some factor outside the function (a global variable).
> If it changes, the function result changes without the function being
> invalidated.
> So it may speed up the query, but it is basically wrong.
>
> Shakespeare

If DETERMINISTIC's caching/reexecution behaviour is limited to one SQL call at a time, then all you are losing is the ability to have ret_pkg.get_date() change its return value during the course of the query, and most likely this is not wanted anyway. However if in some future release deterministic functions' cached return values start to be reused in subsequent queries, then you would have a bug. Received on Sat Sep 20 2008 - 13:01:51 CDT