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:
- 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 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
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.
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.
This entry about the MERGE statement is now in a separate article here.
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:
- 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.
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:
- External Tables
- MERGE Statement
- MERGE Statement Enhancements in Oracle Database 10g
- Oracle9i SQL Reference
Hope this helps. Regards Tim...