Load data

You can expedite indexing by the way you load your data into the table.

Non-indexed tables

Indexing is fastest when you load the data into a non-indexed table and then create the etx index.

Preindexed tables

It is not always possible to insert all data into a table before you create the index. If you must insert large numbers of rows into a preindexed table, collect multiple rows in a nonindexed staging or temporary table and then insert them into the main table as a transaction.

For example, suppose the preindexed table is called maintab. Create a table called stagingtab that looks exactly like the maintab table, but with no etx index built on it. Insert a batch of documents into the stagingtab table; then insert these documents into the maintab table by using an INSERT statement similar to the following example:

BEGIN WORK;
INSERT INTO maintab ... SELECT * FROM stagingtab;
COMMIT WORK;

You can also improve performance by encapsulating the INSERT statement in a transaction with the BEGIN WORK and COMMIT WORK statements; doing this allows a table lock to be taken immediately on the maintab table, so you avoid the cost of lock escalation.

If you are inserting data into a fragmented etx index, you can further optimize the insertion throughput if you can segment your data to match the index fragmentation scheme, if you have multiple CPUs available, and if you can run concurrent data loading.

Create a temporary table and data loading process for each index fragment. Each data loading process would first insert the data into its own copy of a staging table and then into the target table. Doing this affects only one index fragment and locks only that fragment. Additional loaders can load data into separate staging tables and index fragments and take advantage of the parallelism of the multiple CPU system.

You must balance the number of rows you stage against concurrency requirements, because the affected index fragment or fragments are locked against all other users until the insertion is completed and the transaction is committed.