page283
371
You must regard an explain plan as a window into the optimization process just prior
to the point of actual execution where further optimizations may occur.
Explaining SQL statements is a metadata operation; therefore, no warehouse is
required; zero runtime cost are incurred though cloud service costs accrue.
In addition to the immediate knowledge gained for SQL statement execution, other
use cases illustrate the value of explain plan.
•
Delivering explain plan output as part of the software delivery life
cycle provides a baseline for reference when the application has
matured.
•
Using an explain plan is good practice, instills discipline, and catches
preventable issues early.
•
Tabular format allows programmatic identification of issues.
To illustrate how
EXPLAIN
works, let’s reuse the earlier known-good example query
referencing
v_supplier_part
. In this example, I will request
TABULAR
output but might
instead prefer JSON output:
EXPLAIN USING TABULAR
SELECT <attributes>
FROM <table>
WHERE <predicates>
ORDER BY <ordering>;
Figure 10-7 reuses the sample explain plan output from Chapter 3; note that further
information is available if you scroll off to the right of the screen (not shown).
ChapTer 10
OpTimizing perfOrmanCe