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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
What kind of "processings have been passed"?
0
sdstuberCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
DavidSenior 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
sdstuberCommented:
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
awking00Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.