Re: Full table scan performance differences - why?

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 Received on Fri Sep 19 2008 - 07:19:36 CDT