| Oracle® Database 2 Day DBA 11g Release 1 (11.1) Part Number B28301-01 |
|
|
View PDF |
| Oracle® Database 2 Day DBA 11g Release 1 (11.1) Part Number B28301-01 |
|
|
View PDF |
The following sections describe how to create and manage indexes:
Indexes are optional schema objects that are associated with tables. You create indexes on tables to improve query performance. Just as the index in a guide helps you to quickly locate specific information, an Oracle Database index provides a quick access path to table data.
You can create as many indexes on a table as you need. You create each index on one or more columns of a table. For example, in a purchase orders table, if you create an index on the vendor number column, you can then sequentially access the rows of the table in vendor number order, without having to actually sort the rows. Additionally, you can directly access all purchase orders issued to a particular vendor without having to scan the entire table.
After an index is created, it is automatically maintained and used by the database. Changes to the data or structure of a table, such as adding new rows, updating rows, or deleting rows, are automatically incorporated into all relevant indexes. This is transparent to the user.
Some indexes are created implicitly through constraints that are placed on a table. For example, the database automatically creates an index on the columns of a primary key constraint or unique key constraint.
The following sections provide more background information about indexes:
Indexes generally improve the performance of queries and DML statements that operate on a single, existing row or a small number of existing rows. However, too many indexes can increase the processing overhead for statements that add, modify, or delete rows.
To determine if you can improve application performance with more indexes, you can run the SQL Access Advisor in Oracle Enterprise Manager Database Control (Database Control). See "Running the SQL Access Advisor".
Before you add additional indexes, examine the performance of your database for queries and DML. You can then compare performance after the new indexes are added.
See Also:
Oracle Database 2 Day + Performance Tuning Guide for information about using the SQL Performance Analyzer to analyze the SQL performance impact of any type of schema or system changes
Indexes can be created in a number of ways, using various combinations of index attributes. The primary index attributes are the following:
A standard, B-tree index contains an entry for each value in the index key along with a disk address of the row where the value is stored. A B-tree index is the default and most common type of index in an Oracle database.
A bitmap index uses strings of bits to encapsulate values and potential row addresses. It is more compact than a B-tree and can perform some types of retrieval more efficiently. For general use, however, a bitmap index requires more overhead during row operations on the table and should be used primarily for data warehouse environments, as described in Oracle Database Data Warehousing Guide.
The default search through an index is from lowest to highest value, where character data is sorted by ASCII values, numeric data from smallest to largest number, and date from the earliest to the latest value. This default behavior is performed in indexes created as ascending indexes. You can cause index searches to reverse the search order by creating the related index with the descending option.
Typically, an index entry is based on the value or values found in the column or columns of a table. This is a column index. Alternatively, you can create a function-based index in which the indexed value is derived from the table data. For example, to find character data that can be in mixed case, you can use a function-based index based on the UPPER() function to look for the values as if they were all in uppercase characters.
You can create an index on just one column, which is called a single-column index, or on multiple columns, which is called a concatenated index. Concatenated indexes are useful when all of the index columns are likely to be included in the WHERE clause of frequently executed SQL statements.
For concatenated indexes, you must define the columns used in the index carefully so that the column with the fewest duplicate values is named first, the column with next fewest duplicate values is second, and so on. Columns with many duplicate values or many rows with NULL values should not be included or should be the last-named columns in the index definition.
As with tables, you can partition an index. In most situations, it is useful to partition an index when the associated table is partitioned, and to partition the index using the same partitioning scheme as the table. (For example, if the table is range-partitioned by sales date, you create an index on sales date and partition the index using the same ranges as the table partitions.) This is known as a local partitioned index. However, you do not need to partition an index using the partitioning same scheme as its table. You can also create a nonpartitioned, or global, index on a partitioned table.
See Also:
Oracle Database Concepts and Oracle Database Administrator's Guide for design and management considerations of different index types
Oracle Database SQL Language Reference for the syntax to create indexes
Oracle Database VLDB and Partitioning Guide for more information about partitioned tables and indexes
You use the Indexes page of Database Control to view the indexes in your database.
To view indexes:
Go to the Database Home page, logging in as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Indexes.
The Indexes page appears.
In the Search By list, do one of the following:
Select Index Name to search for indexes by name.
Every index has a system-assigned or user-assigned name.
Select Table Name to search for indexes that belong to a particular table.
In the Schema field, enter the name of a schema. Alternatively, click the flashlight icon adjacent to the Schema field to search for a schema.
Do one of the following:
If you are searching by index name, leave Object Name blank to search for and display all indexes in the schema. Alternatively, enter an index name or partial index name as a search string.
If you enter a search string in Object Name, all indexes that have names that start with the search string are displayed. If you precede the search string with an asterisk (*), all indexes that have the search string anywhere in the index name are displayed.
If you are searching by table name, enter a table name or partial table name in the Object Name field.
If you enter a partial table name as a search string, indexes are displayed for all tables that have names that start with the search string. If you precede the search string with an asterisk (*), indexes are displayed for all tables that have the search string anywhere in the table name.
Click Go.
The indexes in the specified schema are displayed.
To view the definition of a particular index, select the index and then click View. Alternatively, double-click the index name.
The View Index page appears. This page includes basic information about the index, including its status, the table and column or columns on which it is built, the space used by the index, the options used in its definition, and index statistics.
See Also:
When you create an index, you specify one or more table columns to be indexed and the type of index that you want to create.
In this example, you create a standard B-tree index on the SUPPLIER_ID column in the SH.PRODUCTS table. (The SH schema is part of the sample schemas.)
To create a supplier index on the SH.PRODUCTS table:
View the tables in the SH schema, as described in "Viewing Tables".
Select the PRODUCTS table.
In the Actions list, select Create Index, and then click Go.
The Create Index page appears.
Enter the following information:
In the Name field, enter PRODUCTS_SUPPLIER_IDX.
For Tablespace, accept the default.
For Index Type, select Standard - B-tree.
In the Table Columns list, select the SUPPLIER_ID column by entering 1 in the Order column.
If your index were to consist of multiple columns (a concatenated index), you would enter 2 in the next column to include, and so on. These numbers indicate the order in which the columns are to be concatenated, from left to right, or from most significant in the sort order to least significant.
For Sorting Order, accept the default selection of ASC (ascending).
Click OK to create the index.
The Indexes page returns and displays a confirmation message. The new index is listed in the table of indexes.
See Also:
If you no longer need an index, you can delete it with Database Control.
In this example, you delete the PRODUCTS_SUPPLIER_IDX index that you created previously on the SH.PRODUCTS table in "Example: Creating an Index".
Note:
You cannot delete an index that is currently used to enforce a constraint. You must disable or delete the constraint and then, if the index is not deleted as a result of that action, delete the index.To delete the supplier index on the SH.PRODUCTS table:
Go to the Database Home page, logging in as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Indexes.
The Indexes page appears.
In the Search By list, select Table Name.
In the Schema field, enter SH.
In the Object Name field, enter PROD.
You can enter only the first few letters of the table name.
Click Go.
All indexes on the PRODUCTS table are displayed.
Select the PRODUCTS_SUPPLIER_IDX index, and then click Delete.
A confirmation page appears.
Click Yes to delete the index.
The Indexes page returns and displays a confirmation message.
See Also:
