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.

Are these indexes local or global. Sounds like global. Are they histogramed? Does the sql use a predicate? The column type matters not, but predicate could cause the index to be ignored. There's lots out there on this type of thing. Check out Hotsos library or any Oak Table link and search for local vs global indexes or "why isn't my index". Johnathan Lewis has multiple index based articles. Also, read the oracle warehouse docs..... Received on Mon Nov 27 2006 - 21:37:57 CST