Re: Oracle 8 partitionning

I am testing oracle 8 partitioning on a large data warehouse. My findings are not good. We have a third party OLAP tool that generates all the of our users queries. The problem is that for partitioning to yield good results, the OLAP generated where clause must reference the partition key in order to do partition elimination. If the partition key is not in the where clause, then you do full partition operations (e.g. range scan of indexes -- but for all partitions, not just the ones of key interest).

Is this an Oracle shortcoming? NO. But the people writing queries, whether internal developers or vendors, must write good SQL that can leverage the feature. If queries are done right, I have found up to 600% improvement. If queries don't include partition key, then I have found up to a 20% decrease. My advice, do lot's of explain plans.

Hope this helps ...

Stephane Dodier wrote in message <01bd244b$c6744e20$f676fdcf_at_stephaned>...
>Hi,
>
>I would like to have feedback in using Oracle 8 partitionning, good and bad
>stories.
>I'm planning to use it in a datawarehouse project.
>
>TIA
Received on Thu Jan 22 1998 - 00:00:00 CST