Oracle 11g New Features

Oracle 11g New Features

  1. a. Alter table to add columns with default value

In Oracle 11g, we can alter a table to add a column with a default value. Earlier, a developer used to update the value for the new column using UPDATE statement. Refer the example code [1].

Example Code [1]

ALTER TABLE EMPLOYEES

ADD EMP_STATUS VARCHAR2(1) DEFAULT ā€˜Dā€™ NOT NULL

The new feature surely reduces the overhead of UPDATE statement.

  1. b. Invisible Indexes

An index can now be created in invisible mode. A new initialization parameter OPTIMIZER_USE_INVISIBLE_INDEXES has been introduced to indicate Oracle optimizer to use invisible indexes or not. It can be set at system level as TRUE or FALSE.

Syntax [1a]: Create an Invisible Index

CREATE OR REPLACE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME) INVISIBLE

Syntax [1b]: ALTER INDEX command to toggle the visibility mode

ALTER INDEX INDEX_NAME [VISIBLE | INVISIBLE]

  1. c. SQL Result cache

Oracle 11g server memory can now cache the SQL query results using RESULT_CACHE hint. In the first execution of an SQL statement using result cache hint, Oracle caches the result in server cache. On the subsequent executions of the same query for the same set of input values (if exist), oracle fetches the result from the cache instead of re-executing the query.

SELECT /*+ result_cache */ DEPARTMENT_ID, MAX(SALARY),

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID;

  1. Read Only tables

In Oracle 11g, a table can be set as READ ONLY. In READ ONLY mode, a table can only be queried; DML and DDL (Truncate and Alter) operations are restricted on such tables. At any point of time in a session, the table mode can be switched from READ WRITE or READ ONLY mode and vice versa. Note that the table can be created in READ WRITE mode only.

Syntax [2]

ALTER TABLE [TABLE NAME] [READ ONLY | READ WRITE]

  1. Virtual columns

Oracle 11g allows database developers to create virtual columns in a table, whose value is always derived through an expression. The expression must use the columns of the same table or a deterministic function. Virtual Columns act as normal columns during indexing, and partitioning. As of now, virtual column must belong to primitive data type family and not to LOBs or collection data types.

Syntax [3]

COLUMN [datatype] [GENERATED ALWAYS] AS ( <colu mn_expression> ) [VIRTUAL] [(

inline_constraint [,…] )]

Example Code [4]:

ST_PERC column in HIST_STUDENT table is a virtual column, which always generates using values of ST_MARKS and ST_MAX_MARKS.

CREATE TABLE HIST_STUDENT(

ST_ROLLNO NUMBER,

ST_MARKS NUMBER,

ST_MAX_MARKS NUMBER,

ST_PERC NUMBER AS ((ST_MARKS/ST_MAX_MARKS)*100)

);

  1. IGNORE_ROW_ON_DUPKEY_INDEX hint

During a direct loading Insert process, unique key conflict in data can result into process failure. To make this check passive during the process, Oracle 11g furnishes a new hint IGNORE_ROW_ON_DUPKEY_INDEX.

  1. Analytic functions ā€“ NTH_VALUE, LISTAGG

Oracle 11g inducts two new functions in analytic family i.e. NTH_VALUE and LISTAGG.

LISTAGG aggregates a column values in a single row format. NTH_VALUE is an extended format of FIRST_VALUE and LAST_VALUE functions to get a random row from a grouped result set.

Syntax [4]

LISTAGG – LISTAGG (measure_expr [, ‘delimiter_expr’]) WITHIN GROUP (ORDER

BY clause) [OVER PARTITION BYclause]

Example:

Aggregate the employee name working in a department

11g.png

NTH_VALUE – NTH_VALUE (measure_expr,n)[FROM {FIRST | LAST}]

[{RESPECT | IGNORE} NULLS ]OVER (analytic_clause)

Example:

Retrieve 2nd highest Salary in each department

11g2.png

  1. Skip locked

This is a utility enhancement in Oracle 11g, which enables the database users to query the records from a table, which are not part of any running transaction in any of the database session. The records which are locked are skipped in the final result set, retrieved by the SELECT query.

The SQL statement in Example Code [7] queries the unlocked records from EMPLOYEES table

Example Code [7]

SELECT *

FROM EMPLOYEES

FOR UPDATE SKIP LOCKED

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s