| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partitioning Tables
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 intoA (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 <ewilliams_at_brownco.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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: ewilliams_at_brownco.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: Sunny Verghese INET: vsgeorge70_at_hotmail.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).Received on Sat Nov 17 2001 - 00:27:31 CST
 |
 |