Oracle SQL Developer: Fetching SQL Statement Result Sets

Posted by thatjeffsmith on Oracle Blogs See other posts from Oracle Blogs or by thatjeffsmith
Published on Wed, 28 May 2014 18:54:03 +0000 Indexed on 2014/05/28 21:47 UTC
Read the original article Hit count: 383

Filed under:
|
|

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.

© Oracle Blogs or respective owner

Related posts about SQL Developer

Related posts about sqldev