EXPLAIN provides a summary of the access and join plans generated by the Optimizer for the query SQL requests. The report details which indexes are to be used to process the request, identifies any intermediate spools that are to be generated, indicates the types of join to be performed, shows whether the requests in a transaction are to be dispatched in parallel, and so on.
When you perform an EXPLAIN against a SQL request, the 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 run strategy determined by the Optimizer, but does not explain the reasons for its choices.
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.
The numbers in EXPLAIN reports allow you to compare alternate coding formulations of the same query with respect to relative performance.
EXPLAIN reports times in seconds, but the times do not correlate with clock time. The times allow you to compare alternate coding formulations of a query with respect to relative performance.
Save the EXPLAIN results for future reference.