Indexes


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

Creating 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); 

Creating a Function-Based Index

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; 

Getting Information on Indexes

You can get information on your indexes from user_indexes. Table 10-7 describes some of the columns in user_indexes.

Table 10-7: Some 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 

Getting Information on the Indexes on a Column

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 
Table 10-8: Some Columns in user_ind_columns

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.

Modifying an Index

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; 

Dropping an Index

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.




Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net