Toad World® Forums

Limit fetched rows from database

We have performance issue while fetching a lot of records using query.

Many people have access to database as they do support and they query the database.

Sometimes they run without where clause or rownum and it causes performance issue.

We need some toad options where we can restrict the number of rows to be fetched like we can restrict using rownum.


In previous versions (do not exactly which version) I assume there was an option:
Toad Options|Schema Browser|Data and Grids|Limit Grid Fetch.
This was done through Set OCI ARRAY BUFFER SIZE to xxx .

In latest Toad version this was defined as minimum memory when toad will stop retrieving records. However, for scripts:
Toad Options|Scripts|Limits result to xx rows

And for Grid, if you are attacking database under Oracle 10:
Toad Options|Oracle|General|Default Buffer size to n rows

All other is not known to me and I’d like to hear from Dell officially if in Toad 12 there is way to make that limitation in grid.


If you need first ten rows then this may be a way:
select x,v from table where rownum <=10;

Or if you have complex query then
select * from (
…your complex query)
where rownum <=10

Be aware that only first ten rows are retrieved (there is no additionally fetch) and your query is pretty unoptimized (regardless it run on the server)