CREATE and ALTER INDEXES

create and alter INDEXES with synonyms and examples


########################### 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;--------------------------------------rebild example
DROP INDEX supplier_idx;------------------------------------------------------------------------------- drop idex example