Deciding what columns to partition on

We are doing a redesign of our 200-Gig data warehouse in 9i.

One of the things we're thinking about is changing which columns we partition on.

Currently, we partition exclusively on date fields. This has proven extremely helpful for doing maintenance, purging old data, archiving, etc. However, I feel that we're getting minimal benefit with regard to partition pruning.

Very few of our most-used queries and reports even include the date field that we are partitioning on. Instead, we usually query primarily on account number, rep number, etc. which is a much more selective column. With indexes
(and little partition pruning), we are getting
response times that are not fantastic but are within tolerable range. However, as the warehouse gets larger, these response times get less and less acceptable. Hence this redesign.

Many of our indexes are not even locally partitioned. I can change them to be locally partitioned. The primary key ones can't be locally partitioned unless they include the columns (date) that the table is partitioned on.
Although the queries that use these primary key indexes don't even include date clauses, I could still add the date field to the index so that it can be locally partitioned.

However, this doesn't seem like a great idea in most cases.

Currently, we seldom seem to be doing any partition pruning in our explain plans. We seem to use indexes that don't include date columns instead. I'm not sure if
the indexes would work better if they had the date column in them, especially
if the date column had to be the leading column in the index. Although the
partitions would then be pruned, I don't think the query would perform as well as it would with a global partitioned index (or even a non-partitioned index) where the acct_no (or whatever highly selective column) is the leading column in the index.

I've tried to do some testing on this but the results haven't been conclusive
because I can't test in production and I don't think my test database is large
enough to give correct test results.

Now to my question: In this sort of situation, should I settle for being able
to either use date partitioning for maintenance benefits only, or to partition
by the columns that I most often query on like acct_no so that I can prune by partition. Or is there some magical hybrid situation where I can have both ease of maintenance and partition pruning for performance? If yes, what
sort of magical partitioning strategy do I need to use?

Thanks for any insights you can offer.

Also, I've been reading Scaling Oracle8i but it's more aimed at OLTP. Can anyone offer a reference for a good data warehousing book, web site, white papers,
etc. that covers 9i? Or even 8i for that matter?

Thanks in advance for your feedback.

Cherie Machler
Oracle DBA
Gelco Information Network

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Cherie_Machler_at_gelco.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).