Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

SQL New Features In Oracle 9i

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

Related articles.

Explicitly Named Indexes On Keys

In Oracle 9i 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 Oracle 9i 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

This entry about the MERGE statement is now in a separate article here.

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 AS empid, 
       hire_date AS hiredate,
       salary AS sal,
       manager_id AS 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)
  WHEN 1=1 THEN
    INTO full_history VALUES(empid,mgr,sysdate)
SELECT employee_id AS empid, 
       hire_date AS hiredate,
       salary AS sal,
       manager_id AS 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_not_99 VALUES(deptid, hiredate)
SELECT department_id AS deptid,
       SUM(salary) AS sal,
       MAX(hire_date) AS hiredate
FROM   employees
GROUP BY department_id;

The restrictions on multitable insertss are:

External Tables

A more complete description of external tables can be found here.

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 more information see:

Hope this helps. Regards Tim...

Back to the Top.