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.

The default is 50...
The default is 50…

So if I run a query that returns MORE than 50 rows:

There's more than 50 records in this resultset, but we have 50 in the grid to start with.
There’s more than 50 records in this resultset, but we have 50 in the grid to start with.

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.

You can ask SQL Developer to just to get all the records at once...
You can ask SQL Developer to just to get all the records at once…

Once all the records have been fetched, you’ll see this:

All rows fetched!
All rows fetched!

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.

thatjeffsmith
Author

I'm a Senior Principal Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

18 Comments

  1. 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

    • thatjeffsmith

      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?

  2. 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?

    • thatjeffsmith

      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.

  3. pratik swami Reply

    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?

  4. 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

  5. Sourav Ghosh Reply

    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

  6. “You can ask SQL Developer to just to get all the records at once…”

    …ok…How?

  7. 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 🙁

    • thatjeffsmith

      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.

    • thatjeffsmith

      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 😉

    • thatjeffsmith

      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?

Write A Comment