Home » SQL & PL/SQL » SQL & PL/SQL » query tuning (Oracle 8i)
query tuning [message #410642] Mon, 29 June 2009 07:58 Go to next message
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 #410647 is a reply to message #410642] Mon, 29 June 2009 08:45 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
You didn't give the explain plan of the sql.
Anyway, instead inserting many times, why can't you use forall?
Don't say that, I can't get the value of cbcnt, rownum is always there. Use it in the forall.

By
Vamsi
Re: query tuning [message #410686 is a reply to message #410642] Mon, 29 June 2009 12:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.

Previous Topic: Update Query Help require
Next Topic: Maximum size for columns with Varchar
Goto Forum:
  


Current Time: Fri Dec 06 01:06:04 CST 2024