SQL Tuning   «Prev  Next»

Lesson 2 Oracle extension to ANSI standard SQL
Objective Explain why Oracle enhances standard SQL.

Oracle SQL enhances Standard SQL

Why use SQL for queries?

Unlike access languages for non-relational databases, using SQL for queries has several important features. Being declarative, the person who writes an SQL statement is not concerned about how the Oracle engine will service the query. That is, the programmer is not concerned about whether Oracle uses an index , performs a hash join, or does a full-table scan against the table.
The Oracle engine takes care of figuring out the optimal way to access the data when the SQL statement is executed.
  1. index: This is a data structure used to facilitate fast access to table rows in a specified sequence.
  2. Hash join: This is an execution plan that creates a hash table in SGA memory and uses this memory structure to join the tables.
  3. Full-table scan: This is an execution plan that accesses a table without an index, reading each block of the table.

The ANSI standards

Most of the major relational database vendors proclaim that their SQL is 100% ANSI standard.
While this is true in most cases, the database vendors always add additional features and functionality to distinguish their product. Oracle is no exception. Oracle SQL has added many useful, but non-standard extensions to their implementation of the ANSI SQL standard. The Oracle dialect of SQL includes additional features that can greatly improve the speed of SQL queries. These extensions are commonly referred to as built-in functions (called BIF s).
BIF: Extensions to standard Oracle SQL
ANSI standard:The American National Standards Institute

The common BIFs include:
  1. Performance extensions (SQL hints, parallel queries)
  2. Data transformation extensions
    (null values (NVL), decode)
  3. Formatting extensions (to_char, to_date, to_upper)
These extensions are unique to Oracle SQL.
Applications that are written to be portable between databases must not use any of the vendor-supplied extensions.
The portable SQL application system is specifically designed to be generic so it can be moved between relational database products without re-writing the SQL statements. For those of you who have asked the question:
Question: Is "Decode" ansi standard sql?
Answer: The answer is No. It is specific to Oracle.
These extensions are very helpful in quickly retrieving and formatting data, but they also have drawbacks to the database.
We will exam ANSI standards in more detail later in the course.
In the next lesson, the basic tools for Oracle tuning will be discussed.

Ad Advanced Oracle SQL Tuning