When looking for a particular topic in a book, you can either scan the whole book looking for your topic, or you can use the book s index to find the exact location of the topic directly. An index for a database table is similar in concept to a book index, except that database indexes are used to find specific rows in a table. The downside of indexes is that when a row is added to the table, additional time is required to update the index for the new row.
Generally, you should only create an index on a column when you find that you are retrieving a small number of rows from a table containing many rows. A good rule of thumb is that an index is useful when you expect any single query to retrieve 10 percent or less of the total rows in a table.
This means that the candidate column for an index should be used to store a wide range of values. A good candidate for indexing would be a column containing a unique number for each record, while a poor candidate for indexing would be a column that only contains a small range of numeric codes such as 1, 2, 3, or 4. This consideration applies to all database types, not just numbers . An Oracle database automatically creates an index for the primary key of a table and for columns included in a unique constraint.
Note | Normally, the DBA is responsible for creating indexes, but as an application developer you will be able to provide the DBA with feedback on which columns are good candidates for indexing. This is because you may know more about the application than the DBA. |
In this section, you ll learn how to:
Create an index
Create a function-based index
Get information on an index from the data dictionary
Modify an index
Drop an index
You create an index using CREATE INDEX , which has the following simplified syntax:
CREATE [UNIQUE] INDEX index_name ON table_name ( column_name [, column_name ...]) TABLESPACE tab_space;
where
UNIQUE specifies the values in the indexed columns must be unique.
index_name specifies the name you assign to the index.
table_name specifies the name of the table on which the index is created.
column_name specifies the name of the column on which the index is created. You can create an index on multiple columns; such an index is known as a composite index .
tab_space specifies the tablespace for the index. If you don t provide a tablespace, the index is stored in the user s default tablespace.
Tip | For performance reasons you should typically store indexes in a different tablespace from tables, but for simplicity the examples in this chapter use the default tablespace. In your own database you should have the DBA create separate tablespaces for tables and indexes. |
I ll now guide you through the thought processes you should follow when creating an index. Then you ll see an example CREATE INDEX statement. Assume that the customers table contains a large number of rows, and you regularly retrieve rows from the customers table using a SELECT that contains a WHERE clause that filters the rows based on the last_name column; for example:
SELECT customer_id, first_name, last_name FROM customers WHERE last_name = 'Brown';
Also assume that the last_name column contains fairly unique values, so that any query using the last_name column in a WHERE clause will return less than 10 percent of the total number of rows in the table. The last_name column is therefore a good candidate for indexing.
The following CREATE INDEX statement creates an index named customers_last_name_idx on the last_name column of the customers table:
CREATE INDEX customers_last_name_idx ON customers(last_name);
Once the index has been created, queries such as the previous one that searched for specific last names should take less time to complete than was required without the index.
You can enforce uniqueness of values in a column using a unique index. For example, the following statement creates a unique index named customers_phone_idx on the customers.phone column:
CREATE UNIQUE INDEX customers_phone_idx ON customers(phone);
You can also create a composite index on multiple columns. For example, the following statement creates a composite index named employees_first_last_name_idx on the first_name and last_name columns of the employees table:
CREATE INDEX employees_first_last_name_idx ON employees(first_name, last_name);
In the previous section you saw the index customers_last_name_idx, which was created as follows :
CREATE INDEX customers_last_name_idx ON customers(last_name);
Let s say you issue the following query:
SELECT first_name, last_name FROM customers WHERE last_name = UPPER('PRICE');
Because this query uses a function ” UPPER() in this case ”the customers_last_name_idx index isn t used. If you want an index to be based on the results of a function you must create a function-based index. For example:
CREATE INDEX customers_last_name_func_idx ON customers(UPPER(last_name));
In addition, your DBA must set the initialization parameter QUERY_REWRITE_ENABLED to true (the default is false) in order to take advantage of function-based indexes. For example:
CONNECT system/manager ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;
You can get information on your indexes from user_indexes. Table 10-7 describes some of the columns in user_indexes.
Column | Type | Description |
---|---|---|
index_name | VARCHAR2(30) | Name of the index. |
table_owner | VARCHAR2(30) | The user who owns the table on which the index was created. |
table_name | VARCHAR2(30) | The name of the table on which the index was created. |
uniqueness | VARCHAR2(9) | Indicates whether the index is unique. Set to UNIQUE or NONUNIQUE . |
status | VARCHAR2(8) | Indicates whether the index is valid. Set to VALID or INVALID . |
Note | You can get information on all the indexes you have access to using all_indexes. |
The following example retrieves the index_name, table_name, uniqueness , and status from user_indexes for the customers and employees tables. Notice the list of indexes includes customers_pk, which is a unique index automatically created by the database for the customer_id primary key column of the customers table:
SELECT index_name, table_name, uniqueness, status FROM user_indexes WHERE table_name IN ('CUSTOMERS', 'EMPLOYEES'); INDEX_NAME TABLE_NAME UNIQUENES STATUS ------------------------------ ---------------- --------- ------ CUSTOMERS_LAST_NAME_IDX CUSTOMERS NONUNIQUE VALID CUSTOMERS_PHONE_IDX CUSTOMERS UNIQUE VALID CUSTOMERS_PK CUSTOMERS UNIQUE VALID CUSTOMERS_LAST_NAME_FUNC_IDX CUSTOMERS NONUNIQUE VALID EMPLOYEES_FIRST_LAST_NAME_IDX EMPLOYEES NONUNIQUE VALID EMPLOYEES_PK EMPLOYEES UNIQUE VALID
You can get information on the indexes on a column by querying user_ind_columns. Table 10-8 describes some of the columns in user_ind_columns.
Note | You can get information on all the indexes you have access to using all_ind_columns. |
The following example retrieves the index_name, table_name, and column_name from user_ind_columns for the customers and employees tables:
COLUMN table_name FORMAT a15 COLUMN column_name FORMAT a15 SELECT index_name, table_name, column_name FROM user_ind_columns WHERE table_name IN ('CUSTOMERS', 'EMPLOYEES'); INDEX_NAME TABLE_NAME COLUMN_NAME ------------------------------ --------------- ---------------- CUSTOMERS_PK CUSTOMERS CUSTOMER_ID CUSTOMERS_LAST_NAME_IDX CUSTOMERS LAST_NAME CUSTOMERS_PHONE_IDX CUSTOMERS PHONE EMPLOYEES_PK EMPLOYEES EMPLOYEE_ID EMPLOYEES_FIRST_LAST_NAME_IDX EMPLOYEES FIRST_NAME EMPLOYEES_FIRST_LAST_NAME_IDX EMPLOYEES LAST_NAME
Column | Type | Description |
---|---|---|
index_name | VARCHAR2(30) | Name of the index. |
table_name | VARCHAR2(30) | The name of the table on which the index was created. |
column_name | VARCHAR2(4000) | Name of the column in which the index was created. |
You modify an index using ALTER INDEX. The following example renames the customers_phone_idx index to customers_phone_number_idx:
ALTER INDEX customers_phone_idx RENAME TO customers_phone_number_idx;
You drop an index using the DROP INDEX statement. The following example drops the customers_phone_number_idx index:
DROP INDEX customers_phone_number_idx;
This concludes the discussion of indexes. In the next section, you ll learn about views.