Database Reference
In-Depth Information
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX UNIQUE SCAN | EMP_PK |
----------------------------------------------
No more information about this topic is provided here because several chapters in Part 4 cover in detail when
and how the different access structures should be used. For the moment, it's just important to recognize that this is a
fundamental SQL optimization technique.
When to Use It
Without the necessary access structures in place, it may be impossible to optimize a SQL statement. Therefore, you
should consider using this technique whenever you're able to change the access structures. Unfortunately, this isn't
always possible, such as when you're working with a packaged application and the vendor doesn't support altering
the access structures.
Pitfalls and Fallacies
When altering the access structures, it's essential to carefully consider possible side effects. Generally speaking, every
altered access structure introduces both positive and negative consequences. In fact, it's unlikely that the impact of such
a measure is restricted to a single SQL statement. There are very few situations where this isn't the case. For instance, if
you add an index like in the previous example, you have to consider that the index will slow down the execution of every
INSERT
and
DELETE
statement on the indexed table as well as every
UPDATE
statement that modifies the indexed columns.
You should also check whether the necessary space is available to add access structures. All things considered, you need
to carefully determine whether the pros outweigh the cons before altering access structures.
Altering the SQL Statement
SQL is a very powerful and flexible query language. Frequently, you're able to submit the very same request in many
different ways. For developers, this is particularly useful. For the query optimizer, however, it's a real challenge to
provide efficient execution plans for all sorts of SQL statements. Remember, flexibility is the enemy of performance.
How It Works
Let's say you're selecting all departments without employees in the
scott
schema. The following four SQL statements,
which can be found in the
depts_wo_emps.sql
script, return the information you're looking for:
SELECT deptno
FROM dept
WHERE deptno NOT IN (SELECT deptno FROM emp)
SELECT deptno
FROM dept
WHERE NOT EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno)
