Ever since the birth of DB2 we have been taught that we should avoid tablespacescan. In fact there are some good exceptions to this rule. I will describe one of them in this tip. But first it is important to understand the basic difference between tablespacescan and index access.
When DB2 performs a tablespacescan the rows in the underlying table is read into the bufferpool by an asynchronous process running in parallel with the process that is actually returning the rows to our application program. It means that there are almost no delayes in data access which is very efficient and burns off a lot of CPU. The involved processes are called sequential prefetch and asynchronous I/O. If you like to look directly at the data in PLAN_TABLE the column is called PREFETCH and contains a S.
When DB2 performs index access it is mainly the the same process that both reads the data and returns them to the application program. This implicates that if data is not already in the bufferpool the application has to wait while data is being read into the bufferpool. The involved process is called synchronous I/O and I have once heard synchronous I/O being reffered to as turtle I/O. If the column PREFETCH in PLAN_TABLE is blank DB2 uses synchronous I/O to retrieve data.
As synchronous I/O is bad to response time, DB2 has a lot of tricks to avoid it. The bufferpool is of course the best avoidance trick but there are also other kinds of PREFETCH (meaning asynchronous I/O) which I will not mention here. As you might know it is the DB2 optimizer that determines the most efficient PREFETCH method. Normally we do not need to worry whether tablespacescan or index access is used.
However it can be quite interesting when DB2 chooses to scan an index instead of scanning the tablespace. This happens for instance if all columns in the SQL statement are located in the same index. A SELECT COUNT(*) FROM table without WHERE or with WHERE on columns in the same index is a good example. This is normally the quickest way of scanning all rows because an index is typically smaller than the tablespace and DB2 uses sequential prefetch for this process.
Occasionally it might happen that an index is very poorly organised and then a sequential prefetch through such an index will perform extremely bad. Then you need to ask the DBA to run a REORG. On rare occasions the DBA will not run the REORG because the tablespace has grown too big. In such a case you can try and convert the index scan into a tablespacescan instead. An example:
The original statement is without "AND columnNotInIndex <> -1", but by adding a predicate that is always true for a column not present in any index you will in most cases force DB2 to perform a tablespacescan. And a tablespacescan does not take into account whether data is badly organised or not. Of course DB2 needs to use CPU to find out that the predicate is true, but it is a very small expense compared to the gain in response time. When I write "in most cases" it is due to the fact that I have seen cases where the optimizer despite the above trick refused to use a tablespacescan.