Running queries, browsing tables – you are often faced with many thousands, if not millions, of rows. Most people are happy with looking at the first few rows. But occasionally you need to see more. SQL Developer doesn’t show you all records, all at once. Instead, it brings the records down in ‘chunks,’ or as-needed.
How It Works
There is a preference that tells SQL Developer how many records to get in a single request, or ‘fetch’ of records.
So if I run a query that returns MORE than 50 rows:
We don’t know how many records are in this result set actually. To show the record count here, we actually go physically query the database with a row count type query.
All we know is that the query has finished executing, and that there are rows available to go fetch. It tells us when it’s done.
As you scroll through the grid, if you get to record 50 and scroll more, we’ll get 50 more records.
Or, you can cheat to get to the ‘bottom’ of the result set.
Once all the records have been fetched, you’ll see this:
A word of caution
There’s a reason we have the default set to 50 and not 1000. Bringing back data can get expensive and heavy. We’ve found the best performance to be found in that 50 to 200 record range.
30 Comments
Hello Jeff,
Is there a way to implement setMaxRow in the default JDBC driver that is used by SQL Developer?
Why would you want to do this?
Hi Jeff
You say above “We don’t know how many records are in this result set actually. To show the record count here, we actually go physically query the database with a row count type query.”
How did you call that row count?
SELECT COUNT(1) FROM (your-query-here
) sub1
Hi Smith,
My query is giving output in less than a second for first 50 records in Oracle SQL Developer. When I select all(Ctrl + A) in Query result then it takes 120 second to fetch all records(165k). Could you please suggest me if there is any scope to optimize this ?
Can you also explain how the query is working for first 50 records of result and then for rest of the records ? Is the SQL engine fetching the rest of records from the data blocks OR from the result cache ?
Many Thanks,
Asit
Increase the array size to 200 and you’ll decrease the number if fetches by a factor of 4.
Increasing the array size to 200 will give me the result of 200 records in a second. But I am concerned about the entire results (165k records) to be exported. Is there any way I can export all the records less than 30 sec or 60 secs. Thanks,Asit
Exporting will go faster than populating the grid.
Hi,
I am exporting the following way. But still it takes 120 secs to export 165k records .Is there any better way ?
set term off
set feed off
set sqlformat csv
spool out.csv
select /*+ parallel */* from t;
spool off
Hi
Here is new cheat Button that gives you all results in one shot
CTRL+END
Thanks
Hi Smith, i am facing one problem in Oracle 12 c where in, my query has total of 5345 rows, however, as per fetch size it returns 50 rows at a time. This is understood, however, when i scroll to next set or rows by pressing down key on key board it fetches next set quickly till row no. 1690, but it takes too long to fetch next set with row starting from 1691 and beyond. This is weird behavior and it causes a null pointer exception on resultset.next() in the API that i am using. Can you shower some light on this issue. That will be great.
Thanks
Chirag
does this happen for every query, or just this one in particular?
I’ve never seen this behavior before…what happens if you execute via F5 (as a script) ?
How do you mean ‘the api that i am using’ – are you not using SQL Developer?
Is there a way I can obtain a size of result set in terms or bytes/mbtyes? e.g 2000 rows and their size is 1 MB if save it in a notepad?
Not easily or w/o having to write some code.
Of course you could simply do the export to flat text and let the OS tell you how big the resulting file is.
How do I export this butt load of data on an excel?
I know that small chunks of data can be exported, but what about this huge dump?
Use the xlsx format on the export, ive done million row exports with no problems.
I cheated it 🙂
After the first 50 rows are returned, select a value in the result set and press Crtl + A. It will return all rows. Thanks
Hi Smith,
Could you please suggest how SQL Developer fetch first 50 rows and wait for user’s inputs to display more rows?
Actually I am writing one similar kind of program to fetch data from Oracle database. My Table contains approx 10000 rows, I am trying to write a java code to fetch first 50 rows & wait for user’s input to display next set of records.
Thanks,
Sourav Ghosh
old docs, but still valid
Hi Smith,
I am in a similar Situation as Sourav. And Your answer to his question is “old docs, but still valid”, I did not get your answer. Could you please elaborate, How SQL Developer fetches next set of records as per user input.
Thanks & Regards,
Venu
the jdbc driver asks for a fetch of the first batch of records from the query resultset, that’s a java/jdbc question, not a sqldeveloper question. And I’m here to help with SQL Developer, not with people building their own Java apps.
“You can ask SQL Developer to just to get all the records at once…”
…ok…How?
I show it in the animated GIF above
On MacOsX use Ctrl + fn + DownArrow.
It is always a good idea to go to the end before you export the data, because otherwise SQL Developer will execute the query again (instead of implicitly scrolling).
So you waited half an hour for the result of a complex query – you click to export – and you wait a long time again before the export even starts 🙁
I wouldn’t say ‘always.’
Because if you fetch all the data down, that’s gonna take a lot of application memory. So, do what works for you.
If it’s a very large dataset, use the sqldev output hint in your query and spool it to a file – that’s probably the fastest way to export your query result data out.
Future version request: a toolbar button to toggle between first 50 and all rows. It would be really handy for big data sets.
you can put that up on our exchange, but you already have a keyboard shortcut, so I doubt it will happen, esp as it is a very HEAVY request
I didn’t realise that was heavy I just thought there’s plenty of room on the toolbar for an extra button or two ;-).
Even using the shortcut, if I go and get lunch while my query is fetching the first 50 rows, I could end up adding an extra half hour (maybe more!) to the execution time. Or should I postpone lunch until I’ve got the first set back 😉
Do you want execute AND fetch all or do you just want a button on the grid toolbar that does a fetch all after the query has executed?
I can grab almost a million records to the grid in just a few minutes – how short is your lunch break?