DBA_IND_EXPRESSIONS view tips

Oracle Tips by Burleson Consulting

DBA_IND_EXPRESSIONS

<< Return to the index

Oracle 11g makes the following comments about the DBA_IND_EXPRESSIONS table: "FUNCTIONAL INDEX EXPRESSIONs on all TABLES and CLUSTERS"

Related notes on DBA_IND_EXPRESSIONS:

Column description of the DBA_IND_EXPRESSIONS view:

INDEX_OWNER

Description of DBA_IND_EXPRESSIONS.INDEX_OWNER: "Index owner"

INDEX_NAME

Description of DBA_IND_EXPRESSIONS.INDEX_NAME: "Index name"

TABLE_OWNER

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

TABLE_NAME

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

COLUMN_EXPRESSION

Description of DBA_IND_EXPRESSIONS.COLUMN_EXPRESSION: "Functional index expression defining the column"

COLUMN_POSITION

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

DBA_IND_EXPRESSIONS View Source

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

select io.name, idx.name, bo.name, base.name, c.default$, ic.pos#
from sys.col$ c, sys.obj$ idx, sys.obj$ base, sys.icol$ ic,
sys.user$ io, sys.user$ bo, sys.ind$ i
where bitand(ic.spare1,1) = 1 /* an expression */
and (bitand(i.property,1024) = 0) /* not bmji */
and ic.bo# = c.obj#
and ic.intcol# = 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)

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