Oracle 11g SQL New Features

Saurabh Gupta

In continuation to my last tutorial on PL/SQL enhancements, I have compiled the list of enhancement pertaining to SQL. Similar to PL/SQL, Oracle had done considerable enhancements in SQL to raise performance level from instrumental to advisory. In the tutorial, we shall go through the performance oriented features, fresh additions and language enhancements.

Performance improvements

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] 
  1. ALTER TABLE EMPLOYEES
  2. ADD EMP_STATUS VARCHAR2(1) DEFAULT ‘D’ NOT NULL

The new feature surely reduces the overhead of UPDATE statement.

The table to be altered must not be a temporary table, object table or Index organized table and also it must not contain LOB columns.

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 
  1. CREATE OR REPLACE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME) INVISIBLE
Syntax [1b]: ALTER INDEX command to toggle the visibility mode 
  1. ALTER INDEX INDEX_NAME [VISIBLE | INVISIBLE]

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.

RESULT_CACHE_MODE parameter specifies the applicability of result cache feature in SQL queries. It accepts two admissible values namely, MANUAL and FORCE. It can be set at system or session level using ALTER command as in Example Code [2a]

Example Code [2a] 
  1. ALTER SYSTEM SET RESULT_CACHE_MODE = MANUAL;
  2. ALTER SESSION SET RESULT_CACHE_MODE = 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. Its usage is depicted in the below Example Code [2b]. Apart from this parameter, there are RESULT_CACHE_MAX_SIZE, RESULT_CACHE_MAX_RESULT, and RESULT_CACHE_REMOTE_ORIENTATION are also set for server cache feature implementation.

Example Code [2b] 
  1. SELECT /*+ result_cache */ DEPARTMENT_ID, MAX(SALARY), 
  2. FROM EMPLOYEES
  3. GROUP BY DEPARTMENT_ID;

New Language features

a. Fresh Additions

1. New data types: SIMPLE_INTEGER, SIMPLE_FLOAT, SIMPLE_DOUBLE

Oracle 11g adds a new set of PL/SQL data types which are well compatible with real native compilation feature of 11g. Due to their fixed synchronization with the hardware, they provide faster arithmetic operations.

The new set of PL/SQL data type includes SIMPLE_INTEGER, SIMPLE_FLOAT, SIMPLE_DOUBLE to support binary integer and floating point values in applications.

A variable declared as SIMPLE_INTEGER doesn’t pass through the not null and value overflow checks followed by the server. Its value ranges from -2147483648 to 2147483648, same as that of PLS_INTEGER is Oracle 10g.

Example Code [3]: Demonstration of Overflow check in SIMPLE_INTEGER 
  1. SET SERVEROUT ON
  2. DECLARE
  3. L_VAR SIMPLE_INTEGER := 2147483646;
  4. BEGIN
  5. WHILE (L_VAR < 10)
  6. LOOP
  7. L_VAR := L_VAR + 1
  8. DBMS_OUTPUT.PUT_LINE(L_VAR);
  9. END LOOP;
  10. END;

2147483647
-2147483648

2. Regular Expressions

REGEXP_COUNT is the new regular expression function, inducted in Oracle 11g release. Basically, the function came as language support enhancements because all major programming languages support functions which count the character or string appearances in a given string.

Example Code [3]: Demonstrate REGEXP_COUNT usage 
  1. WITH C AS
  2. (SELECT ‘Sachin Tendulkar’ STARS FROM DUAL UNION ALL
  3. SELECT ‘Virender Sehwag’ STARS FROM DUAL UNION ALL
  4. SELECT ‘Gautam Gambhir’ STARS FROM DUAL UNION ALL
  5. SELECT ‘MS Dhoni’ STARS FROM DUAL UNION ALL
  6. SELECT ‘Yuvraj Singh’ STARS FROM DUAL)
  7. SELECT STARS, REGEXP_COUNT(STARS, ‘s’, 1, ‘i’) S FROM C;
  8.  
  9. STARS     S
  10. --------- --
  11. Sachin Tendulkar 1
  12. Virender Sehwag 1
  13. Gautam Gambhir 0
  14. MS Dhoni 1
  15. Yuvraj Singh 1

3. 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] 
  1. ALTER TABLE [TABLE NAME] [READ ONLY | READ WRITE]

4. 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] 
  1. COLUMN [datatype] [GENERATED ALWAYS] AS ( <colu mn_expression> ) [VIRTUAL] [( 
  2. 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. 

  1. CREATE TABLE HIST_STUDENT(
  2. ST_ROLLNO NUMBER,
  3. ST_MARKS NUMBER,
  4. ST_MAX_MARKS NUMBER,
  5. ST_PERC NUMBER AS ((ST_MARKS/ST_MAX_MARKS)*100)
  6. );

5. Partitioning

Since its induction in Database family, partitioning has been always been an eye catcher for the database professionals. This is because of its comprehensive results in application performance and logical support to data warehousing. Oracle 11g has made considerable additions in Partitioning techniques. The fresh features in Partitioning include the following:

  • Interval partitioning
  • Extended composite partitioning
  • Reference partitioning
  • System partitioning
  • System-managed domain indexes

6. 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.

7. 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] 
  1. LISTAGG - LISTAGG (measure_expr [, 'delimiter_expr']) WITHIN GROUP (ORDER 
  2. BY clause) [OVER PARTITION BYclause]
Example:

Aggregate the employee name working in a department

  1. NTH_VALUE - NTH_VALUE (measure_expr,n)[FROM {FIRST | LAST}]
  2. [{RESPECT | IGNORE} NULLS ]OVER (analytic_clause)
Example:

Retrieve 2nd highest Salary in each department

b. Enhancements to improve upon work around solutions and enhance usability

1. Sequence usage

In Oracle 11g, sequence value assignment can be used as a PL/SQL construct. Earlier, sequence value was used to be fetched through a SELECT statement, which added up the overhead of context switch between SQL and PL/SQL engines.

Example Code [5]:

Demonstration of Sequence assignment as a PL/SQL construct. 

  1. DECLARE
  2. L_ID NUMBER;
  3. BEGIN
  4. L_ID:= TEST_SEQ.NEXTVAL;
  5. END;

2. Named and Mixed notation

Oracle 11g SQL now supports the function call with both named and positioned parameters. This is known as mixed notation of a function call in an SQL statement.

A function F_PRIMECOUNT in Example Code [6] accepts two parameters to count the numbers prime numbers between the ranges.

Example Code [6] 
  1. SQL> SELECT F_PRIMECOUNT (P_A => 10, 20) FROM DUAL;

3. 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] 
  1. SELECT *
  2. FROM EMPLOYEES
  3. FOR UPDATE SKIP LOCKED

4. DATABASE_ROLE constant for SYS_CONTEXT

Oracle 11g added a new context constant to identify the current database role on the running server. The database role can be PRIMARY, PHYSICAL STANDBY, LOGICAL STANDBY, and SNAPSHOT STANDBY.

Example Code [8] 
  1. SELECT sys_context('USERENV', 'DATABASE_ROLE') FROM dual;

5. NO_DATA_NEEDED exception

Oracle 11g adopted a generic exception NO_DATA_NEEDED for parallel access and pipelined table functions. Prior to 11g, this was identified by the error number ORA-06528

[catlist id=193].

Related posts