• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1320
  • Last Modified:

TOAD implementation

Hey experts!

I have a general question - IF your Oracle (9.2 or 10g) database has say, a terabyte of data in a single table, and you say select * from that table;  TOAD somehow manages to return the first rows of the query, and let you page through the rest...

If you drag the scroll bar to the bottom, you will wait a long time..  then finally see the last screen full of records...

The question is: how is this accomplished?  It seems that all the results are not returned to the client, but instead are kept on the server...  I'm thinking as a snapshot(?).  If this is true, how do you page through a snapshot result set?  any ideas?

alternately, what is the best way to not kill your app with out of memory errors on absurdely large result sets?

thanks for comments.
2 Solutions
TOAD uses the hint FIRST_ROWS to retrive some rows first. So your query is not executed fully. When you scroll down, the quey's remaining part is executed, so you ssee the time consumed. You can do the same trick by usingg FIRST_ROWS hint. For example,

SELECT /*+ FIRST_ROWS */ distinct customer_name FROM customer;

Firstly toad does not immediately download the whole database just because you have run some code, this is common across allmost all database browsers, you'll find an ODBC linked table or query will behave in the same way in MS Access. There are some very obvious reasons why this is not desireable.

Now the hint /*+ FIRST_ROWS */ merely tells Oracle that it should concentrate on returning the first row of the result set as quickly as possible, this is usefull if you are feeding the result set into another process or as in your case you wish to brwse some data.

Alternatively oracle can concentrate on returning the last record as quickly as possible such as in CTAS (Create table as select ...) statement where the dataset is of no use to you untill the table is fully created.

One of the basic concepts within Oracle is that all records in a result set must be concurrent in line with the database being a transactional design this requires Oracle to keep track of what changes are happenign in the database whilst your query is running and still return the data as it was when your querry started. In theory it could do this be taking a copy of the data in each table in your querry and then using these for your job, in practice this is not possible as it would use up a lot of space and the tables would not be updatable by other users whilst being copied so Oracle is slightly more celever that that, merely reataining copies of rows that are altered by another user during your query.

Occaisionaly you may receive a "Snapshot too old" error meaning that other users have made so many changes to the data in your table that you are using that it has run out of temporary space to keep track of all these changes.

When the query runs then it generates a temporary table containing the resultset that is accessible only to the client from which it has run, the client can then page through the resultset. When you drag down to the bottom of teh result grid, TOAD loads each batch of records until it runs out.

The default is to load only 25 records at a time, this can be altered in the options under the OCI Array Buffer Size entry. This means that when you scroll all the way to the bottom TOAD loads the next 25 records, deletes the previous 25 records, loads the next 25 records.... untill it runs out. Tweaking this setting up a bit won't hurt s 25 rows is very small.
randydAuthor Commented:
it turns out that TOAD does in fact store the entire result set in memory when you scroll to the bottom.

What got me on this tamgent was a message that stated Snapshot too old after i failed to scroll to the bottom for awhile...  sorta makes sense that the internal mechanism is a snapshop - but its just a read consistent cursor to the client.

anyway, i was aware of first_rows hints etc.  but will split points among all respondents.. thanks

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now