Data Blocks  «Prev

Oracle Index Statistics

Oracle lists the following index statistics in the DBA_INDEXES, USER_INDEXES, and ALL_INDEXES data dictionary views:
  1. Exact depth of the index from root block to leaf blocks
  2. Number of leaf blocks
  3. Clustering factor (the order of the rows in relation to indexed values)
  4. Number of distinct index values
  5. Average number of leaf blocks per index value
  6. Average number of data blocks per index value (for an index on a table)

Querying Index Information

Several data dictionary views are available to query information about indexes. This section covers certain views and their columns that you should be familiar with before taking the exam.

DBA_INDEXES

The DBA_INDEXES, USER_INDEXES, and ALL_INDEXES views are the primary views used to query for information about indexes (IND is a synonym for USER_INDEXES). The views have the following information (the columns that can be used in the query are provided in parentheses):
  1. Identity (OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE)
  2. Storage (TABLESPACE_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, MIN_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, BUFFER_POOL)
  3. Statistics (BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED)
  4. Miscellaneous create options (UNIQUENESS, LOGGING, DEGREE, CACHE, PARTITIONED)