large table optimizer suggestions

I have a table with 4e9 rows, size about 80 bytes/row, partitioned by time slices (daily), each slice containing about 10e6 rows. One column has a local bitmap index. There are typically 10-1000 rows matching a given column value in each partition.

Some of my queries are multi-partition aggregates. Obviously, parallel full scans are the right way to do this. Other queries are searching a single partition for rows matching a single value from the indexed column. Obviously, the index is useful here (typically about 100 times faster than a single partition FTS)

I have global stats at table, partition, and indexed column level. I think the optimizer should be smart enpough to use parallel FTS on aggregate queries, and use the index on the other queries. But no such luck. It chooses FTS for the indexed column query, unless I hint the SQL. Oracle Corp's geniuses suggested I 'alter table noparallel' Sure enough, then the index gets used, but then the multi-part aggregate queries run single-threaded, which obviously is a killer.

Am I missing something obvious here? Isn't this like a textbook case of a data warehousing situation? Shouldn't the optimizer know how to do this correctly? Received on Wed Aug 24 2005 - 13:48:13 CDT