| There are different types of indexes that can be created on tables in a database, all of which serve the same goalto improve database performance by expediting data retrieval. This hour discusses single-column indexes, composite indexes, and unique indexes.  | Indexes can be created during table creation in some implementations. Most implementations accommodate a command, aside from the CREATE TABLE command, used to create indexes. You must check your particular implementation for the exact syntax for the command, if any, that is available to create an index. |
Single-Column Indexes Indexing on a single column of a table is the simplest and most common manifestation of an index. Obviously, a single-column index is one that is created based on only one table column. The basic syntax is as follows :  CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME) For example, if you want to create an index on the EMPLOYEE_TBL table for employees ' last names , the command used to create the index would look like the following: CREATE INDEX NAME_IDX ON EMPLOYEE_TBL (LAST_NAME); | | You should plan your tables and indexes. Do not assume that because an index has been created that all performance issues are resolved. The index may not help at all (it may actually hinder performance) and may just take up disk space. |
 | Single-column indexes are most effective when used on columns that are frequently used alone in the WHERE clause as query conditions. Good candidates for a single-column index are an individual identification number, a serial number, or a system-assigned key. |
Unique Indexes  | Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. Otherwise, the unique index performs the same way a regular index performs . The syntax is as follows: | CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME) If you want to create a unique index on the EMPLOYEE_TBL table for an employee's last name , the command used to create the unique index would look like the following: CREATE UNIQUE INDEX NAME_IDX ON EMPLOYEE_TBL (LAST_NAME); The only problem with this index is that every individual's last name in the EMPLOYEE_TBL table must be uniquepretty impractical . However, a unique index should be created for a column, such as an individual's Social Security number, because each of these numbers for each individual is unique. You may be wondering, "What if an employee's SSN were the primary key for a table?" An index is usually implicitly created when you define a primary key for a table. However, a company can use a fictitious number for an employee ID, but maintain each employees' SSN for tax purposes. You probably want to index this column and ensure that all entries into this column are unique values. | | A unique index can only be created on a column in a table whose values are unique. In other words, you cannot create a unique index on an existing table with data that already contains records on the indexed key. |
Composite Indexes | | A composite index is an index on two or more columns of a table. You should consider performance when creating a composite index because the order of columns in the index has a measurable effect on data retrieval speed. Generally, the most restrictive value should be placed first for optimum performance. However, the columns that will always be specified should be placed first. The syntax is as follows: | CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN1, COLUMN2) An example of a composite index follows: CREATE INDEX ORD_IDX ON ORDERS_TBL (CUST_ID, PROD_ID); In this example, you create a composite index based on two columns in the ORDERS_TBL table: CUST_ID and PROD_ID. You assume that these two columns are frequently used together as conditions in the WHERE clause of a query. | | Composite indexes are most effective on table columns that are used together frequently as conditions in a query's WHERE clause. |
Single-Column Versus Composite Indexes In deciding whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions. Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, a composite index would be the best choice. Implicit Indexes | | Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints. Why are indexes automatically created for these constraints? Imagine that you are the database server. A user adds a new product to the database. The product identification is the primary key on the table, which means that it must be a unique value. To efficiently check to make sure the new value is unique among hundreds or thousands of records, the product identifications in the table must be indexed. Therefore, when you create a primary key or unique constraint, an index is automatically created for you. |  |
