Oracle pegging solution for DELETE issue

Question

This is the next question to the Strategy for Improving Oracle DELETE Performance . Recall that we have a large database with a hierarchy of tables representing the output from 1D to 4D from the optimization system. Reading and writing this data is fast and provides convenient means for use by our various information systems.

However, deleting unused data has become bearish. 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'm looking for is a way to split Case data so that I can delete the relevant section to delete its data. Ideally, OptimizationRun will have an intermediate section based on CaseId , and this will filter its children. However, 11g does not support the combination of INTERVAL and REF.

I am sure that ENABLE ROW MOVEMENT is out of the question based on the size of the database and the requirement that the table spaces be in ASSM. Maybe RANGE is divided into sections OptimizationRun and REF on the rest?

I assume that with this strategy I will need a trigger that does 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 required trigger before deleting:

 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; 

A good idea? Or is this an idea from SNL Bad Idea Jeans ads?

Update for size reference :

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

Answer 1

I am sure that you are on the right path with partitioning to deal with the problem of the delete problem. However, I do not think you can mix this with triggers. Complicated logic with triggers always bothered me, but besides this, there are problems that you are likely to encounter:

• DDL statements abort transactional logic because Oracle captures the current transaction before any DDL statement.
• Fortunately, you cannot commit a trigger (since Oracle is in the middle of the operation and the database is not in a consistent state).
• Using autonomous transactions to execute DDL would be a (workaround) insertion solution, but it is unlikely to work for DELETE, as this is likely to interfere with ON DELETE CASCADE logic.

It would be easier to code and simplify the procedures associated with dropping and creating 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; 

As for section reduction, you will need to check if this works with referential integrity. You may need to disable foreign key constraints before discarding the parent section of the table with respect to the relationship between the parent and child tables.

Also note that global indexes will remain unusable after a section falls. You will have to rebuild them if you do not specify UPDATE GLOBAL in your reset statement (obviously this will rebuild them automatically, but will take longer).

Answer 2

Impossible - you cannot issue DDL, as in a level trigger.

[possible commentary on the release of the project, edited as indicated]

Have you considered parallelizing your script? Instead of sweeping, relying on the removal of the cascade, use DBMS_SCHEDULER instead to parallelize the job. You can safely run parallel hits on tables at the same level of the dependency tree.

 begin dbms_scheduler.create_program (program_name => 'snapshot_purge_cases', program_type => 'PLSQL_BLOCK', program_action => 'BEGIN delete from purge$Case; insert into purge$Case select CaseId from Case where deleteFlag = 1; delete from purge$Opt; insert into purge$Opt select OptId from OptimizationRun where CaseId in (select CaseId from purge$Case); delete from purge$Step; insert into purge$Step select StepId from OptimizationStep where OptId in (select OptId from purge$Opt); commit; END;', enabled => true, comments => 'Program to snapshot keys for purging'; ); dbms_scheduler.create_program (program_name => 'purge_case', program_type => 'PLSQL_BLOCK', program_action => 'BEGIN loop delete from Case where CaseId in (select Case from purge$Case) where rownum <= 50000; exit when sql%rowcount = 0; commit; end loop; commit; END;', enabled => true, comments => 'Program to purge the Case Table' ); -- repeat for each table being purged end; / 

Only program settings. Now we need to create a chain of tasks so that we can put them together.

 BEGIN dbms_scheduler.create_chain (chain_name => 'purge_case_chain'); END; / 

Now we take steps in the task chain using programs from:

 BEGIN dbms_scheduler.define_chain_step (chain_name => 'purge_case_chain', step_name => 'step_snapshot_purge_cases', program_name => 'snapshot_purge_cases' ); dbms_scheduler.define_chain_step (chain_name => 'purge_case_chain', step_name => 'step_purge_cases', program_name => 'purge_case' ); -- repeat for every table END; / 

Now we need to link the chain steps together. Works will be inflated, for example:

• Remove CaseIds , OptIds and StepIds to clear.
• Clear all OptimizationStep. dependent tables OptimizationStep.
• Clear all OptimizationRun. dependent tables OptimizationRun.
• Clear all Case. specific tables Case.
• Cleaning Case.

So then the code will look like this:

 begin dbms_scheduler.define_chain_rule (chain_name => 'purge_case_chain', condition => 'TRUE', action => 'START step_snapshot_purge_cases', rule_name => 'rule_snapshot_purge_cases' ); -- repeat for every table dependent on OptimizationStep dbms_scheduler.define_chain_rule (chain_name => 'purge_case_chain', condition => 'step_snapshot_purge_cases COMPLETED', action => 'START step_purge_TwoDDataY2', rule_name => 'rule_purge_TwoDDataY2' ); -- repeat for every table dependent on OptimizationRun dbms_scheduler.define_chain_rule (chain_name => 'purge_case_chain', condition => 'step_purge_TwoDDataY2 COMPLETED and step_purge_ThreeDDataZ COMPLETED and ... ', action => 'START step_purge_OnedDataX', rule_name => 'rule_purge_OnedDataX' ); -- repeat for every table dependent on Case dbms_scheduler.define_chain_rule (chain_name => 'purge_case_chain', condition => 'step_purge_OneDDataX COMPLETED and step_purge_TwoDDataY1 COMPLETED and ... ', action => 'START step_purge_Files', rule_name => 'rule_purge_Files' ); dbms_scheduler.define_chain_rule (chain_name => 'purge_case_chain', condition => 'step_purge_Files COMPLETED and step_purge_OptimizationRun COMPLETED and ... ', action => 'START step_purge_Case', rule_name => 'rule_purge_Case' ); -- add a rule to end the chain dbms_scheduler.define_chain_rule (chain_name => 'purge_case_chain', condition => 'step_purge_Case COMPLETED', action => 'END', rule_name => 'rule_purge_Case' ); end; / 

Enable task chain:

 BEGIN DBMS_SCHEDULER.enable ('purge_case_chain'); END; / 

You can start the chain manually:

 BEGIN DBMS_SCHEDULER.RUN_CHAIN (chain_name => 'chain_purge_case', job_name => 'chain_purge_case_run' ); END; / 

Or create a task to schedule it:

 BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'job_purge_case', job_type => 'CHAIN', job_action => 'chain_purge_case', repeat_interval => 'freq=daily', start_date => ... end_date => ... enabled => TRUE); END; /