Re: q:Subqueries

Before you say "don't flame me on this" realize this is a public forum and you will in all likelihood get "some" response.

Since you're working with a 20 million row table, I think you would be very sensitive on how data is retrieved. In your case, an ORDER BY clause without a WHERE clause would retrieve all the rows to a temporary table if your ORDER BY in not on an index. Presorting data in a table should improve data retrieval by keeping fewer blocks of data in cache at one time. When a least recently used block gets aged out of the buffer, it should be no great loss you've gotten all the data you need out of it. If you need a sub-set of a table (ordered) fewer blocks will be read. And if your data is sorted in a single extent, perhaps the data will be cached up by the disk controller in that order.

If you know ahead of time that your data is primarily reference information (no updates or inserts) and data is usually requested in a particular order, then it would make sense to retrieve the data that way.

Another technique you may want to pursue is using views to do SELECTs on your large (ordered) table. Let's say you have your table sorted by SSN. Make tables that start with 0,1,2,3,4, etc. Then use a view to retrieve that data.

I'm sure you're going to get many suggestions here. You ought to try out some of these suggestions, explain a little more about your environment, and report back to all of us. You may run into some other constrictions in your environment that may slow down your processing: too small temp tablespace, low an memory, many users, poor indexing, resource contention, etc.

I was at one site not too long ago that used no indexes. The DBA told me that her MIS director wouldn't buy more disks, but said she had to accept many more millions of rows from a mainframe application. After dropping the indexes, disk space magically appeared. I think her resume is in the works.

--Bob. Received on Sun Aug 27 1995 - 00:00:00 CEST