Database Reference
In-Depth Information
)
As
num_fact_types
FROM
census
.
lu_tracts
As
T
;
Now, if we query our view with this query:
SELECT
tract_id
FROM
vw_stats
;
execution time is about 21 ms on our server because it doesn't run any computation for
certain field such as
num_facts
and
num_fact_types
, fields we did not ask for. If you
looked at the plan, you may be startled to find that it never even touches the facts table
because it's smart enough to know it doesn't need to. But if we use:
SELECT
*
FROM
vw_stats
;
our execution time skyrockets to 681 ms, and the plan is just as we had in
Figure 9-4
.
Although our results in this example suffer the loss of just milliseconds, imagine tables
with tens of millions of rows and hundreds of columns. Those milliseconds could
translate into overtime at the office waiting for a query to finish.
Make Good Use of CASE
We're always surprised how frequently people forget about using the ANSI SQL
CASE
expression. In many aggregate situations, a
CASE
can obviate the need for inefficient
subqueries. We'll demonstrate the point with two equivalent queries and their corre‐
sponding plans.
Example 9-14
uses subqueries.
Example 9-14. Using subqueries instead of CASE
SELECT
T
.
tract_id
,
COUNT
(
*
)
As
tot
,
type_1
.
tot
AS
type_1
FROM
census
.
lu_tracts
AS
T
LEFT
JOIN
(
SELECT
tract_id
,
COUNT
(
*
)
As
tot
FROM
census
.
facts
WHERE
fact_type_id
=
131
GROUP
BY
tract_id
)
As
type_1
ON
T
.
tract_id
=
type_1
.
tract_id
LEFT
JOIN
census
.
facts
AS
F
ON
T
.
tract_id
=
F
.
tract_id
GROUP
BY
T
.
tract_id
,
type_1
.
tot
;
Figure 9-7
shows the graphical plan of
Example 9-14
.
Figure 9-7. Graphical plan of using subqueries instead of CASE
