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).
