Re: Full table scan performance differences - why?

<srivenu_at_hotmail.com> schreef in bericht news: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 Received on Fri Sep 19 2008 - 07:38:36 CDT