ORACLE 11g Features

What are the new features added in ORACLE 11g?

Following are the features added in ORACLE 11g for SQL and PL/SQL. 

PL/SQL "CONTINUE" keyword - This will allow a "C-Like" continue in a loop, skipping an iteration to bypass any "else" Boolean conditions.  A nasty PL/SQL GOTO statement is no longer required to exit a Boolean within a loop.

New "PIVOT" SQL clause - The new "pivot" SQL clause will allow quick ROLLUP, similar to an MS-Excel pivot table, where you can display multiple rows on one column with SQL. The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. The UNPIVOT operator converts column-based data into separate rows.

Using Compound Triggers:  In Oracle 11g, the concept of compound trigger was introduced. A compound trigger is a single trigger on a table that enables you to specify actions for each of four timing points:

1.        Before the firing statement

2.       Before each row that the firing statement affects

3.       After each row that the firing statement affects

4.       After the firing statement

With the compound trigger, both the statement-level and row-level action can be put up in a single trigger. Plus, there is an added advantage: it allows sharing of common state between all the trigger-points using variable.

Two popular reasons to use compound trigger are:

1.        To accumulate rows for bulk-insertion.

2.       To avoid the infamous ORA-04091: mutating-table error.

Change Trigger Execution using Follows Clause: 

The Oracle 11g trigger syntax now includes the Follows/Precedes clause to guarantee execution order for triggers defined with the same timing point.  Use FOLLOWS to indicate that the trigger being created should fire after the specified triggers. Follows clause in a trigger specifies that the current trigger would follow the execution of specified triggers. The specified triggers must already exist, they must be defined on the same table as the trigger being created, and they must have been successfully compiled. They need not be enabled.

Creating Trigger in DISABLED mode: 

Oracle 11g provides flexibility to create a Trigger in DISABLED mode also. They remain deactivated until they are enabled. Prior to Oracle 11g, a trigger can be created in ENABLED mode only.

DDL Wait Option: Oracle Database 11g, using DDL_LOCK_TIMEOUT parameter, when a DDL statement in the session does not get the exclusive lock, it will not error out. Instead, it will wait for specified seconds in DDL_LOCK_TIMEOUT parameter. In those seconds, Oracle continually re-tries the DDL operation until it's successful or the time expires.

Read Only Tables: 

In Oracle 11g, a table can be set READ ONLY mode to restrict write operations on the table. A table can be altered to toggle over READ ONLY and READ WRITE modes.

NEW Data Types and Invisible Indexes: Oracle 11g has designed a new data type SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE keeping in view the hardware requirements and expectations with an Integer value. They are compatible with the native compilation feature of Oracle 11g, which makes supports their faster implementation.

An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE or using INDEX hint. Making an index invisible is an alternative to making it unusable. It can be converted to VISIBLE mode for auto consideration by the optimizer

Using Virtual Columns: 

Oracle 11g allows you to create a "virtual column", an empty column that contains a function upon other table columns (the function itself is stored in the data dictionary). They show same behaviour as other columns in the table in terms of indexing and statistics. 

LISTAGG Analytic Functions and REGEXP_COUNT: Oracle 11g has introduced. LISTAGG analytic function to aggregate the result set in multiple rows into one single column.
 Oracle 11g introduced REGEXP_COUNT in regular expressions, used to count the occurrence of a character or string expression in another string.

SGA Result Cache: Oracle 11g has introduced a new component in SGA - Shared Pool as Result Cache to retain result-sets of SQL queries and PL/SQL functions results. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the result set. 

Pragma Inline and Direct Sequence Assignment: A new pragma PRAGMA INLINE has been introduced to specify whether a subprogram call has to be inclines’ or not. IN lining replaces a subprogram call with a copy of the called subprogram
Prior to Oracle 11g, sequence assignment to a number variable could be done through a SELECT statement only. From oracle 11g we can directly assign a sequence value to a PL/SQL variable providing greater simplicity.

Calling Functions and Recursive Subquery Factoring: In Oracle 11g, functions can now be called using Named, Positional and Mixed notation while calling from SQL SELECT statement. Version 11g release 2 introduced recursive subquery factoring or the recursive with clause. This is an extension to the SQL syntax with which you can do recursive/hierarchical queries.