Data dictionary views about constraints

You can find out more about indexes through views in the data dictionary. There are two basic views that contain information about indexes, the USER_INDEXES view and the USER_IND_COLUMNS view. The USER_INDEXES view contains the following columns, among others:

INDEX_NAME:	The name of the index
INDEX_TYPE:	The type of index
TABLE_OWNER:	The schema that owns the table the index is associated with
TABLE_NAME:	The name of the table the index is associated with
UNIQUENESS:	Will either have the value of UNIQUE or NONUNIQUE
TABLESPACE_NAME:	The name of the tablespace that contains the index
BLEVEL:	The depth of the B*-tree structure
LEAF_BLOCKS:	The number of leaf blocks in the index
DISTINCT_KEYS:	The number of distinct values in the index
NUM_ROWS:	The number of rows in the index

The USER_IND_COLUMNS view contains the following columns:

INDEX_NAME:	The name of the index
TABLE_NAME:	The name of the table the index is associated with
COLUMN_NAME:	The name of the column in the index
COLUMN_POSITION:	The position of the column in the index
COLUMN_LENGTH:	The length of the column

The USER_PART_INDEXES also contains information about the specific partitions used in partitioned indexes.

ALL_INDEXTYPES displays information about the indextypes accessible to the current user.
Related Views

1. DBA_INDEXTYPES displays information about all indextypes in the database.
2. USER_INDEXTYPES displays information about the indextypes owned by the current user.

To view information about indexes in Oracle 11g R2, you can query the data dictionary views that store metadata about indexes. These views provide detailed information about indexes, such as their names, types, status, and the columns they include. The primary views you can use are:

1. USER_INDEXES: Shows indexes owned by the current user.
2. ALL_INDEXES: Displays indexes accessible to the current user.
3. DBA_INDEXES: Provides information about all indexes in the database (requires DBA privileges).
4. USER_IND_COLUMNS: Lists columns associated with the indexes owned by the user.
5. ALL_IND_COLUMNS: Lists columns in indexes accessible to the user.
6. DBA_IND_COLUMNS: Lists all index columns in the database (requires DBA privileges).

Example Queries:

• To view all indexes owned by the current user:

  SELECT * FROM USER_INDEXES;
      

• To view all indexes accessible to the current user:

  SELECT * FROM ALL_INDEXES;
      

• To view all indexes in the database (requires DBA privileges):

  SELECT * FROM DBA_INDEXES;
      

• To get detailed information about index columns:

  SELECT * FROM USER_IND_COLUMNS;
      

Or for all accessible indexes:

  SELECT * FROM ALL_IND_COLUMNS;
  

Additional Information:

• Filtering Results: You can add WHERE clauses to filter results based on specific criteria, such as index name or table name.

  SELECT * FROM ALL_INDEXES WHERE TABLE_NAME = 'YOUR_TABLE_NAME';
      

• Understanding Columns: The columns in these views provide valuable information:
  • INDEX_NAME: The name of the index.
  • INDEX_TYPE: Type of the index (e.g., NORMAL, BITMAP).
  • TABLE_NAME: The name of the table the index is on.
  • UNIQUENESS: Indicates whether the index is unique.
  • STATUS: Status of the index (e.g., VALID, UNUSABLE).
• Joining Views: You can join these views with other data dictionary views to get more comprehensive information.

  SELECT ui.INDEX_NAME, ui.TABLE_NAME, uic.COLUMN_NAME
  FROM USER_INDEXES ui
  JOIN USER_IND_COLUMNS uic ON ui.INDEX_NAME = uic.INDEX_NAME;
      

By querying these data dictionary views, you can effectively retrieve and analyze information about indexes in your Oracle 11g R2 database.
The next lesson shows how to drop an index.