| Lesson 2 | SQL as a declarative language |
| Objective | Describe 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.
- The SQL statement is sent from the library cache
- The optimizer checks the Oracle dictionary and gathers index information (rule-based optimizer)
- 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:
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:
- Security: The application developer wants to avoid SQL injections
- Performance: Using low-impact techniques like analytic functions, rewriting subqueries whenever possible.
- 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.
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.
Select Student_name From Student Where Student_number in (select student_number from honor_roll);
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