Oracle Function based indexes.


I can only access my working environment only using a unix prompt. I log in with sqlplus and did a query to see what indexes my table X has. I got X_PK and X_1IX as a result.

I then proceed to Query to see what column names those are based on, my PK returns a serial number as expected but when i tried X_1IX i get SYS_NC00022$ which doesn't show with the describe command on that table. I know some basics of function based indexes.

I need to find out how and on what columns it was created so i can reproduce it. How can i find these things out?
Are there any tables i can query?
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
user_ind_expressions shows the parameters of function based indexes.

If the INDEX_TYPE for the index is something other than: FUNCTION-BASED NORMAL, you will probably need to look elsewhere.

See if you can shortcut all this by using dbms_metadata calls:

select dbms_metadata.get_dependent_ddl('INDEX','myTABLE','tableOWNER') from dual;
slightwv (䄆 Netminder) Commented:
Where to look depends on the index_type.  I assume you looked in user_ind_columns and user_indexes to get the information you have.

Look at INDEX_TYPE in user_indexes.  This will tell you where to look.

If you can post the TYPE in question we can tell you where to look.

Also, you cross posted this in Oracle 9, 10 and 11.  VIEWs can change from release to release.  What specific version are you using?
MarioC82Author Commented:
Hi, thanks for the speedy reply, i cross posted by mistake but we use version 10 and 11. This is 10g.

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

slightwv (䄆 Netminder) Commented:

SELECT column_expression, column_position
FROM user_ind_expressions
WHERE index_name = ‘TEST_IND_1';
MarioC82Author Commented:
I found the column by doing the following:
select index_name,column_name,column_position from user_ind_columns where index_name like 'X_%%';

However instead of index_name as X_1IX i found X_20081104_1IX, as you can see there was a date appended between the table name X_ and _1IX.

The command you posted above does not yield any rows even when used index_name like 'X_%';. Could this indicate a problem with the indexing of the DB ?. Does user_ind_expressions have to contain an entry for the exact index name X_1IX ?.
MarioC82Author Commented:
Above it was meant to be like 'X_%' in the first query not double %.
Devinder Singh VirdiLead Oracle DBA TeamCommented:
What about this:-

set linesize 1000
col ddl format a1000
Select dbms_metadata.get_ddl('INDEX', 'X_1IX') ddl from dual;
   Generally Oracle create SYS_0000$ kind of indexes for the primary keys or some other constraints. try select * from dba_constraints where constraint_name='SYS_NC00022$' or select * from dba_constraints where index_name='SYS_NC00022$'

select column_name from DBA_CONS_COLUMNS where constraint_name='SYS_NC00022$'
MarioC82Author Commented:
This works when i tested it in a test database, however my problem was that i didn't have access to the table owner account. There were some restrictions. But i found what i was looking for, thank you.
All Courses

From novice to tech pro — start learning today.