9I SQL FEATURES

NULL2 Function–
NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG.
If the data types of expr2 and expr3 are different, then Oracle converts expr3 to the data type of expr2 before comparing them unless expr3 is a null constant. In that case, a data type conversion is not necessary.
The data type of the return value is always the same as the data type of expr2, unless expr2 is character data, in which case the return value's data type is VARCHAR2.

NULLIF (expr1, expr2) –Returns NULL if the first argument is equal to the second, otherwise returns the first argument.

COALESCE (expr1, expr2, expr3, ...) –Returns the first non-null argument.

SELECT... FOR UPDATE Enhancements
Selecting a record for update that is already locked causes the current session to hang indefinitely until the lock is released. If this situation is unacceptable the NOWAIT keyword can be used to instantly return an error if the record is locked. Oracle9i adds more flexibility by allowing the programmer to specify a maximum time limit to wait for a lock before returning an error. This gets round the problem of indefinite waits, but reduces the chances of lock errors being returned:

SELECT * FROM employees WHERE empno = 20 FOR UPDATE WAIT 30;

Merge Statement

The MERGE statement can be used to conditionally insert or update data depending on its presence. This method reduces table scans and can perform the operation in parallel. Consider the following example where data from the HR_RECORDS table is merged into the EMPLOYEES table:

MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);

Explicitly Named Indexes On Keys

In Oracle9i the index used to support primary and unique keys can be defined independently of the constraint itself by using the CREATE INDEX syntax within the USING UNDEX clause of the CREATE TABLE statement:

CREATE TABLE employees
(
Empno NUMBER (6),
Name VARCHAR2 (30),
Deptno NUMBER (2),
CONSTRAINT emp_pk primary key (empno)
USING INDEX
(CREATE INDEX emp_pk_idx ON employee (empno))
);

The constraint can subsequently be dropped without dropping the index using either syntax:

ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;
ALTER TABLE employees DROP CONSTRAINT empno_pk;

Share Locks On Unindexed FKs
In previous versions a share lock was issued on the entire child table while the parent table was being updated if the foreign key between them was unindexed. This had the affect of preventing DML operations on the child table until the parent table transaction was complete.

In Oracle9i this situation has been altered such that a table level share lock is issued and instantly released. This action allows Oracle to check that there are no pending changes on the child table, but the instant release means that DML can resume almost instantly once the parent table update has initiated. If multiple keys are updated Oracle issues a share lock and release on the child table for each row.

PK Lookup During FK Insertion

During insertions foreign key values are checked against the primary keys of referenced tables. This process is optimized in Oracle9i by caching the first 256 PK values of the referenced table on insertion of the second record of a multiple insert. The process is done on the second record to prevent the overhead of managing the cache on a single insert.

Function Based Index Enhancements

Function Based Indexes are now capable of doing an index-only scan. In previous versions this was only possible if the index creation statement explicitly prevented NULL values. Since each built-in operator knows implicitly whether it can produce null values when all it's input parameters are not null, Oracle can deduce if nulls can be produced and therefore decide if index-only scans are possible based on the columns queried using the function based index.

View Constraints

Declarative primary key, unique key and foreign key constraints can now be defined against views. The NOT NULL constraint is inherited from the base table so it cannot be declared explicitly. The constraints are not validated so they must be defined with the DISABLE NOVALIDATE clause:

CREATE VIEW Emp_view
(Id PRIMARY KEY DISABLE NOVALIDATE, firstname)
AS SELECT employee_id, first_name
FROM employees
WHERE department_id = 10;

ALTER VIEW Emp_view
ADD CONSTRAINT emp_view_unq
UNIQUE (first_name) DISABLE NOVALIDATE;