USING MULTIPLE CURSORS

[Vandy02]

This was originally a view, but based on huge tables and a very long runtime I created these cursors which increase speed from 4 minutes to 7 seconds. Everything runs well. I need to return the values of the last cursor. I am able to use a PACKAGE / PACKAGE BODY as a REF CURSOR with one cursor. However, I am unable to do so with multiple cursors as in the c_maxdate. Can anyone provide me some insight?
I did think about place the values of the c_maxdate into a temp table then returning those values, but I do not know how to do this by running all three cursors....

===============================================
DECLARE
---------------------------------------
-----------GETS EQNUM's BASED ON EQNUM OF RIDESTATUS
---------------------------------------
---------------------------------------
--This works and take 7 seconds to run in production - JMH20040630
--
--
CURSOR c_ridestatus IS
SELECT e.eqnum as eqnum
FROM EQUIPMENT e
--, PARENT, children, LEVEL, description
WHERE LEVEL > 1
--START WITH eqnum = '116082'
START WITH eqnum IN (SELECT s.eqnum FROM maxmeter.WDW_RIDESTATUS s)
CONNECT BY PRIOR eqnum = PARENT;
--
--
CURSOR c_jpnum(i_ridestatus IN equipment.eqnum%type) IS
Select distinct nvl(w.jpnum, p.jpnum) as jpnum, w.pmnum as pmnum, w.eqnum as eqnum
From workorder w, pm p, pmsequence ps, eqhistory h
Where w.pmnum = p.pmnum and
w.eqnum = i_ridestatus and
w.status in ('INPRG', 'WSCH', 'WAPPR', 'WMATL', 'APPR') and
w.wolo3 = 'L' and
w.woeq4 >= 0 and
w.eqnum = h.eqnum and
w.jpnum = ps.jpnum;
--
--
--CREATED INDEX WORKORDER_NDX_JOHN(PMNUM, STATUS)
CURSOR c_maxdate(i_eqnum IN workorder.eqnum%type, p_pmnum IN workorder.pmnum%type) IS
SELECT h.wonum as wonum, h.eqnum as eqnum
FROM eqhistory h, workorder w, pm p
WHERE
p.METERFREQUENCY > 0 and
p.PM3 = 'L' AND
w.pmnum = p.pmnum and
w.eqnum = i_eqnum and
w.STATUS IN ('COMP', 'CLOSE') AND
h.wonum = w.wonum and
w.pmnum = p_pmnum;
---------------------------------------
---------------------------------------
---------------------------------------
---------------------------------------
BEGIN
--
dbms_output.enable(1000000);
--
FOR r_ridestatus IN c_ridestatus
LOOP
--DBMS_OUTPUT.PUT_LINE(r_ridestatus.eqnum);
--
--
FOR r_jpnum IN c_jpnum(r_ridestatus.eqnum)
LOOP
--DBMS_OUTPUT.PUT_LINE(r_jpnum.jpnum||' '||r_jpnum.pmnum||' '||r_jpnum.eqnum);
--
FOR r_maxdate IN c_maxdate(r_jpnum.eqnum, r_jpnum.pmnum)
--
--FOR r_maxdate IN c_maxdate(r_jpnum.pmnum)
LOOP
--DBMS_OUTPUT.PUT_LINE('TEST');
DBMS_OUTPUT.PUT_LINE(r_maxdate.wonum);
END LOOP; --c_maxdate
END LOOP; --c_jpnum
--
--
END LOOP; --c_ridestatus
---------------------------------------
---------------------------------------
END;

================

THANKS

 

[sem]

Read about pipelined functions. BTW, by adding hints you may reproduce your current "execution plan" (nested loop) in single query, so you may come back to the original view.

Regards, Dima

http://seminihin.narod.ru

 

[Vandy02]

I will look into this for sure.
As for the interim, until I can use the pipeline, is there a way I can insert the values from r_maxdate into a table? I created a temp table in oracle so I am all set, minus the how to...I have tried everything I know of..

Vandy

 

[Vandy02]

I decided to do the following and it works for the interim:

DECLARE
---------------------------------------
-----------GETS EQNUM's BASED ON EQNUM OF RIDESTATUS
---------------------------------------
---------------------------------------
--This works and take 7 seconds to run in production - JMH20040630
--
--
CURSOR c_ridestatus IS
SELECT e.eqnum as eqnum
FROM EQUIPMENT e
--, PARENT, children, LEVEL, description
WHERE LEVEL > 1
--START WITH eqnum = '116082'
START WITH eqnum IN (SELECT s.eqnum FROM maxmeter.WDW_RIDESTATUS s)
CONNECT BY PRIOR eqnum = PARENT;
--
--
CURSOR c_jpnum(i_ridestatus IN equipment.eqnum%type) IS
Select distinct nvl(w.jpnum, p.jpnum) as jpnum, w.pmnum as pmnum, w.eqnum as eqnum
From workorder w, pm p, pmsequence ps, eqhistory h
Where w.pmnum = p.pmnum and
w.eqnum = i_ridestatus and
w.status in ('INPRG', 'WSCH', 'WAPPR', 'WMATL', 'APPR') and
w.wolo3 = 'L' and
w.woeq4 >= 0 and
w.eqnum = h.eqnum and
w.jpnum = ps.jpnum;
--
--
--CREATED INDEX WORKORDER_NDX_JOHN(PMNUM, STATUS)
CURSOR c_maxdate(i_eqnum IN workorder.eqnum%type, p_pmnum IN workorder.pmnum%type) IS
SELECT
--
h.eqnum as heqnum,
h.meterreading as hmeterreading,
e.parent as eqparent,
e.description eqdesc,
e.meterreading eqmtrreading,
h.wonum as hwonum,
NVL(e.meterreading,0) + NVL(maxmeter.Get_Parent_Lapcount(e.eqnum),0) lastchildmeterreading,
ROUND(e.avgmeterunit,0) avgmeterunit,
(SELECT wonum FROM esmaximo.WORKORDER w
WHERE w.eqnum = e.eqnum AND status IN ('INPRG','WSCH','WAPPR','WMATL','APPR') AND wolo3 = 'L' AND w.pmnum = p.pmnum AND woeq4 > 0) nextwonum,
((h.meterreading + p.meterfrequency) - (e.meterreading + NVL(maxmeter.Get_Parent_Lapcount(e.eqnum),0))) childmetercountdown,
TRUNC(SYSDATE + (((h.meterreading + p.meterfrequency) - (e.meterreading + NVL(maxmeter.Get_Parent_Lapcount(e.eqnum),0))) / e.avgmeterunit)) childeranwodate,
p.meterfrequency * ps.INTERVAL nextpminterval
--
--
--
FROM eqhistory h, workorder w, pm p, equipment e, pmsequence ps
WHERE
p.pmnum = ps.pmnum and
p.METERFREQUENCY > 0 and
p.PM3 = 'L' AND
w.pmnum = p.pmnum and
w.eqnum = i_eqnum and
w.STATUS IN ('COMP', 'CLOSE') AND
w.eqnum = e.eqnum and
h.wonum = w.wonum and
w.pmnum = p_pmnum;
--
--
--CURSOR c_eqnum_parent(
--i_wonum IN workorder
---------------------------------------
---------------------------------------
---------------------------------------
---------------------------------------
BEGIN
--
dbms_output.enable(1000000);
--
DBMS_OUTPUT.PUT_LINE('THE RESULTANT LIST OF WONUMS ARE AS FOLLOWS:');
FOR r_ridestatus IN c_ridestatus
LOOP
--DBMS_OUTPUT.PUT_LINE(r_ridestatus.eqnum);
--
--
FOR r_jpnum IN c_jpnum(r_ridestatus.eqnum)
LOOP
--DBMS_OUTPUT.PUT_LINE(r_jpnum.jpnum||' '||r_jpnum.pmnum||' '||r_jpnum.eqnum);
--
FOR r_maxdate IN c_maxdate(r_jpnum.eqnum, r_jpnum.pmnum)
--
--FOR r_maxdate IN c_maxdate(r_jpnum.pmnum)
LOOP
--DBMS_OUTPUT.PUT_LINE('TEST');
/* 255 character limit per line error
--
DBMS_OUTPUT.PUT_LINE(
'EQNUM: '||r_maxdate.heqnum
||' METERREADING: '||r_maxdate.hmeterreading
||' EQPARENT: '||r_maxdate.eqparent
||' EQDESC: '||r_maxdate.eqdesc
||' EQMTRREADING: '||r_maxdate.eqmtrreading
||' WONUM: '||r_maxdate.hwonum
||' LASTCHILDMETERREADING: '||r_maxdate.lastchildmeterreading
||' AVGMETERUNIT: '||r_maxdate.avgmeterunit
||' NEXTWONUM: '||r_maxdate.nextwonum
||' CHILDMETERCOUNTDOWN: '||r_maxdate.childmetercountdown
||' CHILDERANWODATE: '||r_maxdate.childeranwodate
||' NEXTPMINTERVAL:' ||r_maxdate.nextpminterval
);
*/
DBMS_OUTPUT.PUT_LINE(r_maxdate.hwonum);
--
--
/*
CREATE TABLE CHILDLIGHTBOARD_TEMP_JOHN
(
EQNUM VARCHAR2(10 BYTE),
PARENT VARCHAR2(10 BYTE),
DESCRIPTION VARCHAR2(150 BYTE),
METERREADING NCHAR(15),
WONUM VARCHAR2(10 BYTE),
LASTCHILDMETERREADING NUMBER,
AVGMETERUNIT NUMBER,
NEXTWONUM VARCHAR2(10 BYTE),
CHILDMETERCOUNTDOWN NUMBER,
CHILDERANWODATE DATE,
NEXTPMINTERVAL NUMBER
);
*/
--
--
INSERT INTO CHILDLIGHTBOARD_TEMP_JOHN VALUES(
r_maxdate.heqnum,
r_maxdate.eqparent,
r_maxdate.eqdesc,
r_maxdate.eqmtrreading,
r_maxdate.hwonum,
r_maxdate.lastchildmeterreading,
r_maxdate.avgmeterunit,
r_maxdate.hwonum,
r_maxdate.childmetercountdown,
r_maxdate.childeranwodate,
r_maxdate.nextpminterval);
--
COMMIT;
--
--
END LOOP;
END LOOP;
--
--
END LOOP; --c_ridestatus
---------------------------------------
---------------------------------------
END;

 

[jaggiebunnet]

That looks extremely nasty, I would imagine that will grind to a halt eventually...not to mention having a commit inside the pl/sql, but even worse having it inside the loop - ouch.