RelationalDBDesign RelationalDBDesign


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