Re: Partitioned table usage question

Yes, but you are talking just for the case where you have 'star transformation' enabled, right? I.e., not just Oracle's normal 'star query' detection, which is of more use for 'normal' style star queries. It's my understanding that if you have sparse fact tables and wide dimensions, only then will you benefit from star *transformations*, which I believe defaults to 'off'.

We have many tables in our datawarehouses containing 100's of millions of rows, and we've found the partitioning of them in Ora8 a godsend. Our testing showed enabling star *transformations* (i.e. *not* just star query processing, which is 'on' by default) actually made things worse, as did forced 'star' hinting. We've found you have to have a pretty good reason to override the optimiser in 8.0.5, because if your dw model is solid, it normally does a good job.

Just my take.

MotoX.

On Wed, 16 Dec 1998 21:48:22 GMT, acedba_at_my-dejanews.com wrote:

>Arun:
>
>I have some bad news for you regarding partitioning.
>
>One of my programmers came to me with a query that exceeded the 2-hour time
>limit they had set for queries to complete on this decision-support system.
>The database design is a star schema, with a fact table of 52M rows. He
>didn't think it should be taking that long since he was only requesting rows
>from one partition -- October in this case.
>
>I ran the query but included the PARTITION clause to explicitly target the
>partition I wanted and eliminate the rest. This time the query ran in 13
>minutes.
>
>I opened a TAR with Oracle and what they told me was there is a bug
>(#656946). Actually, it's not really a bug. Rather, it's a limitation
>in the way partition elimination was designed to work.
>
>What happens is that the partition pruning code is only able to eliminate
>partitions using OR predicates by performing OR-expansion, and
>OR-expansion is not compatible with the star query transformation. So you
>can use one or the other but not both.
>
>As I recall, this query used the "in" keyword, which is the same as "or."
>Now maybe if you use "=" it might be a different story, but I wouldn't
>count on it.
>
>Oracle claims they'll have an enhancement in 8.1.3 that should enable the
>database to do a better job of handling queries like this. For now, we're
>stuck.
>
>Angie
>
>In article <756el2$p5t$1_at_nnrp1.dejanews.com>,
> aa_at_triad.com wrote:
>> I have a table that is currently partitioned on a date column ( partitioned
>> at month boundaries). Using explain plan how can I determine that the
>> optimizer is looking for data from a specific partition ( i.e. partition
>> pruning) based on the query I execute. e.g I need to know that when I select
>> data for 12/15/1998 the optimizer looks only in the December partition and
>> then uses the index on the date column to find the exact row I need. Thanks,
>>
>> Arun
>>
>> -----------== Posted via Deja News, The Discussion Network ==----------
>> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Dec 17 1998 - 14:53:36 CST