Managing Indexes

An index is an optional structure associated with tables and clusters. You can create, browse, drop an index and view reports.

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

When you view an index in Object Browser, the Detail pane displays a report containing the index name, index type, table owner, table name, table type, uniqueness, compression, prefix length, tablespace name, status, last analyzed and a listing of the indexed columns.

• Creating an Index
  Create an index using Object Browser.
• Browsing an Index
  Select an index from the Object Selection pane and view different reports about the an index.
• Reports for Indexes
  Alternative views available when browsing an index in Object Browser.
• Dropping an Index
  Select an index from the Object Selection pane and click Drop.

Creating an Index

Create an index using Object Browser.

To create an index:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. Click the Create icon.
3. From the list of object types, select Index.
4. Select a table and select the type of index you want to create. Available index types include:

   • Normal - Indexes one or more scalar typed object attributes of a table

   • Text - Creates a text index (Oracle Text)

5. Click Next.
6. Create the index definition. Specify an index name, select one or more columns to be indexed, and click Next.

   A confirmation page appears, which displays the SQL used to create the index.

7. To confirm, click Create Index.

Browsing an Index

Select an index from the Object Selection pane and view different reports about the an index.

To browse an index:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Indexes.
3. From the Object Selection pane, select an index.

   The index appears displaying the index name, index type, table owner, table name, table type, uniqueness, compression, prefix length, tablespace name, status, last analyzed and a listing of the indexed columns.

4. Click the tabs at the top of the page to view different reports about the index.

Reports for Indexes

Alternative views available when browsing an index in Object Browser.

Table 2-4 describes all available reports for indexes.

Table 2-4 Available Reports for Indexes

View	Description
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. Actions you can perform while viewing Object Details include: Disable - Disables the current index Drop - Drops the current index Rebuild - Rebuilds the current index
Statistics	Displays collected statistics about the current view, including the number of rows, sample size, when the data was last analyzed, and the compression status (enabled or disabled). Click Analyze to refresh the displayed statistics.
SQL	Displays the SQL necessary to re-create this index.

Dropping an Index

Select an index from the Object Selection pane and click Drop.

To drop an index:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Indexes.
3. From the Object Selection pane, select an index.
4. Under Object Details, click Drop.
5. To confirm, click Finish.