Home » SQL & PL/SQL » SQL & PL/SQL » Improve query using bulk collect into (Oracle 11.1 Toad on Windows server)
| Improve query using bulk collect into [message #598159] |
Thu, 10 October 2013 15:43  |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
Hi guys,
I am trying to improve a procedure which is looping through a query to make inserts.
Can someone tell me how make this more efficient ?
FOR P IN (
SELECT O.TYPEID
,o.KEY
,O.ID
,O.NAME
,O.LGNUM
,O.LGNAME
FROM cOD O
WHERE O.KEY <> -1
AND O.CURRFLAG = 1
AND (O.TYPEID = 'PS') --this is returning 3000+ rows
) LOOP
INSERT INTO C_OP ( TYPEID
,CKEY
,CID
,CNAME
,CLGNUM
,CLGNAME
,PKEY
,PID
,PNAME
,PLGNUM
,PLGNAME
,ISBOTTOMORG)
SELECT OP.TYPEID
,OP.KEY
,OP.ID
,OP.NAME
,OP.LGNUM
,OP.LGNAME
,OP.PKEY
,OP.PID
,OP.PNAME
,OP.PGNUM
,OP.PGNAME
,NVL2(Q.ORGKEY, 1, 0)
FROM
(SELECT P.TYPEID
,C.KEY
,C.ID
,C.NAME
,C.LGNUM
,C.LGNAME
,P.KEY AS PKEY
,P.ID AS PID
,P.NAME AS PGNAME
,P.LGNUM AS PGNUM
,P.LGNAME AS PGNAME
FROM COD C
START WITH C.ORGKEY = P.ORGKEY
CONNECT BY PRIOR C.KEY = C.CURRP
AND C.CURRENTFLAG = 1) OP
LEFT JOIN CLQ Q ON OP.KEY = Q.KEY;
end loop;
commit;
end;
Thanks for any help on this in advance.
[Updated on: Thu, 10 October 2013 15:43] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Improve query using bulk collect into [message #598186 is a reply to message #598164] |
Fri, 11 October 2013 03:21  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
BlackSwan wrote on Thu, 10 October 2013 23:16I give up.
Please explain why the top SELECT (SELECT .... FROM cOD O .....) exists, since it does not contribute anything to the code below it.r
You gave up too quickly then since the for loop variables are used in the insert/select, both in the select part and the where clause.
|
|
|
|
| Re: Improve query using bulk collect into [message #598188 is a reply to message #598159] |
Fri, 11 October 2013 03:49 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
jxh461 wrote on Fri, 11 October 2013 02:13
START WITH C.ORGKEY = P.ORGKEY
I fail to understand from where P.ORGKEY came from? Either it should be P.KEY or you need to keep ORGKEY in the select list of the FOR loop.
|
|
|
|
|
|
|
|
| Re: Improve query using bulk collect into [message #598795 is a reply to message #598793] |
Thu, 17 October 2013 15:23 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
jxh461 wrote on Fri, 18 October 2013 01:51I ended up using temp table instead and joined with the rest of the query. Slightly improved performance.
Please post what you did. It might help you to fix your issue even better, also, it will help others browsing this forum.
One more thing to add, you mentioned in the subject "Improve query using bulk collect into". But your code nowhere has any BULK COLLECT. Please post the relevant code so that someone would definitely help/assist you, and that, you need to supply enough information.
[Updated on: Thu, 06 March 2014 03:05] by Moderator Report message to a moderator |
|
|
|
| Re: Improve query using bulk collect into [message #598800 is a reply to message #598792] |
Thu, 17 October 2013 15:42 |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
I wanted to edit my post but I no longer have that option.
FOR P IN (
SELECT O.TYPEID
,o.KEY
,O.ID
,O.NAME
,O.LGNUM
,O.LGNAME
FROM cOD O
WHERE O.KEY <> -1
AND O.CURRFLAG = 1
AND (O.TYPEID = 'PS') --this is returning 3000+ rows
) LOOP
INSERT INTO C_OP ( TYPEID
,CKEY
,CID
,CNAME
,CLGNUM
,CLGNAME
,PKEY
,PID
,PNAME
,PLGNUM
,PLGNAME
,ISBOTTOMORG)
SELECT OP.TYPEID
,OP.KEY
,OP.ID
,OP.NAME
,OP.LGNUM
,OP.LGNAME
,OP.PKEY
,OP.PID
,OP.PNAME
,OP.PGNUM
,OP.PGNAME
,NVL2(Q.ORGKEY, 1, 0)
FROM
(SELECT P.TYPEID
,C.KEY
,C.ID
,C.NAME
,C.LGNUM
,C.LGNAME
,P.KEY AS PKEY
,P.ID AS PID
,P.NAME AS PGNAME
,P.LGNUM AS PGNUM
,P.LGNAME AS PGNAME
FROM COD C
START WITH C.KEY = P.KEY
CONNECT BY PRIOR C.KEY = C.CURRP
AND C.CURRENTFLAG = 1) OP
LEFT JOIN CLQ Q ON OP.KEY = Q.KEY;
end loop;
commit;
end;
I will be posting later on what my alternative is but still very interested in better solutions.
|
|
|
|
| Re: Improve query using bulk collect into [message #598808 is a reply to message #598800] |
Thu, 17 October 2013 16:12 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
1. So, this is a performance tuning question.
2. Michel already posted,
Michel Cadot wrote on Fri, 11 October 2013 10:56
Replace the loop by a single INSERT SELECT.
why to use PL/SQL when you can do it using plain SQL?
3. Did you attempt to find the bottleneck where exactly the code is consuming most of the time?
I insist, to tune a code, it needs more information than just posting a PART OF YOUR CODE. Go through the performance tuning forum sticky on the top, it will help you a lot.
Regards,
Lalit
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 14:23:17 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
