Home » SQL & PL/SQL » SQL & PL/SQL » collection iterator pickler fetch
collection iterator pickler fetch [message #330040] Fri, 27 June 2008 07:31 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

In the below query, I have used a split function

 SELECT
    NVL(SUM(PSAPC.ITEM_QUAN),0)
  INTO
    vItemCount
  FROM
    POB_SHIP_ADDRESS_PROD_CATALOG PSAPC,
    POB_VENDOR_CLAIM_DTL PVCD
  WHERE
    PSAPC.POB_SHIP_ADDRESS_ID IN (SELECT * FROM TABLE(split(psPobShipAddressID))) AND
    PSAPC.POB_PROD_CATALOG_ID = PVCD.POB_PROD_CATALOG_ID AND
    PVCD.POB_VENDOR_CLAIM_ID = piPobVendorClaimID;


which is as follows

CREATE OR REPLACE FUNCTION SPLIT
(
    p_list VARCHAR2,
    p_del VARCHAR2 := ','
) return split_tbl pipelined
is
    l_idx    PLS_INTEGER;
    l_list    VARCHAR2(32767) := p_list;
BEGIN
    LOOP
        l_idx := INSTR(l_list,p_del);
        IF l_idx > 0 THEN
            pipe row(SUBSTR(l_list,1,l_idx-1));
            l_list := SUBSTR(l_list,l_idx+length(p_del));
        ELSE
            pipe row(l_list);
            EXIT;
        END IF;
    END LOOP;
    RETURN;
END SPLIT;


which accepts arguments as 'ARG1,ARG2,ARG3,ARG4' format.

The explain plan showed that

SELECT STATEMENT, GOAL = ALL_ROWS			Cost=42	Cardinality=1	Bytes=27
 SORT AGGREGATE				Cardinality=1	Bytes=27
  HASH JOIN SEMI			Cost=42	Cardinality=7	Bytes=189
   TABLE ACCESS BY INDEX ROWID	Object owner=MASYS	Object name=POB_SHIP_ADDRESS_PROD_CATALOG	Cost=3	Cardinality=16	Bytes=224
    NESTED LOOPS			Cost=17	Cardinality=117	Bytes=2925
     INDEX RANGE SCAN	Object owner=MASYS	Object name=UK_PVCD_PVCIDPPCID_INDX	Cost=2	Cardinality=7	Bytes=77
     INDEX RANGE SCAN	Object owner=MASYS	Object name=PSAPC_PPCID_IDX	Cost=2	Cardinality=16	
   COLLECTION ITERATOR PICKLER FETCH		Object name=SPLIT


as a result of this split collection iterator pickler fetch happens and the cost increases.

Could you please give me some more information on the above and advise me on how to avoid the same?

Thanks,

Sharath
Re: collection iterator pickler fetch [message #330041 is a reply to message #330040] Fri, 27 June 2008 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If psPobShipAddressID is a constant don't use a function rewrite it in the query.

Regards
Michel
Re: collection iterator pickler fetch [message #330203 is a reply to message #330041] Fri, 27 June 2008 22:28 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Michel,

Thank you very much for the reply.

psPobShipAddressID is a string of concatenated numbers sent in from the code.

It varies depending on the number of shippers the user selects on the UI.

So, I cannot do that.

Sharath
Re: collection iterator pickler fetch [message #330217 is a reply to message #330203] Sat, 28 June 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you can.
(When I said "constant" I meant a local variable containing a literal not a column of the table.)

For instance:
http://www.orafaq.com/forum/m/328430/102589/#msg_328430

Regards
Michel
Re: collection iterator pickler fetch [message #330484 is a reply to message #330217] Mon, 30 June 2008 04:59 Go to previous message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Michel,

Performance has improved a lot!!

Thank you very much!!!
Previous Topic: Problem:Update target table (merged)
Next Topic: Statements within variable,
Goto Forum:
  


Current Time: Fri Dec 09 13:52:46 CST 2016

Total time taken to generate the page: 0.10671 seconds