Home » SQL & PL/SQL » SQL & PL/SQL » where the Select statement output will be stored
where the Select statement output will be stored [message #394798] Mon, 30 March 2009 06:37 Go to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi All,

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


i hav a doubt regrdng the execution of a select statemnt.

When we issue a Select statement from SQL*Plus or any other tool ..where actually is stores the data ?..( i mean any buffer it will use) ?..how to find the limit of this ?..because it's possible that ..select statement output may return the data which is more in size than the Buffer..

Regards,
Ravi
Re: where the Select statement output will be stored [message #394804 is a reply to message #394798] Mon, 30 March 2009 06:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The output isn't stored - the rows are only fetched as they are asked for.
When you run a query in SQL*Plus and it displays all the rows, all that is happening is that SQL*Plus requests some rows, displays them, and then requests some more until no more are returned.

It is possible to run out of space for a query if you use the ORDER BY command,as this requires the rows to all be fetched in advance in order to sort them, but as this will page out to disk to get the sort done, you need a very large query to hit problems.
Re: where the Select statement output will be stored [message #394806 is a reply to message #394804] Mon, 30 March 2009 06:52 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Thnks JRowbottom.

I was expecting the answer in case of order by only.
so you mean "there wont be any TEMP buffer or somethng where the output data will be stored and SORT operation will take place later" ?..
Re: where the Select statement output will be stored [message #394813 is a reply to message #394806] Mon, 30 March 2009 07:02 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As I said in thesecond paragraph of my reply, it is possible to run out of space for a query with an Order by clause.
Previous Topic: Convert update sql into merge
Next Topic: Optimization CASE STATEMENT (2 threads merged by bb)
Goto Forum:
  


Current Time: Wed Dec 07 16:47:11 CST 2016

Total time taken to generate the page: 0.08496 seconds