Databases Reference
In-Depth Information
Optimizing joins
One of the most time-consuming operations in a database is the
JOIN
. We use this when we
need to join two or more tables due to the normalized structure of the database. There are
many types of joins (equi-join, self-join, outer join, anti-join, and so on).
In this recipe, we will see some join algorithms the database can use to answer our queries,
performance related to every type of join, and some tricks to avoid joins (when possible).
How to do it...
The following steps will demonstrate some common types of joins:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
2.
Create a table called
MY_CUSTOMERS
as a copy of the
CUSTOMERS
table:
CREATE TABLE sh.MY_CUSTOMERS AS SELECT * FROM sh.CUSTOMERS;
ALTER TABLE sh.MY_CUSTOMERS
ADD CONSTRAINT PK_MY_CUSTOMERS PRIMARY KEY (CUST_ID);
3.
Create a table called
MY_COUNTRIES
as a copy of the
COUNTRIES
table:
CREATE TABLE sh.MY_COUNTRIES AS SELECT * FROM sh.COUNTRIES;
ALTER TABLE sh.MY_COUNTRIES
ADD CONSTRAINT PK_MY_COUNTRIES PRIMARY KEY (COUNTRY_ID);
4. Execute a first join between the table
MY_CUSTOMERS
and the original
CUSTOMERS
table:
SET AUTOT TRACE EXP STAT
SELECT COUNT(*)
FROM sh.MY_CUSTOMERS M, sh.CUSTOMERS C
WHERE M.CUST_ID = C.CUST_ID;
5.
Execute a join between the two tables:
SET AUTOT TRACE EXP STAT
SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, N.COUNTRY_NAME
FROM sh.MY_CUSTOMERS C, sh.MY_COUNTRIES N
WHERE C.COUNTRY_ID = N.COUNTRY_ID;
