EXPLAIN PLAN   «Prev  Next»

Lesson 2SQL as a declarative language
ObjectiveDescribe SQL Hidden Access Paths

Describe SQL Hidden Access Paths

SQL was the first database access language to hide the actual access path to the data tables. Prior to SQL, the database programmer would explicitly specify which indexes were to be used to access a data structure and what linked-lists were to be traversed to achieve the desired result. A declarative language, however, like SQL, is one where the access path to the data is not required. One simply declares, as in the example below:


Execution Plan is created using Declarative Language.

The database will figure the best path to the data. The following Slide Show illustrates how an execution plan is created using a declarative language.

  1. The SQL statement is sent from the library cache
  2. The optimizer checks the Oracle dictionary and gathers index information (rule-based optimizer)
  3. This execution plan is compiled and passed into the library cache

Declarative Languages

SQL Optimizers

The premise behind SQL was simple. The person executing the SQL command should only specify the data that is required, but should not have to know the exact access path to the data. To achieve this result, SQL optimizers were constructed to determine the access path to the data. An SQL optimizer is a software program that reads the SQL and examines the data dictionary to figure out the fastest way to service the query.
It is important to remember that SQL is not 100% declarative. We still need to tell the optimizer the names of the tables that contain the data that we want to see, and we must manually specify the join criteria when multiple tables are involved.

New database access paradigm

Prior to the introduction of SQL, there was only one correct way to write a database query. However, SQL provided added flexibility by allowing numerous ways to specify a query. Given that each variant of the query returns identical results, the only criteria for correctness is the speed in which the query is serviced. To illustrate, consider the following identical queries to display the honor roll list for a school.

Query A
Student_name From Student Where Student_number in (select student_number from honor_roll);

Query B
Student_name From Student s, honor_roll h Where s.student_number - h.student_number;

This simple example should illustrate the tremendous importance of properly tuned SQL on the overall performance of your database. If we can determine the access path to data, we can determine if SQL is optimized for Oracle. To accomplish this, we must use the EXPLAIN PLAN utility. So let us get started by examining the access path to Oracle tables.

Oracle Tuning Reference