|
Not that I think this is a great idea, as I believe that partitioning
granularity should match the granularity of likely queries as well as
ETL/load frequency (neither of which seems to imply "weekly" in this
case), but to answer the question about deleting a day's worth of data
in weekly partitions... <mantra> The fastest and most scalable UPDATE, MERGE, or DELETE operation is an INSERT. </mantra> Anyway, the fastest and most scalable way to "delete" a day's worth of data out of a weekly partition is... 1) CREATE TABLE {scratch-table-name} NOLOGGING PARALLEL {degree} AS SELECT * FROM {partitioned-table} PARTITION {partition-name} WHERE {partition-key-date-column} BETWEEN {start-date-to-keep} AND {end-date-to-keep}; 2) create indexes on the {scratch-table-name} to match the LOCAL indexes on {partitioned-table}, using PARALLEL {degree}, NOLOGGING, and COMPUTE STATISTICS 3) ALTER TABLE {partitioned-table} EXCHANGE PARTITION {partition-name} WITH TABLE {scratch-table-name} INCLUDING INDEXES UPDATE GLOBAL INDEXES PARALLEL {degree}; Step one creates {scratch-table-name} from the six days of data in the week partition {partition-name} that you wish to retain; steps two and three just follow naturally from that. Obviously, specifying the PARTITION clause and the WHERE clause in the CREATE TABLE ... AS SELECT are two different exclusive alternatives. You only need to do one or the other -- you don't need to do both (as shown here), but doing both doesn't hurt anything, so I included them together. Along with most of the respondents on this thread, I'm mystified by the comments implying that 40-50 partitions might be too many to manage. I've been in situations where tens-of-thousands of partitions have been in use quite happily -- obviously the key is automation and scripting. So, instead of using the wrong partitioning granularity for a situation (as seems to be proposed here), use daily partitions instead. Add a new partition when you're loading a new day. Drop a partition when it is too old. If you'd like some code to get started, then the package EXCHPART at http://www.EvDBT.com/exchpart.sql has a procedure named ADD_NEWER_THAN for adding daily partitions and DROP_OLDER_THAN for dropping old partitions, among other things. Keep life simple. Be happy. Hope this helps... Tim Gorman consultant -> Evergreen Database Technologies, Inc. postal => P.O. Box 630791, Highlands Ranch CO 80163-0791 website => http://www.EvDBT.com/ email => Tim@xxxxxxxxx mobile => +1-303-885-4526 fax => +1-303-484-3608 Lost Data? => http://www.ora600.be/ for info about DUDE... Greg Rahn wrote: -- //www.freelists.org/webpage/oracle-lIf you have 7 partitions, one for each day of the week (OP says partition "reference the day of week"), and say you have 6 weeks (42 days) of data and you want to remove the oldest day, how can you do with with a truncate? Hint, you cant: each partition contains 6 days and you want to simply remove one day. If you did week partitions of 7 days (not 7 day of the week partitions) then sure, you could eventually drop off the oldest week -- a nologging operation. On Fri, May 14, 2010 at 7:41 AM, Ivan Ricardo Schuster <ivanrs79@xxxxxxxxx> wrote: |
