How to Create an Index in Oracle/SQL

Indexing tables is an imperative step for a good table design and fast performance. Without indexes, your system can come to a grinding halt with only a few thousand rows. Indexes group table data in the order of the defined index values. The purpose of indexes on a table is the improvement of table performance, since they increase speed on the database engine, helping it find the requested rows. Just like organizing a file cabinet alphabetically, the database engine is able to find the data more quickly since it's better organized.

Things You'll Need

• Oracle database engine

Instructions

1. Open the Oracle command window. This is done either from the management software on your computer or by accessing the server desktop.

2. Create a practice table. For this example, you'll create a small employee table.
   SQL> create table Customer(
   2 ID VARCHAR2(4 BYTE) NOT NULL,
   3 First_Name VARCHAR2(10 BYTE),
   4 Last_Name VARCHAR2(10 BYTE),
   )

3. Determine which columns your users will need to query. For instance, most employees need to search for customers based on last name. Since last name is likely to be a large volume of searches, creating an index on this column will improve database performance. Create an index using the following line of code:
   CREATE INDEX customer_last_name_idx ON customer(last_name);

4. Specify unique indexes for columns where values must always be different. For instance, ID in the table example needs to be unique.
   CREATE [UNIQUE] INDEX customer_Id ON
   customer(Id)
   TABLESPACE customer_space;

5. Drop indexes if you find a table has indexes on columns that are not needed. Too many indexes actually harm performance rather than improve it.
   drop index customer_last_name_idx;

References

• Java2.com