Learn Oracle


I have created this learn oracle "The step 10 process" Oracle Tutorial to help Database aspirants to accomplish their dream of working as Oracle Database administrator. I have tried to keep it simple and User friendly. Use NEXT and PREVIOUS Button to navaigate away and to a chapter. Since i myself had been a Database adminstrator trainer and has got real life experience of working as a Oracle DBA, I would welcome any sort of queries and doubts or modifications to webmaster@oracleonline.info.

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.
Oracle Tutorial - BLOGS

STEP 8 - Next Topics
This step discusses managing tables, indexes, and other schema objects. Click on Oracle tuorial links below:
Managing TablesManaging Tables
Creating TablesCreating Tables
Modifying Table AttributesModifying Tables
Managing IndexesModifying Indexes
Oracle Database - PODCASTS
More Tutorials on Oracle dba ...

Liked it ? Want to share it ? Social Bookmarking
Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Source : Oracle Documentation | Oracle DBA

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info