Databases Reference
In-Depth Information
at a fragment of a query execution plan for a complex query. The full query is query #20
from the TPC-H industry-standard benchmark (see www.tpc.org). The full query is as
follows:
select
s_name,
s_address
from
tpcd.supplier,
tpcd.nation
where
s_suppkey in (
select
ps_suppkey
from
tpcd.partsupp
where
ps_partkey in (
select
p_partkey
from
tpcd.part
where
p_name like ':1%'
)
and ps_availqty > (
select
0.5 * sum(l_quantity)
from
tpcd.lineitem
where
l_partkey = ps_partkey
and l_suppkey = ps_suppkey
and l_shipdate >= date (':2')
and l_shipdate < date (':2') + 1 year
)
)
and s_nationkey = n_nationkey
and n_name = ':3'
order by
s_name;
The full query execution plan for this statement will be very involved, but we can
use a fragment of the query execution plan to highlight the main ideas. Figure 11.5
shows a fragment of the query execution plan that performs a nested-loop join between
the PARTS table and the PARTSUPP (part supplier) table. The query execution plan
