Re: Partitioned table usage question

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 Wed Dec 16 1998 - 15:48:22 CST