| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Partitioning Tables
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). Received on Thu Nov 15 2001 - 13:20:47 CST
 |
 |