EXPLAIN provides a summary of the access and join plans generated by the Optimizer for the query SQL requests. 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 plan generated by the Optimizer is returned to the requestor in the form of a text file that explains the steps of the plan for the request. The EXPLAIN report reproduces the execution strategy determined by 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.
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 relative performance, but do not correlate with clock time. Times and row estimates are not reported for some steps.
Save the EXPLAIN results for future reference.