Re: Advice on Date Range Partitioned Table

Dereck L. Dietz wrote:
> Environment: Oracle 10g R2
>
> There is a large table where I work which is currently in 10 partitioned by
> a DATE field. The DATE field is also indexed.
>
> The person who is responsible for the table has complained that the index
> which is on the partition key isn't being used. He is convinced the reason
> is that the partition key is a DATE field and that it should instead be a
> contrived field of just the year.
>
> At a meeting today I was given the task of determining whether that was a
> sound idea or not and I'm not really sure where to start to either confirm
> it or not. I may be completely wrong but I can't see where using a
> contrived YEAR would be any different than a DATE.
>
> Can anybody offer any advice and/or point me in the right direction of where
> to look?
>
> Thanks.

I like what EscVector wrote but have you considered:

ALTER INDEX <index_name> MONITORING USAGE;

and then after some reasonable period of time ...

SELECT * FROM v$object_usage;

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