Oracle Function Based Indexes

A function-based index is an index built on an expression. It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variety of ways in which you can access data.

Notes:

• You must set the QUERY_REWRITE_ENABLED initialization parameter to TRUE.
• The index is more effective if you gather statistics for the table or schema, using the procedures in the DBMS_STATS package.
• The index cannot contain any null values. Either make sure the appropriate columns contain no null values, or use the NVL function in the index expression to substitute some other value for nulls.

The expression indexed by a function-based index can be an arithmetic expression or an expression that contains a PL/SQL function, package function, C callout, or SQL function. Function-based indexes also support linguistic sorts based on collation keys, efficient linguistic collation of SQL statements, and case-insensitive sorts.

Like other indexes, function-based indexes improve query performance. For example, if you need to access a computationally complex expression often, then you can store it in an index. Then when you need to access the expression, it is already computed. 

Function-based indexes have all of the same properties as indexes on columns. However, unlike indexes on columns which can be used by both cost-based and rule-based optimization, function-based indexes can be used by only by cost-based optimization. 

Advantages of Function-Based Indexes

Function-based indexes:

• Increase the number of situations where the optimizer can perform a range scan instead of a full table scan. For example, consider the expression in this WHERE clause:

  
  CREATE INDEX Idx ON Example_tab(Column_a + Column_b);
  
  SELECT * FROM Example_tab WHERE Column_a + Column_b < 10; 
  

  The optimizer can use a range scan for this query because the index is built on (column_a + column_b). Range scans typically produce fast response times if the predicate selects less than 15% of the rows of a large table. The optimizer can estimate how many rows are selected by expressions more accurately if the expressions are materialized in a function-based index. (Expressions of function-based indexes are represented as virtual columns andANALYZE can build histograms on such columns.)
• Precompute the value of a computationally intensive function and store it in the index. An index can store computationally intensive expression that you access often. When you need to access a value, it is already computed, greatly improving query execution performance.
• Create indexes on object columns and REF columns. Methods that describe objects can be used as functions on which to build indexes. For example, you can use the MAP method to build indexes on an object type column.
• Create more powerful sorts. You can perform case-insensitive sorts with the UPPER and LOWER functions, descending order sorts with the DESCkeyword, and linguistic-based sorts with the NLSSORT function.

Another function-based index calls the object method distance_from_equator for each city in the table. The method is applied to the object column Reg_Obj. A query could use this index to quickly find cities that are more than 1000 miles from the equator:

CREATE INDEX Distance_index 
ON Weatherdata_tab (Distance_from_equator (Reg_obj));

SELECT * FROM Weatherdata_tab 
WHERE (Distance_from_equator (Reg_Obj)) > '1000';

Another index stores the temperature delta and the maximum temperature. The result of the delta is sorted in descending order. A query could use this index to quickly find table rows where the temperature delta is less than 20 and the maximum temperature is greater than 75.

CREATE INDEX compare_index 
ON Weatherdata_tab ((Maxtemp - Mintemp) DESC, Maxtemp);

SELECT * FROM Weatherdata_tab
WHERE ((Maxtemp - Mintemp) < '20' AND Maxtemp > '75');

Examples of Function-Based Indexes

Example: Function-Based Index for Case-Insensitive Searches

The following command allows faster case-insensitive searches in table EMP_TAB.

CREATE INDEX Idx ON Emp_tab (UPPER(Ename));

The SELECT command uses the function-based index on UPPER(e_name) to return all of the employees with name like :KEYCOL.

SELECT * FROM Emp_tab WHERE UPPER(Ename) like :KEYCOL;

Example: Precomputing Arithmetic Expressions with a Function-Based Index

The following command computes a value for each row using columns A, B, and C, and stores the results in the index.

CREATE INDEX Idx ON Fbi_tab (A + B * (C - 1), A, B);

The SELECT statement can either use index range scan (since the expression is a prefix of index IDX) or index fast full scan (which may be preferable if the index has specified a high parallel degree).

SELECT a FROM Fbi_tab WHERE A + B * (C - 1) < 100;

Example: Function-Based Index for Language-Dependent Sorting

This example demonstrates how a function-based index can be used to sort based on the collation order for a national language. The NLSSORT function returns a sort key for each name, using the collation sequence GERMAN.

CREATE INDEX Nls_index 
    ON Nls_tab (NLSSORT(Name, 'NLS_SORT = German'));

The SELECT statement selects all of the contents of the table and orders it by NAME. The rows are ordered using the German collation sequence. The Globalization Support parameters are not needed in theSELECT statement, because in a German session, NLS_SORT is set to German and NLS_COMP is set to ANSI.

SELECT * FROM Nls_tab WHERE Name IS NOT NULL
    ORDER BY Name;

Restrictions for Function-Based Indexes

Note the following restrictions for function-based indexes:

• Only cost-based optimization can use function-based indexes. Remember to call DBMS_STATS.GATHER_TABLE_STATISTICS orDBMS_STATS.GATHER_SCHEMA_STATISTICS, for the function-based index to be effective.
• Any top-level or package-level PL/SQL functions that are used in the index expression must be declared as DETERMINISTIC. That is, they always return the same result given the same input, for example, the UPPER function. You must ensure that the subprogram really is deterministic, because Oracle Database does not check that the assertion is true.
  The following semantic rules demonstrate how to use the keyword DETERMINISTIC:
  • You can declare a top level subprogram as DETERMINISTIC.
  • You can declare a PACKAGE level subprogram as DETERMINISTIC in the PACKAGE specification but not in the PACKAGE BODY. Errors are raised ifDETERMINISTIC is used inside a PACKAGE BODY.
  • You can declare a private subprogram (declared inside another subprogram or a PACKAGE BODY) as DETERMINISTIC.
  • A DETERMINISTIC subprogram can call another subprogram whether the called program is declared as DETERMINISTIC or not.
• If you change the semantics of a DETERMINISTIC function and recompile it, then existing function-based indexes and materialized views report results for the prior version of the function. Thus, if you change the semantics of a function, you must manually rebuild any dependent function-based indexes and materialized views.
• Expressions in a function-based index cannot contain any aggregate functions. The expressions should reference only columns in a row in the table.
• You must analyze the table or index before the index is used.
• Bitmap optimizations cannot use descending indexes.
• Function-based indexes are not used when OR-expansion is done.
• The index function cannot be marked NOT NULL. To avoid a full table scan, you must ensure that the query cannot fetch null values.
• Function-based indexes cannot use expressions that return VARCHAR2 or RAW data types of unknown length from PL/SQL functions. A workaround is to limit the size of the function's output by indexing a substring of known length:
  
  

  -- INITIALS() might return 1 letter, 2 letters, 3 letters, and so on.
  -- We limit the return value to 10 characters for purposes of the index.
  CREATE INDEX func_substr_index ON
    emp_tab(substr(initials(ename),1,10);
  
  -- Call SUBSTR both when creating the index and when referencing
  -- the function in queries.
  SELECT SUBSTR(initials(ename),1,10) FROM emp_tab;

Views in Oracle

A View in Oracle and in other database systems is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used. Use the CREATE VIEW statement to define a view, which is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables. For example, if we frequently issue the following query

SELECT customerid, customername FROM customers WHERE countryid='US';

To create a view use the CREATE VIEW command as seen in this example

CREATE VIEW view_uscustomers

AS

SELECT customerid, customername FROM customers WHERE countryid='US';

This command creates a new view called view_uscustomers. Note that this command does not result in anything being actually stored in the database at all except for a data dictionary entry that defines this view. This means that every time you query this view, Oracle has to go out and execute the view and query the database data. We can query the view like this:

SELECT * FROM view_uscustomers WHERE customerid BETWEEN 100 AND 200;

And Oracle will transform the query into this:

SELECT * 

FROM (select customerid, customername from customers WHERE countryid='US') 

WHERE customerid BETWEEN 100 AND 200

Prerequisites

To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user's schema, you must have the CREATE ANY VIEW system privilege.

To create a subview, you must have the UNDER ANY VIEW system privilege or the UNDER object privilege on the superview.

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

To use the basic constructor method of an object type when creating an object view, one of the following must be true:

• The object type must belong to the same schema as the view to be created.
• You must have the EXECUTE ANY TYPE system privileges.
• You must have the EXECUTE object privilege on that object type.
  
  

Semantics

1. OR REPLACE

Specify OR REPLACE to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it.

INSTEAD OF triggers defined in the view are dropped when a view is re-created.

If any materialized views are dependent on view, then those materialized views will be marked UNUSABLE and will require a full refresh to restore them to a usable state. Invalid materialized views cannot be used by query rewrite and cannot be refreshed until they are recompiled.

FORCE

Specify FORCE if you want to create the view regardless of whether the base tables of the view or the referenced object types exist or the owner of the schema containing the view has privileges on them. These conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view.

If the view definition contains any constraints, CREATE VIEW ... FORCE will fail if the base table does not exist or the referenced object type does not exist.CREATE VIEW ... FORCE will also fail if the view definition names a constraint that does not exist.

NO FORCE

Specify NOFORCE if you want to create the view only if the base tables exist and the owner of the schema containing the view has privileges on them. This is the default.

Benefits of using Views:

Commonality of code being used. Since a view is based on one common set of SQL, this means that when it is called it’s less likely to require parsing. This is because the basic underlying SQL that is called is always the same. However, since you can add additional where clauses when calling a view, you still need to use bind variables. Additional where clauses without a bind variable can still cause a hard parse!

Security. Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to. Views have long been used to hide the tables that actually contain the data you are querying. Also, views can be used to restrict the columns that a given user has access to. Using views for security on less complex databases is probably not a bad thing. As databases become more complex, this solution becomes harder to scale and other solutions will be needed.

* Predicate pushing. Oracle supports pushing of predicates into a given view. Assume we had a set of layered views, like this:

-- View One

CREATE VIEW vw_layer_one

AS SELECT * FROM emp;

-- view two

CREATE VIEW vw_layer_two_dept_100

AS SELECT * FROM vw_layer_one

WHERE deptno=100;

Then assume we issued this query:

SELECT * FROM vw_layer_two_dept_100

WHERE empid=100;

The predicate in this statement is the where empid=100 statement. You may have one of tens or even hundreds of predicates (if you have hundreds, we don’t want to be supporting your SQL code!). Oracle will, in many cases, push those predicates down into the views being called. Thus, Oracle will transform the VW_LAYER_ONE view into a SQL statement that looks like this:

CREATE VIEW vw_layer_one

AS SELECT * FROM emp

WHERE deptno=100

AND empid=100;

Note that both the predicate from view two (where deptno=100) and the predicate from the SQL statement being executed (where empid=100) are pushed down into the final view that is executed. This can have significant performance benefits because now the bottom view can possibly use an index if one exists on deptno and/or empid.

Predicate pushing has a number of restrictions that are beyond the scope of this book, but you can find them in the Oracle documentation. Also, any predicate pushing may result in a hard parse of the underlying SQL that is executed. Hence, it is important to make sure you use bind variables instead of literals in SQL code calling views. Thus, our SQL should look something like this instead for best performance:

SELECT * FROM vw_layer_two_dept_100

WHERE empid=:b100;

http://docs.oracle.com/cd/B28359_01/server.111/b28310/views001.htm

Autonomous Transactions

In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime.

Autonomous transactions allow you to leave the context of the calling transaction, perform an independent transaction, and return to the calling transaction without affecting it's state. The autonomous transaction has no link to the calling transaction, so only committed data can be shared by both transactions.

The following types of PL/SQL blocks can be defined as autonomous transactions:

• Stored procedures and functions.
• Local procedures and functions defined in a PL/SQL declaration block.
• Packaged procedures and functions.
• Type methods.
• Top-level anonymous blocks.

The easiest way to understand autonomous transactions is to see them in action. To do this, we create a test table and populate it with two rows. Notice that the data is not committed.

CREATE TABLE at_test (
  id           NUMBER       NOT NULL,
  description  VARCHAR2(50) NOT NULL
);

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2

2 rows selected.

SQL>

Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO at_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

10 rows selected.

SQL>

As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.

ROLLBACK;
SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

8 rows selected.

SQL>

The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session. As a result rollback was still able to affect the DML issued by the current statement.

Autonomous transactions are commonly used by error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction. For example, the following table holds basic error messages.

CREATE TABLE error_logs (
  id             NUMBER(10)     NOT NULL,
  log_timestamp  TIMESTAMP      NOT NULL,
  error_message  VARCHAR2(4000),
  CONSTRAINT error_logs_pk PRIMARY KEY (id)
);

CREATE SEQUENCE error_logs_seq;

We define a procedure to log error messages as an autonomous transaction.

CREATE OR REPLACE PROCEDURE log_errors (p_error_message  IN  VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_logs (id, log_timestamp, error_message)
  VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
  COMMIT;
END;
/

The following code forces an error, which is trapped and logged.

BEGIN
  INSERT INTO at_test (id, description)
  VALUES (998, 'Description for 998');

  -- Force invalid insert.
  INSERT INTO at_test (id, description)
  VALUES (999, NULL);
EXCEPTION
  WHEN OTHERS THEN
    log_errors (p_error_message => SQLERRM);
    ROLLBACK;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test WHERE id >= 998;

no rows selected

SELECT * FROM error_logs;

        ID LOG_TIMESTAMP
---------- ---------------------------------------------------------------------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
         1 28-FEB-2006 11:10:10.107625
ORA-01400: cannot insert NULL into ("TIM_HALL"."AT_TEST"."DESCRIPTION")


1 row selected.

SQL>

From this we can see that the LOG_ERRORS transaction was separate to the anonymous block. If it weren't, we would expect the first insert in the anonymous block to be preserved by the commit statement in the LOG_ERRORS procedure.

Be careful how you use autonomous transactions. If they are used indiscriminately they can lead to deadlocks, and cause confusion when analyzing session trace.

The Mutation error in Oracle Database Triggers

Most of us who have worked in Oracle have encountered ORA-04091 (table xxx is mutating. Trigger/function might not see it) at some time or the other during the development process.  In this blog post, we will cover why this error occurs and how we can resolve it using different methodology.

Case 1: When Trigger on table refers the same table: 
----------------------------------------------------------------- 
OPERATION       TYPE                        MUTATING? 
----------------------------------------------------------------- 
insert          before/statement-level      No 
insert          after/statement-level       No 
update          before/statement-level      No 
update          after/statement-level       No 
delete          before/statement-level      No 
delete          after/statement-level       No 

insert          before/row-level            Single row   Multi-row 
                                            No           Yes 
insert          after/row-level             Yes 
update          before/row-level            Yes 
update          after/row-level             Yes 
delete          before/row-level            Yes 
delete          after/row-level             Yes 
----------------------------------------------------------------- 

Mutating error normally occurs when we are performing some DML operations and we are trying to select the affected record from the same trigger. So basically we are trying to select records in the trigger from the table that owns the trigger. This creates inconsistency and Oracle throws a mutating error. Let us take a simple scenario in which we have to know total number of invalid objects after any object status is updated to ‘INVALID’. We will see it with an example. First let us create a table and then trigger.

SQL> CREATE TABLE TEST

2  AS SELECT * FROM USER_OBJECTS;

Table created.

CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF STATUS ON TEST
FOR EACH ROW
DECLARE
v_Count NUMBER;
BEGIN

SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;

dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
END;
/

Now if we try to change the status of any object to ‘INVALID’, we will run into mutating error as we are trying to update the record and trigger is trying to select total number of records in ‘INVALID’ status from the same table.

SQL> update test
2  set status = 'INVALID'
3  where object_name = 'TEST1';
update test
*
ERROR at line 1:
ORA-04091: table SCOTT.TEST is mutating, trigger/function may not see it


Having said that there are different ways we can handle mutating table errors. Let us start taking one by one scenario.

First one is to create statement level trigger instead of row level. If we omit the ‘for each row’ clause from above trigger, it will become statement level trigger. Let us create a new statement level trigger.

CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF STATUS ON TEST
DECLARE
v_Count NUMBER;
BEGIN

SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;

dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
END;

Now let us fire the same update statement again.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name = 'TEST1';

Total Invalid Objects are 6


When we defined statement level trigger, update went through fine and it displayed the total number of invalid objects.

Why this is a problem when we are using ‘FOR EACH ROW’ clause? As per Oracle documentation, the session, which issues a triggering statement on the table, cannot query the same table so that trigger cannot see inconsistent data. This restriction applies to all the row level triggers and hence we run into mutating table error.

Second way of dealing with the mutating table issue is to declare row level trigger as an autonomous transaction so that it is not in the same scope of the session issuing DML statement. Following is the row level trigger defined as pragma autonomous transaction.


CREATE OR REPLACE TRIGGER TUA_TEST
AFTER UPDATE OF STATUS ON TEST
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_Count NUMBER;
BEGIN

SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;

dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);
END;


Now let is issue the update statement again and observe the results.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name = 'TEST1';

Total Invalid Objects are 5

1 row updated.

If you closely look at the output, you will see only 5 objects shown in invalid status while statement level trigger showed 6 objects in invalid status. Let us try to update multiple objects at the same time.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name IN ('T1','T2');

Total Invalid Objects are 6
Total Invalid Objects are 6

2 rows updated.


By defining row level trigger as an autonomous transaction, we got rid of mutating table error but result is not correct. The latest updates are not getting reflected in our result set as oppose to statement level trigger. So one has to be very careful when using this approach.


In version 11g, Oracle made it much easier with introduction of compound triggers. We have covered compound triggers in a previous blog post. Let us see in this case how a compound trigger can resolve mutating table error. Let’s create a compound trigger first:

CREATE OR REPLACE TRIGGER TEST_TRIG_COMPOUND
FOR UPDATE
ON TEST
COMPOUND TRIGGER

/* Declaration Section*/
v_count NUMBER;

AFTER EACH ROW IS
BEGIN

dbms_output.put_line(‘Update is done’);

END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN

SELECT count(*)
INTO v_count
FROM TEST
WHERE status = ‘INVALID’;

dbms_output.put_line(‘Total Invalid Objects are ‘ || v_count);

END AFTER STATEMENT;

END TEST_TRIG_COMPOUND;
/

Now let us check how many objects are invalid in the test table.

SQL> select count(*) from test where status = 'INVALID';

COUNT(*)
———-
6

Here is the update statement followed by an output.

SQL> UPDATE TEST
2     SET status = 'INVALID'
3   WHERE object_name = 'T2';

Update is done
Total Invalid Objects are 7

1 row updated.

Here we get correct result without getting mutating table error. This is also one very good advantage of compound triggers. There are other ways also to resolve mutating table error using temporary tables but we have discussed common ones in this blog post.

Conventional and Direct Path Loads

This chapter describes SQL*Loader's conventional and direct path load methods. The following topics are covered:

• Data Loading Methods
• Conventional Path Load
• Direct Path Load
• Using Direct Path Load
• Optimizing Performance of Direct Path Loads
• Optimizing Direct Path Loads on Multiple-CPU Systems
• Avoiding Index Maintenance
• Direct Loads, Integrity Constraints, and Triggers
• Parallel Data Loading Models
• General Performance Improvement Hints

Data Loading Methods

SQL*Loader provides two methods for loading data:

• Conventional Path Load
• Direct Path Load

A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. A direct load does not compete with other users for database resources, so it can usually load data at near disk speed. Considerations inherent to direct path loads, such as restrictions, security, and backup implications, are discussed in this chapter.

The tables to be loaded must already exist in the database. SQL*Loader never creates tables. It loads existing tables that either already contain data or are empty.

The following privileges are required for a load:

• You must have INSERT privileges on the table to be loaded.
• You must have DELETE privileges on the table to be loaded, when using the REPLACE or TRUNCATE option to empty old data from the table before loading the new data in its place.

Figure 11-1 shows how conventional and direct path loads perform database writes.

Figure 11-1 Database Writes on SQL*Loader Direct Path and Conventional Path

Description of "Figure 11-1 Database Writes on SQL*Loader Direct Path and Conventional Path"

Conventional Path Load

Conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications.

When SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources. This can slow the load significantly. Extra overhead is added as SQL statements are generated, passed to Oracle, and executed.

The Oracle database looks for partially filled blocks and attempts to fill them on each insert. Although appropriate during normal use, this can slow bulk loads dramatically.

Conventional Path Load of a Single Partition

By definition, a conventional path load uses SQL INSERT statements. During a conventional path load of a single partition, SQL*Loader uses the partition-extended syntax of the INSERT statement, which has the following form:

INSERT INTO TABLE T PARTITION (P) VALUES ... 

The SQL layer of the Oracle kernel determines if the row being inserted maps to the specified partition. If the row does not map to the partition, the row is rejected, and the SQL*Loader log file records an appropriate error message.

When to Use a Conventional Path Load

If load speed is most important to you, you should use direct path load because it is faster than conventional path load. However, certain restrictions on direct path loads may require you to use a conventional path load. You should use a conventional path load in the following situations:

• When accessing an indexed table concurrently with the load, or when applying inserts or updates to a non-indexed table concurrently with the load
  To use a direct path load (with the exception of parallel loads), SQL*Loader must have exclusive write access to the table and exclusive read/write access to any indexes.
• When loading data into a clustered table
  A direct path load does not support loading of clustered tables.
• When loading a relatively small number of rows into a large indexed table
  During a direct path load, the existing index is copied when it is merged with the new index keys. If the existing index is very large and the number of new keys is very small, then the index copy time can offset the time saved by a direct path load.
• When loading a relatively small number of rows into a large table with referential and column-check integrity constraints. 
  Because these constraints cannot be applied to rows loaded on the direct path, they are disabled for the duration of the load. Then they are applied to the whole table when the load completes. The costs could outweigh the savings for a very large table and a small number of new rows.
• When loading records and you want to ensure that a record is rejected under any of the following circumstances:
  • If the record, upon insertion, causes an Oracle error
  • If the record is formatted incorrectly, so that SQL*Loader cannot find field boundaries
  • If the record violates a constraint or tries to make a unique index non-unique.

Direct Path Load

Instead of filling a bind array buffer and passing it to the Oracle database with a SQL INSERT statement, a direct path load uses the direct path API to pass the data to be loaded to the load engine in the server. The load engine builds a column array structure from the data passed to it.

The direct path load engine uses the column array structure to format Oracle data blocks and build index keys. The newly formatted database blocks are written directly to the database (multiple blocks per I/O request using asynchronous writes if the host platform supports asynchronous I/O).

Internally, multiple buffers are used for the formatted blocks. While one buffer is being filled, one or more buffers are being written if asynchronous I/O is available on the host platform. Overlapping computation with I/O increases load performance

Data Conversion During Direct Path Loads

During a direct path load, data conversion occurs on the client side rather than on the server side. This means that NLS parameters in the initialization parameter file (server-side language handle) will not be used. To override this behavior, you can specify a format mask in the SQL*Loader control file that is equivalent to the setting of the NLS parameter in the initialization parameter file, or set the appropriate environment variable. For example, to specify a date format for a field, you can either set the date format in the SQL*Loader control file as shown in Example 11-1 or set an NLS_DATE_FORMAT environment variable as shown in Example 11-2.

Example 11-1 Setting the Date Format in the SQL*Loader Control File

LOAD DATA
INFILE 'data.dat'
INSERT INTO TABLE emp
FIELDS TERMINATED BY "|"
(
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE 'YYYYMMDD',
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)

Example 11-2 Setting an NLS_DATE_FORMAT Environment Variable

On UNIX Bourne or Korn shell:

% NLS_DATE_FORMAT='YYYYMMDD'
% export NLS_DATE_FORMAT

On UNIX csh:

%setenv NLS_DATE_FORMAT='YYYYMMDD'

Advantages of a Direct Path Load

A direct path load is faster than the conventional path for the following reasons:

• Partial blocks are not used, so no reads are needed to find them, and fewer writes are performed.
• SQL*Loader need not execute any SQL INSERT statements; therefore, the processing load on the Oracle database is reduced.
• A direct path load calls on Oracle to lock tables and indexes at the start of the load and releases them when the load is finished. A conventional path load calls Oracle once for each array of rows to process a SQL INSERT statement.
• A direct path load uses multiblock asynchronous I/O for writes to the database files.
• During a direct path load, processes perform their own write I/O, instead of using Oracle's buffer cache. This minimizes contention with other Oracle users.
• The sorted indexes option available during direct path loads enables you to presort data using high-performance sort routines that are native to your system or installation.
• When a table to be loaded is empty, the presorting option eliminates the sort and merge phases of index-building. The index is filled in as data arrives.
• Protection against instance failure does not require redo log file entries during direct path loads. Therefore, no time is required to log the load when:
  • The Oracle database has the SQL NOARCHIVELOG parameter enabled
  • The SQL*Loader UNRECOVERABLE clause is enabled
  • The object being loaded has the SQL NOLOGGING parameter set

Restrictions on Using Direct Path Loads

The following conditions must be satisfied for you to use the direct path load method:

• Tables are not clustered.
• Tables to be loaded do not have any active transactions pending.
  To check for this condition, use the Oracle Enterprise Manager command MONITOR TABLE to find the object ID for the tables you want to load. Then use the command MONITOR LOCK to see if there are any locks on the tables.
• For versions of the database prior to Oracle9i, you can perform a SQL*Loader direct path load only when the client and server are the same version. This also means that you cannot perform a direct path load of Oracle9i data into a database of an earlier version. For example, you cannot use direct path load to load data from a release 9.0.1 database into a release 8.1.7 database.
  Beginning with Oracle9i, you can perform a SQL*Loader direct path load when the client and server are different versions. However, both versions must be at least release 9.0.1 and the client version must be the same as or lower than the server version. For example, you can perform a direct path load from a release 9.0.1 database into a release 9.2 database. However, you cannot use direct path load to load data from a release 10.0.0 database into a release 9.2 database.
• Tables to be loaded in direct path mode do not have VPD policies active on INSERT.

The following features are not available with direct path load:

• Loading a parent table together with a child table
• Loading BFILE columns
• Use of CREATE SEQUENCE during the load. This is because in direct path loads there is no SQL being generated to fetch the next value since direct path does not generate INSERT statements.

When to Use a Direct Path Load

If none of the previous restrictions apply, you should use a direct path load when:

• You have a large amount of data to load quickly. A direct path load can quickly load and index large amounts of data. It can also load data into either an empty or nonempty table.
• You want to load data in parallel for maximum performance.

Integrity Constraints

All integrity constraints are enforced during direct path loads, although not necessarily at the same time. NOT NULL constraints are enforced during the load. Records that fail these constraints are rejected.

UNIQUE constraints are enforced both during and after the load. A record that violates a UNIQUE constraint is not rejected (the record is not available in memory when the constraint violation is detected).

Integrity constraints that depend on other rows or tables, such as referential constraints, are disabled before the direct path load and must be reenabled afterwards. If REENABLE is specified, SQL*Loader can reenable them automatically at the end of the load. When the constraints are reenabled, the entire table is checked. Any rows that fail this check are reported in the specified error log.

Field Defaults on the Direct Path

Default column specifications defined in the database are not available when you use direct path loading. Fields for which default values are desired must be specified with the DEFAULTIF clause. If a DEFAULTIF clause is not specified and the field is NULL, then a null value is inserted into the database.

Indexes Left in an Unusable State

SQL*Loader leaves indexes in an Index Unusable state when the data segment being loaded becomes more up-to-date than the index segments that index it.

Any SQL statement that tries to use an index that is in an Index Unusable state returns an error. The following conditions cause a direct path load to leave an index or a partition of a partitioned index in an Index Unusable state:

• SQL*Loader runs out of space for the index and cannot update the index.
• The data is not in the order specified by the SORTED INDEXES clause.
• There is an instance failure, or the Oracle shadow process fails while building the index.
• There are duplicate keys in a unique index.
• Data savepoints are being used, and the load fails or is terminated by a keyboard interrupt after a data savepoint occurred.

To determine if an index is in an Index Unusable state, you can execute a simple query:

SELECT INDEX_NAME, STATUS
   FROM USER_INDEXES 
   WHERE TABLE_NAME = 'tablename';

If you are not the owner of the table, then search ALL_INDEXES or DBA_INDEXES instead of USER_INDEXES.

To determine if an index partition is in an unusable state, you can execute the following query:

SELECT INDEX_NAME, 
       PARTITION_NAME,
       STATUS FROM USER_IND_PARTITIONS
       WHERE STATUS != 'VALID';

If you are not the owner of the table, then search ALL_IND_PARTITIONS and DBA_IND_PARTITIONS instead of USER_IND_PARTITIONS.