Home » SQL & PL/SQL » SQL & PL/SQL » improve performance (oracle9i)
improve performance [message #426531] Fri, 16 October 2009 06:45 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Create or replace procedure p1
is
cursor C is
select empno,ename from emp order by ename;
begin
for i in c
loop
dbms_output.put_line(i.empno||i.ename);
end loop;
end;
/

In this code the select statement returns 10 million records
what we have to use to improve the performance of code and also display the same result.

Re: improve performance [message #426532 is a reply to message #426531] Fri, 16 October 2009 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select empno||ename from emp order by ename;

Regards
Michel

Re: improve performance [message #426533 is a reply to message #426531] Fri, 16 October 2009 06:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No-one is going to look at 10,000,000 records.

What is the actual problem that you're trying to solve?
Re: improve performance [message #426537 is a reply to message #426533] Fri, 16 October 2009 07:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
JRowbottom wrote on Fri, 16 October 2009 13:57
No-one is going to look at 10,000,000 records.

What is the actual problem that you're trying to solve?

Besides, it will raise an error when the dbms_output buffer is filled up. Won't take too long..
Since this would raise another error (not performance related), I expect you never actually ran this beast.
So I concur with JRowbottom: what is it you really want to do?

[Updated on: Fri, 16 October 2009 07:26]

Report message to a moderator

Re: improve performance [message #426563 is a reply to message #426537] Fri, 16 October 2009 11:48 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I agree that dbms_output to dump data is probably the wrong approach. Plain sql and utl_file may be better depending on requirements.

10g R2 increases line limit to 32k and buffer size optionally to unlimited.

http://www.oracle-developer.net/display.php?id=327

Re: improve performance [message #426566 is a reply to message #426563] Fri, 16 October 2009 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why all these thoughts when a single SQL statement does the trick?

Regards
Michel

[Updated on: Fri, 16 October 2009 12:04]

Report message to a moderator

Re: improve performance [message #426573 is a reply to message #426566] Fri, 16 October 2009 12:43 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
becuase maybe OP wants to dump data to a flat file or whatever on the DB server, not the client machine or maybe sqlplus isn't an option... As usual, more info is required.
Re: improve performance [message #426688 is a reply to message #426563] Mon, 19 October 2009 00:38 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
andrew again wrote on Fri, 16 October 2009 18:48

10g R2 increases line limit to 32k and buffer size optionally to unlimited.

http://www.oracle-developer.net/display.php?id=327


But OP is on "oracle9i"
Previous Topic: job name in DBMS_SCHEDULER.CREATE_JOB
Next Topic: GroupBy
Goto Forum:
  


Current Time: Wed Sep 28 12:33:19 CDT 2016

Total time taken to generate the page: 0.20350 seconds