Home > SQL Developer Dialog Boxes ... > Create/Edit Index
The following information applies to an index, which is a database object that contains an entry for each value that appears in the indexed column or columns of the table or cluster and provides direct, fast access to rows, as explained in Indexes. For detailed information about all index-related options, see the CREATE INDEX reference section in Oracle Database SQL Language Reference.
Advanced: If this option is checked, the dialog box changes to enable you to set advanced properties (select Advanced on the left side).
Schema: Database schema that owns the table associated with the index.
Table: Name of the table associated with the index.
Name: Name of the index. Must be unique within a schema.
Index Type: Normal for a standard Oracle index, in which case you also specify non-unique, unique, or bitmap, as well as one or more index expressions; or Text for an Oracle Text index (created with INDEXTYPE IS CTXSYS.CONTEXT), in which case you specify the column to be indexed.
Non-unique means that the index can contain multiple identical values; Unique means that no duplicate values are permitted; Bitmap stores rowids associated with a key value as a bitmap.
Index: A list of index expressions, that is, the table columns or column expressions in the index. To add an index expression, click the Add Column Expression (+) icon; this adds a column name here and in Column Expression, where you can edit it. To delete an index expression, click the Remove Column Expression (X) icon; to move an index expression up or down in the list, click the Move Column Up and Move Column Down icons. An index must have at least one index expression.
For example, to create an index on the AUTHOR_LAST_NAME column of the BOOKS table from the tutorial (see Create a Table (BOOKS)), click the + icon, and select AUTHOR_LAST_NAME in Column Name or Expression (next field), which changes BOOKS to AUTHOR_LAST_NAME in the Index field.
Column Name or Expression: A column name or column expression. A column expression is an expression built from columns, constants, SQL functions, and user-defined functions. When you specify a column expression, you create a function-based index.
Order: ASC for an ascending index (index values sorted in ascending order); DESC for a descending index (index values sorted in descending order).
Properties
Enables you to specify index properties such as compression, parallelism, and storage options.
Compress: If this option is checked, key compression is enabled, which eliminates repeated occurrence of key column values and may substantially reduce storage. If this option is checked, you can enter an integer to specify the prefix length (number of prefix columns to compress).
Parallel: If this option is checked, parallel creation of the index is enabled. You can also enter an integer in the text box to specify the degree of parallelism, which is the number of parallel threads used in the parallel operation. (Each parallel thread may use one or two parallel execution servers.) If you specify Parallel without entering an integer, the optimum degree of parallelism is automatically calculated.
Storage Options: Enables you to specify storage options for the index. Displays the Storage Options dialog box.
Partitions
When applicable, enables you to specify whether the index is not partitioned, locally partitioned, or globally partitioned. If you specify Local or Global, additional fields are displayed relevant to the selected partitioning option.
Local: Specifies that the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as its associated table. Oracle Database automatically maintains local index partitioning as the underlying table is repartitioned.
Global: Specifies that the partitioning of the index is user defined and is not equipartitioned with the underlying table. You can partition a global index by range or by hash. In both cases, you can specify up to 32 columns as partitioning key columns. The partitioning column list must specify a left prefix of the index column list. If the index is defined on columns a, b, and c, then for the columns you can specify (a, b, c), or (a, b), or (a, c), but you cannot specify (b, c) or (c) or (b, a). If you omit the partition names, then Oracle Database assigns names of the form SYS_Pn.
Related Topics