INFORMIX
Informix Guide to SQL: Syntax
Chapter 1: SQL Statements
Home Contents Index Master Index New Book

SQL Statements

CREATE INDEX

Use the CREATE INDEX statement to create a new index for one or more columns in a table, a functional value on one or more columns, and, optionally, to cluster the physical table in the order of the index.

When more than one columns or functions are listed, the concatenation of the set of columns is treated as a single composite column for indexing. The indexes can be fragmented into separate dbspaces. You can create a unique or duplicate index, and you can set the object mode of either type of index.

Syntax

Element Purpose Restrictions Syntax

dbspace

The name of the dbspace in which you want to place the index

The dbspace must exist at the time you execute the statement.

Identifier, p. 1-966

percent

The percentage of each index page that is filled by index data when the index is created. The default value is 90.

Value must be in the range 1 to 100. Fillfactor does not apply to an R-tree secondary access method.

Literal Number, p. 1-1001

Usage

A secondary access method (sometimes referred to as an index access method) is a set of server functions that build, access, and manipulate an index structure such as a B-tree, R-tree, or an index structure that a DataBlade module provides. Typically, a secondary access method speeds up the retrieval of data.

Use CREATE INDEX to create the following types of indexes:

When you issue the CREATE INDEX statement, the table is locked in exclusive mode. If another process is using the table, the database server cannot execute the CREATE INDEX statement and returns an error.

For the different secondary access methods that Universal Server provides, see "USING Clause".

UNIQUE and DISTINCT Options

The following example creates a unique index:

A unique index prevents duplicates in the customer_num column. A column with a unique index can have, at most, one null value. The DISTINCT keyword is a synonym for the keyword UNIQUE, so the following statement would accomplish the same task:

The index in either example is maintained in ascending order, which is the default order.

If you do not specify the UNIQUE or DISTINCT keywords in a CREATE INDEX statement, a duplicate index is created. A duplicate index allows duplicate values in the indexed column.

You can also prevent duplicates in a column or set of columns by creating a unique constraint with the CREATE TABLE or ALTER TABLE statement. See the CREATE TABLE or ALTER TABLE statements for more information on creating unique constraints.

How Unique and Referential Constraints Affect Indexes

The database server creates internal B-tree indexes for unique and referential constraints. If a unique or referential constraint is added after the table is created, the user-created indexes are used, if appropriate. An appropriate index is one that indexes the same columns that are used in the referential or unique constraint. If an appropriate index is not available, a nonfragmented index is created in the database dbspace.

CLUSTER Option

Use the CLUSTER option to reorder the physical table in the order designated by the index. The CREATE CLUSTER INDEX statement fails if a CLUSTER index already exists.

This statement creates an index on the customer table that orders the table physically by zip code.

If the CLUSTER option is specified in addition to fragments on an index, the data is clustered only within the context of the fragment and not globally across the entire table.

Warning: Some secondary access methods (such as R-tree) do not support clustering. Before you specify CLUSTER for your index, be sure that it uses an access method that supports clustering.

Index Definition

Use the Index Definition portion of the CREATE INDEX statement to give a name to the index, to specify the table on which the index is created, the value or values to use for the index key, and, optionally, the secondary access method.

Element Purpose Restrictions Syntax

table name

The name of the table on which the index is created

The table must exist. The table can be a regular database table or a temporary table.
This table cannot be an external table.

Table Name,
p. 1-1048

secondary access method

The name of the secondary access method used with the index you are creating.

The access method can be a B-tree, R-tree, or an access method that has been defined by a DataBlade module. The access method must be a valid access method in the sysams system catalog table. The default secondary access method is B-tree.

If the access method is B-tree, you can create only one index for each unique combination of ascending and descending columnar or functional keys with operator classes. This restriction does not apply to other secondary access methods.

Identifier,
p. 1-966

Index Key Specification

Use the Index Key Specification clause of the CREATE INDEX statement to specify the key value for the index, an operator class, and whether the index will be sorted in ascending or descending order.

Element Purpose Restrictions Syntax

column name

The name of the column or columns that you want to index

You must observe restrictions on the location of the columns, the maximum number of columns, the total width of the columns, existing constraints on the columns, and the number of indexes allowed on the same columns. See "Restrictions on the Column Name Variable in CREATE INDEX".

Identifier,
p. 1-966

operator class

The operator class associated with this column or function of the index

If you specify a secondary access method in the USING clause that does not have a default operator class, you must specify an operator class here.
If you use an alternative access method, and if the access method has a default operator class, you can omit the operator class here.
If you do not specify an operator class and the secondary access method does not have a default operator class, the database server returns an error.

Identifier, p. 1-966

The index key value can be one of the following values:

Restrictions on the Column Name Variable in CREATE INDEX

 Observe the following restrictions when you specify the column name variable:

Function Specification

This clause specifies the user-defined function whose return value is the key for a functional index.

Element Purpose Restrictions Syntax

function name

The name of the function used as a key to this index

This must be a non-variant function.
The return type of the function cannot be BYTE or TEXT.

You cannot create an index on built-in algebraic, exponential, log, or hex functions.

Function Name,
p. 1-963

column name

The name of the column or columns on which the function acts

See "Restrictions on the Column Name Variable in CREATE INDEX".

Identifier,
p. 1-966

You can create an index on an external function or an SPL function. You can also create functional indexes within an SPL routine.

A functional index can be a B-tree index or a user-defined index type provided by a DataBlade module.

Functional indexes are indexed on the value returned by the specified function rather than on the value of a column.

For example, the following statement creates a functional index on table zones using the value returned by the function Area() as the key:

Operator Class

 An operator class is the set of operators that Universal Server associates with a secondary access method for query optimization and building the index.

Specify an operator class when you create an index if you have one of the following situations:

For more information, see "Default Operator Classes". The following CREATE INDEX statement creates a B-tree index on the cust_tab table that uses the abs_btree_ops operator class for the cust_num key:

ASC and DESC Keywords

 Use the ASC option to specify an index that is maintained in ascending order. The ASC option is the default ordering scheme. Use the DESC option to specify an index that is maintained in descending order. When a column or list of columns is defined as unique in a CREATE TABLE or ALTER TABLE statement, the database server implements that UNIQUE CONSTRAINT by creating a unique ascending index. Thus, you cannot use the CREATE INDEX statement to add an ascending index to a column or column list that is already defined as unique.

The ASC and DESC options can be used with B-trees only.

You can create a descending index on such columns, and you can include such columns in composite ascending indexes in different combinations. For example, the following sequence of statements is allowed:

Bidirectional Traversal of Indexes

 When you create an index on a column but do not specify the ASC or DESC keywords, the database server stores the key values in ascending order by default. If you specify the ASC keyword, the database server stores the key values in ascending order. If you specify the DESC keyword, the database server stores the key values in descending order.

Ascending order means that the key values are stored in order from the smallest key to the largest key. For example, if you create an ascending index on the lname column of the customer table, last names are stored in the index in the following order: Albertson, Beatty, Currie.

Descending order means that the key values are stored in order from the largest key to the smallest key. For example, if you create a descending index on the lname column of the customer table, last names are stored in the index in the following order: Currie, Beatty, Albertson.

However, the bidirectional traversal capability of the database server lets you create just one index on a column and use that index for queries that specify sorting of results in either ascending or descending order of the sort column.

Example of Bidirectional Traversal of an Index

An example can help to illustrate the bidirectional traversal of indexes by the database server. Suppose that you want to enter the following two queries:

When you specify the ORDER BY clause in SELECT statements such as these, you can improve the performance of the queries by creating an index on the ORDER BY column. Because of the bidirectional traversal capability of the database server, you only need to create a single index on the lname column.

For example, you can create an ascending index on the lname column with the following statement:

The database server will use the ascending index lname_bothways to sort the results of the first query in ascending order and to sort the results of the second query in descending order.

In the first query, you want to sort the results in ascending order. So the database server traverses the pages of the lname_bothways index from left to right and retrieves key values from the smallest key to the largest key. The query result is as follows.
lname fname

Albertson

Frank

Beatty

Lana

Currie

Philip

. . .

Vector

Raymond

Wallack

Jason

Watson

George

Traversing the index from left to right means that the database server starts at the leftmost leaf node of the index and continues to the rightmost leaf node of the index.

In the second query, you want to sort the results in descending order. So the database server traverses the pages of the lname_bothways index from right to left and retrieves key values from the largest key to the smallest key. The query result is as follows.
lname fname

Watson

George

Wallack

Jason

Vector

Raymond

. . .

Currie

Philip

Beatty

Lana

Albertson

Frank

Traversing the index from right to left means that the database server starts at the rightmost leaf node of the index and continues to the leftmost leaf node of the index. For an explanation of leaf nodes in indexes, see the INFORMIX-Universal Server Administrator's Guide.

Choosing an Ascending or Descending Index

In the preceding example, you created an ascending index on the lname column of the customer table by specifying the ASC keyword in the CREATE INDEX statement. Then the database server used this index to sort the results of the first query in ascending order of lname values and to sort the results of the second query in descending order of lname values. However, you could have achieved exactly the same results if you had created the index as a descending index.

For example, the following statement creates a descending index that the database server can use to process both queries:

The resulting lname_bothways2 index stores the key values of the lname column in descending order, from the largest key to the smallest key. When the database server processes the first query, it traverses the index from right to left to perform an ascending sort of the results. When the database server processes the second query, it traverses the index from left to right to perform a descending sort of the results.

So it does not matter whether you create a single-column index as an ascending or descending index. Whichever storage order you choose for an index, the database server can traverse that index in ascending or descending order when it processes queries.

Use of the ASC and DESC Keywords in Composite Indexes

If you want to place an index on a single column of a table, you do not need to specify the ASC or DESC keywords because the database server can traverse the index in either ascending or descending order. The database server will create the index in ascending order by default, but the database server can traverse this index in either ascending or descending order when it uses the index in a query.

However, if you create a composite index on a table, the ASC and DESC keywords might be required. For example, if you want to enter a SELECT statement whose ORDER BY clause sorts on multiple columns and sorts each column in a different order, and you want to use an index for this query, you need to create a composite index that corresponds to the ORDER BY columns.

For example, suppose that you want to enter the following query:

This query sorts first in ascending order by the value of the manu_code column and then in descending order by the value of the unit_price column. To use an index for this query, you need to issue a CREATE INDEX statement that corresponds to the requirements of the ORDER BY clause. For example, you can enter either of following statements to create the index:

Now, when you execute the query, the database server uses the index that you created (either stock_idx1 or stock_idx2) to sort the query results in ascending order by the value of the manu_code column and then in descending order by the value of the unit_price column. If you created the stock_idx1 index, the database server traverses the index from left to right when it executes the query. If you created the stock_idx2 index, the database server traverses the index from right to left when it executes the query.

Regardless of which index you created, the query result is as follows.

stock_num manu_code description unit_price

8

ANZ

volleyball

$840.00

205

ANZ

3 golf balls

$312.00

110

ANZ

helmet

$244.00

304

ANZ

watch

$170.00

301

ANZ

running shoes

$95.00

310

ANZ

kick board

$84.00

201

ANZ

golf shoes

$75.00

313

ANZ

swim cap

$60.00

6

ANZ

tennis ball

$48.00

9

ANZ

volleyball net

$20.00

5

ANZ

tennis racquet

$19.80

309

HRO

ear drops

$40.00

302

HRO

ice pack

$4.50

. . .

113

SHM

18-spd, assmbld

$685.90

1

SMT

baseball gloves

$450.00

6

SMT

tennis ball

$36.00

5

SMT

tennis racquet

$25.00

The composite index that was used for this query (stock_idx1 or stock_idx2) cannot be used for queries in which you specify the same sort direction for the two columns in the ORDER BY clause. For example, suppose that you want to enter the following queries:

If you want to use a composite index to improve the performance of these queries, you need to enter one of the following CREATE INDEX statements. You can use either one of the created indexes (stock_idx3 or stock_idx4) to improve the performance of the preceding queries.

USING Clause

 Use the USING clause to specify the secondary access method to use for the new index. A secondary access method is a set of routines that perform all of the operations needed to make an index available to a server, such as create, drop, insert, delete, update, and scan.

Universal Server provides the following secondary access methods:

    A B-tree index is good for a query that retrieves a range of data values. The database server implements this secondary access method and registers it as btree in the system catalog tables of a database.

    An R-tree index is good for searches on multi-dimensional data (such as box, circle, and so forth). The database server registers this secondary access method as rtree in the system catalog tables of a database.

Important: To use an R-tree index, you must install a spatial DataBlade module such as the 2D DataBlade module, Geodetic DataBlade, or any other 3rd party DataBlade modules that implement the R-tree index. These DataBlade modules implement the R-tree secondary access method.
DataBlade modules might provide other types of secondary access methods. For more information on these other secondary access methods, refer to the DataBlade user guides.

By default, the CREATE INDEX statement creates a generic B-tree index. If you want to create an index with an secondary access method other than B-tree, you must specify that name of the secondary access method in the USING clause.

The following example assumes that the database implements the R-tree index. It creates an R-tree index on the location column that contains a opaque data type, point.

The sample query has a filter on the location column.

Index Parameter

Some DataBlade modules provide indexes that require specific parameters when you create them.

Element Purpose Restrictions Syntax

parameter name

Name of the secondary access method parameter used with this index

The parameter name must be one of the strings allowed for this secondary access method. For more information, refer to the DataBlade module user guide.

Quoted String,
p. 1-1014

parameter
value

Value of the specified parameter

The parameter value must be one of the quoted strings or literal numbers allowed for this secondary access method.

Quoted String,
p. 1-1014 or Literal Number, p. 1-1001

Example of an Index with Parameters

The following CREATE INDEX statement creates an index that uses the secondary access method fulltext, which takes two parameters: WORD_SUPPORT and PHRASE_SUPPORT. It indexes a table t, which has two columns: i, an integer column, and data, a TEXT column.

Composite Indexes

A composite index can have up to 16 key parts. An index key part is either a table column or, if the index is a functional index, the result of a function on one or more table columns. A composite index can have any of the following as an index key:

The following example creates a composite index using the stock_num and manu_code columns of the stock table:

The index prevents any duplicates of a given combination of stock_num and manu_code. The index is in ascending order by default.

The total width of all key parts in a single CREATE INDEX statement cannot exceed 390 bytes. Place key parts in the composite index in the order from most frequently used to least frequently used.

Number of Indexes Allowed

Restrictions exist on the number of indexes that you can create on the same column or the same sequence of columns.

Restrictions on the Number of Indexes on a Single Column

You can create only one ascending index and one descending index on a single column. For example, if you wanted to create all possible indexes on the stock_num column of the stock table, you could create the following indexes:

Because of the bidirectional traversal capability of the database server, you do not need to create both indexes in practice. You only need to create one of the indexes. Both of these indexes would achieve exactly the same results for an ascending or descending sort on the stock_num column. For further information on the bidirectional traversal capability of the database server, see "Bidirectional Traversal of Indexes".

Restrictions on the Number of Indexes on a Sequence of Columns

You can create multiple indexes on a sequence of columns, provided that each index has a unique combination of ascending and descending columns. For example, to create all possible indexes on the stock_num and manu_code columns of the stock table, you could create the following indexes:

Because of the bidirectional-traversal capability of the database server, you do not need to create these four indexes in practice. You only need to create two indexes:

For further information on the bidirectional-traversal capability of the database server, see "Bidirectional Traversal of Indexes".

FILLFACTOR Clause

Use the FILLFACTOR clause to provide for expansion of a B-tree index at a later date or to create compacted indexes. You provide a percent value ranging from 1 to 100, inclusive. The default percent value is 90.

When the B-tree index is created, Universal Server initially fills only that percentage of the nodes specified with the FILLFACTOR value. If you provide a low percentage value, such as 50, you allow room for growth in your B-tree index. The nodes of the B-tree index initially fill to a certain percentage and contain space for inserts. The amount of available space depends on the number of keys in each page as well as the percentage value. For example, with a 50-percent FILLFACTOR value, the page would be half full and could accommodate doubling in growth. A low percentage value can result in faster inserts and can be used for indexes that you expect to grow.

If you provide a high percentage value, such as 99, your indexes are compacted, and any new index inserts result in splitting nodes. The maximum density is achieved with 100 percent. With a 100-percent FILLFACTOR value, the index has no room available for growth; any additions to the index result in splitting the nodes. A 99-percent FILLFACTOR value allows room for at least one insertion per node. A high percentage value can result in faster selects and can be used for indexes that you do not expect to grow or for mostly read-only indexes.

The FILLFACTOR can also be set as a parameter in the ONCONFIG file. The FILLFACTOR clause on the CREATE INDEX statement overrides the setting in the ONCONFIG file.

For more information about the ONCONFIG file and the parameters you can use with ONCONFIG, see the INFORMIX-Universal Server Administrator's Guide.

Indexes on Fragmented and Nonfragmented Tables

When you fragment a table and, at a later time, create an index for that table, the index uses the same fragmentation strategy as the table unless you specify otherwise with the FRAGMENT BY EXPRESSION clause or the IN dbspace clause. Any changes to the table fragmentation result in a corresponding change to the index fragmentation.

In Universal Server, all indexes are detached. When indexes are created with a fragmentation strategy or a dbspace is specified in the IN dbspace clause, the indexes are stored in separate dbspaces from the table. If there is no fragmentation scheme and no dbspace is specified in the IN dbspace clause, the index is created in the same dbspace as the table.

For information on the IN dbspace clause, see page 1-157. For information on the FRAGMENT BY EXPRESSION clause, see page 1-158.

IN dbspace Clause

Use the IN dbspace clause to specify the dbspace where you want your index to reside. With this clause, you create a detached index, even though the index is not fragmented. The dbspace that you specify must already exist. If you do not specify the IN dbspace clause, the index is created in the dbspace where the table was created. In addition, if you do not specify the IN dbspace clause, but the underlying table is fragmented, the index is created as a detached index, subject to all the restrictions on fragmented indexes. See page 1-158 for more information about fragmented indexes.

The IN dbspace clause allows you to isolate an index. For example, if the customer table is created in the custdata dbspace, but you want to create an index in a separate dbspace called custind, use the following statements:

FRAGMENT BY EXPRESSION Clause

(1 of 2)

Element Purpose Restrictions Syntax

dbspace

The dbspace that will contain an index fragment that frag-expression defines

You must specify at least two dbspaces. You can specify a maximum of 2,048 dbspaces. The dbspaces must exist at the time you execute the statement.

Identifier, p. 1-966

frag-expression

An expression that defines a fragment where an index key is to be stored using a range, hash, or arbitrary rule

If you specify a value for remainder dbspace, you must specify at least one fragment expression. If you do not specify a value for remainder dbspace, you must specify at least two fragment expressions. You can specify a maximum of 2,048 fragment expressions. Each fragment expression can contain only columns from the current table and only data values from a single row. The columns contained in a fragment expression must be the same as the indexed columns, or a subset of the indexed columns. No subqueries, stored procedures, current date/time functions, or aggregates are allowed in a fragment expression.

Expression, p. 1-880, and Condition, p. 1-835

remainder dbspace

The dbspace that contains index keys that do not meet the conditions defined in any fragment expression

If you specify two or more fragment expressions, remainder dbspace is optional. If you specify only one fragment expression, remainder dbspace is required. The dbspace specified in remainder dbspace must exist at the time you execute the statement.

Identifier, p. 1-966

You use the FRAGMENT BY EXPRESSION clause to define the expression-based distribution scheme.

In an expression-based distribution scheme, each fragment expression in a rule specifies a dbspace. Each fragment expression within the rule isolates data and aids the database server in searching for index keys. You can specify one of the following rules:

    A range rule specifies fragment expressions that use a range to specify which index keys are placed in a fragment, as the following example shows:

    A hash rule specifies fragment expressions that are created when you use a hash algorithm, which is often implemented with the MOD function, as the following example shows:

    An arbitrary rule specifies fragment expressions based on a predefined SQL expression that typically includes the use of OR clauses to group data, as the following example shows:

Warning: When you specify a date value in a fragment expression, make sure to specify 4 digits instead of 2 digits for the year. When you specify a 4-digit year, the DBCENTURY environment variable has no effect on the distribution scheme. When you specify a 2-digit year, the DBCENTURY environment variable can affect the distribution scheme and can produce unpredictable results. See the "Informix Guide to SQL: Syntax" for more information on the DBCENTURY environment variable.

Creating Index Fragments

 When you fragment a table, all indexes for the table become fragmented the same as the table, unless you specify a different fragmentation strategy.

Fragmentation of Unique Indexes
You can fragment unique indexes only with a table that uses an expression-based distribution scheme. The columns referenced in the fragment expression must be part of the indexed columns. If your CREATE INDEX statement fails to meet either of these restrictions, the CREATE INDEX fails, and work is rolled back.

Fragmentation of System Indexes
System indexes (such as those used in referential constraints and unique constraints) utilize user indexes if they exist. If no user indexes can be utilized, system indexes remain nonfragmented and are moved to the dbspace where the database was created. To fragment a system index, create the fragmented index on the constraint columns, and then add the constraint using the ALTER TABLE statement.

Fragmentation of Indexes on Temporary Tables
You can create explicit temporary tables with the TEMP TABLE clause of the CREATE TABLE statement or with the INTO TEMP clause of the SELECT statement. If you specified more than one dbspace in the DBSPACETEMP environment variable, but you did not specify an explicit fragmentation strategy, the database server fragments the temporary table round-robin across the dbspaces that DBSPACETEMP specifies.

If you then try to create a unique index on the temporary table, but you do not specify a fragmentation strategy for the index, the index is not fragmented in the same way as the table. You can fragment a unique index only if the underlying table uses an expression-based distribution scheme, but the temporary table is fragmented according to a round-robin distribution scheme.

Instead of fragmenting the unique index on the temporary table, the database server creates the index in the first dbspace that the DBSPACETEMP environment variable specifies. To avoid this result, use the FRAGMENT BY EXPRESSION clause to specify a fragmentation strategy for the index.

For more information on the DBSPACETEMP environment variable, see the Informix Guide to SQL: Syntax.

Object Modes for Unique Indexes

You can set unique indexes in the following modes: disabled, enabled, and filtering. The following list explains these modes.

Object Mode Effect

disabled

A unique index created in disabled mode is not updated after insert, delete, and update operations that modify the base table. Because the contents of the disabled index are not up to date, the optimizer does not use the index during the execution of queries.

enabled

A unique index created in enabled mode is updated after insert, delete, and update operations that modify the base table. Because the contents of the enabled index are up to date, the optimizer uses the index during the execution of queries. If an insert or update operation causes a duplicate key value to be added to a unique enabled index, the statement fails.

filtering

A unique index created in filtering mode is updated after insert, delete, and update operations that modify the base table. Because the contents of the filtering mode index are up to date, the optimizer uses the index during the execution of queries. If an insert or update operation causes a duplicate key value to be added to a unique index in filtering mode, the statement continues processing, but the bad row is written to the violations table associated with the base table. Diagnostic information about the unique-index violation is written to the diagnostics table associated with the base table.

If you specify filtering mode, you can also specify one of the following error options.

Error Option Effect

WITHOUT ERROR

When a unique-index violation occurs during an insert or update operation, no integrity-violation error is returned to the user. You can specify this option only with the filtering-object mode.

WITH ERROR

When a unique-index violation occurs during an insert or update operation, an integrity-violation error is returned to the user. You can specify this option only with the filtering-object mode.

Specifying Object Modes for Unique Indexes

You must observe the following rules when you specify object modes for unique indexes in CREATE INDEX statements:

Adding a Unique Index When Duplicate Values Exist in the Column

 If you attempt to add a unique index in the enabled mode but receive an error message because duplicate values are in the indexed column, take the following steps to add the index successfully:

    1. Add the index in the disabled mode. Issue the CREATE INDEX statement again, but this time specify the DISABLED keyword.

    2. Start a violations and diagnostics table for the target table with the START VIOLATIONS TABLE statement.

    3. Issue a SET statement to switch the object mode of the index to the enabled mode. When you issue this statement, existing rows in the target table that violate the unique-index requirement are duplicated in the violations table. However, you receive an integrity-violation error message, and the index remains disabled.

    4. Issue a SELECT statement on the violations table to retrieve the nonconforming rows that are duplicated from the target table. You might need to join the violations and diagnostics tables to get all the necessary information.

    5. Take corrective action on the rows in the target table that violate the unique-index requirement.

    6. After you fix all the nonconforming rows in the target table, issue the SET statement again to switch the disabled index to the enabled mode. This time the index is enabled, and no integrity violation error message is returned because all rows in the target table now satisfy the new unique-index requirement.

Object Modes for Duplicate Indexes

If you create a duplicate index, you can set the object mode of the index to the disabled or enabled mode. The following table explains these modes.

Object Mode Effect

disabled

A duplicate index is created in disabled mode. The disabled index is not updated after insert, delete, and update operations that modify the base table. Because the contents of the disabled index are not up to date, the optimizer does not use the index during the execution of queries.

enabled

A duplicate index is created in enabled mode. The enabled index is updated after insert, delete, and update operations that modify the base table. Because the contents of the enabled index are up to date, the optimizer uses the index during the execution of queries. If an insert or update operation causes a duplicate key value to be added to a duplicate enabled index, the statement does not fail.

Specifying Object Modes for Duplicate Indexes

You must observe the following rules when you specify object modes for duplicate indexes in CREATE INDEX statements:

How the Database Server Treats Disabled Indexes

 Whether a disabled index is a unique or duplicate index, the database server effectively ignores the index during data-manipulation operations.

When an index is disabled, the database server stops updating it and stops using it during queries, but the catalog information about the disabled index is retained. So you cannot create a new index on a column or set of columns if a disabled index on that column or set of columns already exists.

Similarly, you cannot create an active (not disabled) unique, foreign-key, or primary-key constraint on a column or set of columns if the indexes needed by the active constraint exist and are disabled.

References

See the ALTER INDEX, CREATE OPCLASS, DROP INDEX, and CREATE TABLE statements in this manual.

For a more detailed description of the different types of indexes, refer to Chapter 3 of the INFORMIX-Universal Server Performance Guide. For information about when to use the different types of indexes and other performance issues with indexes, refer to Chapter 4 of the INFORMIX-Universal Server Performance Guide.

For information about operator classes, refer to the CREATE OPCLASS statement and the Extending INFORMIX-Universal Server: Data Types manual.

For information about the GLS aspects of the CREATE INDEX statement, refer to the Guide to GLS Functionality.

For information about the indexes provided by DataBlade modules, refer to

your DataBlade module user's guide.

CREATE OPAQUE TYPE

Use the CREATE OPAQUE TYPE statement to create an opaque data type.

Syntax

Element Purpose Restrictions Syntax

type name

The name of the new opaque data type

The name you specify must follow the conventions of SQL identifiers. In an ANSI-compliant database, the combination
owner.type must be unique within the database. In a database that is not ANSI compliant, the type name must be unique within the database.

Identifier, p. 1-966

Data Type, p .1-859

length

The number of bytes needed by the database server to store a value of a fixed-length opaque data type

The number must match the positive integer reported when the C language sizeof() directive is applied to the type structure.

Literal Number, p. 1-1001

Usage

 The CREATE OPAQUE TYPE statement registers a new opaque data type in the database. Universal Server stores information on extended data types, including opaque types, in the sysxtdtypes system catalog table.

Naming an Opaque Data Type

The actual name of an opaque data type is an SQL identifier. When you create an opaque data type, the type name must be unique within a database. The type name cannot be the same as any distinct-type names or named row-type names.

ANSI
When you create an opaque data type in an ANSI-compliant database, owner.type_name must be unique within the database.

The owner name is case sensitive. If you do not put quotes around the owner name, the name of the opaque-type owner is stored in uppercase letters.

Privileges on an Opaque Data Type

To create a new opaque type within a database, you must have the Resource privilege on the database. The CREATE OPAQUE TYPE statement creates a new opaque type with Usage privilege granted to the owner of the opaque type and the DBA. To use the opaque data type in an SQL statement, you must have Usage privilege. The owner can grant Usage privilege to other users with the USAGE ON TYPE clause of the GRANT statement. For more information, see the GRANT statement on page 1-461.

INTERNALLENGTH Modifier

The CREATE OPAQUE TYPE statement must indicate the name of the opaque type and its internal length. The INTERNALLENGTH modifier specifies the size of an opaque data type. The way you specify the internal length defines whether the opaque data type is fixed length or varying length.

Fixed-Length Opaque Data Types

A fixed-length opaque type has an internal structure that has a fixed size. To create a fixed-length opaque data type, specify the size of the internal structure, in bytes, for the INTERNALLENGTH modifier. The following statement creates a fixed-length opaque type called fixlen_typ. The database server allocates 8 bytes for this type.

Varying-Length Opaque Data Types

 A varying-length opaque data type has an internal structure whose size might vary from one instance of the opaque type to another. For example, the internal structure of an opaque type might hold the actual value of a string up to a certain size but beyond this size it might use an LO-pointer to a CLOB to hold the value.

To create a varying-length opaque data type, use the VARIABLE keyword for the INTERNALLENGTH modifier. The following statement creates a variable-length opaque type called varlen_typ:

Opaque-Type Modifier

Element Purpose Restrictions Syntax

maximum length

For varying-length opaque types, the maximum length in bytes, to allocate for instances of the type. Values that exceed this length return errors.

The length must be a positive integer less than or equal to 32K. Do not specify for fixed-length types. If maximum length is not specified for a variable-length type, the system default is 2 Kilobytes.

Literal Number, p.1-1001

alignment

The byte boundary on which the database server aligns the opaque type when passing it to a user-defined routine

The alignment must be 1, 2, 4, or 8, depending upon the C definition of the opaque type and the hardware and compiler used to build the object file for the type. If alignment is not specified, the system default is 4 bytes.

Literal Number, p.1-1001

Use modifiers to specify the following optional information:

    You must provide an appropriate hash function for the database server to evaluate GROUP BY clauses on the type.

    By default, opaque types are passed to user-defined routines by reference.

Defining an Opaque Data Type

 To define the opaque data type to the database server, you must provide the following information in the C language:

    The internal storage details of the data type are hidden, or opaque. Once you define a new opaque type, the database server can manipulate it without knowledge of the C structure in which it is stored.

    The support functions tell the database server how to interact with the internal structure of the type. These support functions must be written in the C programming language.

    Possible additional functions include operator functions and casts that operate on the opaque data type. You can also write SQL functions for an opaque data type; SQL functions can appear within an SQL statement.

The following table summarizes the support functions for an opaque data type.

(1 of 3)

Function Purpose When Invoked

input

Converts the opaque data type from its external LVARCHAR representation to its internal representation.

When a client application sends a character representation of the opaque type in an INSERT, UPDATE, or LOAD statement.

output

Converts the opaque data type from its internal representation to its external LVARCHAR representation.

When the database server sends a character representation of the opaque type as a result of a SELECT or FETCH statement.

receive

Converts the opaque data type from its internal representation on the client computer to its internal representation on the server computer. Provides platform-independent results regardless of differences between client and server computer types.

When a client application sends an internal representation of the opaque type in an INSERT, UPDATE, or LOAD statement.

send

Converts the opaque data type from its internal representation on the server computer to its internal representation on the client computer. Provides platform-independent results regardless of differences between client and database server computer types.

When the database server sends an internal representation of the opaque type as a result of a SELECT or FETCH statement.

import

Performs any tasks need to convert from the external (character) representation of an opaque type to the internal representation for a bulk copy.

When DB-Access (LOAD) or the High Performance Loader initiates a bulk copy from a text file to a database.

export

Performs any tasks need to convert from the internal representation of an opaque type to the external (character) representation for a bulk copy.

When DB-Access (UNLOAD) or the High Performance Loader initiates a bulk copy from a database to a text file.

importbinary

Performs any tasks need to convert from the internal representation of an opaque type on the client computer to the internal representation on the server computer for a bulk copy.

When DB-Access (LOAD) or the High Performance Loader initiates a bulk copy from a binary file to a database.

exportbinary

Performs any tasks need to convert from the internal representation of an opaque type on the server computer to the internal representation on the client computer for a bulk copy.

When DB-Access (UNLOAD) or the High Performance Loader initiates a bulk copy from a database to a binary file.

assign()

Does any processing required before storing the opaque type to disk. This function must be named assign().

When the database server executes an INSERT, UPDATE, and LOAD statement, before it stores the opaque type to disk.

destroy()

Does any processing necessary before removing a row that contains the opaque type. This function must be named destroy().

When the database server executes the DELETE and DROP TABLE statements, before it removes the opaque type from disk.

lohandles()

Returns a list of the LO-pointer structures (pointers to smart large objects) in an opaque data type.

Whenever the database server must search opaque types for references to smart large objects: when the oncheck utility runs, when an archive is performed.

compare()

Compares two values of the opaque type and returns an integer value to indicate whether the first value is less than, equal to, or greater than the second value.

When the database server encounters an ORDER BY, UNIQUE, DISTINCT, or UNION clause in a SELECT statement, or when it executes the CREATE INDEX statement to create a B-tree index.

Once you write the necessary support functions for the opaque type, use the CREATE FUNCTION statement to register these support functions in the same database as the opaque type. Certain support functions convert other data types to or from the new opaque type. After you create and register these support functions, use the CREATE CAST statement to associate each function with a particular cast. The cast must be registered in the same database as the support function.

When you have written the necessary source code to define the opaque data type, you then use the CREATE OPAQUE TYPE statement to register the opaque type in the database.

References

See the CREATE CAST, CREATE DISTINCT TYPE, CREATE FUNCTION, CREATE ROW TYPE, CREATE TABLE, and DROP TYPE statements in this manual.

For a summary of an opaque data type, see Chapter 2 of the Informix Guide to SQL: Syntax. For information on how to define an opaque data type, see the Extending INFORMIX-Universal Server: Data Types manual.

For information about the GLS aspects of the CREATE OPAQUE TYPE statement, refer to the Guide to GLS Functionality.

CREATE OPCLASS

Use the CREATE OPCLASS statement to create an operator class for a secondary access method.

Syntax

Element Purpose Restrictions Syntax

opclass
name

Name of the operator class being created

The operator class name must be unique within the database.
In an ANSI-compliant database, the combination owner.opclassname must be unique within the database.

Identifier, p. 1-966

sec_access
method name

Name of the secondary access method with which the specified operator class is being associated

The secondary access method must already exist and must be registered in the sysams system catalog table.

The database server provides the B-tree and R-tree secondary access method.

Identifier, p. 1-966

support-function
name

Name of a support function required by the specified secondary access method

The support functions must be listed in the order expected by the specified access method.

Identifier, p. 1-966

Usage

 An operator class is the set of operators that Universal Server associates with the sec_ access method name secondary access method for query optimization and building the index. A secondary access method (sometimes referred to as an index access method) is a set of server functions that build, access, and manipulate an index structure such as a B-tree, R-tree, or an index structure that a DataBlade module provides.

You define a new operator class when you want:

You must have the Resource privilege or be the DBA to create an operator class. The actual name of an operator class is an SQL identifier. When you create an operator class, opclass name must be unique within a database.

ANSI
When you create an operator class in an ANSI-compliant database, owner.opclass_name must be unique within the database.

The owner name is case sensitive. If you do not put quotes around the owner name, the name of the operator-class owner is stored in uppercase letters.

The following CREATE OPCLASS statement creates a new operator class called abs_btree_ops for the btree secondary access method:

For more information on the btree secondary access method, see "Default Operator Classes".

An operator class has two kinds of operator-class functions:

    Specify strategy functions of an operator class in the STRATEGY clause of the CREATE OPCLASS statement. In the preceding CREATE OPCLASS statement, the abs_btree_ops operator class has five strategy functions.

    Specify support functions of an operator class in the SUPPORT clause of the CREATE OPCLASS statement. In the preceding CREATE OPCLASS statement, the abs_btree_ops operator class has one support function.

STRATEGY Clause

Strategy functions are functions that end-users can invoke within an SQL statement to operate on a data type. The query optimizer uses the strategy functions to determine if a particular index can be used to process a query. If an index exists on a column or user-defined function in a query, and the qualifying operator in the query matches one of the strategy functions in the Strategy Specification list, the optimizer considers using the index for the query. For more information on query plans, see the INFORMIX-Universal Server Performance Guide.

When you create a new operator class, you specify the strategy functions for the secondary access method in the STRATEGY clause. The Strategy Specification lists the name of each strategy function. List these functions in the order that the secondary access method expects. For the specific order of strategy operators for the default operator classes for a B-tree index and an R-tree index, refer to the Extending INFORMIX-Universal Server: Data Types manual.

Strategy Specification

Element Purpose Restrictions Syntax

input data type

Data type of the input argument for the strategy function

This is the data type for which you want to use a specific secondary access method.
A strategy function takes two input arguments and one optional output argument.

Data Type, p. 1-859

output data type

Data type of the optional output argument for the strategy function

This is an optional output argument for side effect indexes.

Data Type, p. 1-859

strategy-function name

The name of an strategy function to associate with the specified operator class

The operators must be listed in the order expected by the specified secondary access method. For more information, refer to the user's guide of the DataBlade module that provides the secondary access method.

Identifier,1-966

The strategy_function name function is an external function. The CREATE OPCLASS statement does not verify that a user-defined function of strategy_function name exists. However, for the secondary access method to use the strategy_function name function, this function must be:

You can optionally the signature of an strategy function in addition to its name. A strategy function can only take two input parameters and an optional output parameter. To specify the function signature, you specify:

You can specify user-defined data types as well as built-in types. If you do not specify the function signature, the database server assumes that each strategy function takes two arguments of the same data type and returns a boolean value.

Side-Effect Indexes

Side-effect data is additional data that a strategy function returns when Universal Server executes a query containing the strategy function. For example, an image DataBlade module might use a fuzzy index to search image data. The index ranks the images according to how closely they match the search criteria. The database server returns the rank value as the side effect data, along with the qualifying images.

SUPPORT Clause

Support functions are functions that the secondary access method uses internally to build and search the index. You specify the support functions for the secondary access method in the SUPPORT clause of the CREATE OPCLASS statement. You must list the names of the support functions in the order that the secondary access method expects. For the specific order of support operators for the default operator classes for a B-tree index and an R-tree index, refer to "Default Operator Classes".

The support_function name function is an external function. The CREATE OPCLASS statement does not verify that a user-defined function of support_function name exists. However, for the secondary access method to use the support_function name function, this function must be:

Default Operator Classes

Each secondary access method has a default operator class that is associated with it. By default, the CREATE INDEX statement creates associates the default operator class with an index. For example, the following CREATE INDEX statement creates a B-tree index on the zipcode column and automatically associates the default B-tree operator class with this column:

For each of the secondary access methods that Universal Server provides, it provides a default operator class, as follows:

    The database server implements the operator-class functions for this operator class and registers it as btree_ops in the system catalog tables of a database.

    The database server registers this operator class as rtree_ops in the system catalog tables of a database. The database server does not implement the operator-class functions for the default R-tree operator class.

Important: To use an R-tree index, you must install a spatial DataBlade module such as the Spatial DataBlade module, Geodetic DataBlade, or any other third-party DataBlade module that implements the R-tree index. These DataBlade modules implement the R-tree operator-class functions.
For information on the operator-class functions of these operator classes, refer to the chapter on operator classes in the Extending INFORMIX-Universal Server: Data Types manual.

DataBlade modules can provide other types of secondary access methods. If a DataBlade module provides a secondary access method, it might also provide a default operator class. For more information, refer to the DataBlade user guides.

References

See the CREATE FUNCTION and DROP OPCLASS statements in this manual. For more information on how to specify a secondary access method or an operator class for an index, see the CREATE INDEX statement in this manual.

For information on how to create and extend an operator class, see the Extending INFORMIX-Universal Server: Data Types manual.

For information about the GLS aspects of the CREATE OPCLASS statement, refer to the Guide to GLS Functionality.



Informix Guide to SQL: Syntax, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.