Oracle 11g New Features

• The PL/SQL in oracle 11g is implemented with the new object oriented concepts.

• In oracle 11g the code compilation has been changed from interpreted to Native. i.e. It is no more dependent on the third party c compiler instead PL/SQL source code is used to generate the native code. This helps in faster execution.

• Database passwords are made case sensitive with oracle 11g.

• Read only tables: A table can be set to read only mode to restrict write operations on the table. This can be altered using the ‘alter table’ command.

• Invisible indexes: Oracle 11g introduces invisible indexes for query performance testing. This can be used to test the performance of the index on the query using either index hint or altering the session to use the invisible indexes. If the index is improving the performance then it can be made as visible index.

• Virtual Columns: This is a column whose value is derived from the other actual columns in the table. This shows the same behavior as other columns when it comes for indexing and gathering statistics. Oracle does not support LONG and RAW values for virtual columns.

• Result cache: A new SGA component (in Shared pool) in oracle 11g i.e. result cache to store the result of SQL and PL/SQL for the same input set. This can be achieved in PL/SQL block by including additional RESULT_CACHE clause in the function definition.

• Compound trigger: In compound trigger logic all the timing point logics are clubbed into one single body. This can be used to avoid the mutating trigger problem.

• Set the triggering order by ‘FOLLOWS’ clause: Using ‘FOLLOWS’ clause the order of the trigger execution set on the same timing point can be configured. Means the developer can decide which trigger has to be executed first and the next one and so on.

• Oracle trigger can be created in DISABLE mode in oracle 11g. Prior to oracle 11g the trigger must be created in ENABLED mode only.

• Additional partitioning strategies in oracle 11g:

  § Extended composite partitioning.

  § Virtual column based partition

  § Interval partitioning

  § REF partition

• Program inlining (PLSQL_OPTIMIZE_LEVEL) : It refers to replacing a program call with the copy of the program. There were two admissible values for the parameter before 3^rd level has been introduced in oracle 11g. A new pragma parameter PRAGMA INLINE has been introduced.

• REGEXP_COUNT: This new feature counts the occurrence of a character or string in another string.

• LISTAGG: This analytical function aggregates the result set in multiple rows into single column.

• Oracle 11g allows providing a default value during the table alteration.

• Oracle 11g allows direct sequence assignment to variables. Prior to oracle 11g it was done through select statement.

• A new clause ‘CONTINUE’ has been introduced in oracle 11g to skip the records which are not meeting the condition.