Home » SQL & PL/SQL » SQL & PL/SQL » index
index [message #346361] Mon, 08 September 2008 06:05 Go to next message
vijay838
Messages: 9
Registered: September 2008
Junior Member
How to find type of index and index name in a table.please help me.i am new to oracle
Re: index [message #346362 is a reply to message #346361] Mon, 08 September 2008 06:11 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Use %_indexes


http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#3239


Regards,
Oli

[Updated on: Mon, 08 September 2008 06:14]

Report message to a moderator

Re: index [message #346365 is a reply to message #346361] Mon, 08 September 2008 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For a list of dictionary views:
SQL> desc dict
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 TABLE_NAME                                VARCHAR2(30)
 COMMENTS                                  VARCHAR2(4000)

Regards
Michel
Re: index [message #346366 is a reply to message #346362] Mon, 08 September 2008 06:26 Go to previous messageGo to next message
vijay838
Messages: 9
Registered: September 2008
Junior Member
Thanks for your reply but i want to find type of index name on a column in a table.is there any way to get type of index,column name and index_name from a table by writting query?
Re: index [message #346368 is a reply to message #346366] Mon, 08 September 2008 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search in DICT, you will find the view with the help of the comment column.

Regards
Michel
Re: index [message #346370 is a reply to message #346361] Mon, 08 September 2008 06:32 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
select * from user_indexes
where table_name='YOUR_Table_name'
Re: index [message #346373 is a reply to message #346366] Mon, 08 September 2008 06:41 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member


You can get the info you required by joining..
all_indexes -To know the index type, name in a table
all_ind_columns-Lists Indexed Columns for a Table

Quote:

Search in DICT, you will find the view with the help of the comment column.


The best way to find out the views required.




Quote:

Back in the day when Oracle's documentation set wasn't so freely available on the Web, it was incredibly convenient that Oracle made
the DICTIONARY and DICT_ COLUMNS views available. Knowing just those two views, you could bootstrap to learning about all the
other views, and from thence to learning about your entire database.
Even today, it's convenient to know about DICTIONARY and DICT_COLUMNS. Often, if you aren't quite certain which view describes a
given object type, you can issue a wildcard query to find out. For example, to get a handle on what views might describe tables in your
select table_name, comments
from dictionary
where table_name LIKE '%TABLE%'
order by table_name;

This query returns all data dictionary view names that include the term "TABLE". This approach takes advantage of Oracle's fairly
consistent data dictionary view naming conventions. Views describing tables are all likely to contain "TABLE" in their name. (Sometimes,
as in the case of ALL_TAB_COLUMNS, TABLE is abbreviated TAB.)





Regards,
Oli
Re: index [message #346374 is a reply to message #346370] Mon, 08 September 2008 06:42 Go to previous messageGo to next message
vijay838
Messages: 9
Registered: September 2008
Junior Member
What should i get with that query ramana..i want to find table name,index type,column name and index name of my schema
Re: index [message #346384 is a reply to message #346374] Mon, 08 September 2008 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of dumbly try to execute a query, smartly follow the advice and find it by yourself.

Regards
Michel
Re: index [message #346391 is a reply to message #346384] Mon, 08 September 2008 07:23 Go to previous message
vijay838
Messages: 9
Registered: September 2008
Junior Member
i got it michel thanks for your advice
Previous Topic: Help On Query to fetch the master records?
Next Topic: Dynamic Memory allocation
Goto Forum:
  


Current Time: Sun Dec 04 08:36:46 CST 2016

Total time taken to generate the page: 0.05176 seconds