Extensions for SQL (oracle-sql) is the first course in a three-part series.
Programming in PL/SQL
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
- Oracle Text,
- Monitor, and
- Table spaces.
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:
- Performance extensions (SQL hints, parallel queries)
- Data transformation extensions (null values (NVL), decode)
- 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%');