13 Replies Latest reply on Mar 18, 2011 5:03 AM by ganex27lin

    PipeLine Function Taking time to return Table record

    ganex27lin
      Hi,

      I want to use a function in join clause. so i go for pipelined function(using for loop to get record & 1 more loop to fetch in table type variable). i achieved what i required. but problem is it takes much time to fetch data. is there any other approach which returns table records without pipelined function.

      please suggest me a better approach as soon as possible.
        • 1. Re: PipeLine Function Taking time to return Table record
          Billy~Verreynne
          A square peg does not fit a round hold.

          Pipeline table functions are and should be an exception. With sound technical justification as to why it is needed. And equally sound and robust justification for joining SQL data against a PL/SQL pipe line - across SQL and PL/SQL engine boundaries.

          Never mind explaining why data resides in PL/SQL structures (consuming very expensive dedicated server memory) in the first place.

          Of course it will by its very nature be slower and not as fast or scalable as using native SQL data only.

          What are your justifications are reasons for:
          a) storing data in PL/SQL "+table records+" ?
          b) attempting to join that using a pipeline with SQL data sets?

          We need to understand the issues and problems in order to diagnose a problem and suggest a solution.
          • 2. Re: PipeLine Function Taking time to return Table record
            Hoek
            Without any example, database version, knowing how many rows are returned, it's hard to say anything useful.
            It would also be nice to know what client invokes your function, is it JAVA or...?


            However, instead of a pipelined function, have you considered returning a REF CURSOR?
            See:
            http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:19481671347143#1562629900346809174
            PL/SQL 101 : Understanding Ref Cursors
            http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php

            Only you can decide if that is a better approach, since you have not given all information we need.
            • 3. Re: PipeLine Function Taking time to return Table record
              6363
              ganex27lin wrote:

              I want to use a function in join clause.

              please suggest me a better approach as soon as possible.
              Don't use a user defined function in the where clause or join it will be slow.

              Don't attempt to mark your question as requiring immediate attention, it is rude and a violation of the forum terms and conditions.

              http://www.oracle.com/html/terms.html

              >
              4. Use of Community Services
              Community Services are provided as a convenience to users and Oracle is not obligated to provide any technical support for, or participate in, Community Services. While Community Services may include information regarding Oracle products and services, including information from Oracle employees, they are not an official customer support channel for Oracle.

              You may use Community Services subject to the following: (a) Community Services may be used solely for your personal, informational, noncommercial purposes; (b) Content provided on or through Community Services may not be redistributed; and (c) personal data about other users may not be stored or collected except where expressly authorized by Oracle
              >

              If you require attention as soon as possible please log a service request with support or hire a qualified consultant.

              http://www.google.com/search?q=oracle+consultant
              • 4. Re: PipeLine Function Taking time to return Table record
                William Robertson
                This part could be a problem:
                using for loop to get record & 1 more loop to fetch in table type variable
                Within the pipelined function it should return (pipe) rows as it fetches them. If you have two separate loops you might be fetching all of the data into memory first, and then going through it a second time to return the results. This will be very inefficient and rather defeats the purpose of pipelining.
                • 5. Re: PipeLine Function Taking time to return Table record
                  ganex27lin
                  Hi,

                  Thanks all for the quick response.

                  I am using oracle 10g

                  this is the table details & the entire function.



                  Create object & table type to use in the function
                  CREATE OR REPLACE TYPE SYSADM.STR_TYPE AS OBJECT(COLUMNVALUE VARCHAR2(8),FromNo Int,ToNo Int)
                  /
                  CREATE OR REPLACE TYPE SYSADM.STR_ARRAY AS TABLE OF STR_TYPE
                  /

                  table involved
                  View : VesselType
                  column : Code varchar2(5)

                  Table : T065_SHIP
                  column : SKIPS_KODE varchar2(4)
                  CODE_SHIPTYPE varchar2(5)

                  Table : SelsKap
                  column : SELSKAPSKODE varchar2(4)

                  Table : t041_shiptypeusers
                  column : Code_ShipType varchar2(5)
                  USERID varchar2(8)

                  View : PositionBook
                  column : VesselCode varchar2(8)
                  VoyageNo integer
                  Company varchar2(4)

                  Table : T62_BRUKER_SELSKAP
                  column : SELSKAPSKODE varchar2(4)
                  BRUKER varchar2(8)

                  Pipelined function
                  CREATE OR REPLACE FUNCTION SYSADM.TF_ShiporShipTypeByUser
                  (
                  In_UserName IN VARCHAR2,
                  In_Type IN VARCHAR2,
                  In_VesselCode IN VARCHAR2,
                  In_CompanyHistory IN NUMBER DEFAULT 0
                  )
                  RETURN str_array PIPELINED AS
                  l_ShipTypeUser NUMBER(10,0);
                  l_CompanyUser NUMBER(10,0);
                  l_UseCompanyHistory NUMBER(1,0);
                  l_Snicsacct NUMBER(1,0);
                  BEGIN
                  
                  BEGIN
                  SELECT shiptype_user,
                  company_user
                  INTO l_ShipTypeUser,
                  l_CompanyUser
                  FROM User_Segregation;
                  EXCEPTION
                  WHEN OTHERS THEN
                  l_ShipTypeUser := NULL;
                  l_CompanyUser := NULL;
                  END;
                  
                  BEGIN
                  SELECT 1 INTO l_Snicsacct
                  FROM DUAL
                  WHERE NOT EXISTS ( SELECT 1 FROM sn_user_cfg WHERE UserID='SNICS' and CfgID='ACCTSYS');
                  EXCEPTION
                  WHEN OTHERS THEN
                  NULL;
                  END;
                  
                  IF In_CompanyHistory = 1
                  THEN
                  BEGIN
                  SELECT CfgData
                  INTO l_UseCompanyHistory
                  FROM SN_User_Cfg
                  WHERE CfgID = 'USE COMPANY HISTORY'
                  AND UserID = 'SNICS';
                  EXCEPTION
                  WHEN OTHERS THEN
                  l_UseCompanyHistory := 0;
                  END;
                  END IF;
                  
                  
                  
                  IF In_UserName = 'SYSADM' OR (l_CompanyUser = 0 AND l_ShipTypeUser = 0)
                  OR (l_CompanyUser = 1 and l_Snicsacct =1 ) or (In_CompanyHistory = 0 and l_CompanyUser = 1 and In_Type ='ShipType')
                  OR (l_ShipTypeUser = 1 and In_Type = 'Company')
                  THEN
                  BEGIN
                  IF In_Type = 'ShipType'
                  THEN
                  BEGIN
                  FOR cur IN (SELECT Code ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM VesselType )
                  LOOP
                  PIPE ROW(str_type(cur.ShipCode,cur.VoyageFrom,cur.VoyageTo));
                  END LOOP;
                  
                  RETURN;
                  END;
                  ELSIF In_Type = 'Ship'
                  THEN
                  BEGIN
                  FOR cur IN (SELECT SKIPS_KODE ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM T065_SHIP
                  WHERE SKIPS_KODE = NVL(In_VesselCode,SKIPS_KODE ))
                  LOOP
                  PIPE ROW(str_type(cur.ShipCode,cur.VoyageFrom,cur.VoyageTo));
                  END LOOP;
                  
                  RETURN;
                  END;
                  ELSIF In_Type = 'Company'
                  THEN
                  BEGIN
                  FOR cur IN (SELECT SELSKAPSKODE ShipCode
                  , NULL VoyageFrom
                  , NULL VoyageTo
                  FROM SelsKap)
                  LOOP
                  PIPE ROW(str_type(cur.ShipCode,cur.VoyageFrom,cur.VoyageTo));
                  END LOOP;
                  
                  RETURN;
                  END;
                  END IF;
                  END;
                  ELSE
                  IF In_Type = 'Ship'
                  THEN
                  BEGIN
                  IF l_ShipTypeUser =1
                  THEN
                  BEGIN
                  FOR cur IN (SELECT S.SKIPS_KODE ShipCode
                  , 1 FromVoyage
                  , 999999999 ToVoyage
                  FROM T065_SHIP S
                  JOIN t041_shiptypeusers U
                  ON S.CODE_SHIPTYPE = U.Code_ShipType
                  AND USERID = In_UserName
                  WHERE S.SKIPS_KODE = NVL(In_VesselCode, S.SKIPS_KODE)
                  GROUP BY S.SKIPS_KODE)
                  LOOP
                  PIPE ROW(str_type(cur.ShipCode,cur.FromVoyage,cur.ToVoyage));
                  END LOOP;
                  RETURN;
                  END;
                  ELSIF l_CompanyUser = 1
                  THEN
                  BEGIN
                  IF l_UseCompanyHistory = 1 AND In_CompanyHistory = 1
                  THEN
                  FOR cur IN (SELECT a.VesselCode ShipCode
                  , a.VoyageNo VoyageFrom
                  , a.VoyageNo VoyageTo
                  FROM PositionBook a
                  JOIN T62_BRUKER_SELSKAP b
                  ON a.Company = b.SELSKAPSKODE
                  AND b.BRUKER = In_UserName
                  WHERE a.VesselCode = NVL(In_VesselCode, a.VesselCode)
                  UNION
                  SELECT a.VesselCode ShipCode
                  , a.VoyageNo VoyageFrom
                  , a.VoyageNo VoyageTo
                  FROM PositionBook a
                  LEFT JOIN T62_BRUKER_SELSKAP b
                  ON a.company = b.SELSKAPSKODE
                  WHERE a.VesselCode = NVL(In_VesselCode, a.VesselCode)
                  AND b.SELSKAPSKODE IS NULL
                  GROUP BY a.VesselCode,
                  a.VoyageNo,
                  a.VoyageNo)
                  LOOP
                  PIPE ROW(str_type(cur.ShipCode,cur.VoyageFrom,cur.VoyageTo));
                  END LOOP;
                  
                  RETURN;
                  ELSE
                  FOR cur IN (SELECT a.SKIPS_KODE ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM T065_SHIP a
                  JOIN T62_BRUKER_SELSKAP b
                  ON a.Company = b.SELSKAPSKODE
                  AND b.BRUKER = In_UserName
                  WHERE a.SKIPS_KODE = NVL(In_VesselCode, a.SKIPS_KODE)
                  AND NVL(l_UseCompanyHistory,0) = 0
                  UNION
                  SELECT a.SKIPS_KODE ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM T065_SHIP a
                  LEFT JOIN T62_BRUKER_SELSKAP b
                  ON a.company = b.SELSKAPSKODE
                  WHERE a.SKIPS_KODE = NVL(In_VesselCode, a.SKIPS_KODE)
                  AND b.SELSKAPSKODE IS NULL
                  GROUP BY a.SKIPS_KODE)
                  LOOP
                  PIPE ROW(str_type(cur.ShipCode,cur.VoyageFrom,cur.VoyageTo));
                  END LOOP;
                  
                  RETURN;
                  END IF;
                  END;
                  END IF;
                  END;
                  ELSIF In_Type = 'ShipType'
                  THEN
                  IF l_ShipTypeUser = 1
                  THEN
                  BEGIN
                  FOR cur IN (SELECT S.CODE_SHIPTYPE ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM T065_SHIP S
                  JOIN t041_shiptypeusers U
                  ON S.CODE_SHIPTYPE = U.Code_ShipType
                  AND USERID = In_UserName
                  WHERE S.SKIPS_KODE = NVL(In_VesselCode, S.SKIPS_KODE)
                  GROUP BY S.CODE_SHIPTYPE)
                  LOOP
                  PIPE ROW(str_type(cur.ShipCode,cur.VoyageFrom,cur.VoyageTo));
                  END LOOP;
                  
                  RETURN;
                  END;
                  ELSIF l_CompanyUser = 1
                  THEN
                  IF l_UseCompanyHistory = 1 AND In_CompanyHistory = 1
                  THEN
                  BEGIN
                  FOR cur IN (SELECT a.VesselType ShipCode
                  , a.VoyageNo VoyageFrom
                  , a.VoyageNo VoyageTo
                  FROM PositionBook a
                  JOIN T62_BRUKER_SELSKAP b
                  ON a.COMPANY = b.SELSKAPSKODE
                  AND b.BRUKER = In_UserName
                  WHERE a.VesselCode = NVL(In_VesselCode,a.VesselCode)
                  UNION
                  SELECT a.VesselType ShipCode
                  , a.VoyageNo VoyageFrom
                  , a.VoyageNo VoyageTo
                  FROM PositionBook a
                  LEFT JOIN T62_BRUKER_SELSKAP b
                  ON a.company = b.SELSKAPSKODE
                  WHERE a.VesselCode = NVL(In_VesselCode, a.VesselCode)
                  AND b.SELSKAPSKODE IS NULL
                  GROUP BY a.VesselType,
                  a.VoyageNo,
                  a.VoyageNo)
                  LOOP
                  PIPE ROW(str_type(cur.ShipCode,cur.VoyageFrom,cur.VoyageTo));
                  END LOOP;
                  
                  RETURN;
                  END;
                  ELSE
                  BEGIN
                  FOR cur IN (SELECT S.CODE_SHIPTYPE ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM T065_SHIP S
                  JOIN T62_BRUKER_SELSKAP b
                  ON S.company = b.SELSKAPSKODE
                  AND B.BRUKER = In_UserName
                  WHERE S.SKIPS_KODE = NVL(In_VesselCode, S.SKIPS_KODE)
                  AND NVL(l_UseCompanyHistory,0) = 0
                  UNION
                  SELECT S.CODE_SHIPTYPE ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM T065_SHIP S
                  LEFT JOIN T62_BRUKER_SELSKAP b
                  ON S.company = b.SELSKAPSKODE
                  WHERE S.SKIPS_KODE = NVL(In_VesselCode, S.SKIPS_KODE)
                  AND b.SELSKAPSKODE IS NULL)
                  LOOP
                  PIPE ROW(str_type(cur.ShipCode,cur.VoyageFrom,cur.VoyageTo));
                  END LOOP;
                  
                  RETURN;
                  END;
                  END IF;
                  
                  END IF;
                  ELSIF In_Type = 'Company'
                  THEN
                  BEGIN
                  FOR cur IN (SELECT a.SELSKAPSKODE ShipCode
                  , NULL VoyageFrom
                  , NULL VoyageTo
                  FROM Selskap a
                  JOIN T62_BRUKER_SELSKAP b
                  ON a.SELSKAPSKODE = b.SELSKAPSKODE
                  WHERE b.BRUKER = In_UserName
                  Union All
                  Select a.SELSKAPSKODE ShipCode
                  , NULL VoyageFrom
                  , NULL VoyageTo
                  From Selskap a
                  LEFT JOIN T62_BRUKER_SELSKAP b
                  ON a.SELSKAPSKODE = b.SELSKAPSKODE
                  Where b.SELSKAPSKODE IS NULL
                  GROUP BY a.SELSKAPSKODE)
                  LOOP
                  PIPE ROW(str_type(cur.ShipCode,cur.VoyageFrom,cur.VoyageTo));
                  END LOOP;
                  
                  RETURN;
                  END;
                  END IF;
                  
                  END IF;
                  END;
                  /
                  select statement which i call the function it takes minimum 6 seconds to execute. It gives 8339 records
                  WITH deftbl AS ( SELECT /*+ CACHE(deftbl) */
                  /*+ result_cache */
                  a.VesselCode,
                  a.VoyageNo,
                  CASE a.BallastLeg When 0
                  THEN MIN(a.ArrivalDate)
                  ELSE NVL(( SELECT MAX(DepartureDate)
                  FROM PositionBook b
                  WHERE b.VesselCode = a.VesselCode
                  AND b.VoyageNo = (Select MAX(VoyageNo) From PositionBook c Where c.VesselCode = a.VesselCode and c.VoyageNo<a.VoyageNo)
                  ),MIN(a.ArrivalDate))
                  END AS StartOfVoyage,
                  MAX(DepartureDate) EndOfVoyage
                  FROM PositionBook a
                  JOIN TABLE(*TF_ShiporShipTypeByUser*('BKA', 'Ship', NULL, 1)) D        /*Calling the function*/
                  ON D.ColumnValue = a.VesselCode
                  AND a.VoyageNo BETWEEN D.FromVoyageNo AND D.ToVoyageNo
                  GROUP BY VesselCode,VoyageNo,a.BallastLeg )
                  SELECT /*+ result_cache */
                  ROW_NUMBER() OVER(PARTITION BY a.VesselCode ORDER BY a.VoyageNo DESC) as "Row"
                  ,a.VesselCode
                  ,v.Name AS VesselName
                  ,a.VoyageNo
                  ,c.StartOfVoyage AS FromPort
                  ,c.EndOfVoyage AS ToPort
                  /*,a.FROMPORT
                  ,a.TOPORT*/
                  , CASE
                  WHEN ( SELECT PortName
                  FROM PositionBook b
                  WHERE b.VesselCode = a.VesselCode
                  AND b.VoyageNo = a.VoyageNo
                  AND SYSDATE BETWEEN ArrivalDate AND DepartureDate ) IS NOT NULL
                  THEN ( SELECT FIRST_VALUE(PortName) OVER(PARTITION BY b.vesselcode,b.VoyageNo ORDER BY b.vesselcode,b.VoyageNo,b.ArrivalDate,b.secondarytime)
                  FROM PositionBook b
                  WHERE b.VesselCode = a.VesselCode
                  AND b.VoyageNo = a.VoyageNo
                  AND SYSDATE BETWEEN ArrivalDate AND DepartureDate
                  AND ROWNUM=1
                  )
                  WHEN SYSDATE BETWEEN c.StartOfVoyage AND c.EndOfVoyage
                  THEN ( SELECT 'Steam From '||FIRST_VALUE(UPPER(PortName)) OVER(PARTITION BY b.vesselcode,b.VoyageNo ORDER BY b.vesselcode,b.VoyageNo,b.ArrivalDate desc,b.secondarytime desc)
                  FROM POSITIONBOOK b
                  WHERE b.VesselCode = a.VesselCode AND b.VoyageNo = a.VoyageNo
                  AND b.ArrivalDate<SYSDATE
                  AND ROWNUM=1
                  )
                  WHEN (c.StartOfVoyage-SYSDATE)>0
                  THEN 'Voyage Not Started'
                  WHEN (c.EndOfVoyage-SYSDATE)<0
                  THEN 'Voyage Completed'
                  ELSE Null
                  END "Location"
                  ,( SELECT MIN(b.PortName) KEEP(DENSE_RANK FIRST ORDER BY b.ArrivalDate,b.secondarytime) OVER(PARTITION BY b.VesselCode,b.VoyageNo)
                  FROM POSITIONBOOK b
                  WHERE b.VesselCode = a.VesselCode AND b.VoyageNo = a.VoyageNo
                  AND b.ArrivalDate>SYSDATE
                  AND ROWNUM=1
                  ) AS NextPort
                  , ( SELECT MIN(b.ArrivalDate) KEEP(DENSE_RANK FIRST ORDER BY b.ArrivalDate,b.secondarytime) OVER(PARTITION BY b.VesselCode,b.VoyageNo)
                  FROM POSITIONBOOK b
                  where b.VesselCode = a.VesselCode AND b.VoyageNo = a.VoyageNo
                  AND b.ArrivalDate>SYSDATE
                  AND ROWNUM=1
                  ) AS NextETA
                  ,a.STATUS VoyageStatus
                  ,a.Trade
                  ,UFN_MyVoyConcatinate_Values('FIXNOTE', a.VesselCode, a.VoyageNo) FixNote
                  ,UFN_MyVoyConcatinate_Values('CHARTERER', a.VesselCode, a.VoyageNo) Charterer
                  ,CASE WHEN st.CurrentStatus=0
                  THEN 1
                  WHEN st.CurrentStatus=1
                  THEN 0
                  ELSE st.CurrentStatus
                  END AS CurrentStatus
                  FROM Positionbook a
                  JOIN deftbl c
                  ON a.VesselCode = c.VesselCode
                  AND a.VoyageNo = c.VoyageNo
                  JOIN Vessel v
                  ON v.Code = c.VesselCode
                  LEFT JOIN GTT_VOYAGESTATUS st
                  ON st.VesselCode = c.Vesselcode
                  AND st.VoyageNo = c.VoyageNo
                  GROUP BY a.VesselCode
                  , v.Name
                  , a.VoyageNo
                  , c.StartOfVoyage
                  , c.EndOfVoyage
                  /*, a.FROMPORT
                  , a.TOPORT*/
                  , a.Status
                  , a.Trade
                  , st.CurrentStatus;
                  Note:


                  i changed the function without pipelined also. but it doesnt show much difference

                  non pipelined function
                  CREATE OR REPLACE FUNCTION TF_ShiporShipTypeByUser_nonp
                  (
                  In_UserName IN VARCHAR2,
                  In_Type IN VARCHAR2,
                  In_VesselCode IN VARCHAR2,
                  In_CompanyHistory IN NUMBER DEFAULT 0
                  )
                  RETURN str_array AS
                  
                  l_ShipTypeUser NUMBER(10,0);
                  l_CompanyUser NUMBER(10,0);
                  l_UseCompanyHistory NUMBER(1,0);
                  l_Snicsacct NUMBER(1,0);
                  l_str_array str_array:=str_array();
                  BEGIN
                  
                  BEGIN
                  SELECT shiptype_user,
                  company_user
                  INTO l_ShipTypeUser,
                  l_CompanyUser
                  FROM User_Segregation;
                  EXCEPTION
                  WHEN OTHERS THEN
                  l_ShipTypeUser := NULL;
                  l_CompanyUser := NULL;
                  END;
                  
                  BEGIN
                  SELECT 1 INTO l_Snicsacct
                  FROM DUAL
                  WHERE NOT EXISTS ( SELECT 1 FROM sn_user_cfg WHERE UserID='SNICS' and CfgID='ACCTSYS');
                  EXCEPTION
                  WHEN OTHERS THEN
                  NULL;
                  END;
                  
                  IF In_CompanyHistory = 1
                  THEN
                  BEGIN
                  SELECT CfgData
                  INTO l_UseCompanyHistory
                  FROM SN_User_Cfg
                  WHERE CfgID = 'USE COMPANY HISTORY'
                  AND UserID = 'SNICS';
                  EXCEPTION
                  WHEN OTHERS THEN
                  l_UseCompanyHistory := 0;
                  END;
                  END IF;
                  
                  
                  
                  IF In_UserName = 'SYSADM' OR (l_CompanyUser = 0 AND l_ShipTypeUser = 0)
                  OR (l_CompanyUser = 1 and l_Snicsacct =1 ) or (In_CompanyHistory = 0 and l_CompanyUser = 1 and In_Type ='ShipType')
                  OR (l_ShipTypeUser = 1 and In_Type = 'Company')
                  THEN
                  BEGIN
                  IF In_Type = 'ShipType'
                  THEN
                  BEGIN
                  SELECT STR_TYPE(Code,1,999999999)
                  BULK COLLECT INTO l_str_array
                  FROM VesselType;
                  RETURN l_str_array;
                  END;
                  ELSIF In_Type = 'Ship'
                  THEN
                  BEGIN
                  SELECT STR_TYPE(SKIPS_KODE,1,999999999)
                  BULK COLLECT INTO l_str_array
                  FROM T065_SHIP
                  WHERE SKIPS_KODE = NVL(In_VesselCode,SKIPS_KODE);
                  RETURN l_str_array;
                  END;
                  ELSIF In_Type = 'Company'
                  THEN
                  BEGIN
                  SELECT STR_TYPE(SELSKAPSKODE,NULL,NULL)
                  BULK COLLECT INTO l_str_array
                  FROM SelsKap;
                  RETURN l_str_array;
                  END;
                  END IF;
                  END;
                  ELSE
                  IF In_Type = 'Ship'
                  THEN
                  BEGIN
                  IF l_ShipTypeUser =1
                  THEN
                  BEGIN
                  SELECT STR_TYPE(S.SKIPS_KODE,1,999999999)
                  BULK COLLECT INTO l_str_array
                  FROM T065_SHIP S
                  JOIN t041_shiptypeusers U
                  ON S.CODE_SHIPTYPE = U.Code_ShipType
                  AND USERID = In_UserName
                  WHERE S.SKIPS_KODE = NVL(In_VesselCode, S.SKIPS_KODE)
                  GROUP BY S.SKIPS_KODE;
                  RETURN l_str_array;
                  END;
                  ELSIF l_CompanyUser = 1
                  THEN
                  BEGIN
                  IF l_UseCompanyHistory = 1 AND In_CompanyHistory = 1
                  THEN
                  SELECT STR_TYPE(ShipCode,VoyageFrom,VoyageTo)
                  BULK COLLECT INTO l_str_array
                  FROM ( SELECT a.VesselCode ShipCode
                  , a.VoyageNo VoyageFrom
                  , a.VoyageNo VoyageTo
                  FROM PositionBook a
                  JOIN T62_BRUKER_SELSKAP b
                  ON a.Company = b.SELSKAPSKODE
                  AND b.BRUKER = In_UserName
                  WHERE a.VesselCode = NVL(In_VesselCode, a.VesselCode)
                  UNION
                  SELECT a.VesselCode ShipCode
                  , a.VoyageNo VoyageFrom
                  , a.VoyageNo VoyageTo
                  FROM PositionBook a
                  LEFT JOIN T62_BRUKER_SELSKAP b
                  ON a.company = b.SELSKAPSKODE
                  WHERE a.VesselCode = NVL(In_VesselCode, a.VesselCode)
                  AND b.SELSKAPSKODE IS NULL
                  GROUP BY a.VesselCode,
                  a.VoyageNo,
                  a.VoyageNo);
                  RETURN l_str_array;
                  ELSE
                  SELECT STR_TYPE(ShipCode,VoyageFrom,VoyageTo)
                  BULK COLLECT INTO l_str_array
                  FROM (SELECT a.SKIPS_KODE ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM T065_SHIP a
                  JOIN T62_BRUKER_SELSKAP b
                  ON a.Company = b.SELSKAPSKODE
                  AND b.BRUKER = In_UserName
                  WHERE a.SKIPS_KODE = NVL(In_VesselCode, a.SKIPS_KODE)
                  AND NVL(l_UseCompanyHistory,0) = 0
                  UNION
                  SELECT a.SKIPS_KODE ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM T065_SHIP a
                  LEFT JOIN T62_BRUKER_SELSKAP b
                  ON a.company = b.SELSKAPSKODE
                  WHERE a.SKIPS_KODE = NVL(In_VesselCode, a.SKIPS_KODE)
                  AND b.SELSKAPSKODE IS NULL
                  GROUP BY a.SKIPS_KODE);
                  
                  RETURN l_str_array;
                  END IF;
                  END;
                  END IF;
                  END;
                  ELSIF In_Type = 'ShipType'
                  THEN
                  IF l_ShipTypeUser = 1
                  THEN
                  BEGIN
                  SELECT STR_TYPE(ShipCode,VoyageFrom,VoyageTo)
                  BULK COLLECT INTO l_str_array
                  FROM (SELECT S.CODE_SHIPTYPE ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM T065_SHIP S
                  JOIN t041_shiptypeusers U
                  ON S.CODE_SHIPTYPE = U.Code_ShipType
                  AND USERID = In_UserName
                  WHERE S.SKIPS_KODE = NVL(In_VesselCode, S.SKIPS_KODE)
                  GROUP BY S.CODE_SHIPTYPE);
                  
                  RETURN l_str_array;
                  END;
                  ELSIF l_CompanyUser = 1
                  THEN
                  IF l_UseCompanyHistory = 1 AND In_CompanyHistory = 1
                  THEN
                  BEGIN
                  SELECT STR_TYPE(ShipCode,VoyageFrom,VoyageTo)
                  BULK COLLECT INTO l_str_array
                  FROM ( SELECT a.VesselType ShipCode
                  , a.VoyageNo VoyageFrom
                  , a.VoyageNo VoyageTo
                  FROM PositionBook a
                  JOIN T62_BRUKER_SELSKAP b
                  ON a.COMPANY = b.SELSKAPSKODE
                  AND b.BRUKER = In_UserName
                  WHERE a.VesselCode = NVL(In_VesselCode,a.VesselCode)
                  UNION
                  SELECT a.VesselType ShipCode
                  , a.VoyageNo VoyageFrom
                  , a.VoyageNo VoyageTo
                  FROM PositionBook a
                  LEFT JOIN T62_BRUKER_SELSKAP b
                  ON a.company = b.SELSKAPSKODE
                  WHERE a.VesselCode = NVL(In_VesselCode, a.VesselCode)
                  AND b.SELSKAPSKODE IS NULL
                  GROUP BY a.VesselType,
                  a.VoyageNo,
                  a.VoyageNo);
                  
                  
                  RETURN l_str_array;
                  END;
                  ELSE
                  BEGIN
                  SELECT STR_TYPE(ShipCode,VoyageFrom,VoyageTo)
                  BULK COLLECT INTO l_str_array
                  FROM ( SELECT S.CODE_SHIPTYPE ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM T065_SHIP S
                  JOIN T62_BRUKER_SELSKAP b
                  ON S.company = b.SELSKAPSKODE
                  AND B.BRUKER = In_UserName
                  WHERE S.SKIPS_KODE = NVL(In_VesselCode, S.SKIPS_KODE)
                  AND NVL(l_UseCompanyHistory,0) = 0
                  UNION
                  SELECT S.CODE_SHIPTYPE ShipCode
                  , 1 VoyageFrom
                  , 999999999 VoyageTo
                  FROM T065_SHIP S
                  LEFT JOIN T62_BRUKER_SELSKAP b
                  ON S.company = b.SELSKAPSKODE
                  WHERE S.SKIPS_KODE = NVL(In_VesselCode, S.SKIPS_KODE)
                  AND b.SELSKAPSKODE IS NULL);
                  
                  RETURN l_str_array;
                  END;
                  END IF;
                  
                  END IF;
                  ELSIF In_Type = 'Company'
                  THEN
                  BEGIN
                  SELECT STR_TYPE(ShipCode,VoyageFrom,VoyageTo)
                  BULK COLLECT INTO l_str_array
                  FROM ( SELECT a.SELSKAPSKODE ShipCode
                  , NULL VoyageFrom
                  , NULL VoyageTo
                  FROM Selskap a
                  JOIN T62_BRUKER_SELSKAP b
                  ON a.SELSKAPSKODE = b.SELSKAPSKODE
                  WHERE b.BRUKER = In_UserName
                  Union All
                  Select a.SELSKAPSKODE ShipCode
                  , NULL VoyageFrom
                  , NULL VoyageTo
                  From Selskap a
                  LEFT JOIN T62_BRUKER_SELSKAP b
                  ON a.SELSKAPSKODE = b.SELSKAPSKODE
                  Where b.SELSKAPSKODE IS NULL
                  GROUP BY a.SELSKAPSKODE);
                  
                  RETURN l_str_array;
                  END;
                  END IF;
                  
                  END IF;
                  END;
                  please kindly provide me your valuable suggestions

                  Edited by: ganex27lin on Mar 16, 2011 1:54 AM
                  • 6. Re: PipeLine Function Taking time to return Table record
                    ganex27lin
                    Hi,

                    I am new to the forum and hence not able to align the code.
                    Please let me know how to modify the earlier reply code to view properly for you all.

                    For the function..

                    we are using front end as DOT Net and using ref_cursor for returning the set of records.
                    I am calling the function in the joins as a TABLE(function(input parameters) which is time consuming.

                    Kindly suggest a better solution as the performance of the query is very slow from the application as it is taking more than 2 minutes to load the page window.
                    • 7. Re: PipeLine Function Taking time to return Table record
                      William Robertson
                      You can use
                       tags to format code. See the <a href="http://forums.oracle.com/forums/help.jspa">FAQ</a> for more formatting tips.                                                                                                                                                                                                                                                                            
                      • 8. Re: PipeLine Function Taking time to return Table record
                        Billy~Verreynne
                        ganex27lin wrote:

                        please kindly provide me your valuable suggestions
                        How about proper data modeling?

                        The design and development of code can only ever be as good as the data model and structures that need to be used. And ugly large hammers (like your pipeline function) is a direct result of poor data model design and implementation.
                        • 9. Re: PipeLine Function Taking time to return Table record
                          ganex27lin
                          Hi billy,

                          As we were following the data modeling for the past 10 years we are not in the situation to change that. so please kindly please suggest me any other approaches to reduce the execution time.
                          • 10. Re: PipeLine Function Taking time to return Table record
                            ganex27lin
                            hi william,

                            i added tags for coding part as you mentioned.
                            thank u :)
                            • 11. Re: PipeLine Function Taking time to return Table record
                              Sven W.
                              Not sure where to start, there are so many potential problems in this code, it would take a week to clarify all that.
                              Best this would be to properly indent you codes to help yourself and other developers to read and understand it better.

                              The next this to do would be to trace and profile this procedure, to see where most of the time is spent and concentrate on those parts. See the FAQ about how to create a trace file (when your query takes too long). Look also in the documentation about dbms_profile.

                              But here is something that I 'm most suspect of.
                              In some of your queries you can't take advantage of an index because how use added the NVL function.

                              example sniplet
                              BEGIN
                              FOR cur IN (SELECT S.SKIPS_KODE ShipCode
                              , 1 FromVoyage
                              , 999999999 ToVoyage
                              FROM T065_SHIP S
                              JOIN t041_shiptypeusers U
                              ON S.CODE_SHIPTYPE = U.Code_ShipType
                              AND USERID = In_UserName
                              WHERE S.SKIPS_KODE = NVL(In_VesselCode, S.SKIPS_KODE)
                              GROUP BY S.SKIPS_KODE)
                              LOOP
                              PIPE ROW(str_type(cur.ShipCode,cur.FromVoyage,cur.ToVoyage));
                              END LOOP;
                              RETURN;
                              END;
                              A little better would be this. Any index on S.SKIPS_KODE could be used.
                              There are so many such places in this code that I would prefere a complete rewrite.

                              example improved
                              BEGIN
                                 if In_VesselCode is null then
                                    FOR cur IN (SELECT S.SKIPS_KODE ShipCode
                                           FROM T065_SHIP S
                                           JOIN t041_shiptypeusers U ON S.CODE_SHIPTYPE = U.Code_ShipType
                                           WHERE u.USERID = In_UserName
                                           GROUP BY S.SKIPS_KODE)
                                    LOOP
                                       PIPE ROW(str_type(cur.ShipCode,1,999999999 ));
                                    END LOOP;
                                 else
                                    /* if In_VesselCode is not null then only one line is returned. Therefore no need to loop */
                                   select S.SKIPS_KODE ShipCode
                                   into v_dummy
                                   FROM T065_SHIP S
                                   JOIN t041_shiptypeusers U ON S.CODE_SHIPTYPE = U.Code_ShipType
                                   WHERE u.USERID = In_UserName
                                   and In_VesselCode = S.SKIPS_KODE
                                   and rownum <= 1;
                              
                                   PIPE ROW(str_type(In_VesselCode,1,999999999 ));
                                 end if;
                                 RETURN;
                              exception
                                 when no_data_found then 
                                    /* could it happen that you enter a vessel code that is not in T065_SHIP for this user? */
                                    raise no_data_found;
                              END;
                              • 12. Re: PipeLine Function Taking time to return Table record
                                Billy~Verreynne
                                ganex27lin wrote:

                                As we were following the data modeling for the past 10 years we are not in the situation to change that. so please kindly please suggest me any other approaches to reduce the execution time.
                                You need to face some cold and hard facts.

                                What is a pipeline? How does it work in the SQL engine (as PL/SQL code)? What resources does it consume?

                                And that is the problem.

                                In order for the SQL engine to execute PL/SQL code, it needs to context switch to the PL/SQL engine when making PL/SQL calls. Context switching is an expensive overhead in any environment - from switching between CPU rings, to switching between user and kernel modes, to context switching between the SQL and PL/SQL engines.

                                What makes it worse is that your PL/SQL pipeline needs to use SQL cursors.. which means that it again needs to context switch to the SQL engine.

                                Sure, you can alleviate the context switching in the pipeline code to some extent using bulk processing - but this comes with a price. And the coinage is consuming expensive private process memory. Your pipeline simply throws all caution into the wind and bulk collect in single mass fetches. This can cause severe memory abuse and the server to crash. The prudent approach to bulk processing is to manage PGA consumption by using the LIMIT clause to limit a bulk fetch.

                                Okay, now the pipeline are also joined - the CBO has no fast I/O paths to the data set that is piped as output by the pipeline. This basically means full table scans.

                                I do not see any silver-lining to this cloud - it all seems pretty dark.

                                So what is the core problem? Is pipelines itself a problem and something that should not be used?

                                Not exactly - but something along those lines is relevant. Thus my comment about fitting a square peg into a round hold.

                                Pipelines is a tool for data transformation. It needs to be correctly used (and not as a hammer for driving in screws for example).

                                Pipelines can, for example, turn a web service (returning a SOAP envelope) into something that looks like a regular SQL table with columns - thus providing a standard (and pretty powerful) SQL interface into a non-SQL data service. It makes the interface significantly easier to use - ideal for abstracting the technical complexities of the data transformation process from those who simple need to use the data.

                                So when someone is using a pipeline on SQL data.. my immediate reaction is why? Why do you need to use a pipeline to transform existing SQL data structures into.. SQL data structures? That more than likely mean either not understanding the workings of a pipeline, or having a broken data model that requires dynamic transformation to make it usable.

                                I see your problem being the flawed thinking that PL/SQL pipelines will solve your problem - when that is not going to be the case as you are treating symptoms and not the root cause.
                                • 13. Re: PipeLine Function Taking time to return Table record
                                  ganex27lin
                                  Hi Billy,


                                  Thanks for your reply. I am a beginner I can't have the deep knowledge to catch your points. will you please provide me in details with example. please guide me.