INDEX is the link to access the data from the database very quickly. This index will decide the rows storing place based on the index type so that the data can be easily accessed without any delay. INDEX is an ordered set of pointers to the rows of table data. An Index can be partitioned or non-partitioned.

CREATE INDEX -

CREATE INDEX used to create the INDEX. Only single table indexes are supported.

CREATE INDEX index_nam
ON TABLE base_table_name (col_name, ...)
AS 'index.handler.class.name'
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name=property_value, ...)]
[IN TABLE index_table_name]
[PARTITIONED BY (col_name, ...)]
[
   [ ROW FORMAT ...] STORED AS ...
   | STORED BY ...
]
[LOCATION hdfs_path]
[TBLPROPERTIES (...)]
[COMMENT "index comment"]

Index partitioning matches the partitioning of the base table by default. The PARTITIONED BY clause may be used to specify a subset of the table's partitioning columns. Indexes cannot be created on views. Index handlers may require the base table being indexed.

Assume we have a requirement to create student information with the columns student number, name, class, grade for a school.

The below command will show how to create index std_index on std_details table.

Hive> CREATE INDEX std_index ON TABLE std_db.std_details
 AS 'org.apache.hadoop.Hive.ql.index.compact.
 CompactIndexHandler';

Once the above statement successfully executed, the std_db.std_index created on std_db.std_details.

ALTER INDEX -

ALTER INDEX used to rebuild the index.

ALTER INDEX index_name ON table_name
 [PARTITION (...)] REBUILD

The ALTER INDEX ... REBUILD command used to build the index structure for all partitions or a single partition. If data in the base table changes, then the REBUILD command must be used to bring the index up to date.

Assume we have a requirement to create student information with the columns student number, name, class, grade for a school.

The below command will show how to alter the index std_index on std_details table.

Hive> ALTER INDEX std_index ON std_db1.std_details1;

Once the above statement successfully executed, the std_db.std_index got altered to recreate on std_db1.std_details1.

DROP INDEX:

DROP INDEX used to drop/delete index at any time.

Hive> DROP INDEX index_name ON table_name

An index table can be dropped directly with DROP TABLE. When an indexed base table is dropped, the DROP implicitly cascades to all indexes.

Assume we have a requirement to create student information with the columns student number, name, class, grade for a school.

The below command will show how to drop index std_index on std_details.

Hive> DROP INDEX std_index

Once the above statement successfully executed, the std_db.std_index got dropped.