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 following is a comprehensive list of the knowledge you should have before moving on to the next module.

1. All SQL is declarative.
2. There are many ways to write any SQL query, and some will retrieve rows faster than other methods.
3. Oracle uses a table called PLAN to hold the access information about the query.
4. You can create a PLAN table by running the SQL in $ORACLE_HOME/rdbms/admin/utlxplan.sql.
5. EXPLAIN PLAN output is very cryptic and difficult to interpret. The output is read inside-out, bottom to top.
6. You can easily identify full table scans in EXPLAIN PLAN output by looking for “TABLE ACCESS FULL.” Full-table scans on large tables take a huge amount of I/O, which can sometimes be alleviated by adding indexes to the target table.
7. When you issue a table join or a non-correlated subquery, Oracle will use the nested loop method to join the tables together. This nested loop method requires a driving table and a probe table.
8. Sometimes the use_hash hint will improve the speed of nested loop joins.
9. Non-correlated subqueries should only be used with the NOT IN predicate.
10. Index range scans commonly occur when a range comparison has been made against a numeric column in a table, and an index exists for that column.
11. Oracle SQL Analyzer tool (an extra-cost add-on), Q diagnostic center, or SQLab can be used to simplify the reading of complex EXPLAIN PLAN output.

Another important benefit of programmability is the ability of the database server to protect the integrity of the data from malicious users and applications accessing the database across a network. While basic relational integrity rules such as referential integrity are generally best defined declaratively, as part of the database schema, database triggers make it possible for the server to actively enforce arbitrary business rules that require a procedural definition. By centralizing business logic in the database, it need not be coded in every application that accesses the database, thus avoiding redundancy and errors, and making it feasible to provide end users with direct access to the data.
For its part, Oracle Corporation used the Ada programming language as a model for PL/SQL, its own proprietary procedural language. Like Ada, PL/SQL includes language features like exception handling and parameter type declarations that facilitate the development of reliable, large-scale, and complex systems. The procedural language eventually added to the SQL standard resembles PL/SQL in many respects. PL/SQL first appeared for client-side use (in Oracle's SQL*Forms) in 1988, and with Oracle7 in 1992 for triggers and stored procedures that execute within the database.

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