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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

slightwv (䄆 Netminder) Commented:

SELECT column_expression, column_position
FROM user_ind_expressions
WHERE index_name = ‘TEST_IND_1';
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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 %.
slightwv (䄆 Netminder) 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;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.