RelationalDBDesignRelationalDBDesign 





EXPLAIN PLAN   «Prev 

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.


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

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.
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.

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