Syntax
The basic syntax for creating an R-tree index is:
CREATE INDEX index_name
ON table_name (column_name op_class)
USING RTREE (parameters)
index_options;The parameters and index_options arguments
are optional.
Important: The ONLINE keyword of the CREATE
INDEX and DROP INDEX statements is not supported for R-Tree indexes.
The
arguments are described in the following table.
| Arguments | Purpose | Restrictions |
|---|---|---|
| index_name | The name you want to give your index | The name must be unique in the database. |
| table_name | The name of the table that contains the column you want to index | The table must already exist. |
| column_name | The name of the column you want to index For example, you can create an R-tree index on columns of data type MyShape, defined in the sample DataBlade® module. | You can create an R-tree index on a single column only; you cannot create a single R-tree index on multiple columns. The data type of this column must support R-tree indexes. For more information on the data types that support R-tree indexes, check the DataBlade module user's guide. |
| op_class | The name of the operator class For example, to index columns of data type MyShape, defined in the sample DataBlade module, you must specify the MyShape_ops operator class. | If you have registered in your database a DataBlade module
that supplies its own operator class, you must specify it when you
create an R-tree index. If you do not specify an operator class, or if you specify the default rtree_ops operator class without knowingly setting up your data type and functions to use it, the R-tree index might appear to work correctly but will function unpredictably. Check the DataBlade module user's guide for more information about which operator class you must specify when you create an R-tree index.You must run the UPDATE STATISTICS statement after you create the index or the query optimizer might not choose to use the index at appropriate times. |
| parameters | The parameters that specify how an R-tree index is built These parameters only affect the building of the index, not the subsequent use of the index. | You can specify the following index parameters: BOTTOM_UP_BUILD, BOUNDING_BOX_INDEX, NO_SORT, SORT_MEMORY, FILLFACTOR. For detailed information about each index parameter and when you should use it, refer to R-tree index parameters. |
| index_options | The fragmentation and storage options of the index, described in detail in the section R-tree index options | The options available for R-tree indexes are FRAGMENT BY and IN. The options CLUSTER, UNIQUE, DISTINCT, ASC, DESC, and FILLFACTOR are not supported. |
For more information on the CREATE INDEX statement, refer to the Informix® Guide to SQL: Syntax.