Live Database Reverse Engineering Function-based Index

In Oracle 8i and later versions, you can create indexes based on functions and expressions that involve one or more columns in the table being indexed. A function-based index precomputes the value of the function or expression and stores it in the index. The function or the expression will replace the index column in the index definition.

An index column with an expression is stored in system tables with a LONG data type that cannot be concatenated in a string statement during reverse engineering.

To bypass this limitation, SqlListQuery (Index category in the DBMS) contains a call to the user-defined query SqlExpression used to recover the index expression in a column with the LONG data type and concatenate this value in a string statement (the following is a subset of SqlListQuery):

select 
 '%SCHEMA%',
 i.table_name,
 i.index_name,
 decode(i.index_type, 'BITMAP', 'bitmap', ''),
 decode(substr(c.column_name, 1, 6), 'SYS_NC', '%SqlExpression.Xpr'||i.table_name||i.index_name||c.column_position||'%', c.column_name)||' '||c.descend||', ',
 c.column_position
from 
 user_indexes i,
 user_ind_columns c
where 
 c.table_name=i.table_name
 and c.index_name=i.index_name
[  and i.table_owner='%SCHEMA%']
[  and i.table_name='%TABLE%']
[  and i.index_name='%INDEX%']

The execution of SqlListQuery calls the execution of the user-defined query SqlExpression.

SqlExpression is followed by a user-defined variable defined as follow:

{VAR, VAL}

select
 'Xpr'||table_name||index_name||column_position,
 column_expression
from
 all_ind_expressions
where 1=1
[  and table_owner='%SCHEMA%']
[  and table_name='%TABLE%']

The name of the user-defined variable is unique, it is the result of the concatenation of "Xpr", table name, index name, and column position.