Everything You Need to Know [About] Alter Index in DB2

Creating an index on a Table increases search speed (you will get results quicker). Coming to indexes, you can create more than one on a Table. Don’t be confused with the Primary key.

Indexes are two types-Unique indexes and Non-unique indexes. It is up to you to decide the number of indexes. Here’re the examples that use in DB2.

How to create an index

Below is the syntax to create an index. The Unique states that the values in this column should be unique. Here, the column name is DEPTNO. The ‘ASC’ denotes in ascending order.

CREATE UNIQUE INDEX SCHEMA.INDEX_NAME 
     ON SCHEMA.DEPT(DEPTNO ASC);

How to alter an index

Alter index in DB2, which you can use to change the parameters. Moreover, suppose you added a new column to a table, and you want to add that column to the index. Yes, you can do that. Here is the way.

ALTER TABLE SCHEMA.DEPT ADD COLUMN ZIPCODE CHAR(5); 
ALTER INDEX SCHEMA.INDEX_NAME ADD COLUMN (ZIPCODE); 
COMMIT;

In other ways, suppose you want to delete a column from an index, then you need to drop index, and then you need to re-create it.

How to drop an index

Coming to drop an index, here is a syntax to drop. Here the stores_demo is a database. The ‘prod’ is an instance. The ‘Informix’ is the name of the owner.

DROP INDEX stores_demo@prod:"informix".SCHEMA.INDEX_NAME;

If data is present in the table, and you want to delete a column from an index, first drop index, then recreate it with column names as needed.

Shopping

Best Sellers in Home & Kitchen

Related