DBA_IND_COLUMNS view tips

Oracle Tips by Burleson Consulting

DBA_IND_COLUMNS

<< Return to the index

Oracle 11g makes the following comments about the DBA_IND_COLUMNS table: "COLUMNs comprising INDEXes on all TABLEs and CLUSTERs"

Related notes on DBA_IND_COLUMNS:

Column description of the DBA_IND_COLUMNS view:

INDEX_OWNER

Description of DBA_IND_COLUMNS.INDEX_OWNER: "Index owner"

INDEX_NAME

Description of DBA_IND_COLUMNS.INDEX_NAME: "Index name"

TABLE_OWNER

Description of DBA_IND_COLUMNS.TABLE_OWNER: "Table or cluster owner"

TABLE_NAME

Description of DBA_IND_COLUMNS.TABLE_NAME: "Table or cluster name"

COLUMN_NAME

Description of DBA_IND_COLUMNS.COLUMN_NAME: "Column name or attribute of object column"

COLUMN_POSITION

Description of DBA_IND_COLUMNS.COLUMN_POSITION: "Position of column or attribute within index"

COLUMN_LENGTH

Description of DBA_IND_COLUMNS.COLUMN_LENGTH: "Maximum length of the column or attribute, in bytes"

CHAR_LENGTH

Description of DBA_IND_COLUMNS.CHAR_LENGTH: "Maximum length of the column or attribute, in characters"

DESCEND

Description of DBA_IND_COLUMNS.DESCEND: "DESC if this column is sorted in descending order on disk, otherwise ASC"

DBA_IND_COLUMNS View Source

Oracle 11g's data dictionary defines the DBA_IND_COLUMNS view using the following source query:

select io.name, idx.name, bo.name, base.name,
decode(bitand(c.property, 1024), 1024,
(select decode(bitand(tc.property, 1), 1, ac.name, tc.name)
from sys.col$ tc, attrcol$ ac
where tc.intcol# = c.intcol#-1
and tc.obj# = c.obj#
and tc.obj# = ac.obj#(+)
and tc.intcol# = ac.intcol#(+)),
decode(ac.name, null, c.name, ac.name)),
ic.pos#, c.length, c.spare3,
decode(bitand(c.property, 131072), 131072, 'DESC', 'ASC')
from sys.col$ c, sys.obj$ idx, sys.obj$ base, sys.icol$ ic,
sys.user$ io, sys.user$ bo, sys.ind$ i, sys.attrcol$ ac
where ic.bo# = c.obj#
and decode(bitand(i.property,1024),0,ic.intcol#,ic.spare2) = c.intcol#
and ic.bo# = base.obj#
and io.user# = idx.owner#
and bo.user# = base.owner#
and ic.obj# = idx.obj#
and idx.obj# = i.obj#
and i.type# in (1, 2, 3, 4, 6, 7, 9)
and c.obj# = ac.obj#(+)
and c.intcol# = ac.intcol#(+)

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

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books