Re: Full table scan performance differences - why?

On 19 Sep., 14:19, sriv..._at_hotmail.com wrote:
> 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

Yes, this works! Thanks a lot! This helped a lot. Received on Fri Sep 19 2008 - 07:50:59 CDT