Home » SQL & PL/SQL » SQL & PL/SQL » Index column name- where is it stored?
Index column name- where is it stored? [message #216863] Tue, 30 January 2007 13:57 Go to next message
karaseg
Messages: 14
Registered: January 2007
Junior Member
I am assuming this is Newbie level:

I would like to be able to get the column name that an index is based on.

Let me explain:
I want to query the database to determine what indexes (indices?) exist and all the relevant info to recreate them after dropping....

For example- I created an index as:

CREATE INDEX EDSTAGE.INDX_TRAN_CODE_TRAN_TYPE_ID
ON EDSTAGE.TRANSACTION_CODE(TRANSACTION_TYPE_ID);

When I look in DBA_INDEXES I see the owner, the index_name, the table_name and all the type stuff (index_type, uniqueness, etc..)
but do not see the column name the index is based on (in this example TRANSACTION_TYPE_ID.
Where in the heck is this stored? It looks like it is in DBA_CONS_COLUMNS
But- when I look at DBA_CONSTRAINTS and DBA_CONS_COLUMNS, I can find all the constraints that are type 'C' and associated with that owner and table_name but see no data in index_name column from the dba_constraints table to link to dba_indexes, nor do I see any reference to the generated index name (SYS_C00102458) in the DBA_INDEXES table to connect to either DBA_CONSTRAINTS or DBA_CONS_COLUMNS to get the column name.

Does anyone know what I am missing?
If this is better posted in a different forum please let me know.
Thanks!
Re: Index column name- where is it stored? [message #216864 is a reply to message #216863] Tue, 30 January 2007 14:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
scott@9i > create index myindex on emp(ename);

Index created.

scott@9i > select index_name,table_name,column_Name from user_ind_columns where index_name='MYINDEX';

INDEX_NAME                     TABLE_NAME                     COLUMN_NAM
------------------------------ ------------------------------ ----------
MYINDEX                        EMP                            ENAME
 
Re: Index column name- where is it stored? [message #216865 is a reply to message #216863] Tue, 30 January 2007 14:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>to determine what indexes (indices?) exist and all the relevant info to recreate them after dropping
Wouldn't it be easier to just extract the Index DDL?
Re: Index column name- where is it stored? [message #216866 is a reply to message #216863] Tue, 30 January 2007 14:04 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
SELECT VIEW_NAME FROM DBA_VIEWS WHERE VIEW_NAME LIKE '%IND%';
The results from the query above should provide some useful clues WRT where you should actually find your answer(s).
Re: Index column name- where is it stored? [message #216867 is a reply to message #216864] Tue, 30 January 2007 14:06 Go to previous messageGo to next message
karaseg
Messages: 14
Registered: January 2007
Junior Member
You're awesome- thanks!
Stupid me! I had searched for views as:

select view_name from dba_views where
view_name like '%INDEX%'

never occurred to me to do like '%IND%'

Thanks!
Re: Index column name- where is it stored? [message #216868 is a reply to message #216865] Tue, 30 January 2007 14:08 Go to previous messageGo to next message
karaseg
Messages: 14
Registered: January 2007
Junior Member
>>Wouldn't it be easier to just extract the Index DDL?

which is stored where?
Re: Index column name- where is it stored? [message #216869 is a reply to message #216868] Tue, 30 January 2007 14:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
scott@9i > @get_index.ddl


  CREATE INDEX "SCOTT"."MYINDEX" ON "SCOTT"."EMP" ("ENAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"
 ;

scott@9i > get get_index.ddl
  1  set long 500000000000
  2  set linesize 10000
  3  set feed off;
  4  SET HEAD off;
  5  set trimspool on;
  6  column xxx  format a600
  7  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',FALSE);
  8  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',true);
  9  exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true);
 10  SELECT DBMS_METADATA.GET_DDL('INDEX',D.INDEX_NAME)||';' as xxx FROM user_indexes D;
 11  set head on;
 12* set feed on;
Re: Index column name- where is it stored? [message #216871 is a reply to message #216869] Tue, 30 January 2007 14:15 Go to previous messageGo to next message
karaseg
Messages: 14
Registered: January 2007
Junior Member
Thanks, but no- I think using DBA_INDEXES and DBA_IND_COLUMNS give me what I need in the most simplistic manner.

I am creating dynamic sql scripts to spool out create and drop scripts for our PKs, FKs and INDEXES on certain tables.
Got the PK and FK completed using DBA_CONSTRAINTS and DBA_CONS_COLUMNS, but just wasn't aware there was the DBA_IND_COLUMNS view out there ti get the final info I needed.

Thanks to all for your quick responses and help!
Re: Index column name- where is it stored? [message #216872 is a reply to message #216871] Tue, 30 January 2007 14:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> for our PKs, FKs and INDEXES on certain tables
I do not see the point.
With DBMS_METADATA you can just specify the TABLE_NAME and get all its dependent objects (or for a whole schema).
Why do you want to write something new?
But, it is upto you Smile

[Updated on: Tue, 30 January 2007 14:20]

Report message to a moderator

Re: Index column name- where is it stored? [message #216875 is a reply to message #216872] Tue, 30 January 2007 14:28 Go to previous message
karaseg
Messages: 14
Registered: January 2007
Junior Member
Thanks! But what I've created took only a few minutes to create (other than trying to find that one darn column) and is only a few lines, plus it does not require me to examine and figure out these packages that I've never used before...

Thanks again for your help!
Previous Topic: Causes of internal code error :??
Next Topic: help on sql query
Goto Forum:
  


Current Time: Mon Dec 05 11:15:52 CST 2016

Total time taken to generate the page: 0.13471 seconds