SQL New Features In Oracle9i

There are a large number of SQL enhancements in Oracle9i. Here is a subset of them that are relevant for the Oracle9i Database: New Features For Administrators OCP exam:

Explicitly Named Indexes On Keys
Share Locks On Unindexed FKs
PK Lookup During FK Insertion
View Constraints
Function Based Index Enhancements
MERGE Statement
Multitable Inserts
External Tables
SELECT .. FOR UPDATE Enhancements

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 INDEX clause of the CREATE TABLE statement:

CREATE TABLE employees
(
  empno NUMBER(6),
  name VARCHAR2(30),
  dept_no NUMBER(2),
  CONSTRAINT emp_pk primary key(empno)
    USING INDEX
    (CREATE INDEX emp_pk_idx ON employees(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.

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;

Function Based Index Enhancements

Function Based Indexes are now capable of doing an index-only scan. In previous versions this was only possible if NULL values were explicitly prevented by the index creation statement. 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.

MERGE Statement

The MERGE statement can be used to conditionally insert or update data depending on it's 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);

Multitable Inserts

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format:

-- Unconditional insert into ALL tables

INSERT ALL
  INTO sal_history VALUES(empid,hiredate,sal)
  INTO mgr_history VALUES(empid,mgr,sysdate) 
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
  FROM employees WHERE employee_id > 200;

-- Pivoting insert to split non-relational data
INSERT ALL
  INTO Sales_info VALUES (employee_id,week_id,sales_MON)
  INTO Sales_info VALUES (employee_id,week_id,sales_TUE)
  INTO Sales_info VALUES (employee_id,week_id,sales_WED)
  INTO Sales_info VALUES (employee_id,week_id,sales_THUR)
  INTO Sales_info VALUES (employee_id,week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
    sales_WED, sales_THUR,sales_FRI 
FROM Sales_source_data; 

-- Conditionally insert into ALL tables
INSERT ALL
  WHEN SAL>10000 THEN
    INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  WHEN MGR>200 THEN
    INTO mgr_history VALUES(EMPID,MGR,SYSDATE)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
  FROM employees WHERE employee_id > 200;

-- Insert into the FIRST table with a matching condition
INSERT FIRST
  WHEN SAL > 25000  THEN
    INTO special_sal VALUES(DEPTID,SAL)
  WHEN HIREDATE like ('%00%') THEN
    INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
  WHEN HIREDATE like ('%99%') THEN 
    INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
  ELSE
    INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL,
    MAX(hire_date) HIREDATE
  FROM employees GROUP BY department_id;

The restrictions on multitable INSERTs are:

Multitable inserts can only be performed on tables, not on views or materialized views.
You cannot perform a multitable insert via a DB link.
You cannot perform multitable inserts into nested tables.
The sum of all the INTO columns cannot exceed 999.
Sequences cannot be used in the subquery of the multitable insert statement.

External Tables

External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader. No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.

Oracle uses the CREATE TABLE..ORGANIZATION EXTERNAL syntax to store metadata about the external table:

-- Create directory object to data location
CREATE DIRECTORY EXT_TABLES AS 'C:\Oracle\External_Tables';

-- Create the external table
-- Files must exist in the specified location
CREATE TABLE employees_ext
  (empno NUMBER(8), first_name VARCHAR2(30), last_name VARCHAR2(30))
  ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_tables
      ACCESS PARAMETERS
      (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
      )
      LOCATION ('employees1.txt','employees2.txt')
    )
  PARALLEL 5
  REJECT LIMIT 200;

-- Query the table
SELECT * FROM employees_ext;

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;

For further information see:

Oracle9i SQL Reference

Hope this helps. Regards Tim...

Back to the Top.