select * from ALL_INDEXES where TABLE_NAME='yourTable'; ALL_INDEXES This view contains descriptions of indexes on tables accessible to the user. To gather statistics for this view, use the SQL command ANALYZE. This view supports parallel partitioned index scans. Column Datatype NULL Description OWNER VARCHAR2(30) NOT NULL Username of the owner of the index INDEX_NAME VARCHAR2(30) NOT NULL Name of the index INDEX_TYPE VARCHAR2(12) Type of index TABLE_OWNER VARCHAR2(30) NOT NULL Owner of the indexed object TABLE_NAME VARCHAR2(30) NOT NULL Name of the indexed object TABLE_TYPE CHAR(5) Type of the indexed object UNIQUENESS VARCHAR2(9) Uniqueness status of the index: UNIQUE or NONUNIQUE TABLESPACE_NAME VARCHAR2(30) Name of the tablespace containing the index INI_TRANS NUMBER Initial number of transactions MAX_TRANS NUMBER Maximum number of transactions INITIAL_EXTENT NUMBER Size of the initial extent NEXT_EXTENT NUMBER Size of secondary extents MIN_EXTENTS NUMBER Minimum number of extents allowed in the segment MAX_EXTENTS NUMBER Maximum number of extents allowed in the segment PCT_INCREASE NUMBER Percentage increase in extent size PCT_THRESHOLD NUMBER Threshold percentage of block space allowed per index entry INCLUDE_COLUMN NUMBER User column-id for last column to be included in index organized table top index FREELISTS NUMBER Number of process freelists allocated to this segment FREELIST_GROUPS NUMBER Number of freelist groups allocated to this segment PCT_FREE NUMBER Minimum percentage of free space in a block LOGGING VARCHAR(2(3) Logging information BLEVEL NUMBER B-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same. LEAF_BLOCKS NUMBER Number of leaf blocks in the index DISTINCT_KEYS NUMBER Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table (USER_TABLES.NUM_ROWS) AVG_LEAF_BLOCKS _PER_KEY NUMBER Average number of leaf blocks in which each distinct value in the index appears. This statistic is rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always 1. AVG_DATA_BLOCKS _PER_KEY NUMBER Average number of data blocks in the table that are pointed to by a distinct value in the index. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns. This statistic is rounded to the nearest integer. CLUSTERING_FACTOR NUMBER Statistic that represents the amount of order of the rows in the table based on the values of the index. If its value is near the number of blocks, then the table is very well ordered. In such a case, the index entries in a single leaf block tend to point to rows in the same data blocks. If its value is near the number of rows, then the table is very randomly ordered. In such a case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks. STATUS VARCHAR2(8) State of the index: DIRECT LOAD or VALID NUM_ROWS NUMBER Number of rows in this index SAMPLE_SIZE NUMBER Size of the sample used to analyze this index LAST_ANALYZED DATE Timestamp for when this index was last analyzed DEGREE VARCHAR2(40) Number of threads per instance for scanning the index, NULL if PARTITIONED=NO. INSTANCES VARCHAR2(40) Number of instances across which the indexes to be scanned. NULL if PARTITIONED=NO. PARTITIONED VARCHAR2(3) Indicates whether this index is partitioned. Set to `YES' if it is partitioned TEMPORARY VARCHAR2(1) Can the current session only see data that it places in this object itself? GENERATED VARCHAR2(1) Was the name of this index system generated? BUFFER_POOL VARCHAR2(7) Name of the default buffer pool for the appropriate object
from ALL_INDEXES
where TABLE_NAME='yourTable';
ALL_INDEXES
This view contains descriptions of indexes on tables accessible to the user. To gather statistics for this view, use the SQL command ANALYZE. This view supports parallel partitioned index scans. Column Datatype NULL Description
OWNER VARCHAR2(30) NOT NULL Username of the owner of the index
INDEX_NAME VARCHAR2(30) NOT NULL Name of the index
INDEX_TYPE VARCHAR2(12)
Type of index
TABLE_OWNER VARCHAR2(30) NOT NULL Owner of the indexed object
TABLE_NAME VARCHAR2(30) NOT NULL Name of the indexed object
TABLE_TYPE CHAR(5)
Type of the indexed object
UNIQUENESS VARCHAR2(9)
Uniqueness status of the index: UNIQUE or NONUNIQUE
TABLESPACE_NAME VARCHAR2(30)
Name of the tablespace containing the index
INI_TRANS NUMBER
Initial number of transactions
MAX_TRANS NUMBER
Maximum number of transactions
INITIAL_EXTENT NUMBER
Size of the initial extent
NEXT_EXTENT NUMBER
Size of secondary extents
MIN_EXTENTS NUMBER
Minimum number of extents allowed in the segment
MAX_EXTENTS NUMBER
Maximum number of extents allowed in the segment
PCT_INCREASE NUMBER
Percentage increase in extent size
PCT_THRESHOLD NUMBER
Threshold percentage of block space allowed per index entry
INCLUDE_COLUMN NUMBER
User column-id for last column to be included in index organized table top index
FREELISTS NUMBER
Number of process freelists allocated to this segment
FREELIST_GROUPS NUMBER
Number of freelist groups allocated to this segment
PCT_FREE NUMBER
Minimum percentage of free space in a block
LOGGING VARCHAR(2(3)
Logging information
BLEVEL NUMBER
B-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.
LEAF_BLOCKS NUMBER
Number of leaf blocks in the index
DISTINCT_KEYS NUMBER
Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table (USER_TABLES.NUM_ROWS)
AVG_LEAF_BLOCKS
_PER_KEY NUMBER
Average number of leaf blocks in which each distinct value in the index appears. This statistic is rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always 1.
AVG_DATA_BLOCKS
_PER_KEY NUMBER
Average number of data blocks in the table that are pointed to by a distinct value in the index. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns. This statistic is rounded to the nearest integer.
CLUSTERING_FACTOR NUMBER
Statistic that represents the amount of order of the rows in the table based on the values of the index. If its value is near the number of blocks, then the table is very well ordered. In such a case, the index entries in a single leaf block tend to point to rows in the same data blocks. If its value is near the number of rows, then the table is very randomly ordered. In such a case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.
STATUS VARCHAR2(8)
State of the index: DIRECT LOAD or VALID
NUM_ROWS NUMBER
Number of rows in this index
SAMPLE_SIZE NUMBER
Size of the sample used to analyze this index
LAST_ANALYZED DATE
Timestamp for when this index was last analyzed
DEGREE VARCHAR2(40)
Number of threads per instance for scanning the index, NULL if PARTITIONED=NO.
INSTANCES VARCHAR2(40)
Number of instances across which the indexes to be scanned. NULL if PARTITIONED=NO.
PARTITIONED VARCHAR2(3)
Indicates whether this index is partitioned. Set to `YES' if it is partitioned
TEMPORARY VARCHAR2(1)
Can the current session only see data that it places in this object itself?
GENERATED VARCHAR2(1)
Was the name of this index system generated?
BUFFER_POOL VARCHAR2(7)
Name of the default buffer pool for the appropriate object