SQL> explain plan for

SELECT a.user_id, a.user_agency_id, a.user_system_id, 

DECODE(a.action,'S','SEARCH','R','RETRIEVAL','A','ATTACHMENT','UNKNOWN') action,

b.agency_id,

TO_CHAR(a.created_on,'MM-DD-YYYY HH24:MI:SS') created_on

FROM AUSER.sr_log a, 

(select distinct sr_log_id, agency_id from onedoj.sr_data_source_log) b 

WHERE a.sr_log_id = b.sr_log_id 

AND a.created_on BETWEEN to_date('09-15-2009 12:00:00','MM-DD-YYYY HH24:MI:SS') AND to_date('09-21-2009 12:00:00','MM-DD-YYYY HH24:MI:SS') + 1

AND (AUSER.user_hits_on_sr('TEST', a.sr_log_id) > 0 

OR AUSER.user_hits_on_sr('TEST2', a.sr_log_id) > 0 )

ORDER BY a.user_id, a.created_on;

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3792515627



-----------------------------------------------------------------------------------------------

| Id  | Operation                | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT         |            | 13998 |  1599K|       |  4064   (2)| 00:00:49 |

|   1 |  SORT ORDER BY           |            | 13998 |  1599K|  3576K|  4064   (2)| 00:00:49 |

|*  2 |   HASH JOIN              |            | 13998 |  1599K|       |  3693   (2)| 00:00:45 |

|*  3 |    TABLE ACCESS FULL     | SR_LOG     |  2174 |    97K|       |  3554   (1)| 00:00:43 |

|   4 |    VIEW                  |            | 14001 |   970K|       |   138   (4)| 00:00:02 |

|   5 |     HASH UNIQUE          |            | 14001 |   232K|       |   138   (4)| 00:00:02 |

|   6 |      INDEX FAST FULL SCAN| OSDSL_PK00 | 64978 |  1078K|       |   134   (1)| 00:00:02 |

-----------------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   2 - access("A"."SR_LOG_ID"="B"."SR_LOG_ID")

   3 - filter("A"."CREATED_ON">=TO_DATE(' 2009-09-15 12:00:00', 'syyyy-mm-dd

              hh24:mi:ss') AND ("AUSER"."USER_HITS_ON_SR"('TEST',"A"."SR_LOG_ID")>0 OR

              "AUSER"."USER_HITS_ON_SR"('TEST2',"A"."SR_LOG_ID")>0) AND

              "A"."CREATED_ON"<=TO_DATE(' 2009-09-22 12:00:00', 'syyyy-mm-dd hh24:mi:ss'))





The function looks like this:

 CREATE OR REPLACE FUNCTION "ASUER"."USER_HITS_ON_SR" (p_user_group   in varch

ar2,p_sr_log_id    in number)

RETURN NUMBER IS



  hits   number := 0;



BEGIN



  SELECT nvl(sum(1),0)

  INTO   hits

  FROM   auser.user_group a,

         auser.group_access_local b,

         auser.data_source c,

         auser.sr_data_source_log d

  WHERE  d.sr_log_id = p_sr_log_id

  AND    a.group_name = p_user_group

  AND    b.user_group_id = a.user_group_id

  AND    c.data_source_id = b.data_source_id

  AND    d.agency_id = c.data_source_name;



  RETURN hits;



END;

create snapshot abcd

as

SELECT d.sr_log_id, a.group_name, nvl(sum(1),0) hits

  FROM   auser.user_group a,

         auser.group_access_local b,

         auser.data_source c,

         auser.sr_data_source_log d

  WHERE  b.user_group_id = a.user_group_id

  AND    c.data_source_id = b.data_source_id

  AND    d.agency_id = c.data_source_name;

group by d.sr_log_id, a.group_name

;



create unique index abcd_u1 on abcd (sr_log_id, group_name);

--drop table sr_data_source_log

create table sr_data_source_log (

sr_log_id number

,agency_id number

);



--drop table data_source

create table data_source (

data_source_id number primary key

,data_source_name varchar2(20)

);



--drop table group_access_local

create table group_access_local (

user_group_id number

,data_source_id number

);



--drop table user_group

create table user_group (

user_group_id number primary key

,group_name varchar2(20)

);



--drop table sr_log

create table sr_log (

sr_log_id number primary key

,user_id number

,created_on date

,action varchar2(1)

,user_system_id number

,user_agency_id number

);



--drop snapshot abcd

create snapshot abcd

as

SELECT d.sr_log_id, a.group_name, nvl(sum(1),0) hits

  FROM   user_group a,

         group_access_local b,

         data_source c,

         sr_data_source_log d

  WHERE  b.user_group_id = a.user_group_id

  AND    c.data_source_id = b.data_source_id

  AND    d.agency_id = c.data_source_name

group by d.sr_log_id, a.group_name

;



create unique index abcd_u1 on abcd (sr_log_id, group_name);



CREATE OR REPLACE FUNCTION USER_HITS_ON_SR (p_user_group   in varchar2,p_sr_log_id    in number)

RETURN NUMBER IS



  hits   number := 0;



BEGIN



  SELECT a.hits

  INTO   hits

  FROM   abcd a

  WHERE  sr_log_id = p_sr_log_id

  AND    group_name = p_user_group

  ;

  

  RETURN hits;



END;

create snapshot log_agency

as

select distinct sr_log_id

     , agency_id

from sr_data_source_log

;



create index log_agency_i1 on log_agency (sr_log_id)

SELECT a.user_id

     , a.user_agency_id

     , a.user_system_id

     , DECODE(a.action,'S','SEARCH','R','RETRIEVAL','A','ATTACHMENT','UNKNOWN') action

     , b.agency_id

     , TO_CHAR(a.created_on,'MM-DD-YYYY HH24:MI:SS') created_on

FROM sr_log a 

JOIN log_agency b ON  a.sr_log_id = b.sr_log_id 

WHERE a.created_on BETWEEN to_date('09-15-2009 12:00:00','MM-DD-YYYY HH24:MI:SS')

                       AND to_date('09-21-2009 12:00:00','MM-DD-YYYY HH24:MI:SS') + 1

AND (user_hits_on_sr('TEST', a.sr_log_id) > 0 

  OR user_hits_on_sr('TEST2', a.sr_log_id) > 0 )

ORDER BY a.user_id, a.created_on;

create index sr_log_i1 on sr_log (created_on);