|
Step 8 - Managing Indexes
|
Learn Oracle - Managing Indexes
Indexes are optional structures associated with tables. They can be created to improve
query performance. Just as the index in this book helps Oracle DBA to quickly locate specific
information, an Oracle index provides a quick access path to table data.
Indexes can be created on one or more columns of a table. After an index is created, it
is automatically maintained and used by Oracle. Changes to a table’s data or structure,
such as adding new rows, updating rows, or deleting rows, are automatically
incorporated into all relevant indexes with complete transparency to the user.
Some indexes are created implicitly through constraints that are placed on a table. For
example, a column with the constraint that its values be unique causes Oracle to create
a unique key index.
When Oracle DBA click the Indexes link under the Schema heading of the Administration
page, an Indexes page appears.
Viewing Indexes
To find out what indexes exist in your database, use the search options in the Indexes
page. The search works like the one for tables described earlier except that, when
searching by object name, Oracle DBA can use the name of the index or the name of the table
on which the index is built. Select whether the search is for the name of an index or of
a table by clicking the appropriate entry in the Search By list.
To view the details of an index, click its name in the Results list on the Indexes page.
Oracle DBA can find basic information about the index, such as its status and the column or
columns on which it is built, in the General area of the main page for each individual
index. Other areas include information about the space consumed by the index and the
options used in its definition.
Creating a New Index
Oracle automatically creates the indexes necessary to support data integrity defined
with constraints when Oracle DBA add or enable those constraints. For performance purposes,
Oracle DBA might want to add an index to the columns Oracle DBA define in a child table when
adding a foreign key constraint. Before Oracle DBA add any other indexes, Oracle DBA should be
ready to examine the performance of your database after they are added.
After index creation, Oracle automatically synchronizes the index with any subsequent
inserts, updates or deletes to the base table.
Indexes are generally of value to queries and to SQL statements that need to operate
on a single, existing row or a small number of existing rows. However, too many
indexes can cause serious problems by increasing the processing overhead for
statements that add, modify, or delete rows. In some cases, a statement could use two
or more indexes and the optimizer will pick just one of them. Unless other statements
can take advantage of the unused indexes, they are not providing any benefit.
Therefore, Oracle DBA might find yourself deleting indexes if Oracle DBA create too many.
Index Types
Indexes can be categorized in a number of ways, the primary options are listed in the
following section:
Standard (B-tree) and Bitmap: A standard, B-tree index contains an entry for each value in
the index key along with an address to 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 the Oracle Data Warehousing Guide.
Ascending and Descending: 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 with as ascending indexes. Oracle DBA can cause
index searches to reverse the search order by creating the related index with the
descending option.
Column and Functional: Typically, an index entry is based on the value or values found
in the table’s column or columns. This is a column index. Alternatively, Oracle DBA can create
a function-based index in which the indexed value is derived from the table data. For
example, to find character data which can be in mixed case, Oracle DBA could use a
function-based index to look for the values as if they were all in uppercase characters.
Single Column and Concatenated: Oracle DBA can create an index on just one column (single
column index) or on multiple columns (concatenated index). Concatenated indexes are
useful when all of the columns are likely to be included in the WHERE clause of
frequently-executed SQL statements. For concatenated indexes, Oracle DBA should 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 no value (Nulls) should
not be included or should be the last-named columns in the index definition.
Non-partitioned and Partitioned: As with tables, Oracle DBA can create your indexes without or
with partitions. In most situations, it is most useful to partition an index when the
associated table is also partitioned and the index uses the same partitioning scheme as
the table, known as a local index. This does not mean Oracle DBA must partition an index like
the underlying table. Oracle DBA may even create a non-partitioned, or global, index on a
partitioned table.
Index Creation Example
Oracle DBA can create indexes with Enterprise Manager. To create an index, you specify one or
more columns to be indexed and the type of index you want to create.
The following example creates a standard B-tree index on the SUPPLIER_ID column in
the SH.PRODUCTS table, which is part of the sample schema. To create this index, do
the following:
1. On the Administration page under Schema, click Table. The Tables page appears.
2. In the Search section, enter SH as the Schema and click Go. Under Results, the list
of tables in the SH sample schema appears.
3. Select the PRODUCTS table and select Create Index from the Actions drop-down
menu. Click Go.
4. The Create Index page appears. Name the new index PRODUCTS_SUPPLIER_
IDX. For Tablespace, accept , and select Standard B-tree as the index type.
5. In the list of table columns, select the SUPPLIER_ID column by entering 1 in the
Order column. Accept ASC as the Sorting Order. Click OK to create the index.
6. After the index is created, the Indexes page appears with a confirmation message.
The new index is listed under Results.
Dropping Indexes
To drop the table using Enterprise Manager, follow these steps:
1. From the Indexes page, identify the index you want to delete by selecting the
required schema and the required index name.
2. Select the index you need to drop from the Results table and click Delete.
3. The Confirmation page lets you choose whether to continue the operation. If Oracle DBA
are certain you have identified the correct index, click Yes.
4. The Edit table page displays a confirmation message if the index is successfully
deleted.
|
|
|
