Home » SQL & PL/SQL » SQL & PL/SQL » Tables with Composite Indexes (Oracle 10g)
Tables with Composite Indexes [message #378757] Thu, 01 January 2009 04:53 Go to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Is it possible to find the table names having composite indexes created on them? If so, Can any one please let me know which data dictinary stores the details of composite indexes? I have seen in user_indexes, user_tables,user_segments etc.,. But, I couldnt find any.

Sam
Re: Tables with Composite Indexes [message #378758 is a reply to message #378757] Thu, 01 January 2009 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
user_ind_columns

Regards
Michel
Re: Tables with Composite Indexes [message #378759 is a reply to message #378757] Thu, 01 January 2009 05:24 Go to previous messageGo to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Michel,

Thanks for the reply. But, i need to find only the table names which have composite indexes created on it.This will just display the tables name and indexes created on them. But, how can i find whether it is a composite index?

Sam
Re: Tables with Composite Indexes [message #378762 is a reply to message #378759] Thu, 01 January 2009 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is a composite index?

Regards
Michel
Re: Tables with Composite Indexes [message #378765 is a reply to message #378762] Thu, 01 January 2009 06:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT INDEX_NAME
FROM USER_IND_COLUMNS
WHERE COLUMN_POSITION = 2


Ross Leishman
Re: Tables with Composite Indexes [message #378766 is a reply to message #378765] Thu, 01 January 2009 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Aargh! You cut my line. I wanted to make him this find by himself.

Regards
Michel

[Updated on: Thu, 01 January 2009 06:43]

Report message to a moderator

Re: Tables with Composite Indexes [message #378767 is a reply to message #378766] Thu, 01 January 2009 07:07 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sorry about that. I had no idea what was going on when you asked a question to which both you and the OP clearly knew the answer. It just seemed weird and I wanted it to get normal again. Quickly.

If you are trying to help people develop research or descriptive skills, then you should be up-front and explain that you are offering guidance and not a solution.

Then I will know what you up to and leave you alone to get on with it.

Nuance is a very difficult thing to get across in 10pt Arial.
Re: Tables with Composite Indexes [message #378780 is a reply to message #378765] Thu, 01 January 2009 13:49 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
@Michel/rleishman,

I think a small modification required
SELECT TABLE_NAME,INDEX_NAME
FROM USER_IND_COLUMNS
WHERE COLUMN_POSITION > 1


@sam
If you want to get more details like on which columns the use
Decode,Max function and group by..

Regards,
Oli


[Updated on: Thu, 01 January 2009 13:51]

Report message to a moderator

Re: Tables with Composite Indexes [message #378782 is a reply to message #378780] Thu, 01 January 2009 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I think a small modification required
SELECT TABLE_NAME,INDEX_NAME
FROM USER_IND_COLUMNS
WHERE COLUMN_POSITION > 1

And you are wrong.
"= 2" is important if you don't want duplicates.

Quote:
If you want to get more details like on which columns the use
Decode,Max function and group by..

This is meaningless, why not SUM, AVG, LPAD, LTRIM and all SQL functions?

Regards
Michel
Re: Tables with Composite Indexes [message #378783 is a reply to message #378782] Thu, 01 January 2009 15:19 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Got it...
Thanks Michel.

Quote:

This is meaningless, why not SUM, AVG, LPAD, LTRIM and all SQL functions?




I said that it can be done using functions like (e.g,) DECODE,MAX and GROUP BY

[Updated on: Thu, 01 January 2009 15:23]

Report message to a moderator

Previous Topic: DEFAULT value
Next Topic: Count per Hour
Goto Forum:
  


Current Time: Fri Dec 09 05:57:49 CST 2016

Total time taken to generate the page: 0.13817 seconds