Databases Reference
In-Depth Information
SUM(Q2.$C5),SUM(Q2.$C6),SUM(Q2.$C7), Q2.$C0, Q2.$C1
FROM
(SELECT Q1.L_LINESTATUS, Q1.L_RETURNFLAG, Q1.COUNT,
Q1.S5, Q1.S4, Q1.S3, Q1.S2, Q1.S1
FROM TPCD.L_SUMMARY AS Q1
WHERE (Q1.L_SHIPDATE <= '09/02/1998')) AS Q2
GROUP BY Q2.$C1, Q2.$C0) AS Q3
ORDER BY Q3.$C7, Q3.$C6
The rewritten query is a little difficult to read, because it uses internal representa-
tions of tables and columns instead of the normal user-defined names that the database
designer has specified in the DDL. That's okay, because the rewritten version of the
query is just a steppingstone during the internal processing of the query, prior to query
execution plan selection. It's not essentially designed for human beings to read. How-
ever, many database vendors allow users to view the rewritten query through the query
execution plan visualization tooling. This can be helpful in determining if a perfor-
mance problem has been caused by the database during this rewrite phase.
It's possible that during query rewrite the database query compiler may find such a
good match for a materialized view that one is selected and substituted into the rewrit-
ten query, though most vendors leave the final selection of materialized views for the
query optimization step the majority of the time.
Finally, a query execution plan can be selected based on the combined data within
the query graph. This will include not only the rewritten query text, the list of objects,
columns accessed, available indexes, and materialized views and other physical design
attributes, but also existing statistics on the object (table, materialized view, and index
statistics) and information about the physical resources of the database server, such as
CPU speed, storage device access speeds, and network speed and latency. As discussed in
Chapter 3, the process of query execution plan selection is complex and can involve
varying degrees of search complexity. There are almost always multiple ways the result
set for a query can be determined, and the database will attempt during query optimiza-
tion to select the query execution plan that can retrieve the result set most efficiently.
Different databases have different goals for determining “most efficient,” though select-
ing the query execution plan with the lowest resource consumption appears to be a
common strategy.
However, the query execution plan itself is still an abstraction. A final step is
required prior to retrieving the answer set, called
code generation
. This process generates
the executable code required to perform the tasks recommended by the query execution
plan identified by the query optimization process. Code generation will convert the
abstract notation of the query execution plan into compiled object code that the data-
base can understand and execute.
Finally, the object code produced by the code-generation phase is executed, includ-
ing the operations for index access, joins, sorts, etc., and the query result is produced
