Discovering SQL: A Hands-On Guide for Beginners

AN INDEX FOR ALL SEASONS

One of the best ways to improve database performance is through effective indices. Without an index, the database engine will search in the dark, methodically scanning each and every record in the table until it finds the set satisfying the search criteria. With an index, it first asks for directions and then gets what's needed much faster (in most cases). As you will see in Chapter 9, indices are very important in helping the RDBMS figure out an execution plan (how to find and combine the data requested). While speeding up the retrieval, indices are detrimental for INSERT and UPDATE queries. It pays to slow down and think about how your data will be used before creating an index.

An index is an auxiliary object; it does not exist separate from a table. The basic syntax is identical across RDBMSs. Here's a statement to create an index named IX_BK_ID on column BK_ID for the table BOOKS:

CREATE INDEX ix_bk_id
   ON books (bk_id);

An index can be created for an empty table or for one that already contains data. In the latter case, some restrictions might apply, as you will see later in the chapter.

The basic CREATE INDEX syntax is deceptively simple, but rest assured that as you start digging, the complexity quickly mounts. Oracle's CREATE INDEX statement, for example, taking into consideration attributes, logging, and partitions, can span several pages of ...