3.5 Managing Indexes

An index is an optional structure associated with tables and clusters. Use Object Browser to create, view, or drop an index.

You can create indexes on one or more columns of a table to speed access to data on those columns.

• Creating an Index
  Create an index using Object Browser.
• Viewing an Index
  Select an index from the Object Tree and access tabs in the Object Detail View.
• Dropping an Index
  Select an index from the Object Tree pane and click Drop on the Object Details tab.

3.5.1 Creating an Index

Create an index using Object Browser.

To create an index:

1. In Object Browser, click the Create Database Objects menu and select Index.

   Tip:

   To create new objects from the Object Tree, right-click the object and select the Create option.

   The Create Index Wizard appears.

2. Table - Select a table on which you wish to create the index.
3. Type of Index - Specify the type of index:
   • Normal - Generally used for indexing NUMBER and VARCHAR data.
   • Text - Generally used for indexing CLOB columns.
   • Spatial - Generally used for indexing SDO_GEOMETRY columns.
4. Index name - Enter the name of the index. The name must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore. This field contains an automatically generated name by default, based on the selected table name and type of index.
5. Unique Index - Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.
6. Index Column 1 - Specify the column for the first position in index. When creating "Normal" index, select up to four index columns. When creating "Text" index or "Spatial" index, select only one column.
7. Click Create Index.

3.5.2 Viewing an Index

Select an index from the Object Tree and access tabs in the Object Detail View.

To browse an index:

1. In Object Browser, Object Tree, expand Indexes and select a index to view.
2. The Object Detail View appears and displays the following tabs:
   • Object Details - Displays the index name, index type, table owner, table name, table type, uniqueness, compression, prefix length, tablespace name, status, last analyzed and also a listing of the indexed columns. Available actions include:
     • Disable
     • Drop
     • Rebuild
     • Refresh
   • Statistics - Displays collected statistics about the current view, including the number of rows, the compression status (enabled or disabled), and when the data was last analyzed. Available actions include:

     • Gather Statistics - Run a background process that gathers index statistics using DBMS_STATS. Clicking Gather Statistics displays Gather statistics in progress... in the Last Analyzed column value.

       Click Refresh to get the index statistics information. If the background process finishes, the statistics information displays with Last Analyzed column value updated.

     • Refresh - Refresh queries statistics information gathered already from USER_INDEXES view.
   • DDL - Displays the DDL necessary to re-create this index. Available actions include:
     • Download
     • Refresh

3.5.3 Dropping an Index

Select an index from the Object Tree pane and click Drop on the Object Details tab.

To drop an index:

1. In Object Browser, Object Tree, expand Indexes and select an index to view.

   The Object Detail View appears.

2. In the Object Details tab, click Drop.
   1. Drop - Review the details.
   2. SQL - Displays the SQL generated to drop the sequence. To copy the displayed SQL, click the Copy icon.
   3. To confirm your selection, click Drop again.