Databases Reference
In-Depth Information
The first trick is denormalization. If we denormalize some tables, we don't need to join them
while executing our queries on these tables, because all the required data is in a single table.
We can also use clusters to speed our join queries (when the cluster key contains our join
keys); by reading clusters we get the data from both the tables, and hence can avoid joins.
The last trick is to use materialized views, enabling query rewrite, so we can find the answer
for our join queries in the materialized view.
See also
F
We have seen denormalization in
Chapter 2
,
Optimizing Application Design
in the
Optimizing performance with schema denormalization
recipe
F
We have talked about index clusters in the
Using index clusters
recipe in
Chapter 3
,
Optimizing Storage Structures
F
For more information on materialized views, see the
Reducing the number of
requests to the database using materialized views
recipe also in
Chapter 2
Using subqueries
We often use subqueries in our SQL statements to nest more queries in one statement,
using the results from an "inner" query to calculate other values.
In this recipe, we will see the use of subqueries for getting only a subset of records,
demonstrating the constructs
(NOT)
EXISTS
and
(NOT)
IN
, highlighting the semantic
difference between them (and when to choose one type of statement or the other).
How to do it...
The following steps will demonstrate the use of subqueries:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
SET AUTOT TRACE EXP STAT
2.
Select a table using the
IN
operator:
SET AUTOT TRACE EXP STAT
SELECT AMOUNT_SOLD FROM sh.SALES S
WHERE S.CUST_ID IN (
SELECT C.CUST_ID FROM sh.CUSTOMERS C
WHERE C.CUST_CREDIT_LIMIT IN (10000, 11000, 15000)
);
