Eric, In my prev. assignment I had to deal with partitions quite a bit. Thankfully, on the tables I had to deal with, the parent and child records were on partitions corresponding to the same month. Your's is a tricky situation. I have an idea but I am not sure it is feasible for you. But here it is anyway. Good luck my friend!!!!
Assumptions : I am assuming that there is a foreign key based on sesion_id from C --> A, and also from B --> A. Also assuming that data is partitioned by month. So if we need at least 90 days worth of days, say we have partitions for Jan, Feb, Mar, and April (3 full months + the current month. At the end of the current month we will actually have 120 days worth of data). On the last day of April, we will drop the partition for Jan and create one for May. Also assuming that if the child is less than 90 days old, but the parent is older than 90, the complete set (parent and child) need to be retained (and not deleted). Also assuming that you have a little window of down time when you will be doing this. Using the above example, Partition maintenance would involve. step 1. Drop the "Jan" partition from B step 2. Drop "Jan" partition from C Now the tricky part. J step 4. Any session_ids in the "Jan" partition of A that exists in the "Feb", "MAR" or "Apr" partitions of B OR C, CANNOT be deleted 'cose they have children that are not old enough ( ?). Identify these records. (NOTE : It’s been a while since I’ve worked on this, and I don’t remember if we can update the partition key (i.e, date).. something tells me that it is not possible, but I could be wrong !!!!, if it is possible, as an easier option we can update the date so that these records get moved to the Feb partition (on A), drop the Jan partition, and then reset them back to their old dates, and skip the remaining steps other than 8. ) Step 5. Temporarily Disable the foreign constraints from B --> A and from C --> A. Step 6. Exchange the oldest partition from A. ALTER TABLE A EXCHANGE PARTITION "jan" WITH TABLE exchange_table WITHOUT VALIDATION; Step 7. Drop the oldest partition. Step 8. Create a partition for "May". Step 9. Copy records identified in step 4 from the exchange table back into A (this will now be in the "Feb" partition) Step 10. Re-enable the foreign keys from B--> A and from C--> A. You are disabling the constraints for a small window and that too during down time (I assume). I don’t like having to disable constraints too, but this may be faster than delete cascades, involving "Non indexed" B and C. Sunny >From: Erik Williams <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Partitioning Tables >Date: Thu, 15 Nov 2001 11:00:54 -0800 > >I have a set of three tables that I am having trouble deleting from. The >issues are caused by the size of the tables and the foreign keys. There are >no indexes on B and C due to insertion speed requirements. The last time I >emailed the group about this, partitioning was a popular response. I am now >concidering and testing partitioning. I need to keep 90 days of data >online, >so partiioning by date seemed like the logical solution. So, I created a >test environment and range partitioned by the date attributes. This worked >great for tables B and C. I was able to export the oldest partitions and >then drop them quickly. The problem was with the A table. I could not drop >partitions becuase of the FK constraints. I would have to disable the >constraints and this is not a great solution. I then thought about hash >partitioning by session_id. Then when I deleted from A with ON DELETE >CASCADE, scans (no indexes on B and C) would be limited to the size of the >partitions. This also seems to be suboptimal, as the deletes are taking a >very long time in my test environment. > >Does anyone have any experience on this sort of design that can provide >some >guidance? I am at a loss and hope that someone has done this sort of thing >sucessfully and can point me in the right direction. > >Erik > > >Table A - Session > session_id (primary key) > start_dtm (date) > >Table B - Session Event > session_event (primary key) > session_id (FK to session table) > event_datetime (date) > >Table C - Session Quote > session_quote (primary key) > session_id (FK to session table) > quote_datetime (date) > > > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Erik Williams > INET: [EMAIL PROTECTED] > >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: [EMAIL PROTECTED] (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). _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sunny Verghese INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
