Home » SQL & PL/SQL » SQL & PL/SQL » howto know a indexed column?
howto know a indexed column? [message #267553] Thu, 13 September 2007 12:55 Go to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

i have a db with a lot of indexes and if i see all_indexes table, i can see the index name, and the table that index is checking but, how to know what colum is "indexing"?

after that i want to rebuild ALL indexes... is there any other way that
alter index index_name rebuild;
?

thnx for any help
Re: howto know a indexed column? [message #267556 is a reply to message #267553] Thu, 13 September 2007 12:59 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
select view_name from user_views where view_name like '%COL%' and view_name like '%IND%'

>after that i want to rebuild ALL indexes
Never confuse movement with progress.
Going around in circles is movement, but most folks do not consider it to be progress.
You might just as well get better performance by rotating the tires on your car.

[Updated on: Thu, 13 September 2007 13:01] by Moderator

Report message to a moderator

Re: howto know a indexed column? [message #267558 is a reply to message #267553] Thu, 13 September 2007 12:59 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
user_ind_columns

However, there is usually no good reason for rebuilding indexes. Why do you think you need to rebuild indexes?
Re: howto know a indexed column? [message #267560 is a reply to message #267556] Thu, 13 September 2007 13:00 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

anacedent wrote on Thu, 13 September 2007 12:59
select view_name from user_views where view_name like '%COL%' and view_name like '%IND%'

thnx a lot all_ind_columns FTW
Re: howto know a indexed column? [message #267561 is a reply to message #267558] Thu, 13 September 2007 13:01 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

joy_division wrote on Thu, 13 September 2007 12:59
user_ind_columns

However, there is usually no good reason for rebuilding indexes. Why do you think you need to rebuild indexes?

because i must to move all tables to another filesystem
Re: howto know a indexed column? [message #267563 is a reply to message #267561] Thu, 13 September 2007 13:07 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Ok, that's one of the reasons, but it is not necessary to know the columns involved. A simple
alter index index_name rebuild;
like you mentioned initially is all you need.
Re: howto know a indexed column? [message #267565 is a reply to message #267553] Thu, 13 September 2007 13:10 Go to previous message
thorin666
Messages: 144
Registered: March 2007
Senior Member

yes, but the point to know what column use that index is to check that a list of columns are indexed and for what index_name
(my boss ask me for a report)
Wink
Previous Topic: Using a stored proc through a db-link, in a package, on a pass-through-account?
Next Topic: query on a result of another query
Goto Forum:
  


Current Time: Sun Dec 04 00:50:52 CST 2016

Total time taken to generate the page: 0.03918 seconds