Databases Reference
In-Depth Information
useful. How so? Because if the database thinks the design feature is useful, it will exploit
it in the query execution plan. For example, if an index A is beneficial to a query at
runtime we should expect to see access to index A in the query execution plan for the
query. In this chapter we'll show examples of the different ways that query execution
plans are presented both in text and graphically by the major database vendors, and
illustrate how they can help improve physical database design.
11.1
Getting from Query Text to Result Set
How does a query submitted to a database get transformed into an answer set? Figure
11.1 shows the basic order of operations that occurs for incoming SQL statements.
When a query is submitted to a database, the first thing the database will do is parse the
statement text to catch errors and then construct an internal representation of the query
that is usually stored in a graph model that the user cannot see.
The query is then examined for semantic correctness to ensure the query makes
sense. For example, do all the object's references in the query exist, and can the query
even conceptually be executed (even with a NULL answer set)? During this phase the
database may also extend the query graph by adding logic for constraint checking and
triggers.
Following semantic checking, the query is then rewritten by the database to
improve the efficiency of the text. Here's an example using query 1 from the industry
standard TPC-R benchmark.
select l_returnflag, l_linestatus, sum(l_quantity) as
sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as
sum_disc_price, sum(l_extendedprice * (1 - l_discount) *
(1 + l_tax)) as sum_charge, avg(l_quantity)as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from tpcd.lineitem
where l_shipdate <= date ('1998-12-01') - 90 day
group by l_returnflag, l_linestatus
order by l_returnflag, l_linestatus
Here is the same text after it has been rewritten by a database:
SELECT Q3.$C7 AS “L_RETURNFLAG”,
Q3.$C6 AS “L_LINESTATUS”,
Q3.$C5 AS “SUM_QTY”,
