CREATE INDEX

CREATE INDEX

Syntax

create_index ::=

table_index_clause ::=, memory_index_clause ::=, disk_index_clause ::=, storage_clause ::=

table_index_clause ::=

memory_index_clause ::=

index_partitioning_clause ::=, domain_index_clause ::=, directkey_clause ::=, memory_index_attributes ::=

disk_index_clause ::=

index_partitioning_clause ::=, domain_index_clause ::, disk_index_attributes ::=, physical_attributes_clause ::=

domain_index_clause ::=

directkey_clause ::=

memory_index_attributes ::=

parallel_clause ::=

storage_clause ::=

index_partitioning_clause ::=

index_partition_definition ::=

disk_index_attributes ::=

parallel_clause ::=

physical_attributes_clause ::=

physical_attributes_clause ::=

Prerequisites

Only the SYS user, users having the CREATE INDEX system privilege, and users having sufficient privileges to modify index objects in the table to which the index is to be added can execute this statement.

In order to create function-based indexes, the same privileges as conventional indexes are required. However, if a user-defined function is included in the expression, the function must be marked DETERMINISTIC. The user must also have the EXECUTE object privilege on user-defined functions used in the function-based index owned by another user.

Description

This statement is used to create an index on the basis of one or more of the columns or expressions in a table. When a partitioned index (i.e. local index) is created, the LOCALUNIQUE keyword can be optionally specified. If the LOCALUNIQUE option or local keyword is not used, a non-partitioned index is created when creating an index on a disk partitioned table.

A partitioned index is classified as either a prefixed index or a non-prefixed index, depending on the relationship between the partition key and the index columns. If the leftmost index partition key is the same column as the leftmost index column, it is a prefixed index. If they are different columns, it is a non-prefixed index.

Function-based indexes are based on expressions. Expressions can include built-in SQL functions or user-defined functions.

user_name

This is used to specify the name of the owner of the index to create. If omitted, Altibase will create the index in the schema of the user connected via the current session.

index_name

This is used to specify the name of the index to create. Refer to "Rules for Object Names" for more information on specifying names.

UNIQUE

This keyword indicates that duplicate values are not allowed.

LOCALUNIQUE

This keyword is useful when creating a partitioned index. It indicates that the UNIQUE constraint must be satisfied within each index partition of a partitioned (local) index.

index_expr

This specifies the column or expression to be indexed.

Table columns, constants, SQL functions and user-defined functions can be included in expressions. When specifying an expression instead of a column, a function-based index is created.

Limitations of function-based indexes:

• Only the columns of the table to be indexed can be included in expressions. Schema and table names cannot be specified in front of column names.
• Constants (character strings or integers) can be included in expressions.
• Aggregate functions like SUM cannot be included in expressions.
• Cursors and cursor properties(%ISOPEN, SQLCODE, SQLERRM, etc.) cannot be used for stored procedures included in expressions.
• For global partitioned indexes, expressions cannot be partitioning keys.
• Regardless of the absence of function arguments, all functions must be specified with parentheses. On omission, database servers perceive them to be column names.
• DATE constants not fully specified are permitted in expressions. If the year/month is not specified in the DATE type value, the current year/month is assumed as the default value. The time zone value cannot be specified for the DATE type of Altibase.
• Built-in SQL functions that always return identical values can be included in expressions; for example, the SYSDATE function cannot be included.
• User-defined functions included in expressions must be marked DETERMINISTIC.
• Subqueries cannot be included in expressions.
• Sequences cannot be included in expressions.
• Pseudo columns of all sorts cannot be included in expressions.
• The PRIOR operator cannot be included in expressions.
• LOB data cannot be included in expressions.
• A function-based index can be selected during query optimization only if the QUERY_REWRITE_ENABLE property is set to 1.

ASC/DESC

The use of the ASC or DESC keywords respectively specifies that each column of the index is to be sorted in ascending or descending order.

index_partitioning_clause

This is used to specify that the index to be created is a partitioned index. If omitted, index partitions will be stored in the table's default tablespace.

The easiest way to create a partitioned index is simply to specify the LOCAL keyword when creating the index. Alternatively, the attributes of the index partition to be created for each table partition can be specified in greater detail.

If only the LOCAL keyword is specified, an index partition is created for each table partition, and the names of the partitions are automatically determined by the system. Index partitions are successively named SYS_IDX1, SYS_IDX2, etc.

The attributes of index partitions can be expressly specified for some or all table partitions. When the attributes of only some index partitions are specified, the attributes of the index partitions for the remaining table partitions are determined automatically as outlined above.

If no tablespace is specified when creating a partitioned index, the process for determining the tablespace in which each of the index partitions is stored is as shown in the following diagram:

Note: Even if the tablespace in which each parititon is to be stored is specified, it is ignored, as memory indexes are not stored in tablespaces.

BTREE

This is used to specify that the index is a B+ -tree index, which is useful in situations where ranges are often searched. An index can be either a B+ -tree or an R-tree index. If the INDEXTYPE IS clause is omitted, the index will be a B+ -tree index by default.

RTREE

This is used to specify that the index is an R-tree index, which is useful for processing multidimensional data.

directkey_clause

Creates direct key indexes. A direct key is a record stored in the index node; a direct key index can reduce index scan cost since it stores the actual record and the record pointer.

MAXSIZE integer

Sets the maximum size of a direct key; on omission, the default value is 8 bytes. Please refer to the table below for supported data types.

If a value larger than MAXSIZE is set for a data type that supports partial keys, it is stored as a prefix that corresponds to MAXSIZE and a direct key index is successfully created.

If a value larger than MAXSIZE is set for a data type that does not support partial keys, a direct key index fails to be created.

Direct Key Index Restrictions

• If a direct key index is created on composite index, the first column is set as the direct key.
• A direct key index cannot be created on compressed or encrypted column.
• A direct key index cannot be created for indexes residing on disk.

The following data types support direct key indexes.

Category	Data Type	MAXSIZE (Supports Full Keys)	Support Partial Keys)
Native Numeric Data Type	BINGINT	8	X
	DOUBLE	8	X
	INTEGER	4	X
	REAL	4	X
	SMALLINT	2	X
Non-Native Numeric Data Type	FLOAT FLOAT(p)	23 3 + ( ( p + 2 ) / 2 )	X
	NUMBER NUMBER(p, s) NUMBER(p)	23 3 + ( ( p + 2 ) / 2 ) 3 + ( ( p + 2 ) / 2 )	X
	NUMERIC NUMERIC(p, s) NUMERIC(p)  (=DECIMAL)	23 3 + ( ( p + 2 ) / 2 ) 3 + ( ( p + 2 ) / 2 )	X
Character Data Type	CHAR(M)	M + 2	O
	VARCHAR(M)	M + 2	O
	NCHAR(M)	(M * 2) + 2 <UTF16> (M * 3) + 2 <UTF8	O
	NVARCHAR(M)	(M * 2) + 2 <UTF16> (M * 3) + 2 <UTF8>	O
Date Data Type	DATE	8	X
Binary Data Type	BIT(M)	(M / 8) + 4	X
	VARBIT(M)	(M / 8) + 4
	BYTE(M)	M + 2	X
	NIBBLE(M)	(M / 2) + 1

physical_attributes_clause

INITRANS Clause

This is used to set the initial number of TTS (Touched Transaction Slots). The default is 8.

MAXTRANS Clause

This is used to set the maximum number of TTS (Touched Transaction Slots). The default is 50.

TABLESPACE Clause

This is used to specify the name of the tablespace in which the index is to be stored. If this clause is omitted, Altibase stores the index in the default tablespace for the owner of the schema to which the index belongs. However, when an index is created for a memory table, even if the tablespace is specified, this clause is ignored because memory indexes are not stored in any tablespace.

parallel_clause

This is a hint for setting the number of threads used to create an index, with the aim of realizing a performance improvement. Altibase determines the optimal number of index creation threads in consideration of the user-defined parallel_factor, which is specified using a hint, the size of the tables for which the indexes are being created, and the amount of available memory at the time the index is created.

The value of parallel_factor can be set within the range from 0 to 512. The default is the number of CPUs in the host on which Altibase is running. Because the number of index creation threads is determined using the above optimized determination method, it is safe to omit parallel_factor.

If parallel_factor is not set, or is set to 0, the value of the INDEX_BUILD_THREAD_COUNT property in the altibase.properties file, which has the same meaning as parallel_factor, is used instead. If the INDEX_BUILD_THREAD_COUNT property has not been set either, the number of CPUs is used as a hint to set the optimum number of index creation threads.

If parallel_factor is set to a value that is greater than the number of CPUs, or is set to a value greater than 512, the user-defined value is ignored and the number of CPUs is used as a hint for setting the optimum number of threads.

logging_clause

The LOGGING or NOLOGGING clause can be used to enable or disable logging when an index is created for a disk table. Logging is enabled by default, meaning that information about the creation of the index is logged when an index is created.

The FORCE and NOFORCE options are used to determine whether to forcibly store a disk index to disk immediately after the index is created.

For more details about logging_clause, please refer to the section pertaining to indexes in the "Objects and Privileges" chapter of the Administrator's Manual.

storage_clause

This is used to set parameters for managing extents in segments.

INITEXTENTS Clause

This sets the number of extents that are allocated by default when a segment is created. The default is 1.

NEXTEXTENTS Clause

This sets the number of extents that are added to the segment every time the segment is increased in size. The default is 1.

MINEXTENTS Clause

This sets the minimum number of extents in a segment. The default is 1.

MAXEXTENTS Clause

This set the maximum number of extents in a segment. If this isn't specified, there is no maximum limit on the number of extents in a segment.

Considerations

• In the case of an index for a partitioned table, i.e. a partitioned index, the tablespace in which each local index is stored is defined separately in index_partitioning_clause. disk_index_attributes cannot be used to specify the tablespace for an entire partitioned index. Additionally, a local index can only be a B+ -tree index.

• In the event of a system or media fault, the consistency of an index that was created using the NOLOGGING (FORCE/NOFORCE) option cannot be guaranteed. In this case, the error "The index is inconsistent" will be raised. To fix this error, locate the inconsistent index, drop it, and create it again. The consistency of an index can be checked using the V$DISK_BTREE_HEADER performance view.

• An index cannot be created on the basis of a LOB column.

Example 1: Index Creation Basics

<Query 1> Create the index emp_idx2 on the column eno in ascending order and on the column dno in descending order.

iSQL> CREATE INDEX emp_idx2
    ON employees (eno ASC, dno DESC);
Create success.

Example 2: Creating a Unique Index

<Query 2> Create a unique index named emp_idx2 for the dno column in the employees table in descending order. (This is possible when there are no records in the employees table, or when there are only unique values in the column dno.)

iSQL> CREATE UNIQUE INDEX emp_idx2
    ON employees (dno DESC);
Create success.

Example 3: Creating a B+Tree Index

<Query 3> Create the B+ -tree index emp_idx3 in ascending order for the eno column in the employees table. Because a primary key already exists for the eno column of the employees table, it must first be deleted before the index emp_idx3 can be created. If it is not deleted first, the following error will be raised:

ERR-3104C: Duplicate key columns in an index

iSQL> ALTER TABLE employees
    DROP PRIMARY KEY;
Alter success.
iSQL> CREATE INDEX emp_idx3
    ON employees (eno ASC)
    INDEXTYPE IS BTREE;
Create success.

Example 4: Specifying Tablespace

<Query 4> Create the index idx1 in the user_data tablespace on the basis of the i1 column in the table_user table.

iSQL> CREATE INDEX idx1
    ON table_user (i1)
    TABLESPACE user_data;
Create success.

Example 5: Using Parallel Option

<Query 5> Create the index idx2 in the user_data tablespace on the basis of the i1 column in the table_user table using the parallel option.

iSQL> CREATE INDEX idx1
    ON table_user (i1)
    TABLESPACE user_data PARALLEL 4;
Create success. 

Example 6: Creating a Local Index

<Query 6> Create a local index, that is, an index in which the partitions correspond to respective table partitions, based on product_id. Allow the partition names to be determined automatically

CREATE INDEX prod_idx ON products(product_id) LOCAL;

Example 7: Creating a Local Index - Specifying Index Partitions

<Query 7> Create a local index, specifying the attributes for each index partition.

CREATE INDEX prod_idx ON products(product_id) 
LOCAL
( 
  PARTITION p_idx1 ON p1 TABLESPACE tbs_disk1,
  PARTITION p_idx2 ON p2 TABLESPACE tbs_disk2,
  PARTITION p_idx3 ON p3 TABLESPACE tbs_disk3 
);

Example 8: Creating a Local Index - Specifying Partial Index Partitions

<Query 8> Create a local index, specifying the attributes for only some of the index partitions. The attributes for the other partitions are determined automatically.

CREATE INDEX prod_idx ON products(product_id) 
LOCAL
( 
  PARTITION p_idx1 ON p1 TABLESPACE tbs_disk1,
  PARTITION p_idx3 ON p3 TABLESPACE tbs_disk3 
);

Example 9: Using LOGGING Option

<Query 9> Create the index idx1 in the table employees based on the employee number column (eno). Enable logging to ensure the availability of the index in the event of a system or media fault. Assume that the table employees is in a disk tablespace.

iSQL> CREATE INDEX idx1
    ON employees (eno);
Create success.

Or

iSQL> CREATE INDEX idx1
    ON employees (eno) LOGGING ;
Create success.

Example 10: Using NOLOGGING and NOLOGGING FORCE Options

<Query 10> Create the index idx1 in the table employees with the NOLOGGING option using the employee number column (eno) in ascending order and the department number column (dno) in ascending order. Ensure that the index is available in the event of a system fault after the index is created (FORCE). Assume that the table employees is in a disk tablespace.

iSQL> CREATE INDEX idx1
    ON employees (eno ASC, dno ASC)
    NOLOGGING;
Create success.

Or
employee number(eno): ASC
department number(dno): ASC
iSQL> CREATE INDEX idx1
    ON employees (eno ASC, dno ASC)
    NOLOGGING FORCE;
Create success.

Example 11: Using NOLOGGING NOFORCE Option

<Query 11> Create the index idx1 in the table employees with the NOLOGGING option using the employee number column (eno) in ascending order and the department number column (dno) in ascending order. Specify that the index is not to be written to disk (NOFORCE). Assume that the table employees is in a disk tablespace.

iSQL> CREATE INDEX idx1
    ON employees (eno ASC, dno ASC)
    NOLOGGING NOFORCE;
Create success.

Example 12: Specifying Extent Management Parameters

<Query 12> Create the index LOCAL_IDX in the disk tablespace USERTBS for the table LOCAL_TBL. Allocate one extent to the index when it is created and specify that 2 extents are to be added whenever it is necessary to increase the size of the index segment, and also that there is no upper limit to the total number of extents in the index segment.

iSQL> CREATE INDEX LOCAL_IDX ON LOCAL_TBL ( I1 ) 
 TABLESPACE USERTBS
 STORAGE ( INITEXTENTS 1 NEXTEXTENTS 2 MAXEXTENTS UNLIMITED );
Create success.

Example 13: Creating a Function-Based Index

<Query 13> Create a function-based index on salary using the salary column of the employees table.

iSQL> CREATE INDEX income_idx ON employees (salary*12);
Create success.

Example 14: Creating a Function-Based Index - Using User-Defined Functions

<Query 14> Create the same index as using a user-defined function.

CREATE OR REPLACE FUNCTION get_annual_salary
(salary in integer) 
RETURN integer
DETERMINISTIC
AS
BEGIN
    RETURN salary*12;
END;
/

iSQL> CREATE INDEX income_idx ON employees(sys.get_annual_salary(salary));
Create success.

Example 15: Creating a Direct Key Index

<Query 15> Create a direct key index on the eno column in the table employees.

iSQL> CREATE INDEX direct_idx ON employees ( eno ) DIRECTKEY ;
Create success.

Example 16: DIRECTKEY MAXSIZE

<Query 16> Create a direct key index that can store 32 byte records on the name column (VARCHAR(100)) in the tab1 table.

iSQL> CREATE INDEX idx1 ON tab1 ( name ) DIRECTKEY MAXSIZE 32;
Create success.