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]
ALTER TABLE EMPLOYEES
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
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]
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]
ALTER SYSTEM SET RESULT_CACHE_MODE = MANUAL;
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]
SELECT /*+ result_cache */ DEPARTMENT_ID, MAX(SALARY),
FROM EMPLOYEESGROUP 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
SET SERVEROUT ON
DECLAREL_VAR SIMPLE_INTEGER := 2147483646;
BEGINWHILE (L_VAR < 10)
LOOP
L_VAR := L_VAR + 1
DBMS_OUTPUT.PUT_LINE(L_VAR);
END LOOP;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
WITH C AS
(SELECT ‘Sachin Tendulkar’ STARS FROM DUAL UNION ALL
SELECT ‘Virender Sehwag’ STARS FROM DUAL UNION ALL
SELECT ‘Gautam Gambhir’ STARS FROM DUAL UNION ALL
SELECT ‘MS Dhoni’ STARS FROM DUAL UNION ALL
SELECT ‘Yuvraj Singh’ STARS FROM DUAL)
SELECT STARS, REGEXP_COUNT(STARS, ‘s’, 1, ‘i’) S FROM C;
STARS S
--------- --Sachin Tendulkar 1Virender Sehwag 1Gautam Gambhir 0MS Dhoni 1Yuvraj 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]
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]
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)
);
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]
LISTAGG - LISTAGG (measure_expr [, 'delimiter_expr']) WITHIN GROUP (ORDER
BY clause) [OVER PARTITION BYclause]
Example:
Aggregate the employee name working in a department
NTH_VALUE - NTH_VALUE (measure_expr,n)[FROM {FIRST | LAST}]
[{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.
DECLAREL_ID NUMBER;BEGINL_ID:= TEST_SEQ.NEXTVAL;
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]
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]
SELECT *
FROM EMPLOYEESFOR 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]
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
