SQL Extensions  «Prev  Next»

Lesson 2Introduction to Oracle: SQL and PL/SQL
Objective Complete your preparation for the first test as DBA

Oracle SQL Extensions

Extensions for SQL (oracle-sql) is the first course in a three-part series.
Programming in PL/SQL and PL/SQL Stored Objects will complete your preparation for the test in the
Oracle Certified Database Administrator and/or Certified Application Developer track.

Oracle SQL has added many non-standard extensions to their implementation of the ANSI SQL standard. The Oracle variation of SQL includes additional features that can improve the speed of SQL queries.
Current Oracle extensions consist of
  1. Explain,
  2. Jobs,
  3. Oracle Text,
  4. Sessions,
  5. Monitor, and
  6. Table spaces.

Oracle Extension to ANSI Standard SQL

The majority of 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 from the other vendors. Oracle SQL has many useful, but non-standard extensions to their implementation of the ANSI SQL standard.
The Oracle version of SQL includes additional features that can greatly improve the speed of SQL queries. These extensions are commonly referred to as built-in functions known as BIFs. 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)
It is important to note that these extensions are unique to Oracle SQL. This is because applications that are written to be portable between databases must not use any of the vendor-supplied extensions. The portable SQL application systems are specifically designed to be generic so they can be moved between relational database products without altering the SQL statements.
These extensions are very helpful in quickly retrieving and formatting data, but they also have drawbacks to the database. The primary disadvantage is that until Oracle8i you could not use an index to query a column that contained a BIF. For example, assume that we have the last_name column of the CUSTOMER table indexed.
The following query would use the index:
Select * from customer where last_name like 'B%'; 
This query, because it uses a BIF, cannot use the index:
 
Select * from customer where last_name like to_upper('b%');