Re: Deciding what columns to partition on

Cherie_Machler_at_gelco.com wrote:
>
> 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.
>

Cherie,

   I am not sure that this really relates to your problem but if I think that you should consider partitioning more in relation to physical degradation (containment) and ease of maintenance (especially the ability to truncate partitions) than in terms of pure performance, especially on a 'clean' database. I have never found the argument 'you scan a single partition' very compelling when your data is properly indexed.

   I have carried out experiments recently and I was surprised to discover that the best results I had were gained by partitioning on a column which was updated (allowing for row migration) and not on the one I thought was the obvious candidate. Update was unsurprisingly twice as costly as in the other cases, but given the special mix of queries is still was the best overall, especially after a lot of inserts and deletes. Experiment carefully.

-- 
Regards,

Stephane Faroult
Oriole Ltd
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).