EXPLAIN Report Overview
EXPLAIN provides a natural language summary of the access and join plans generated by the Optimizer for the query SQL_request: the report details which indexes would be used to process the request, identifies any intermediate spools that would be generated, indicates the types of join to be performed, shows whether the requests in a transaction would be dispatched in parallel, and so on.
When you perform an EXPLAIN against any SQL request, that request is parsed and optimized, and the parse tree (access and join plans) generated by the Optimizer is returned to the requestor in the form of a text file that explains the steps taken in the optimization of the request as well as the relative time it would take to complete the request given the statistics the Optimizer had to work with. The EXPLAIN report reproduces the execution strategy of the Optimizer, but does not explain why it makes the choices it does.
EXPLAIN helps you to evaluate complex queries and to develop alternative, more efficient, processing strategies.
References to bit mapping might appear when complex conditional expressions involving nonunique secondary indexes are applied to a very large table.
Such expressions can be resolved by mapping each subtable row ID to a number in the range 0 through 32,767. This number is used as an index into a bit map in which the bit for each qualifying data row is set equal to 1.
The Optimizer is better able to determine whether the table is a candidate for bit mapping when up-to-date statistics exist for the important columns of that table. See SQL Data Definition Language.
Only the first 255 characters of a conditional expression are displayed in an EXPLAIN. The entire conditional expression is enclosed in APOSTROPHE characters.
Although the times reported in the EXPLAIN output are presented in units of seconds, they are actually arbitrary units of time. These numbers are valuable because they permit you to compare alternate coding formulations of the same query with respect to their relative performance. They do not correlate with clock time.
Save EXPLAIN results for future reference.