unused indexes

I have 1200 out of 1500 indexes in 10g that are never been used within 4 weeks and most of the processings have been passed. What should I do with them.


Thanks
dba1234Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
If you are sure they are not necessary, maybe drop them?

I would document them in the event you ned to recreate them.

After you drop indexes, keep monitoring performance to see if you might need to recreate one or more.
0
 
awking00Commented:
What kind of "processings have been passed"?
0
 
sdstuberConnect With a Mentor Commented:
also note, some indexes exist for the purpose of enforcing constraints.  So your users might not be querying from them but the indexes were used at the time of insert, update or delete

if you're using monitoring,  the recursive action of looking at the index for enforcement of constraints is not captured.
Only direct action.


these will be monitored

select * from table where indexed_column = x;
delete from table where indexed_column = y;
update table set some_column = 1234 where indexed_column = z;

this will not be monitored

delete myparent table;
index lookup of children via fk (hopefully your fk's are indexed);

you'll get partial monitoring here...
emp has two indexes, one on name and a unique index on empid
update emp set name='sdstuber' where empid = 1234;

empid index will be monitored,  the update on the name index will not be
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dba1234Author Commented:
Processings means our monthly and weekly jobs.
0
 
dba1234Author Commented:
sdstuber, except monitoring what's the other way to monitor indexes. and how can I make sure which indexes are being used in the delete statement as you said.
delete myparent table;
index lookup of children via fk (hopefully your fk's are indexed
0
 
DavidConnect With a Mentor Senior Oracle Database AdministratorCommented:
Just to confirm you're not thinking of removing eBS (APPS) indexes.....  

It's my opinion that without a predicate, the delete table statement would require a full table scan.  With child tables present, and a referential integrity constraint (foreign key), I'd expect the PK to be used.

Do you wish to drop related child rows, or leave them?
0
 
sdstuberConnect With a Mentor Commented:
you can't, at least not in any kind of "good" way

you could turn on tracing for the entire instance, then grep through the trace files for index access .
That would work, but tracing every statement of every session isn't really a viable option for most systems.

you could audit every statement, collecting action and table, then join those results to dba_constraints and dba_indexes to try to capture all of the non-monitored actions.   possible, but a lot of work.  You might get false positives doing that too.  probably preventable, but off hand I'm not positive it's completely reliable.

rather than trying to find them all,  I'd try targetting a few that might be problematic.  
The drop and see who complains method is drastic but might be the most efficient.  Especially if you have high confidence they are not used and can easily rebuild them if/when you are wrong.
0
 
awking00Connect With a Mentor Commented:
What do your monthly and weekly jobs do? If they are merely inserting, updating, and/or deleting records in database tables, that doesn't mean any queries might not be using the indexes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.