Question
Why does the DB2 optimizer sometimes choose an access plan which uses an index scan followed by a full table scan instead of just a full tablescan?
After gathering a query explain, you may see in certain cases that the access plan shows an index scan that is joined with a full table scan.
For example (with a table that has only 300 rows):
300
TBSCAN
( 4)
26.0089
2
|
300
SORT
( 5)
26.0086
2
|
300
FETCH
( 6)
25.9339
2
/---+----\
300 300
IXSCAN TABLE:
( 7) TBL1
0.152007
0
|
300
INDEX:
TBL1_INDEX1
Answer
In certain cases the Optimizer will choose to do a full index scan and join it with the table scan, because there may be 'EMPTY' pages in the table. Performing an index scan can help avoid these empty pages, as opposed to a full table scan.
To calculate the number of empty pages in a table, query the FPAGES and NPAGES columns in the SYSCAT.TABLES view and then subtract the NPAGES value (the number of pages that contain rows) from the FPAGES value (the total number of pages in use). Empty pages can occur when entire ranges of rows are deleted.
As the number of empty pages increases, so does the need for table reorganization. Reorganizing a table reclaims empty pages and reduces the amount of space that a table uses. In addition, because empty pages are read into the buffer pool during a table scan, reclaiming unused pages can improve scan performance. The quickest solution is to REORG the table to remove the empty pages.
http://www-01.ibm.com/support/docview.wss?uid=swg21656021