Let us consider the library database to understand the examples described below :
Figure 1: Library Database
Indexes are used for the faster retrieval of records. It is another lookup table where the mapping between the column and the data are kept. It actually points to the memory address for each record. Hence when we use those columns in the WHERE clause, the index table is accessed to check the address. Once address is retrieved, it directly goes to that location get all other details.
Table of Contents
Indexes created by using below syntax :
CREATE INDEX index_name ON table_name (column_name/s);
There are several types of indexes like implicit, single column, complex, unique etc.
Implicit Index :
These indexes are created automatically when a column is defined as primary key or unique. We need not create index on such columns. Below screenshot shows the default index created on BOOKS table when primary key on ISBN is created. Hence we can see index name is system generated one.
Single column Index :
Indexes can be created on single or multiple columns of a same table. If index is created on single column of a table, then it is called as single column index.
Let us create an index on BOOK_NAME of BOOKS table. Since we are creating index on one column of BOOKS, it is a single column index.
CREATE INDEX ind_book_name ON BOOKS (BOOK_NAME);
Unique Index :
When indexes are created, they simply create a lookup table in which they will have the mapping between the columns of index and their address location. But it does not guarantee that the values entered for the column or columns of index are unique. It does not allow any duplicate values to be entered. Thus it guarantees the data integrity as well as faster retrieval of data.
CREATE UNIQUE INDEX ind_book_name ON BOOKS (BOOK_NAME);
We can see the indexes and constraints created on any tables in its schema browser, when any tools like SQL developer or Toad is used. Otherwise we can query data dictionary view, USER_INDEXES to see all the indexes created by current user. If we want to see the index created on any particular table, then we can query like below :
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = ‘BOOKS’;
Composite Index :
These are the indexes created on more than one column of a table. Suppose we have to query BOOK_LOAN table. Usually when we query this table, we would like to see which book is borrowed on particular date. Hence we will have ISBN, its copy number and loan date in the WHERE clause. In order to make this query faster, if we create index on these three columns, we can easily find the records with these three column combination. Thus:
CREATE INDEX ind_book_loan ON BOOK_LOAN (ISBN, BOOK_CP_NUMBER, LOAN_DATE);
Suppose we have another query to see the books borrowed by the borrower on particular date. Then the query involves borrower id and loan date in the WHERE clause. Hence if we create index on these two columns, we can get faster result.
CREATE INDEX ind_bl_brwr ON BOOK_LOAN (BRWR_ID, LOAN_DATE);
Indexes created on the table can be verified by querying USER_INDEXES view.
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = ‘BOOK_LOAN’;
Figure 2: USER_INDEXES View
Figure 3: Schema Browser
DROP Index :
Indexes can be dropped if they are no longer required on the table. If we have indexes on table which is of no use, then it will increase the retrieval time. It is always better to have minimal indexes on table.
General method of deleting the index is by dropping it:
DROP INDEX index_name;
DROP INDEX ind_book_name;
One can verify, if index still exists on the table or not by querying USER_INDEXES view.
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = ‘BOOKS’;
