query tuning [message #410642] |
Mon, 29 June 2009 07:58 |
rs1969
Messages: 24 Registered: December 2007 Location: UK
|
Junior Member |
|
|
Can the following PL/SQL be tuned to make it more efficient? I understand that a single INSERT statement is better than a FOR loop.
Thanks
BEGIN
cbcnt := 1;
FOR x IN (SELECT DISTINCT customer_id
FROM HSS_DEL_TBL_BILL)
LOOP
INSERT INTO IVR_TEMP
(SELECT cbcnt, customer_id, LEVEL
FROM customer_all
WHERE paymntresp IS NULL AND cstype = 'a'
CONNECT BY PRIOR customer_id = customer_id_high
START WITH customer_id = x.customer_id
UNION
SELECT cbcnt, x.customer_id, 1
FROM DUAL);
cbcnt := cbcnt + 1;
COMMIT;
END LOOP;
END;
|
|
|
|
Re: query tuning [message #410686 is a reply to message #410642] |
Mon, 29 June 2009 12:04 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
rs1969 wrote on Mon, 29 June 2009 07:58 | Can the following PL/SQL be tuned to make it more efficient?
|
Yes, particularly in 8i.
|
|
|
Re: query tuning [message #410717 is a reply to message #410686] |
Mon, 29 June 2009 22:15 |
rs1969
Messages: 24 Registered: December 2007 Location: UK
|
Junior Member |
|
|
smartin wrote on Mon, 29 June 2009 12:04 | rs1969 wrote on Mon, 29 June 2009 07:58 | Can the following PL/SQL be tuned to make it more efficient?
|
Yes, particularly in 8i.
|
Please suggest a way to convert into one UPDATE statement by getting rid of the FOR LOOP.
Many thanks.
|
|
|
Re: query tuning [message #410813 is a reply to message #410642] |
Tue, 30 June 2009 07:03 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
MYDBA@orcl > create table test(a varchar2(10));
Table created.
MYDBA@orcl > insert into test select dummy from dual;
1 row created.
MYDBA@orcl > select * from test;
A
----------
X
1 row selected.
|
|
|