Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

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
0
dba1234
Asked:
dba1234
  • 2
  • 2
  • 2
  • +2
5 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now