cross-partition query performance

From: George Jansen <gjansen_at_cs.umd.edu>
Date: 8 Jul 1998 16:54:01 GMT
Message-ID: <6o0879$r0n$1@walter.cs.umd.edu>

I have run into what is perhaps an anomaly in 8.0.4, and would appreciate any suggestions.

We have a fair-sized partitioned table, call it X, partitioned on column Y. Column Y has a bitmap index on it. Now, if I run

select Y, count(*) from X where Y in ('T', 'U') group by Y,

T and U being in the same partition, the results return very quickly. If I run

select Y, count(*) from X where Y in ('L', 'U') group by Y,

L and U being in different partitions, it takes 7 to 10 times as long. We have an 8-processor Sun box, and explain plan indicates that each query is doing

2.1 PARTITION CONCATENATED PARALLEL_COMBINED_WITH_PARENT 3.1 SORT GROUP BY PARALLEL_TO_SERIAL 4.1 TABLE ACCESS FULL X PARALLEL_TO_PARALLEL Any pointers on where to look will be much appreciated.

George Jansen Received on Wed Jul 08 1998 - 11:54:01 CDT