Re: Deciding what columns to partition on

Stephane,

Thanks for the reply. I'm not sure that I understand what you mean about containment.

I definitely understand about the maintenance part however. This database has been
a real bear to maintain. I can't imagine it without partitioning in order to truncate off last
quarter's unneeded data.

Cherie

                                                                                       
                       
                    Stephane                                                           
                       
                    Faroult              To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>  
                    <sfaroult_at_orio       cc:                                           
                       
                    le.com>              Subject:     Re: Deciding what columns to 
partition on               
                    Sent by:                                                           
                       
                    root_at_fatcity.c                                                     
                       
                    om                                                                 
                       
                                                                                       
                       
                                                                                       
                       
                    12/18/01 02:55                                                     
                       
                    PM                                                                 
                       
                    Please respond                                                     
                       
                    to ORACLE-L                                                        
                       
                                                                                       
                       
                                                                                       
                       

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).




-- 
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).