Indexes can now be created and dropped in a non-exclusive method. Use the CREATE INDEX ONLINE and DROP INDEX ONLINE statements to create and drop indexes in an online, or dynamic, environment. The database and its associated tables will not be locked exclusively, which means they will be available for updates or reads.
You do not need to place an exclusive lock on the table during the index build if an index is created with the ONLINE keyword. Reads and updates can occur on the table. Creation of an index will not have to wait until an exclusive lock can be placed on the table.
If an index is created with the ONLINE keyword, the database server logs the operation with a specific flag. Restore and recovery operations will now check for that flag and can recreate the index.
The advantages of creating indexes using the CREATE INDEX ONLINE statement are as follows:
- An index can be immediately created without a lock placed over the table
- Updates can occur to a table while the index is being created
- The optimizer can update statistics in unlocked tables, enabling more optimal query plans
Attached indexes can be created using CREATE INDEX ONLINE, but ONLINE only applies when dirty read is the transaction isolation level. The index creation places an exclusive lock on the table and waits for all other concurrent processes scanning the table to finish using index partitions before it creates the attached index. If the table is being read or updated, the CREATE INDEX ONLINE statement waits for the exclusive lock.
If LOCK MODE is not set to WAIT, the creation of the attached index could fail, because it will not wait for other users to finish. Note that the engine places a lock after index creation for a short time while it updates system catalog information.
Listing 14 provides an example use of CREATE INDEX with the ONLINE syntax.
Listing 14. Sample CREATE INDEX with ONLINE syntax
CREATE INDEX i1 ON tab1(c1) ONLINE=3
ONLIDX_MAXMEM configuration parameter is
used to limit the amount of memory allocated to a preimage log pool or
to an updater log pool. These pools are created in shared memory when
an index is created with the ONLINE keyword. This might be helpful if
you plan to complete other operations on a table column while an index
is being created (using the ONLINE syntax) on that column.
The range of values of the
16KB through 4294967295KB. The default size in the onconfig.std is
ONLIDX_MAXMEM configuration parameter can be modified dynamically with the
onmode -wf command or superseded with the
onmode -wm command.
DROP INDEX ONLINE parameter enables you to drop an
index without the necessity of an exclusive lock. Indexes can be
dropped (using the ONLINE keyword) even when dirty read is the
transaction isolation level.
The advantages of dropping indexes using the DROP INDEX ONLINE statement are as follows:
- An inefficient index can be dropped without disturbing ongoing queries, even queries using that particular index
- The query optimizer will be notified not to use the index for new SELECT operations on tables
The execution of a
DROP INDEX ONLINE
statement does not occur until after a table update is completed. After you issue the
DROP INDEX ONLINE statement, no new
operations can reference the index, but concurrent operations can use
the index until the operations are completed. The database server
waits to drop the index until all current users have finished
accessing the index.
Listing 15 contains an example use of DROP INDEX with the ONLINE syntax.
Listing 15. Sample DROP INDEX with ONLINE syntax
DROP INDEX i1 ONLINE
An index self-join is a join in which a table is joined to itself. An index self-join can help performance in either of the following circumstances:
- A query involving the lead key, or the most important column (usually the first one), of an index has man duplicates
- The cost or number of rows to examine with a lead-key is judged to be higher than the cost of looking through rows returned by non-lead-key columns
The way to conceptualize how this works is to think of the query being broken into a union of many subqueries. Each subquery is the result set of the non-restrictive lead-key column. The optimizer can then look at those columns and use them to look at the more restrictive non-lead-key columns.
To produce a self-join, use aliases such
that the same table is listed twice in the
from clause, as shown in Listing 16.
Listing 16. Sample syntax to generate a self-join
SELECT a1.customer_num FROM customer a1, customer b1 WHERE a1.customer_num = b1.customer_num AND ... Additional logic here
Note the use of two aliases that both reference the same table.
A self-join can be determined in a query plan very simply with set explain output, as shown in Listing 17.
Listing 17. Example of set explain output showing a self-join
Index Self Join Keys (customer_num lname ) Lower bound: informix.a.customer_num >= 00702 AND (informix.a.lname >= 'Aaron ' ) Upper bound: informix.a.customer_num <= 12345 AND (informix.a.lname <= 'Croyle' )
Note that the keys involved in the columns involved in the index self-join will be shown.
Following are two optimizer directives can be used to direct the optimizer to use the self-join functionality:
- The INDEX_SJ directive forces an index self-join path by using the specified index or by choosing the least costly index in a list of indexes, even if data distribution statistics are not available for the leading index key columns.
- The AVOID_INDEX_SJ directive prevents a self-join path for a specified index or for a list of indexes.
NO_SORT is a new option for creation of indexes that can help performance in specialized scenarios (such as static clustered tables). With NO_SORT, you can use a function returning a numeric spatial key to create a statically clustered table, according to a functional b-tree index. When an r-tree index is created on the resulting clustered table, the r-tree secondary access method does not need to sort the data. It will build an index from the bottom up, because the table is already sorted according to the same criteria that the r-tree bottom-up build would use. The B-tree functional index does the sorting.
To create an r-tree index using the NO_SORT index parameter, complete the following steps.
- Check the DataBlade module documentation for a function that, given an object of the data type that is being indexed, returns a spatial key.
- Create a clustered functional b-tree index on the table using this
function, as shown in Listing 18.
Listing 18. Create a clustered functional b-tree index on the table using the DataBlade function
CREATE CLUSTER INDEX btree_functional_index ON table1 (SpatialKey(col1));
btree_functional_indexis the name of the clustered functional b-tree index.
table1is the name of the table.
SpatialKeyis the fuction.
col1is the name of the column that contains the spatial data.
- Create an r-tree index on the spatial keyed column using the
NO_SORT="YES", as shown in Listing 19.
Listing 19. Create an r-tree index on the spatial keyed column using the syntax NO_SORT="YES"
CREATE INDEX rtree_index ON table1 (col1 op1) USING RTREE (NO_SORT = 'YES');
rtree_indexis the name of the r-tree index.
op1is the name of the operator class associated with the data type of column
- Drop the now-extraneous b-tree index, as shown in Listing 20.
Listing 20. Drop the now-extraneous b-tree index
DROP INDEX btree_functional_index;
NOSORTINDEX environment variable is
set in the environment, then the default behavior of creating an r-tree
index will be equivalent to setting
Improved performance on index searches using is possible using the new forest of trees (FOT) indexing scheme, which was introduced in Informix 11.70. A single large b-tree index is replaced by a number of smaller b-tree indexes. The FOT index is built by hashing one or more columns in the index. Queries hash on that value and are directed to the correct index in the FOT index. The upper levels of an index are the most heavily accessed pages. The FOT index reduces contention by reducing heavy access of the top index pages.
To create a FOT index, add the
HASH ON clause to the CREATE INDEX
statement, as shown in the examples in Listing 21.
Listing 21. Examples of creating an FOT index
CREATE INDEX idx1 ON tab1(c1) HASH ON (c1) WITH 100 BUCKETS; CREATE INDEX idx2 ON tab2(c1, c2, c3) HASH ON (c1, c2) WITH 10 BUCKETS;
BTSCANNER configuration parameter was
introduced in Informix 10.00. New function was recently added in
the form of adaptive linear index cleaning (alice mode, Informix
11.10) and b-tree compression (Informix Dynamic Server 11.50).
Scanning enabled with the alice mode (logged databases only) keeps a bitmap for each index partition that shows the index keys marked for cleaning. The scanning to clean items marked for deletion excludes parts of the index where no deletes should be done. Using alice mode provides a marked performance improvement over the older, range-scanning method. The alice mode is not used for tables with multiple attached indexes, where a leaf scan should be used.
B-tree compression is designed to keep a b-tree index size at a level that favors efficient searches. The compression setting sets thresholds for merging partially filled index pages.
Listing 22 shows the onconfig.std values for
Listing 22. BTSCANNER examples
BTSCANNER num=1, threshold=5000, rangesize=-1, alice=6, compression=default
The server starts
num threads and puts the index on a hotlist
for cleaning when it hits the
threshold number of items to
rangesize is the size in KB that an index
fragment must exceed before the index is cleaned with range scanning.
It is recommended that this value be set to
-1 (off) for Informix 11.10
and above so that alice mode will be used where appropriate.
The alice parameter sets the initial bitmap size. Set it to 6
or 7 for small to medium size systems, and set it to larger values for systems
expected to be large. The
compression is set to default
(medium), low, medium, or high, depending on the processing behavior
you expect on the system.
Your desired setting for compression might change as an index grows or shrinks. The administrative API (sysadmin database) has a built-in function for changing the setting, as shown in Listing 23.
Listing 23. Changing the compression setting
EXECUTE FUNCTION task("set index compression","1048611","high");
The compression for index with partnum 1048611 is set to high in this example.