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.