Home » RDBMS Server » Performance Tuning » The column name in user_ind_colums shows a SYS_ name when function based index created (10g)
The column name in user_ind_colums shows a SYS_ name when function based index created [message #330661] Mon, 30 June 2008 13:32 Go to next message
DRDBA
Messages: 20
Registered: March 2007
Junior Member
SQL> create index sno_idx on test_index(sno) tablespace users;

Index created.

Elapsed: 00:00:00.53
SQL> create index function_based_index on test_index(to_char(d_date,'dd-mm-yyyy'));

Index created.

Elapsed: 00:00:01.93

SQL> select index_name,column_name from user_ind_columns where table_name='TEST_INDEX';

INDEX_NAME COLUMN_NAME
------------------------------ ----------------------
SNO_IDX SNO
FUNCTION_BASED_INDEX SYS_NC00004$
Elapsed: 00:00:00.92

Is this a normal behavior ?
How would we query on which column the function based index is created ?
Do I need to query from a differnt view ?

Thanks
Ram
Re: The column name in user_ind_colums shows a SYS_ name when function based index created [message #330664 is a reply to message #330661] Mon, 30 June 2008 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is this a normal behavior ?

Yes

Quote:
How would we query on which column the function based index is created ?

A function based index is not created upon a column but on an expression.

Quote:
Do I need to query from a differnt view ?

Query DBA_IND_EXPRESSIONS

Regards
Michel
Re: The column name in user_ind_colums shows a SYS_ name when function based index created [message #330668 is a reply to message #330664] Mon, 30 June 2008 13:58 Go to previous message
DRDBA
Messages: 20
Registered: March 2007
Junior Member
Excellent..That worked

Thank you Michel for your quick responce.
Previous Topic: reduce the wait event for db file sequential read event
Next Topic: Best way to partition my table
Goto Forum:
  


Current Time: Sat Dec 03 03:42:27 CST 2016

Total time taken to generate the page: 0.12932 seconds