| |
 |
|
DBA_INDEXES view tips
Oracle Tips by Burleson Consulting |
DBA_INDEXES
<< Return to the index
Oracle 11g makes the following comments about the DBA_INDEXES table: "Description for all indexes in the database"
Related notes on DBA_INDEXES:
Column description of the DBA_INDEXES view:OWNERDescription of DBA_INDEXES.OWNER: "Username of the owner of the index" INDEX_NAMEDescription of DBA_INDEXES.INDEX_NAME: "Name of the index" INDEX_TYPENo comments exist for the INDEX_TYPE column of the DBA_INDEXES table. TABLE_OWNERDescription of DBA_INDEXES.TABLE_OWNER: "Owner of the indexed object" TABLE_NAMEDescription of DBA_INDEXES.TABLE_NAME: "Name of the indexed object" TABLE_TYPEDescription of DBA_INDEXES.TABLE_TYPE: "Type of the indexed object" UNIQUENESSDescription of DBA_INDEXES.UNIQUENESS: "Uniqueness status of the index: "UNIQUE", "NONUNIQUE", or "BITMAP"" COMPRESSIONDescription of DBA_INDEXES.COMPRESSION: "Compression property of the index: "ENABLED", "DISABLED", or NULL" PREFIX_LENGTHDescription of DBA_INDEXES.PREFIX_LENGTH: "Number of key columns in the prefix used for compression" TABLESPACE_NAMEDescription of DBA_INDEXES.TABLESPACE_NAME: "Name of the tablespace containing the index" INI_TRANSDescription of DBA_INDEXES.INI_TRANS: "Initial number of transactions" MAX_TRANSDescription of DBA_INDEXES.MAX_TRANS: "Maximum number of transactions" INITIAL_EXTENTDescription of DBA_INDEXES.INITIAL_EXTENT: "Size of the initial extent" NEXT_EXTENTDescription of DBA_INDEXES.NEXT_EXTENT: "Size of secondary extents" MIN_EXTENTSDescription of DBA_INDEXES.MIN_EXTENTS: "Minimum number of extents allowed in the segment" MAX_EXTENTSDescription of DBA_INDEXES.MAX_EXTENTS: "Maximum number of extents allowed in the segment" PCT_INCREASEDescription of DBA_INDEXES.PCT_INCREASE: "Percentage increase in extent size" PCT_THRESHOLDDescription of DBA_INDEXES.PCT_THRESHOLD: "Threshold percentage of block space allowed per index entry" INCLUDE_COLUMNDescription of DBA_INDEXES.INCLUDE_COLUMN: "User column-id for last column to be included in index-only table top index" FREELISTSDescription of DBA_INDEXES.FREELISTS: "Number of process freelists allocated in this segment" FREELIST_GROUPSDescription of DBA_INDEXES.FREELIST_GROUPS: "Number of freelist groups allocated to this segment" PCT_FREEDescription of DBA_INDEXES.PCT_FREE: "Minimum percentage of free space in a block" LOGGINGDescription of DBA_INDEXES.LOGGING: "Logging attribute" BLEVELDescription of DBA_INDEXES.BLEVEL: "B-Tree level" LEAF_BLOCKSDescription of DBA_INDEXES.LEAF_BLOCKS: "The number of leaf blocks in the index" DISTINCT_KEYSDescription of DBA_INDEXES.DISTINCT_KEYS: "The number of distinct keys in the index" AVG_LEAF_BLOCKS_PER_KEYDescription of DBA_INDEXES.AVG_LEAF_BLOCKS_PER_KEY: "The average number of leaf blocks per key" AVG_DATA_BLOCKS_PER_KEYDescription of DBA_INDEXES.AVG_DATA_BLOCKS_PER_KEY: "The average number of data blocks per key" CLUSTERING_FACTORDescription of DBA_INDEXES.CLUSTERING_FACTOR: "A measurement of the amount of (dis)order of the table this index is for" STATUSDescription of DBA_INDEXES.STATUS: "Whether non-partitioned index is in UNUSABLE state or not" NUM_ROWSNo comments exist for the NUM_ROWS column of the DBA_INDEXES table. SAMPLE_SIZEDescription of DBA_INDEXES.SAMPLE_SIZE: "The sample size used in analyzing this index" LAST_ANALYZEDDescription of DBA_INDEXES.LAST_ANALYZED: "The date of the most recent time this index was analyzed" DEGREEDescription of DBA_INDEXES.DEGREE: "The number of threads per instance for scanning the partitioned index" INSTANCESDescription of DBA_INDEXES.INSTANCES: "The number of instances across which the partitioned index is to be scanned" PARTITIONEDDescription of DBA_INDEXES.PARTITIONED: "Is this index partitioned? YES or NO" TEMPORARYDescription of DBA_INDEXES.TEMPORARY: "Can the current session only see data that it place in this object itself?" GENERATEDDescription of DBA_INDEXES.GENERATED: "Was the name of this index system generated?" SECONDARYDescription of DBA_INDEXES.SECONDARY: "Is the index object created as part of icreate for domain indexes?" BUFFER_POOLDescription of DBA_INDEXES.BUFFER_POOL: "The default buffer pool to be used for index blocks" USER_STATSDescription of DBA_INDEXES.USER_STATS: "Were the statistics entered directly by the user?" DURATIONDescription of DBA_INDEXES.DURATION: "If index on temporary table, then duration is sys$session or sys$transaction else NULL" PCT_DIRECT_ACCESSDescription of DBA_INDEXES.PCT_DIRECT_ACCESS: "If index on IOT, then this is percentage of rows with Valid guess" ITYP_OWNERDescription of DBA_INDEXES.ITYP_OWNER: "If domain index, then this is the indextype owner" ITYP_NAMEDescription of DBA_INDEXES.ITYP_NAME: "If domain index, then this is the name of the associated indextype" PARAMETERSDescription of DBA_INDEXES.PARAMETERS: "If domain index, then this is the parameter string" GLOBAL_STATSDescription of DBA_INDEXES.GLOBAL_STATS: "Are the statistics calculated without merging underlying partitions?" DOMIDX_STATUSDescription of DBA_INDEXES.DOMIDX_STATUS: "Is the indextype of the domain index valid" DOMIDX_OPSTATUSDescription of DBA_INDEXES.DOMIDX_OPSTATUS: "Status of the operation on the domain index" FUNCIDX_STATUSDescription of DBA_INDEXES.FUNCIDX_STATUS: "Is the Function-based Index DISABLED or ENABLED?" JOIN_INDEXDescription of DBA_INDEXES.JOIN_INDEX: "Is this index a join index?" IOT_REDUNDANT_PKEY_ELIMDescription of DBA_INDEXES.IOT_REDUNDANT_PKEY_ELIM: "Were redundant primary key columns eliminated from iot secondary index?" DROPPEDDescription of DBA_INDEXES.DROPPED: "Whether index is dropped and is in Recycle Bin" VISIBILITYDescription of DBA_INDEXES.VISIBILITY: "Whether the index is VISIBLE or INVISIBLE to the optimizer" DOMIDX_MANAGEMENTDescription of DBA_INDEXES.DOMIDX_MANAGEMENT: "If this a domain index, then whether it is system managed or user managed"
DBA_INDEXES View SourceOracle 11g's data dictionary defines the DBA_INDEXES view using the following source query:
select u.name, o.name,
decode(bitand(i.property, 16), 0, '', 'FUNCTION-BASED ') ||
decode(i.type#, 1, 'NORMAL'||
decode(bitand(i.property, 4), 0, '', 4, '/REV'),
2, 'BITMAP', 3, 'CLUSTER', 4, 'IOT - TOP',
5, 'IOT - NESTED', 6, 'SECONDARY', 7, 'ANSI', 8, 'LOB',
9, 'DOMAIN'),
iu.name, io.name,
decode(io.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED'),
decode(bitand(i.property, 1), 0, 'NONUNIQUE', 1, 'UNIQUE', 'UNDEFINED'),
decode(bitand(i.flags, 32), 0, 'DISABLED', 32, 'ENABLED', null),
i.spare2,
decode(bitand(i.property, 34), 0, decode(i.type#, 9, null, ts.name),
2, null, decode(i.ts#, 0, null, ts.name)),
decode(bitand(i.property, 2),0, i.initrans, null),
decode(bitand(i.property, 2),0, i.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(i.type#, 4, mod(i.pctthres$,256), NULL), i.trunccnt,
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),
decode(bitand(i.property, 2),0,i.pctfree$,null),
decode(bitand(i.property, 2), 2, NULL,
decode(bitand(i.flags, 4), 0, 'YES', 'NO')),
i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac,
decode(bitand(i.property, 2), 2,
decode(i.type#, 9, decode(bitand(i.flags, 8),
8, 'INPROGRS', 'VALID'), 'N/A'),
decode(bitand(i.flags, 1), 1, 'UNUSABLE',
decode(bitand(i.flags, 8), 8, 'INPROGRS',
'VALID'))),
rowcnt, samplesize, analyzetime,
decode(i.degree, 32767, 'DEFAULT', nvl(i.degree,1)),
decode(i.instances, 32767, 'DEFAULT', nvl(i.instances,1)),
decode(bitand(i.property, 2), 2, 'YES', 'NO'),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)),
decode(bitand(i.flags, 64), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(i.property, 64), 64, 'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(i.flags, 128), 128, mod(trunc(i.pctthres$/256),256),
decode(i.type#, 4, mod(trunc(i.pctthres$/256),256), NULL)),
itu.name, ito.name, i.spare4,
decode(bitand(i.flags, 2048), 0, 'NO', 'YES'),
decode(i.type#, 9, decode(o.status, 5, 'IDXTYP_INVLD',
1, 'VALID'), ''),
decode(i.type#, 9, decode(bitand(i.flags, 16), 16, 'FAILED', 'VALID'), ''),
decode(bitand(i.property, 16), 0, '',
decode(bitand(i.flags, 1024), 0, 'ENABLED', 'DISABLED')),
decode(bitand(i.property, 1024), 1024, 'YES', 'NO'),
decode(bitand(i.property, 16384), 16384, 'YES', 'NO'),
decode(bitand(o.flags, 128), 128, 'YES', 'NO'),
decode(bitand(i.flags,2097152),2097152,'INVISIBLE','VISIBLE'),
decode(i.type#, 9, decode(bitand(i.property, 2048), 2048,
'SYSTEM_MANAGED', 'USER_MANAGED'), '')
from sys.ts$ ts, sys.seg$ s,
sys.user$ iu, sys.obj$ io, sys.user$ u, sys.ind$ i, sys.obj$ o,
sys.user$ itu, sys.obj$ ito
where u.user# = o.owner#
and o.obj# = i.obj#
and i.bo# = io.obj#
and io.owner# = iu.user#
and bitand(i.flags, 4096) = 0
and bitand(o.flags, 128) = 0
and i.ts# = ts.ts# (+)
and i.file# = s.file# (+)
and i.block# = s.block# (+)
and i.ts# = s.ts# (+)
and i.indmethod# = ito.obj# (+)
and ito.owner# = itu.user# (+)
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
 |
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
