Chapter 21. Advanced SQL Tuning
In the last chapter, we emphasized that high-performance stored programs require optimized SQL statements. We then reviewed the basic elements of SQL tuning — namely, how to optimize single-table accesses and simple joins. These operations form the building blocks for more complex SQL operations.
In this chapter, we will look at optimizing such SQL operations as:
Subqueries using the
INandEXISTSoperators"Anti-joins" using
NOT INorNOT EXISTS"Unamed" views in
FROMclausesNamed or permanent views
DML statements (
INSERT,UPDATE, andDELETE)
Tuning Subqueries
A subquery is a SQL statement that
is embedded within the WHERE clause
of another statement. For instance, Example 21-1 uses a subquery to
determine the number of customers who are also employees.
Example 21-1. SELECT statement with a subquery
SELECT COUNT(*)
FROM customers
WHERE (contact_surname, contact_firstname,date_of_birth)
IN (select surname,firstname,date_of_birth
FROM employees)We can identify the subquery through the DEPENDENT SUBQUERY tag in the Select type column of the EXPLAIN statement output, as shown
here:
Explain plan
------------
ID=1 Table=customers Select type=PRIMARY Access type=ALL
Rows=100459
Key= (Possible= )
Ref= Extra=Using where
ID=2 Table=employees Select type=DEPENDENT SUBQUERY Access type=ALL
Rows=1889
Key= (Possible= )
Ref= Extra=Using whereThe same query can also be rewritten as an EXISTS subquery, as in Example 21-2.
Example 21-2. SELECT statement with an EXISTS ...