Declarative SQL

The following is a comprehensive list of the knowledge you should have before moving on to the next module.

1. All SQL is declarative.
2. There are many ways to write any SQL query, and some will retrieve rows faster than other methods.
3. Oracle uses a table called PLAN to hold the access information about the query.
4. You can create a PLAN table by running the SQL in $ORACLE_HOME/rdbms/admin/utlxplan.sql.
5. EXPLAIN PLAN output is very cryptic and difficult to interpret. The output is read inside-out, bottom to top.
6. You can easily identify full table scans in EXPLAIN PLAN output by looking for “TABLE ACCESS FULL.” Full-table scans on large tables take a huge amount of I/O, which can sometimes be alleviated by adding indexes to the target table.
7. When you issue a table join or a non-correlated subquery, Oracle will use the nested loop method to join the tables together. This nested loop method requires a driving table and a probe table.
8. Sometimes the use_hash hint will improve the speed of nested loop joins.
9. Non-correlated subqueries should only be used with the NOT IN predicate.
10. Index range scans commonly occur when a range comparison has been made against a numeric column in a table, and an index exists for that column.
11. Oracle SQL Analyzer tool (an extra-cost add-on), Q diagnostic center, or SQLab can be used to simplify the reading of complex EXPLAIN PLAN output.

Another important benefit of programmability is the ability of the database server to protect the integrity of the data from malicious users and applications accessing the database across a network. While basic relational integrity rules such as referential integrity are generally best defined declaratively, as part of the database schema, database triggers make it possible for the server to actively enforce arbitrary business rules that require a procedural definition. By centralizing business logic in the database, it need not be coded in every application that accesses the database, thus avoiding redundancy and errors, and making it feasible to provide end users with direct access to the data.
For its part, Oracle Corporation used the Ada programming language as a model for PL/SQL, its own proprietary procedural language. Like Ada, PL/SQL includes language features like exception handling and parameter type declarations that facilitate the development of reliable, large-scale, and complex systems. The procedural language eventually added to the SQL standard resembles PL/SQL in many respects. PL/SQL first appeared for client-side use (in Oracle's SQL*Forms) in 1988, and with Oracle7 in 1992 for triggers and stored procedures that execute within the database.