 |
|
Dictionary query to display function-based index function
Oracle Tips by Burleson Consulting
|
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.
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')
