Re: HowTo find out used (useful) and unused (usesless) indexes?

Shakespeare wrote:

> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht 
> news:1208624947.696580_at_bubbleator.drizzle.com...

> 
> I was aiming at data with for example a 'year' column. This column could be 
> indexed by design, but in the first year (all records same value) this index 
> is not useful and won't be used. But on the first entry in the second year 
> it is useful to find entries of that year and so on. A script to remove or 
> disable unused indexes would remove/disable this index in the first year.
> 
> Shakespeare 

Interesting example.

First year the index is worthless. Second year it is valuable for some short amount of time and then, again, may become useless as the percentage of records in each of the two years approaches 50%. Go for the third year ... again possibly usable for some period of time and then all three years probably tend toward 33.3%. By the end of year four you are going for 25%.

I would not be all that ready to build that index unless I know, again using DBMS_STATS.SET_TABLE_STATS and SET_INDEX_STATS how Oracle was going to use it. That is not to say the index might not make a lot of sense ... I just would insist on testing any assumption before deciding to either build it or not.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org