Home » SQL & PL/SQL » SQL & PL/SQL » FUNCTION-BASE INDEX for user defined function
FUNCTION-BASE INDEX for user defined function [message #187326] Sat, 12 August 2006 02:53 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi everyone,

Could someone help me out how to create
FUNCTION-BASE INDEX for the following function?
or is it possible at all?

The idea is I want to improve the performance of the following function to works it as fastest as possible so one way i found is to create index on function and if you have any other ideas
to improve its performance i.e. changing its sql statement style
to run fast,please suggest me.



FUNCTION f_GetFiscalInfo (v_Date IN  DATE)
  RETURN tFiscalMonthRec
  IS
    v_FiscalInfo             tFiscalMonthRec;
  BEGIN
    SELECT BeginDate, EndDate, FiscalMonth
    INTO   v_FiscalInfo
    FROM   tblFiscalMonth
    WHERE  BeginDate <= v_Date
    AND    EndDate >= v_Date;
    RETURN (v_FiscalInfo);
  EXCEPTION
    WHEN OTHERS THEN
      v_FiscalInfo.FiscalMonth := 'PEND';
      RETURN (v_FiscalInfo);
  END f_GetFiscalInfo;

---following record type used for the above function.

TYPE tFiscalMonthRec IS RECORD
   (FirstDay                 tblFiscalMonth.BeginDate%TYPE,
    LastDay                  tblFiscalMonth.EndDate%TYPE,
    FiscalMonth              tblFiscalMonth.FiscalMonth%TYPE);







Re: FUNCTION-BASE INDEX for user defined function [message #187364 is a reply to message #187326] Sat, 12 August 2006 16:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
In order to create a function-based index, the function must be determinisitic. If you pass the same parameters, the function must be such that it will always return the same results. If the results are dependent on the data selected from a non-static, dynamic table, then it cannot be deterministic and you cannot use it for a function-based index. Although it might let you create it, it might provide wrong results, based on previous results. If your table is static then add the deterministic keyword and create the index.
Re: FUNCTION-BASE INDEX for user defined function [message #187367 is a reply to message #187364] Sat, 12 August 2006 17:36 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member

Hey Barbara , Thank you very much for your reply.

Data in table do change over a period,
so I think this will not serve my purpose of
making the function faster.

so how do improve the function performance?

will it be usefull TABLE FUNCTION WITH PIPELINED
in this situation?

or is ther any other technique to improve performance?



Re: FUNCTION-BASE INDEX for user defined function [message #187369 is a reply to message #187367] Sat, 12 August 2006 17:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
Your function, as it is now, can only handle returning one row. If you are expecting more than one row to meet the criteria, then using a pipelined function would be one way. If you are trying to improve the speed of the query within the function, then indexes on begindate and enddate, with current statistics should help. I don't know what context you are using the function in. It might be better just to include the query within the function as a subquery or just add the filter condition to whatever query you are using the function in, instead of using a function.
Re: FUNCTION-BASE INDEX for user defined function [message #187599 is a reply to message #187369] Mon, 14 August 2006 11:34 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member

I am trying to create TABLE-FUNCTION,
but could not successed
and want to use PIPLINED too ,

could anybody help me?



CREATE OR REPLACE TYPE     tFiscalMonthRec2 IS object
    (FirstDay                 date,
     LastDay                  date,
     FiscalMonth              varchar2(6));
 /

CREATE  TYPE tficalmonthrectype IS TABLE OF tFiscalMonthRec2;
/




SQL>  create or replace function ftest1 (v_date in date)
  2   RETURN tFiscalMonthRec2
  3   IS
  4   CURSOR c1 IS SELECT BEGINDATE,ENDDATE,FISCALMONTH
  5               FROM tblfiscalmonth
  6               WHERE begindate <= v_date
  7               AND   enddate   >= v_date;
  8   TYPE c1type  IS TABLE OF c1%ROWTYPE INDEX BY PLS_INTEGER;
  9       c1rec     c1type;
 10      tfiscalmonthtab     tfiscalmonthrectype := tfiscalmonthrectype ();
 11   BEGIN
 12        OPEN c1;
 13        LOOP
 14        FETCH c1 BULK COLLECT INTO c1rec;
 15         FOR i IN c1rec.first..c1rec.last
 16         LOOP
 17          tfiscalmonthtab.EXTEND;
 18         tfiscalmonthtab(tfiscalmonthtab.LAST).firstday := c1rec(i).begindate;
 19          tfiscalmonthtab(tfiscalmonthtab.LAST).lastday := c1rec(i).enddate;
 20         tfiscalmonthtab(tfiscalmonthtab.LAST).fiscalmonth := c1rec(i).fiscalmonth;
 21         END LOOP;
 22         RETURN tfiscalmonthtab.LAST;
 23    EXIT WHEN c1%NOTFOUND;
 24    END LOOP;
 25    CLOSE c1;
 26    EXCEPTION
 27       WHEN OTHERS THEN
 28         tfiscalmonthtab(tfiscalmonthtab.LAST):= 'PEND';
 29         RETURN (tfiscalmonthtab(tfiscalmonthtab.LAST));
 30*    END fTEST1;
SQL> /

Warning: Function created with compilation errors.

SQL> show err
Errors for FUNCTION FTEST1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
22/8     PL/SQL: Statement ignored
22/31    PLS-00382: expression is of wrong type
28/8     PL/SQL: Statement ignored
28/48    PLS-00382: expression is of wrong type
29/8     PL/SQL: Statement ignored
29/15    PLS-00382: expression is of wrong type
SQL> 




Re: FUNCTION-BASE INDEX for user defined function [message #187658 is a reply to message #187599] Mon, 14 August 2006 17:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@10gXE> CREATE TABLE tblFiscalMonth
  2    (BeginDate		 DATE,
  3  	Enddate 		 DATE,
  4  	FiscalMonth		 VARCHAR2 (6))
  5  /

Table created.

SCOTT@10gXE> INSERT ALL
  2  INTO tblFiscalMonth VALUES (SYSDATE - 1, SYSDATE + 1, 'AUG')
  3  INTO tblFiscalMonth VALUES (SYSDATE - 2, SYSDATE + 2, 'AUG')
  4  INTO tblFiscalMonth VALUES (SYSDATE - 3, SYSDATE + 3, 'AUG')
  5  INTO tblFiscalMonth VALUES (SYSDATE + 1, SYSDATE + 3, 'AUG')
  6  INTO tblFiscalMonth VALUES (SYSDATE - 3, SYSDATE - 1, 'AUG')
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@10gXE> CREATE OR REPLACE TYPE tFiscalMonthRec AS OBJECT
  2    (FirstDay		 DATE,
  3  	LastDay 		 DATE,
  4  	FiscalMonth		 VARCHAR2 (6));
  5  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE TYPE tFiscalMonthRecType AS TABLE OF tFiscalMonthRec;
  2  /

Type created.

SCOTT@10gXE> CREATE OR REPLACE FUNCTION f_GetFiscalInfo
  2    (p_date IN DATE)
  3    RETURN tFiscalMonthRecType PIPELINED
  4  AS
  5  BEGIN
  6    FOR c1rec IN
  7  	 (SELECT BeginDate, EndDate, FiscalMonth
  8  	  FROM	 tblFiscalMonth
  9  	  WHERE  BeginDate <= p_date
 10  	  AND	 EndDate   >= p_date)
 11    LOOP
 12  	 PIPE ROW (tFiscalMonthrec (c1rec.BeginDate, c1rec.EndDate, c1rec.FiscalMonth));
 13    END LOOP;
 14    RETURN;
 15  END f_GetFiscalInfo;
 16  /

Function created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> SELECT * FROM TABLE (f_GetFiscalInfo (SYSDATE))
  2  /

FIRSTDAY  LASTDAY   FISCAL
--------- --------- ------
13-AUG-06 15-AUG-06 AUG
12-AUG-06 16-AUG-06 AUG
11-AUG-06 17-AUG-06 AUG

SCOTT@10gXE> 

Re: FUNCTION-BASE INDEX for user defined function [message #187695 is a reply to message #187658] Mon, 14 August 2006 23:53 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member

Many thanks Barbara!

you took so much effort for me thanks a lot.

I am using this table function quite well now.

I have a question : Is it possible to
use this table function in a package also?
i mean to assigned a values to a variable
which is based on a record datatype?

if it is possible can you just show
the part of assigning a values to variable.

like..

TYPE tFiscalMonthRec as record
(FirstDay date,
LastDay date,
FiscalMonth varchar2(6));

v_PendingLastDay date;
v_FromFiscalInfo tFiscalMonthRec;
v_ToFiscalInfo tFiscalMonthRec;
v_CurrentInfo tFiscalMonthRec;
begin
......................................
.........
v_CurrentInfo := f_getfiscalinfo(to_date('28-AUG-05'));
v_PendingLastDay := v_currentinfo.lastday;
v_ThisFiscalMonth := v_CurrentInfo.FiscalMonth;



I have a function in the package which does the above
job but that function OCURRENCE are high ie like
for 2 million records its OCURRENCE are 3.6 million
hence it consume lots of time when i try for 20 million rows,
so i am looking for some other approve to improve performace.

I need your help please.


Re: FUNCTION-BASE INDEX for user defined function [message #187724 is a reply to message #187695] Tue, 15 August 2006 03:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
Here is a link to an example in the online documentation:


http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2358
Re: FUNCTION-BASE INDEX for user defined function [message #187728 is a reply to message #187695] Tue, 15 August 2006 03:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@10gXE> CREATE TABLE tblFiscalMonth
  2    (BeginDate		 DATE,
  3  	Enddate 		 DATE,
  4  	FiscalMonth		 VARCHAR2 (6))
  5  /

Table created.

SCOTT@10gXE> INSERT ALL
  2  INTO tblFiscalMonth VALUES (SYSDATE - 1, SYSDATE + 1, 'AUG')
  3  INTO tblFiscalMonth VALUES (SYSDATE - 2, SYSDATE + 2, 'AUG')
  4  INTO tblFiscalMonth VALUES (SYSDATE - 3, SYSDATE + 3, 'AUG')
  5  INTO tblFiscalMonth VALUES (SYSDATE + 1, SYSDATE + 3, 'AUG')
  6  INTO tblFiscalMonth VALUES (SYSDATE - 3, SYSDATE - 1, 'AUG')
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@10gXE> CREATE OR REPLACE PACKAGE your_pkg
  2  AS
  3    TYPE tFiscalMonthRec IS RECORD
  4  	 (FirstDay    tblFiscalMonth.BeginDate%TYPE,
  5  	  LastDay     tblFiscalMonth.EndDate%TYPE,
  6  	  FiscalMonth tblFiscalMonth.FiscalMonth%TYPE);
  7    TYPE tFiscalMonthRecType IS TABLE OF tFiscalMonthRec;
  8    FUNCTION f_GetFiscalInfo
  9  	 (p_date IN DATE)
 10  	 RETURN tFiscalMonthRecType PIPELINED;
 11  END your_pkg;
 12  /

Package created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> CREATE OR REPLACE PACKAGE BODY your_pkg
  2  AS
  3    FUNCTION f_GetFiscalInfo
  4  	 (p_date IN DATE)
  5  	 RETURN tFiscalMonthRecType PIPELINED
  6    AS
  7  	 v_FiscalInfo tFiscalMonthRec;
  8    BEGIN
  9  	 FOR c1rec IN
 10  	   (SELECT BeginDate, EndDate, FiscalMonth
 11  	    FROM   tblFiscalMonth
 12  	    WHERE  BeginDate <= p_date
 13  	    AND    EndDate   >= p_date)
 14  	 LOOP
 15  	   v_FiscalInfo.FirstDay    := c1rec.BeginDate;
 16  	   v_FiscalInfo.LastDay     := c1rec.EndDate;
 17  	   v_FiscalInfo.FiscalMonth := c1rec.FiscalMonth;
 18  	   PIPE ROW (v_FiscalInfo);
 19  	 END LOOP;
 20  	 RETURN;
 21    END f_GetFiscalInfo;
 22  END your_pkg;
 23  /

Package body created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> SELECT * FROM TABLE (your_pkg.f_GetFiscalInfo (SYSDATE))
  2  /

FIRSTDAY  LASTDAY   FISCAL
--------- --------- ------
14-AUG-06 16-AUG-06 AUG
13-AUG-06 17-AUG-06 AUG
12-AUG-06 18-AUG-06 AUG

SCOTT@10gXE> 


Re: FUNCTION-BASE INDEX for user defined function [message #187982 is a reply to message #187728] Wed, 16 August 2006 10:37 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Thanks Barbara,

But how do i assigned it to variable in the
this situation:

suppose this way?


v_PendingLastDay     date;

v_FromFiscalInfo            tFiscalMonthRectype := tFiscalMonthRectype ();   
v_ToFiscalInfo              tFiscalMonthRectype := tFiscalMonthRectype ();
v_CurrentInfo               tFiscalMonthRectype := tFiscalMonthRectype ();

begin
......................................
.........
v_CurrentInfo         := f_getfiscalinfo(to_date('28-AUG-05'));
v_PendingLastDay      := v_currentinfo(v_currentinfo.last).lastday;
v_ThisFiscalMonth      := v_CurrentInfo.(v_currentinfo.last).FiscalMonth;




do i have EXTEND the type before i use it ?

how do i dextend it?

Thanking you again,
Re: FUNCTION-BASE INDEX for user defined function [message #188027 is a reply to message #187982] Wed, 16 August 2006 16:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
I am not quite sure what you are looking for, perhaps something like this?:

SCOTT@10gXE> DECLARE
  2    v_CurrentInfo your_pkg.tFiscalMonthRectype := your_pkg.tFiscalMonthRectype ();
  3  BEGIN
  4    SELECT * BULK COLLECT
  5    INTO   v_CurrentInfo
  6    FROM   TABLE (your_pkg.f_GetFiscalInfo (SYSDATE));
  7    DBMS_OUTPUT.PUT_LINE ('FIRSTDAY	LASTDAY   FISCAL');
  8    DBMS_OUTPUT.PUT_LINE ('--------	-------   ------');
  9    FOR i IN 1 .. v_CurrentInfo.COUNT LOOP
 10  	 DBMS_OUTPUT.PUT_LINE
 11  	   (v_CurrentInfo(i).FirstDay
 12  	    || ' '
 13  	    || v_CurrentInfo(i).LastDay
 14  	    || ' '
 15  	    || v_CurrentInfo(i).FiscalMonth);
 16    END LOOP;
 17  END;
 18  /
FIRSTDAY  LASTDAY   FISCAL
--------  -------   ------
15-AUG-06 17-AUG-06 AUG
14-AUG-06 18-AUG-06 AUG
13-AUG-06 19-AUG-06 AUG

PL/SQL procedure successfully completed.

SCOTT@10gXE>


Going back to what you originally said about wanting to improve performance, I think you are doing the opposite by adding unnecessary complexity. It is hard to tell without knowing the overall problem, but I keep thinking that all you need is to have the following query in an appropriate place in your ultimate code:

SELECT BeginDate, EndDate, FiscalMonth
FROM tblFiscalMonth
WHERE BeginDate <= p_date
AND EndDate >= p_date

Putting it in a function, then creating a table from that function and using the results to populate a collection, is all going to slow things down, not speed it up.
Re: FUNCTION-BASE INDEX for user defined function [message #188057 is a reply to message #188027] Wed, 16 August 2006 21:37 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Barbara, I do not know how to use
a SELECT STATEMENT
as you mentioned like
SELECT begindate
      ,enddate
      ,fiscalmonth
from tblfiscalmonth
where begindate <= p_date 
and   enddate   >= p_date;

where i am using FUNCTION (f_GetFiscalInfo) of that statement
if you see in the procedure where many places
function is working like multiple variables.

I agree with you there may be a way to do it which
i don't know.

Please help & advise.


PROCEDURE p_ClaimSplit (v_LastDay IN DATE)  -----,v_Rc OUT NUMBER)
  IS
  
/* VARIABLES */
       v_ThisFiscalMonth  tblclaimdetmonth_s_part.FiscalMonth%TYPE; --'200402';
       v_PendingLastDay   tblclaimdetmonth_s_part.FromDate%TYPE;  --Current fiscal month.
       v_SvcFiscalMonth   tblclaimdetmonth_s_part.FiscalMonth%TYPE;
       v_ProvAmount       tblclaimdetmonth_s_part.ProvAmount%TYPE;
       v_PlanAmount       tblclaimdetmonth_s_part.PlanAmount%TYPE;  --LJK 06/27/2005  New field Added.
       v_Date             tblclaimdetmonth_s_part.Fromdate%TYPE := NULL;
       v_FiscalDate       tblclaimdetmonth_s_part.Fromdate%TYPE := NULL;
       v_TOFiscalMonth    tblclaimdetmonth_s_part.FiscalMonth%TYPE := NULL;
       v_AmtUsed          tblclaimdetmonth_s_part.ProvAmount%TYPE := 0;
       v_PlanAmtUsed      tblclaimdetmonth_s_part.ProvAmount%TYPE := 0;  --LJK 06/27/2005  New field Added.
       v_ClaimID          tblClaimDetail.ClaimID%TYPE := NULL;
       v_ClaimLineNumb    tblClaimDetail.ClaimLineNumb%TYPE := NULL;
       v_Ct               PLS_INTEGER :=0;
       v_FMPart           PLS_INTEGER;
       v_CTX              PLS_INTEGER := 0;
       error_number       number;
       error_message      varchar2(200);
       v_SeqNumb          NUMBER := 0;
     --v_EmailTo          EMAIL.AddressList_Tab;
     --v_EmailFrom        VARCHAR2(50) := USER || '@GENTIVA.COM';
     --v_EmailSubject     VARCHAR2(50) := 'MONTH-END CLAIM SPLIT';
     --v_EmailText        VARCHAR2(2000) := NULL;
       x                  PLS_INTEGER := 0;
       y                  PLS_INTEGER := 100000;
       z                  PLS_INTEGER := 0;
       v_FromFiscalInfo   tFiscalMonthRec;
       v_ToFiscalInfo     tFiscalMonthRec;
       v_CurrentInfo      tFiscalMonthRec;
           

   CURSOR curcd
   IS
      SELECT o.claimid
            ,o.claimlinenumb
            ,o.regionalnetworkcenter
            ,o.paymentfiscalmonth
            ,DECODE (o.accountingdepartmentrptcd
                    ,'1', '1'
                    ,'2', '2'
                    ,'3', '3'
                    ,'4', '4'
                    ,'5', '3'
                    ,'6', '3'
                    ,'7', '3'
                    ,'9', '9'
                    ,'1'
                    ) feeforservice
            ,o.servicecatid
            ,o.fromdate
            ,o.todate
            ,o.fromdatefiscalmonth
            ,o.provamount
            ,o.planamount
            ,NVL (o.claimdetailhistrectypecd, 'A') claimdetailhistrectypecd
            ,NVL (o.claimdetailhistseqnumb, 0) claimdetailhistseqnumb
            , (o.todate - o.fromdate) + 1 totalservicedays
            ,carid
            ,o.provpaymenttype provtype
        FROM tblclaimdetmonth_s_part o
        WHERE FISCALDATE = '01-AUG-05'
        AND   (O.ProvAmount <> 0 OR O.PlanAmount <> 0);

   TYPE curcd_type IS TABLE OF curcd%ROWTYPE INDEX BY PLS_INTEGER;

   reccd         curcd_type;

   TYPE claimsplit_type IS TABLE OF w_claimsplit%ROWTYPE;

   claimsplit    claimsplit_type := claimsplit_type ();
   
   bulk_errors   EXCEPTION;
   
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
 
BEGIN

            v_CurrentInfo     := f_GetFiscalInfo('28-AUG-05');
            v_PendingLastDay  := v_CurrentInfo.LastDay;
            v_ThisFiscalMonth := v_CurrentInfo.FiscalMonth;
            v_FiscalDate      := '01-AUG-05';

   EXECUTE IMMEDIATE 'TRUNCATE TABLE w_ClaimSplit';

   OPEN curcd;

   LOOP                                                       ----loop 1st
         FETCH curcd
         BULK COLLECT INTO reccd LIMIT 100000;                    --> It depends

   EXIT  WHEN reccd.COUNT = 0;

        FOR i IN 1 .. reccd.LAST
        LOOP    
		 
		    v_SeqNumb := 0;
		 
        	v_SvcFiscalMonth := recCD(i).FromDateFiscalMonth;
            v_FromFiscalInfo := f_GetFiscalInfo(recCD(i).FromDate);
            v_ToFiscalInfo   := f_GetFiscalInfo(recCD(i).ToDate);
		    
			
		IF  v_ToFiscalInfo.LastDay IS NULL THEN
		    v_ToFiscalInfo.LastDay := v_PendingLastDay + 360;
        END IF;                                             -----loop 2nd


            
		IF  v_ToFiscalInfo.LastDay > v_PendingLastDay THEN
	     	v_ToFiscalInfo.FiscalMonth := 'PEND';
        END IF;

			
		IF  v_FromFiscalInfo.LastDay > v_PendingLastDay OR
            v_FromFiscalInfo.FiscalMonth = 'PEND'       OR
            recCD(i).TotalServiceDays <= 0            THEN
            	      
			v_ProvAmount            := recCD(i).ProvAmount;
            v_PlanAmount            := recCD(i).PlanAmount;
			v_SvcFiscalMonth        := 'PEND';                   --lk 10/04/2004
            v_SeqNumb               := v_SeqNumb + 1;

			reccd(i).claimdetailhistseqnumb := v_seqnumb;		 

			claimsplit.EXTEND;
		
		 claimsplit (claimsplit.LAST).claimid                  := reccd (i).claimid;
         claimsplit (claimsplit.LAST).claimlinenumb            := reccd (i).claimlinenumb;
         claimsplit (claimsplit.LAST).claimdetailhistseqnumb   := reccd(i).claimdetailhistseqnumb;
         claimsplit (claimsplit.LAST).claimdetailhistrectypecd := reccd (i).claimdetailhistrectypecd;
         claimsplit (claimsplit.LAST).operationcentercode      := reccd (i).regionalnetworkcenter;
         claimsplit (claimsplit.LAST).feeforservice            := reccd (i).feeforservice;
         claimsplit (claimsplit.LAST).servicecatid             := reccd (i).servicecatid;
         claimsplit (claimsplit.LAST).svcfiscalmonth           := v_svcfiscalmonth;
         claimsplit (claimsplit.LAST).paymentfiscalmonth       := reccd (i).paymentfiscalmonth;
         claimsplit (claimsplit.LAST).provamount               := reccd (i).provamount;
         claimsplit (claimsplit.LAST).carid                    := reccd (i).carid;
         claimsplit (claimsplit.LAST).provtypecd               := reccd (i).provtype;
         claimsplit (claimsplit.LAST).planamount               := reccd (i).planamount;
			
			v_FromFiscalInfo.FiscalMonth := 'PEND';
	
	   ELSE
	
                  v_AmtUsed     := 0;
         	      v_PlanAmtUsed := 0;    --LJK 06/27/2005  Added to initialize new field.
                  v_Date        := recCD(i).FromDate;
       
	    WHILE v_FromFiscalInfo.LastDay <= 
		   NVL(v_ToFiscalInfo.LastDay,(v_CurrentInfo.LastDay + 1))
        LOOP
                v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth;
       
	       IF v_FromFiscalInfo.LastDay > v_PendingLastDay THEN  --LK 10/04/2004
       
	          v_ProvAmount     := recCD(i).ProvAmount - v_AmtUsed; -- Into the Pending month
       	      v_PlanAmount     := recCD(i).PlanAmount - v_PlanAmtUsed;  --LJK 08/23/2005.  Set the PlanAmount not the Used Amount.
       
	          v_FromFiscalInfo.FiscalMonth := 'PEND';
       
	          v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth;
       
	       ELSIF v_FromFiscalInfo.LastDay = v_ToFiscalInfo.LastDay THEN
       
	          v_ProvAmount     := recCD(i).ProvAmount - v_AmtUsed;
              v_PlanAmount     := recCD(i).PlanAmount - v_PlanAmtUsed;
              v_SvcFiscalMonth := v_FromFiscalInfo.FiscalMonth; --v_ToFiscalMonth;
       
	          v_FromFiscalInfo.FiscalMonth := 'PEND';
              v_FromFiscalInfo.LastDay     := v_ToFiscalInfo.LastDay + 1;
           ELSE
              v_FMPart         := ((v_FromFiscalInfo.LastDay - v_Date) + 1) / recCD(i).TotalServiceDays;
              v_ProvAmount     := Round(recCD(i).ProvAmount * v_FMPart,2);
              v_PlanAmount     := Round(recCD(i).PlanAmount * v_FMPart,2);
              v_AmtUsed        := v_AmtUsed + v_ProvAmount;
              v_PlanAmtUsed    := v_PlanAmtUsed + v_PlanAmount;
              v_FromFiscalInfo := f_GetFiscalInfo(v_FromFiscalInfo.LastDay + 1);
              v_Date           := v_FromFiscalInfo.FirstDay;
           END IF;
            
		      v_SeqNumb := v_SeqNumb + 1;
	 		
		 ClaimSplit.EXTEND;
        
		 ClaimSplit(ClaimSplit.LAST).ClaimID                  := recCD(i).ClaimId;
         ClaimSplit(ClaimSplit.LAST).ClaimLineNumb            := recCD(i).ClaimLineNumb;
         ClaimSplit(ClaimSplit.LAST).ClaimDetailHistSeqNumb   := v_seqnumb;
         ClaimSplit(ClaimSplit.LAST).ClaimDetailHistRecTypeCd := recCD(i).ClaimDetailHistRecTypeCd;
         ClaimSplit(ClaimSplit.LAST).OperationCenterCode      := recCD(i).RegionalNetworkCenter;
         ClaimSplit(ClaimSplit.LAST).FeeForService            := recCD(i).FeeForService;
         ClaimSplit(ClaimSplit.LAST).ServiceCatId             := recCD(i).ServiceCatId;
         ClaimSplit(ClaimSplit.LAST).SvcFiscalMonth           := v_SvcFiscalMonth;
         ClaimSplit(ClaimSplit.LAST).PaymentFiscalMonth       := recCD(i).PaymentFiscalMonth;
         ClaimSplit(ClaimSplit.LAST).ProvAmount               := v_ProvAmount;
         ClaimSplit(ClaimSplit.LAST).CarId                    := recCD(i).CarId;
         ClaimSplit(ClaimSplit.LAST).ProvTypeCD               := recCD(i).ProvType;
         ClaimSplit(ClaimSplit.LAST).PlanAmount               := v_PlanAmount;

       IF v_svcfiscalmonth = 'PEND' THEN
         EXIT;
       END IF; 
       
	END LOOP;
       
	   END IF;

    END LOOP;
		  
         BEGIN
            FORALL i IN 1 .. claimsplit.COUNT SAVE EXCEPTIONS
               INSERT INTO w_claimsplit
                    VALUES claimsplit (i);
         COMMIT;
		 
		 EXCEPTION

            WHEN bulk_errors
            THEN

               FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
               LOOP
                  DBMS_OUTPUT.put_line
                            (   'Error from element #'
                             || TO_CHAR (SQL%BULK_EXCEPTIONS (j).ERROR_INDEX)
                             || ': '
                             || SQLERRM (SQL%BULK_EXCEPTIONS (j).ERROR_CODE)
                            );
               END LOOP;
         END;

         claimsplit.DELETE;

		 
 EXIT WHEN curcd%NOTFOUND;

 END LOOP;

 COMMIT;
 
 CLOSE curCD;
 
EXCEPTION
      WHEN OTHERS THEN
      error_number  := SQLCODE;
      error_message := substr(SQLERRM, 1, 200);
      dbms_output.put_line('error: ' || error_number || ' ' || error_message);
      DBMS_OUTPUT.PUT_LINE(v_ClaimID || ' ' || v_ClaimLineNumb);
END p_claimsplit;



Re: FUNCTION-BASE INDEX for user defined function [message #188512 is a reply to message #188057] Fri, 18 August 2006 23:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
It looks like your function is only expected to return one row, so you don't need a pipelined function at all. The following is a partial example of how I would simplify the whole thing. I didn't include all of the logic from your procedure, just enough to give you the general idea of where to put the sub-queries.

CREATE SEQUENCE SeqNumb
/
CREATE OR REPLACE TRIGGER w_claimsplit_bir
  BEFORE INSERT ON w_claimsplit
  FOR EACH ROW
BEGIN
  SELECT SeqNumb.NEXTVAL
  INTO   :NEW.ClaimDetailHistSeqNumb
  FROM   DUAL;
END w_claimpslit_bir;
/
CREATE OR REPLACE PROCEDURE p_ClaimSplit 
    (v_LastDay IN DATE)  
AS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE w_ClaimSplit';
  INSERT INTO w_claimsplit (
         ClaimID                     
        ,ClaimLineNumb              
        ,ClaimDetailHistRecTypeCd   
        ,OperationCenterCode        
        ,FeeForService              
        ,ServiceCatId               
        ,SvcFiscalMonth             
        ,PaymentFiscalMonth         
        ,ProvAmount                 
        ,CarId                      
        ,PlanAmount
         )                
  SELECT ClaimID                     
        ,ClaimLineNumb              
        ,NVL (o.claimdetailhistrectypecd, 'A') 
        ,RegionalNetworkCenter       
        ,DECODE (o.accountingdepartmentrptcd
                 ,'1', '1'
                 ,'2', '2'
                 ,'3', '3'
                 ,'4', '4'
                 ,'5', '3'
                 ,'6', '3'
                 ,'7', '3'
                 ,'9', '9'
                 ,'1')             
        ,ServiceCatId               
        ,t.FiscalMonth  
        ,PaymentFiscalMonth         
        ,CASE WHEN (SELECT EndDate
                    FROM   tblFiscalMonth
                    WHERE  BeginDate <= o.FromDate
                    AND    EndDate   >= o.FromDate) 
                   > t.EndDate
              THEN o.ProvAmount
              WHEN (SELECT EndDate
                    FROM   tblFiscalMonth
                    WHERE  BeginDate <= o.FromDate
                    AND    EndDate   >= o.FromDate) = 
                   (SELECT EndDate
                    FROM   tblFiscalMonth
                    WHERE  BeginDate <= o.ToDate
                    AND    EndDate   >= o.ToDate) 
              THEN o.ProvAmount 
              WHEN ...
              THEN ...
         END  
        ,CarId                      
        ...                
  FROM   tblclaimdetmonth_s_part o  
        ,(SELECT BeginDate, Enddate, FiscalMonth
          FROM   tblFiscalMonth
          WHERE  BeginDate <= v_LastDay
          AND    EndDate   >= v_LastDay) t
  WHERE  o.FiscalDate = t.BeginDate 
  AND    (O.ProvAmount <> 0 OR O.PlanAmount <> 0);
  COMMIT; 
END p_ClaimSplit;
/




Re: FUNCTION-BASE INDEX for user defined function [message #189695 is a reply to message #188512] Fri, 25 August 2006 11:58 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hey Barabara,

Thanks a lot for your excellent reply.

I never thought this way could be done.

Actully i just was following old code and tried
to make changes on that only which made me real mad without thinking other approache.

But i have a question if you don't mind to reply :
performance wise , will this faster than the old code?
because there are 21,000,000 records to be inserted.

and could you please help me to figured out how to write other logics as well,
as you have already showed me one of these ( sorry i am not asking you to actully write code for me)
but i need little more push to make this code success.

I will be obliged.


Thanking you again for your help.


Code and tables are as attached.
  • Attachment: orafaq.txt
    (Size: 33.17KB, Downloaded 160 times)
Re: FUNCTION-BASE INDEX for user defined function [message #189771 is a reply to message #189695] Sun, 27 August 2006 01:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
You need to post an attempt of your own. Just take the code that I provided and continue adding to it. You need to figure out the logic for the rest of it. If you have a specific problem, then post your attempt and what part you can't figure out. I am not going to just write the whole thing for you. Maybe someone else with more free time would like to so. I am rather busy lately.
Re: FUNCTION-BASE INDEX for user defined function [message #189789 is a reply to message #189771] Sun, 27 August 2006 09:41 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
I made my own effort too.
but i am not getting desired results.

It is giving me missing expression error.


 CREATE OR REPLACE PROCEDURE p_ClaimSplit
     (v_LastDay IN DATE)
 AS
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE w_ClaimSplit';
   INSERT INTO w_claimsplit (
          ClaimID
         ,ClaimLineNumb
         ,ClaimDetailHistRecTypeCd
         ,OperationCenterCode
         ,FeeForService
         ,ServiceCatId
         ,SvcFiscalMonth
         ,PaymentFiscalMonth
         ,ProvAmount
         ,CarId
         ,PlanAmount
          )
   SELECT /*+ RULE */ ClaimID
         ,ClaimLineNumb
         ,NVL (o.claimdetailhistrectypecd, 'A')
         ,RegionalNetworkCenter
         ,DECODE (o.accountingdepartmentrptcd
                  ,'1', '1'
                  ,'2', '2'
                  ,'3', '3'
                  ,'4', '4'
                  ,'5', '3'
                  ,'6', '3'
                  ,'7', '3'
                  ,'9', '9'
                  ,'1')
         ,ServiceCatId
         ,CASE 
                WHEN  (SELECT /*+ RULE */EndDate
                     FROM   tblFiscalMonth
                     WHERE  BeginDate <= o.FromDate
                     AND    EndDate   >= o.FromDate)
                    > t.EndDate 
                THEN t.FiscalMonth = 'PEND'
                WHEN (SELECT /*+ RULE */EndDate
                     FROM   tblFiscalMonth
                     WHERE  BeginDate <= o.toDate
                     AND    EndDate   >= o.toDate)
                    > t.EndDate  or (o.todate - o.fromdate + 1) <= 0
                THEN t.FiscalMonth = 'PEND'
                
                WHEN (SELECT /*+ RULE */ EndDate
		                     FROM   tblFiscalMonth
		                     WHERE  BeginDate <= o.FromDate
		                     AND    EndDate   >= o.FromDate) <= 
		                    NVL (SELECT EndDate
				         FROM   tblFiscalMonth
				         WHERE  BeginDate <= o.ToDate
		                         AND    EndDate   >= o.ToDate), 
		                     (SELECT EndDate + 1 
		                         FROM   tblFiscalMonth
		                         WHERE  BeginDate <= v_lastday
		                         AND    EndDate   >= v_lastday)
               THEN t.fiscalmonth
           
            ,PaymentFiscalMonth
            ,CASE WHEN (SELECT /*+ RULE */EndDate
                     FROM   tblFiscalMonth
                     WHERE  BeginDate <= o.FromDate
                     AND    EndDate   >= o.FromDate)
                    > t.EndDate
               THEN o.ProvAmount
                WHEN (SELECT /*+ RULE */ EndDate
   		                     FROM   tblFiscalMonth
   		                     WHERE  BeginDate <= o.FromDate
   		                     AND    EndDate   >= o.FromDate) <= 
   		
                    NVL (SELECT EndDate
   				         FROM   tblFiscalMonth
   				         WHERE  BeginDate <= o.ToDate
   		                         AND    EndDate   >= o.ToDate), 
   		                     (SELECT EndDate + 1 
   		                         FROM   tblFiscalMonth
   		                         WHERE  BeginDate <= v_lastday
   		                         AND    EndDate   >= v_lastday)
   
               THEN o.ProvAmount
               WHEN (SELECT /*+ RULE */ EndDate
   		     FROM   tblFiscalMonth
   		     WHERE  BeginDate <= o.FromDate
   		     AND    EndDate   >= o.FromDate) = 
   		     (SELECT EndDate
   	              FROM   tblFiscalMonth
   	             WHERE  BeginDate <= o.ToDate
   	             AND    EndDate   >= o.ToDate)
      	THEN o.ProvAmount
          END
         ,CarId
         ,CASE WHEN (SELECT /*+ RULE */EndDate
                     FROM   tblFiscalMonth
                     WHERE  BeginDate <= o.FromDate
                     AND    EndDate   >= o.FromDate)
                    > t.EndDate
               THEN o.planamount
                WHEN (SELECT /*+ RULE */ EndDate
		                     FROM   tblFiscalMonth
		                     WHERE  BeginDate <= o.FromDate
		                     AND    EndDate   >= o.FromDate) <= 
		                    NVL (SELECT EndDate
				         FROM   tblFiscalMonth
				         WHERE  BeginDate <= o.ToDate
		                         AND    EndDate   >= o.ToDate), 
		                     (SELECT EndDate + 1 
		                         FROM   tblFiscalMonth
		                         WHERE  BeginDate <= v_lastday
		                         AND    EndDate   >= v_lastday)
           THEN o.planamount
           WHEN (SELECT /*+ RULE */ EndDate
   		     FROM   tblFiscalMonth
   		     WHERE  BeginDate <= o.FromDate
   		     AND    EndDate   >= o.FromDate) = 
   		     (SELECT EndDate
   	              FROM   tblFiscalMonth
   	             WHERE  BeginDate <= o.ToDate
   	             AND    EndDate   >= o.ToDate)
           THEN o.planamount
          END
 
 FROM   tblclaimdetmonth_s_part o
         ,(SELECT BeginDate, Enddate, FiscalMonth
           FROM   tblFiscalMonth
           WHERE  BeginDate <= v_LastDay
           AND    EndDate   >= v_LastDay) t
   WHERE  o.FiscalDate = t.BeginDate
   AND    (O.ProvAmount <> 0 OR O.PlanAmount <> 0);
   COMMIT;
 END p_ClaimSplit;


Re: FUNCTION-BASE INDEX for user defined function [message #190698 is a reply to message #189789] Thu, 31 August 2006 18:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
There are numerous errors in your create table and insert statements and procedure. Your create table statements have not null and commas in the wrong position and the columns are in a different order than inserted. You obviously didn't test it. The procedure attempts to select columns that don't exist in the posted table structure. You need to create and test your procedure one section at a time, adding one column to be inserted between tests, so that you know what is causing the error, then debug it and move on to the next column. You might want to try just creating and testing the select statement by itself, then put it in the procedure.
Re: FUNCTION-BASE INDEX for user defined function [message #191690 is a reply to message #190698] Thu, 07 September 2006 14:44 Go to previous message
Akshar
Messages: 116
Registered: May 2006
Senior Member

Hi Barabara,

I have solved the problem.

Your help is appreciated.

Thank you,
Previous Topic: Aggregate function
Next Topic: insert new rows / update existing columns
Goto Forum:
  


Current Time: Sat Dec 03 10:23:05 CST 2016

Total time taken to generate the page: 0.08121 seconds