Home » SQL & PL/SQL » SQL & PL/SQL » Displaying N number of rows from a large result set (Oracle 10g/11g)
Displaying N number of rows from a large result set [message #571753] Thu, 29 November 2012 23:18 Go to next message
syednoman84
Messages: 1
Registered: November 2012
Location: Pakistan
Junior Member

I have a query that returns 11 Million rows but not all of them can be displayed in SQLDeveloper or DBVisualizer because of limited memory or other type of issues. I need to copy the entire result set to excel for further calculations.

Is there any way that i can select N number of rows out of my actual result set.

For example:
a) A result set contains 10 Million rows in total.
b) I want to display first 5 Million rows by executing a query
c) Then I want to display the remaining 5 Million rows by executing the query again with any parameter changes.

So all I want is to extract the rows of my actual result set in two or more executions, depending on the number of rows.

Thanks in advance for your help.
Re: Displaying N number of rows from a large result set [message #571757 is a reply to message #571753] Fri, 30 November 2012 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59791
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL FAQ, see points 10, 11 and 21.

Regards
Michel

[Updated on: Fri, 30 November 2012 01:11]

Report message to a moderator

Re: Displaying N number of rows from a large result set [message #571761 is a reply to message #571753] Fri, 30 November 2012 02:36 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
syednoman84 wrote on Fri, 30 November 2012 06:18
... A result set contains 10 Million rows in total... I need to copy the entire result set to excel for further calculations ...


As far as I know since Excel 2007, you're limited to almost 1,000,000 rows. So even if you split the result set into several separated parts, as I understand (according to what you explained in the OP) the final purpose is to assemble the whole result set in a single Excel file. With the max limit of 1,000,000 rows I think you will be in trouble unless you use Excel 2013 with PowerPivot built-in.


Regards,
Dariyoosh

[Updated on: Fri, 30 November 2012 02:43]

Report message to a moderator

Re: Displaying N number of rows from a large result set [message #571773 is a reply to message #571761] Fri, 30 November 2012 04:17 Go to previous message
Roachcoach
Messages: 1225
Registered: May 2010
Location: UK
Senior Member
If you can, create a table of results and have sql developer export that. It circumvents client memory issues with the query pane.

It does have other baggage, obviously, but it may help.
Previous Topic: balance of stock amount
Next Topic: Case function error
Goto Forum:
  


Current Time: Wed Nov 26 13:17:29 CST 2014

Total time taken to generate the page: 0.10311 seconds