CODEWITHSUNDEEP
oracle-databaseindexing
user1495959
user1495959

Reputation: 43

Column name is masked in oracle indexes

I have a table in oracle db which has a unique index composed of two columns (id and valid_from). The column valid_from is of type timestamps with time zone.
When I query the SYS.USER_IND_COLUMNS to see which columns my table is using as unique index, I can not see the name of the valid_from column but instead I see smth like SYS_NC00027$.

Is there any possibility that I can display the name valid_from rather than SYS_NC00027$. ?

Upvotes: 1

Views: 6077

Answers (2)

Suresh Dooly
Suresh Dooly

Reputation: 57

Use the below to verify the col info.

select column_name,virtual_column,hidden_column,data_default from user_tab_cols where table_name='EMP';

Upvotes: 0

user330315
user330315

Reputation:

Apparently Oracle creates a function based index for timestamp with time zone columns.

The definition of them can be found in the view ALL_IND_EXPRESSIONS

Something like this should get you started:

select ic.index_name, 
       ic.column_name,
       ie.column_expression
from all_ind_columns ic
  left join all_ind_expressions ie 
    on ie.index_owner = ic.index_owner
   and ie.index_name = ic.index_name
   and ie.column_position = ic.column_position
where ic.table_name = 'FOO';

Unfortunately column_expression is a (deprecated) LONG column and cannot easily be used in a coalesce() or nvl() function.

Upvotes: 2

Related Questions