Oracle partitioning solution for DELETE performance problem

Question

This is a follow-up question to Strategy to improve Oracle DELETE performance. To recap, we have a large DB with a hierarchy of tables representing 1D through 4D output data from an optimization system. Reading and writing this data is fast and provides a convenient means for our various systems to utilize the information.

However, deleting unused data has become a bear. The current table hierarchy is below.

/* Metadata tables */
Case(CaseId, DeleteFlag, ...) On Delete Cascade CaseId
OptimizationRun(OptId, CaseId, ...) On Delete Cascade OptId
OptimizationStep(StepId, OptId, ...) On Delete Cascade StepId

/* Data tables */
Files(FileId, CaseId, Blob) /* deletes are near instantateous here */

/* Data per run */
OnedDataX(OptId, ...)
TwoDDataY1(OptId, ...) /* packed representation of a 1D slice */

/* Data not only per run, but per step */
TwoDDataY2(StepId, ...)  /* packed representation of a 1D slice */
ThreeDDataZ(StepId, ...) /* packed representation of a 2D slice */
FourDDataZ(StepId, ...)  /* packed representation of a 3D slice */
/* ... About 10 or so of these tables exist */

What I am looking for is a means of partitioning the Case data such that I could drop a partition relating to the case to remove its data. Ideally, OptimizationRun would have an interval partition based on CaseId and this would filter down through to its children. However, 11g doesn't support the combination of INTERVAL and REF partitioning.

I'm fairly certain ENABLE ROW MOVEMENT is out of the question based on the DB size and the requirement that the tablespaces live in ASSM. Maybe RANGE partitioning on OptimizationRun and REF partitioning on the rest?

My guess is with that strategy I would need a trigger that accomplishes something like the following:

CREATE OR REPLACE TRIGGER Case_BeforeInsert_MakePartitions
BEFORE INSERT
    ON Case
    FOR EACH ROW
DECLARE
    v_PartName varchar(64)       := 'CASE_OPTPART_' || :new.CaseId;
    v_PartRange Case.CaseId%type := :new.CaseId
BEGIN
    -- Take :new.CaseId and create the partition
    ALTER TABLE OptimizationRun
        ADD PARTITION v_PartName
        VALUES LESS THAN ( v_PartRange );
END;

And then the requisite trigger for before deletion:

CREATE OR REPLACE TRIGGER Case_BeforeDelete_RemovePartitions
BEFORE DELETE
    ON Case
    FOR EACH ROW
DECLARE
    v_PartName varchar(64) := 'CASE_OPTPART_' || :old.CaseId;
BEGIN
    -- Drop the partitions associated with the case
    ALTER TABLE OptimizationRun
        DROP PARTITION v_PartName;
END;

Good idea? Or is this an idea out of the SNL Bad Idea Jeans commercial?

Update, for size reference:

• 1D data tables ~1.7G
• 2D data tables ~12.5G
• 3D data tables ~117.3G
• 4D data tables ~315.2G

Answer 1

I'm pretty sure that you're on the right track with partitionning to deal with your delete performance problem. However, I don't think you'll be able to mix this with triggers. Complex logic with triggers has always bothered me but aside from this here are the problems you are likely to encounter:

• DDL statements break transaction logic since Oracle performs a commit of the current transaction before any DDL statement.
• Fortunately, you can't commit in a trigger (since Oracle is in the middle of an operation and the DB is not in a consistent state).
• Using autonomous transactions to perform DDL would be a (poor?) workaround for the insert but is unlikely to work for the DELETE since this would probably interfere with the ON DELETE CASCADE logic.

It would be easier to code and easier to maintain procedures that deal with the dropping and creation of partitions such as:

CREATE PROCEDURE add_case (case_id, ...) AS
BEGIN
   EXECUTE IMMEDIATE 'ALTER TABLE OptimizationRun ADD partition...';
   /* repeat for each child table */
   INSERT INTO Case VALUES (...);
END;

Concerning the drop of partitions, you'll have to check if this works with referential integrity. It may be needed to disable the foreign key constraints before dropping a parent table partition in a parent-child table relationship.

Also note that global indexes will be left in an unusable state after a partition drop. You'll have to rebuild them unless you specify UPDATE GLOBAL in your drop statement (obviously this would rebuild them automatically but will take more time).