Lesson 10

Well, we should now have a firm grasp of the basic concepts behind running the EXPLAIN PLAN to see the access path to data. This module was not intended to make you an expert, and there is always more to learn, especially when you are working with complex SQL queries. The important concepts from this module include the ability to:

1. Understand SQL as a declarative language
2. Create a PLAN table
3. Run the EXPLAIN PLAN utility
4. Detect full-table scans with EXPLAIN PLAN
5. Use EXPLAIN PLAN to diagnose table joins
6. Read non-correlated subqueries
7. Detect index range scans
8. Analyze complex EXPLAIN PLAN output

Here are some terms that might have been new to you.

1. EXPLAIN PLAN: This is an Oracle utility to show the access path.
2. Index range scan: This is a scan of a table using an index.
3. Nested loop: This is a join method that queries the driving table and then probes the other tables via an index.
4. Non-correlated subquery : This is an SQL subquery that does not have a WHERE condition that references values in the outer query.
5. Optimizer: This is an Oracle tool used to determine Oracle SQL execution plans.
6. PLAN TABLE: This is an internal table that contains the display of the execution plan.

The next lesson delves into SQL optimizers.

Before moving on to the next module, click the Quiz link below to test your understanding of the EXPLAIN PLAN utility.
Explain Plan - Quiz