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 : Its been a while since Ive worked on this, and I dont 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 dont 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
