Home » RDBMS Server » Performance Tuning » Passing record to a variable - Performance Issue (Oracle 10gR2 10.2.0.5 Server:AIX Client:Windows)
Passing record to a variable - Performance Issue [message #557725] Fri, 15 June 2012 03:03 Go to next message
Rektanocrit
Messages: 24
Registered: September 2011
Location: Philippines
Junior Member

I need to optimize a module(Oracle Forms 6i), then I found so many statement that goes like this:

DECLARE
   v_param   VARCHAR2 (10);
BEGIN
   FOR rec IN (SELECT col_name
                 FROM table_name)
   LOOP
      v_param := rec.col_name;
      EXIT;
   END LOOP;
END;

I was planning on changing/ simplifying it to something like this:

DECLARE
   v_param   VARCHAR2 (10);
BEGIN
   SELECT col_name
     INTO v_param
     FROM table_name;
END;


Is there any difference? Or I'll just leave it as is? Thanks!

{hr]
[Edit MC: add code tags]

[Updated on: Fri, 15 June 2012 03:35] by Moderator

Report message to a moderator

Re: Passing record to a variable - Performance Issue [message #557726 is a reply to message #557725] Fri, 15 June 2012 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any difference?


Yes, it is much better.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Passing record to a variable - Performance Issue [message #557727 is a reply to message #557726] Fri, 15 June 2012 03:17 Go to previous messageGo to next message
Rektanocrit
Messages: 24
Registered: September 2011
Location: Philippines
Junior Member

Thanks Sir Michel Cadot. I format my code, I just forgot to put [code] in it. I've read the guide on posting and make sure to follow it next time.

By the way, will it run much faster? I can't see the execution plan of both statement that's why I ask it here. Very Happy
Thanks again!
Re: Passing record to a variable - Performance Issue [message #557728 is a reply to message #557727] Fri, 15 June 2012 03:34 Go to previous message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The difference in the performances will not be noticeable but the maintenance of the code will.
It is clear you want to have and fetch only one value in the second code when one could ask why there is an EXIT in the LOOP in the first one.

Regards
Michel
Previous Topic: how to find the SQL statement which have many hard parse
Next Topic: combination index issue
Goto Forum:
  


Current Time: Tue Sep 21 01:23:27 CDT 2021