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:

SELECT
     Stuff
FROM
     tables
WHERE
    Boolean_conditions;

Execution Plan is created using Declarative Language.

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

1) The SQL statement is sent from the library cache into the SQL optimizer.
1) The SQL statement is sent from the library cache into the SQL optimizer.

2) The optimizer checks the Oracle dictionary and gathers index information (rule-based) optimizer or statistics (cost-based optimizer) and uses this data to compute an optimal access path.
2) The optimizer checks the Oracle dictionary and gathers index information (rule-based optimizer) or statistics (cost-based optimizer) and uses this data to compute an optimal access path.

3) This execution plan is compiled and passed into the library cache for execution by Oracle
3) This execution plan is compiled and passed into the library cache for execution by Oracle

  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

SQL Declarative Languages

SQL used to be nothing more than a data access method, a way to get rows from the database into an application program, but Oracle's extensions have transformed SQL into a procedural language.
SQL as a declarative language has many ways to create and execute any query, with identical results but with a huge variation in performance.
There are several areas of SQL programming best practices:
  1. Security: The application developer wants to avoid SQL injections
  2. Performance: Using low-impact techniques like analytic functions, rewriting subqueries whenever possible.
  3. Standards: Writing standard SQL is a challenge.

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.

display honor roll list
Query A
Select Student_name
From
  Student
Where
  Student_number in
  (select student_number from honor_roll);

display honor roll list
Query B
Select 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