New Features in Oracle :

Oracle has announced the new version Oracle and announced the new features in that version. Oracle has added around 500 Oracle New Features for DBA.(exact number is 482 features).The Most exciting features are DBA related features and simplicity and well structured DBA tasks. Another important Oracle 11 G New Feature is related to Performance Tuning and optimization. Oracle has added considerable enhancements in SQL to raise the performance of SQL Query .In this article i will explain the new features in Oracle with examples.I am writing this article to give you idea about the Oracle 11 G New Features with examples. These New Features are important for programmers as well as DBA to use it for different purpose.

CLICK HERE TO GET 15 IMPORTANT COMPLEX SQL QUERIES..

• Following are some Oracle 11 G New Features for DBA :

1.ALTER Table with Default values:

In Oracle previous version there is no facility to alter the table with adding the default values in it.In Oracle 11 G ,Oracle provides the facility to alter the table with adding default values in the specified column.So developer efforts of updating the column values has been reduced.

Scenario:

Add the New column in Employee table in which Employee_Status is ‘Working’

Query:

Alter table Employee

Add Empoyee_Status varchar2(20)  Default ‘Working’ not null;

2.New Table Datatypes added named ‘simple_integer’,’simple_float’,’simple_double’:

In Oracle 11 G new datatype is introduced which is simple_integer datatype.This simple_integer datatype is always not null.This datatype is faster than Integer and PLS_INTEGER datatype in SQL.These all datatypes are subtypes of NLS_INTEGER,INS_FLOAT,INS_DOUBLE datatype.

Simple_Integer is nothing but PLS Integer with not null constraint

Example:

CREATE OR REPLACE PROCEDURE P_Simple_Integer_test AS
v_start NUMBER;
v_loops NUMBER := 10000000;
v_pls_integer PLS_INTEGER := 0; —PLS integer declaration
v_pls_integer1 PLS_INTEGER := 1;
v_simple_integer SIMPLE_INTEGER := 0;
v_simple_integer1 SIMPLE_INTEGER := 1; –Simple_integer declaration
BEGIN

v_start := DBMS_UTILITY.get_time; –We are using the database utility get_time

FOR i IN 1 .. v_loops LOOP
v_pls_integer := v_pls_integer + v_pls_integer1; —Summation of PLS integers and check the time
END LOOP;

DBMS_OUTPUT.put_line(‘PLS_INTEGER: ‘ || (DBMS_UTILITY.get_time – v_start) || ‘ hsecs’); —time for PLS integer

v_start := DBMS_UTILITY.get_time;

FOR i IN 1 .. v_loops LOOP
v_simple_integer := v_simple_integer + v_simple_integer1; —Summation of simple_integer integers and check the time
END LOOP;

DBMS_OUTPUT.put_line(‘SIMPLE_INTEGER: ‘ || (DBMS_UTILITY.get_time – v_start) || ‘ hsecs’);

END P_Simple_Integer_test;

Output:

PLS_INTEGER: 19 hsecs
SIMPLE_INTEGER: 12 hsecs

3.Invisible Indexes:

Indexes can be created in Invisible mode in Oracle 11 G.The new parameter has been introduced named OPTIMIZER_USE_INVISIBLE_INDEXES and optimizer uses the same parameter if it sets to ‘TRUE’ or ‘FALSE’.This parameter sets to true or false at system level.

Syntax:

Create or replace index Index_name on Table_name(Column_name) Invisible;

Alter index:

ALTER Index Indexname set Invisible=[TRUE|FALSE];

4.SQL /*result_cache*/ Hint:

The result data is cached in the data buffer using this hint.This hint improves the performance of query drastically as the result data is stored in data buffer the data is been fetched directly from the buffer location.The RESULT_CACHE_MODE parameter specifies the applicability of result cache feature in SQL queries.It accepts two admissible values ‘Manual’ and ‘Force’.For MANUAL mode, RESULT_CACHE hint must be specified with the SQL statements to use the feature. In FORCE mode, server enables the caching feature with all the SQL statements.

Syntax:

ALTER SYSTEM SET RESULT_CACHE_MODE=’MANUAL’;

ALTER SYSTEM SET RESULT_CACHE_MODE=’FORCE’;

Example of Hint:

Select /*+result_cache*/ Department_ID,Dept_num

from Department;

5.Read Only Mode of Tables:

Oracle 11G provides the facility to change the mode of the table to Read only mode.In read_only mode table can only be queried.The DML and DDL (Truncate and Alter) options are restricted in read_only mode.At any point of time the table mode has been changed from Read mode to write mode.

Syntax:

ALTER TABLE TABLE_NAME [READ ONLY|READ WRITE];

Query to Find Second highest salary of Employee with explaination..

6.Regular Expression (REGEXP_COUNT function):

REGEXP_COUNT is the new regular expression function, introduced in Oracle 11g release. This function is introduced for language support which is used to count the character or string appearances in a given string.

Example:
SELECT ‘Rohit Shinde’ Programmer,REGEXP_COUNT(Programmer, ‘s’, 1, ‘i’) ‘Expression_Output’ from dual;

Output:

Programmer   Expression_Output

Rohit Shinde   1

7.Virtual Columns:

Oracle 11 G provides new feature of virtual columns which allows user to provide the column virtually where value is specified in Expression itself.Virtual columns acts like a normal columns during indexing and partitioning.Virtual columns belongs to LOBs or collection datatypes.

Example:

CREATE TABLE Employee(
Employee_num NUMBER,
Salary NUMBER,
Dearness_allowance NUMBER,
Total_salary NUMBER AS ((Salary+Dearness_allowance)    –this column is virtual column
);

8.Fully Automatic Tuning:

In Previous version Oracle 10 G only advices for tuning using SQL Tuning Adviser.In Oracle 11 G provides facility of automatically applying the SQL profile to improve the performance of the query. The performance comparisons are done by a new administrative task during a user-specified maintenance window.

9.Partitioning:

The partitioning plays an important role in database performance and every SQL professional wants to do the perfect partitioning to improve the performance of SQL queries.There are following new features added in SQL partitioning:

1.System Partitioning

2.Reference Partitioning

3.Interval Partitioning

4.Extended Composite Partitioning

10.Analytical Functions-NTH,LISTAGG:

Oracle 11 G specifies two new aggregate functions:

1.LISTAGG:
LISTAGG aggregates a column values in a single row format.

2.NTH_VALUE:

NTH_VALUE is an extended format of FIRST_VALUE and LAST_VALUE functions to get a random row from a grouped result set.

Syntax:

LISTAGG – LISTAGG (measure_expr [, ‘delimiter_expr’]) WITHIN GROUP (ORDER
BY clause) [OVER PARTITION BYclause]

Real Life Scenario:

Kindly consider following table.We need to find aggregate results departmentwise in form of rows.

Employee table:

Query used:

select Department_ID,listagg(Employee_name,’,’) within group(order by Employee_name) as Employee_name from Employee group by Department_id;

Output:

So  listagg function is very useful function for converting columns in to single row format.

11.Skip locked utility:

Oracle 11 G provides the new utility where the records are directly fetched from table.The records from running transactions are not considered.

“Skip locked utility doesnot fetches the locked records in transaction”

Example:

select * from Employee

for update

Skip locked;

12.XML SQL queries – Oracle11g will support query mechanisms for XML including XQuery and SQL XML, emerging standards for querying XML data stored inside tables.

Here I have tried to cover the Newly added features in Oracle 11G. Please do let me know if any other information needs to be added in this. Please comment below if you needed extra information or suggestions if any..

HOME