Huge SELECTs reuslting in Time out: What is the solution?

 I have an SQL statement which is timing out. It uses primary key and all usual options of tuning have been tried. What do you suggest?

Cursors with “Package SIZE” are useful to avoid time-out issues while fetching huge number of records.

A SELECT statement transfers entire result set into the application server. Where as when CURSOR is used with PACKAGE SIZE, only the number of rows mentioned in PACKAGE statement is copied in one go.

For example if your result set has 5 million records, using a SELECT statement would time-out. This is because in one database fetch, all 5 million records would be tried to transfer into the application server, thus timing out.

But if I use a CURSOR with 10,000 as package size, there would be 500 database fetches, but there would not be any time-out since only 10,000 records will be fetched in one go.

But, yes, there is a sacrifice we have done due to 499 extra fetches. What if we use a package size of 1 million?