Oracle SQL Developer: Fetching SQL Statement Result Sets

thatjeffsmith SQL Developer 14 Comments

Tell Others About This Story:

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.

Tell Others About This Story:

Comments 14

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

    1. thatjeffsmith Post
  2. 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

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

    Sourav Ghosh

    1. thatjeffsmith Post
    1. thatjeffsmith Post
  4. 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 🙁

    1. thatjeffsmith Post

      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.

      1. Future version request: a toolbar button to toggle between first 50 and all rows. It would be really handy for big data sets.

      2. thatjeffsmith Post
      3. 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 😉

      4. thatjeffsmith Post

        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?

Leave a Reply

Your email address will not be published. Required fields are marked *