ABOUT INDEXES

ABOUT INDEXES


################## SYNTAXES #######################

CREATE [UNIQUE] INDEX index_name  ON table_name (column1, column2, ... column_n)  [ COMPUTE STATISTICS ] TABLESPACE QP_INDEX;
CREATE [UNIQUE] INDEX index_name  ON table_name (function1, function2, . function_n)  [ COMPUTE STATISTICS ] TABLESPACE QP_INDEX;----Function means conditions


ALTER INDEX index_name  RENAME TO new_index_name TABLESPACE QP_INDEX;-------------Rename syntax
ALTER INDEX index_name  REBUILD COMPUTE STATISTICS TABLESPACE QP_INDEX;------- Rebuild statistics/index


DROP INDEX index_name;--------------------drop index sysntax


CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type]  ON tbl_name (index_col_name,...)  [index_option] ...


index_col_name:
    col_name [(length)] [ASC | DESC]


index_type:
    USING {BTREE | HASH}


index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name




################# EXAMPLES #######################

CREATE INDEX supplier_idx  ON supplier (supplier_name) TABLESPACE QP_INDEX;

CREATE INDEX supplier_idx  ON supplier (supplier_name, city)  COMPUTE STATISTICS TABLESPACE QP_INDEX;

CREATE INDEX supplier_idx  ON supplier (UPPER(supplier_name)) TABLESPACE QP_INDEX;-----------------------------------function example

ALTER INDEX supplier_idx  RENAME TO supplier_index_name TABLESPACE QP_INDEX;---------------------------------------rename example

ALTER INDEX supplier_idx  REBUILD COMPUTE STATISTICS TABLESPACE QP_INDEX;--------------------------------------rebuild example

DROP INDEX supplier_idx;------------------------------------------------------------------------------- drop index example