Index

CLUSTERED INDEX

GLOBAL PARTITIONED INDEX

.

LOGGING|NOLOGGING

LOGGING|NOLOGGING(FORCE|NOFORCE)

PCTFREE, PCTUSED, INITRANS, MAXTRANS

INITRANS, MAXTRANS

COMPRESS|NOCOMPRESS

INDEXTYPE IS [INDEXTYPE NAME]

INDEX TYPE IS [BTREE|RTREE]

ALTIBASE HDB supports Btree by default.
Rtree is useful for processing multidimensional data.

SORT|NOSORT

Specify NOSORT to indicate to the database that the rows are already stored in the database in ascending order, so that ORACLE Database does not have to sort the rows when creating the index.

REVERSE

Specify REVERSE to store the bytes of the index block in reverse order, excluding the rowid.

ONLINE

Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index.

CREATE UNIQUE INDEX emp_idx2
ON employees (dno DESC);

CREATE INDEX idx2
ON table_user (i1)
TABLESPACE user_data PARALLEL 4;

CREATE INDEX emp_idx3
ON employees (eno ASC)
INDEXTYPE IS [INDEX TYPE NAME]; 

CREATE INDEX prod_idx ON products(product_id) LOCAL;

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
);

CREATE UNIQUE INDEX emp_idx2
ON employees (dno DESC);

CREATE INDEX idx2
ON table_user (i1)
TABLESPACE user_data PARALLEL 4;


CREATE INDEX emp_idx3
ON employees (eno ASC)
INDEXTYPE IS BTREE;

CREATE INDEX prod_idx ON products(product_id) LOCAL;

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
);

ENABLE|DISABLE

ALTIBASE HDB provides "ALL INDEX ENABLE|DISABLE
But it can be used in  "ALTER TABLE" statement

UNUSABLE

COALESCE

MONITORING|NOMONITORING USAGE

UPDATE BLOCK REFERENCES

DEALLOCATE UNUSED

ALLOCATE EXTENT

SHRINK SPACE(COMPACT CASCADE)

PCTFREE, PCTUSED, INITRANS

REBUILD (PARTITION|SUBPARTITION,
REVERSE|NOREVERSE)

REBUILD PARTITION

ALTIBASE HDB only supports REBUILD PARTITION clause.

COMPUTE STATISTICS

In ALTIBASE HDB, rebuilding statistics can be executed using "alter index .. aging" statement.
It might be faster than index rebuilding operation because it only searches the leaf nodes.
When "__DISK_INDEX_REBUILD_STAT" property value is set to 0,
at the time of aging operation using "alter index .. aging",
index aging operation might be executed.
When "__DISK_INDEX_REBUILD_STAT" property value is set to 1,
at the time of aging operation using "alter index .. aging",
rebuilding statistics operation might be executed.
During rebuilding statistics, it rebuilds cardinality, min , max value of indexes.

ONLINE

COMPRESS|NOCOMPRESS

RENAME TO

RENAME TO

AGING

This is used to record a transaction commit SCN in an index page and delete old versions of nodes.
This statement is only available for disk-based indexes.

SET PERSISTENT = ON|OFF

To change a non-persistent index into a persistent index. and vice versa.
The use of persistent indexes as memory table
indexes is supported in order to reduce the boot-up time when the system is started.

ALTER INDEX IDX1 REBUILD PARTITION IDX_P5 TABLESPACE TBS1;

ALTER INDEX IDX1 REBUILD PARTITION IDX_P5 TABLESPACE TBS1;

ALTER INDEX emp_idx1 RENAME TO emp_idx2;

ALTER INDEX emp_idx1 RENAME TO emp_idx2;

ALTER INDEX ord_customer_ix REBUILD REVERSE;

ALTER INDEX ord_customer_ix REBUILD;

ALTER INDEX ord_customer_ix REBUILD PARALLEL;

ALTER INDEX ord_customer_ix REBUILD;

DROP INDEX ord_customer_ix_demo FORCE;

DROP INDEX ord_customer_id_demo;