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?

 

Answer:  The 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 script.

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 statistics and function-based indexes.

 

Option 1: Display function-based indexes:

 

set lines 100

col c1 heading 'Table|Name' format a25
col c2 heading 'Index|Name' format a25
col c3 heading 'Expression' format a50

break on c1 skip 2

select
   table_name c1,
   index_name c2,
   column_expression c3
from
   dba_ind_expressions
where
   table_owner not in ('XDB','SYS','SYSTEM');

 

Option 2: display function-based indexes from dba_indexes:

 

-- *****************************
-- detect function-based indexes
-- *****************************
select
   owner,
   index_name,
   index_type
from
   dba_indexes
where
   index_type like 'FUNCTION-BASED%'
and
   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 'Expression'
SET LINES 130
ttitle 'Functional Index Report'
BREAK ON index_owner on index_name

SELECT
   index_owner,
   index_name,
   table_name,
   column_expression
FROM
   dba_ind_expressions,
SEE CODE DEPOT FOR FULL SCRIPT
ORDER BY
Index_owner,index_name,column_position;


Here is a sample listing.

Owner Index Table Expression
---------- ---------------- ----------------- ---------------------
TELE_DBA DEC_CLIENTSV8i CLIENTSV8i DECODE ("CREATION_SY_USER",1,'B
OSS',2,'Manager',3,'Clerk','Ev
eryone else')