Re: Advice on Date Range Partitioned Table

DA Morgan wrote:
> 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

Good thought. Deeper dive would be 10053 trace and xplan examination. This would tell exactly what the CBO is "thinking". Received on Tue Nov 28 2006 - 00:01:35 CST