The INDEX is used to create and retrieve data from the database very quickly. An Index can be created by using a single or group of columns in a table. When the index is created, it is assigned a ROWID for each row before it sorts out the data.
Proper indexes are good for performance in large databases, but you need to be careful while creating an index. A Selection of fields depends on what you are using in your SQL queries.
For example, the following SQL syntax creates a new table called CUSTOMERS and adds five columns in it.
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Now, you can create an index on a single or multiple columns using the syntax given below.
CREATE INDEX index_name ON table_name ( column1, column2.....);
To create an INDEX on the AGE column, to optimize the search on customers for a specific age, you can use the follow SQL syntax which is given below −
CREATE INDEX idx_age ON CUSTOMERS ( AGE );
To drop an INDEX constraint, use the following SQL syntax.
ALTER TABLE CUSTOMERS DROP INDEX idx_age;