Display function-based indexes
Oracle Database Tips by Donald Burleson
Question: I had good
working query while selecting from dba_indexes and
dba_columns. But then it turns out that if I have a function
based index, this column names do not appear properly - I get
only their system generated names.
The names I need to pull are in dba_ind_expressions and I
tried to join this view to my existing query but this column is
a long type, and I get errors. I tried to convert the long
value to varchar, lob, etc, with no success.
How do I display the value of a function within a function-based
index dictionary query?
Oracle script collection
has dictionary queries to display the values of a function-based
index, but I'm not allowed to display the whole dictionary
The dba_indexes view has a new column that makes this
easier, the funcidx_status column. The funcidx_status
column contains NULL if the index is not a function-based index,
ENABLED if it is a function-based index and is ready for use,
and DISABLED if it is a function-based index that is disabled
and can't be used. If the value in funcidx_status is not NULL,
a join to the dba_ind_expressions view will provide the
information on the expression used to create the function-based
column in the index.
Note: Once a
function-based index is created, you need to create CBO
statistics, but beware that there are numerous bugs and issues when
analyzing a function-based index. See these important notes on
Option 1: Display function-based indexes:
set lines 100
col c1 heading 'Table|Name'
heading 'Index|Name' format a25
col c3 heading 'Expression' format a50
break on c1 skip 2
table_owner not in ('XDB','SYS','SYSTEM');
Option 2: display function-based indexes from dba_indexes:
-- detect function-based indexes
index_type like 'FUNCTION-BASED%'
owner not in ('XDB','SYS','SYSTEM');
The script below demonstrates this type of report; an example of
the output from this script follows.
COLUMN owner FORMAT a6 HEADING 'Owner'
COLUMN index_name FORMAT a14 HEADING 'Index'
COLUMN table_name FORMAT a20 HEADING 'Table'
COLUMN column_expression FORMAT a80 WORD_WRAPPED HEADING
SET LINES 130
ttitle 'Functional Index Report'
BREAK ON index_owner on index_name
SEE CODE DEPOT FOR FULL
Here is a sample listing.
Owner Index Table Expression
---------- ---------------- -----------------
TELE_DBA DEC_CLIENTSV8i CLIENTSV8i DECODE ("CREATION_SY_USER",1,'B