FUNCTIONAL INDEX

[SAM8080]

Hi,

How to view the particular field of a functional index..i have looked into dba_indexes and other views..It's specified as sys_***.... i have created the index for this functrion

to_char(record_date,'yyyymm')



Regards,

sampath

[tamilselvan]

SELECT * FROM USER_IND_EXPRESSIONS;

[Dramus8]

We have indexes that have one field which is function based (We have used the DESC keyword) and another which is normal. Does anyone have a script that joins USER_IND_COLUMNS and USER_IND_EXPRESSIONS nicely?

[Edited by Dramus8 on 01-08-2002 at 10:18 AM]

[Shestakov]

select i.table_name,i.index_name,e.column_expression
from all_ind_columns i, all_ind_expressions e
where i.index_owner = e.index_owner (+) and
i.table_name = e.table_name (+) and
i.index_name = e.index_name (+)
and
i.index_owner = '....you_owner...';

[Dramus8]

That doesn't seem to work. The values in ALL_IND_EXPRESSIONS are just the function based columns. The other columns are still in ALL_IND_COLUMNS (along with a system generated field name for the function based column). The problem is...You cannot union the two together because the column_name in all_ind_columns is a varchar and the column_expression in all_ind_expressions is a long.

[balajiyes]

To identify the function expression for a given column in an index, there shud be a join on COLUMN_POSITION also. The query thus wud be:

SELECT
B.TABLE_NAME, B.INDEX_NAME, SUBSTR(TRIM(B.COLUMN_NAME),1,30),
A.COLUMN_EXPRESSION
FROM DBA_IND_EXPRESSIONS A,
DBA_IND_COLUMNS B
WHERE A.INDEX_NAME(+) = B.INDEX_NAME
AND A.INDEX_OWNER(+) = B.INDEX_OWNER
AND A.TABLE_NAME(+) = B.TABLE_NAME
AND A.TABLE_OWNER(+) = B.TABLE_OWNER
AND A.COLUMN_POSITION(+) = B.COLUMN_POSITION;

Hope this helps.