DROP PARTITION strategy using LIST-REF composite partitioning

Question

We have a large Oracle 11gR2 instance with some tables that have recently become an annoyance when deleting data. This database powers a large optimization cluster that takes input metadata from the DB and stores files (using 11g's SecureFiles) and 1D-4D output data used in visualizations. Reads/writes from the DB are really fast, and really things have been good until we needed to clean out data.

The software engineers were allowed to run free (read: me) and setup this system without any partitioning, naively assuming deletes would work well. Our table structure is below, and previously relied on ON DELETE CASCADE to remove data per Case where DeleteFlag = 1.

/* 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 */

The fan-out looks like: 1 case = 50 runs = 2000 steps = 5-35M rows across the data tables. Sum total of the data tables is ~500GB and just shy of 1B rows.

With some help on the big sister site I've arrived at a partitioning scheme likely to work for our setup (i.e. partition by CaseId). It uses LIST-REFERENCE composite partitioning, like so:

/* Metadata tables */
Case(CaseId, DeleteFlag, ...)

OptimizationRun(OptId, CaseId, ...)
    Partition By List (CaseId)
    ( partition default_case values (default) )
    /* Some PL/SQL is run when we add a Case to do:
     * alter table OptimizationRun split partition ...
     */

OptimizationStep(StepId, OptId, ...)
    Partition By Reference (Fk_OptId)

/* Data Tables */
OnedDataX(OptId, ...)
    Partition By Reference (Fk_OptId)
...
FourDDataZ(StepId, ...) /* packed representation of a 3D slice */
    Partition By Reference(Fk_StepId)

Which was painless to setup, improves a lot of the plans for our usual suspect queries, and gives us the ability to delete all of the data for a case with a single DROP PARTITION. This is where I have run into a problem: index rebuilding!

The current pseudo-PL/SQL to do the delete looks like:

for casedel in (select CaseId from Case where DeleteFlag = 1)
loop
    execute immediate 'alter table OptimizationRun'
        || ' drop partition case_' || casedel.CaseId
        || ' update indexes';
end loop;

delete from Case where DeleteFlag = 1;
commit;

We end up with errors about some of the indexes on the data tables that are unrelated to the foreign key used in the REFERENCE-partitioning.

Do I really have to create the ALTER INDEX blah REBUILD states for each of these ancillary indexes, or is there a smarter way?

Update: some actual DDL for your reading pleasure.

create table opt_case (
    case_id number not null,
    name varchar2(240) not null,
    delete_date date,
    constraint pk_optcase primary key (case_id) using index
);

create table opt_run (
    opt_run_id number not null,
    case_id number not null,
    constraint pk_opt_run primary key (opt_run_id) using index,
    constraint fk_opt_run_case
        foreign key (case_id) references opt_case (case_id)
)
partition by list (case_id)
(
    -- This is the catch-all partition. It is an error to insert into
    -- this partition; it will be unused. Instead this is the
    -- partition which is split when a new CASE_ID is added to
    -- OPTCASE.
    partition default_case values (default)
);

create index idx_fk_opt_run_case on opt_run (case_id);

create table opt_step (
    opt_step_id number not null,
    opt_run_id number not null,
    step number not null,
    step_value number(7,2),
    constraint pk_opt_step primary key (opt_step_id) using index enable,
    constraint fk_opt_step_opt_run
        foreign key (opt_run_id) references opt_run (opt_run_id)
)
-- This is the golden ticket from here on out for partitioning.
-- Attach to the foreign key on OPT_RUN_ID to enjoy the
-- parent table partitioning on CASE_ID.
partition by reference (fk_opt_step_opt_run);

create index idx_fk_opt_step_opt_run on opt_step (opt_run_id);

-- One of the bigger data tables for reference
create table twod_data (
    opt_step_id number not null, 
    spatial_coord_id number not null, 
    in_group number(1,0), 
    z_value number(3,0), 
    y_value number(5,3), 
    x_value number(5,1), 
    constraint pk_twod_data primary key (opt_step_id, spatial_coord_id), 
    constraint fk_twod_data_spat_coord
        foreign key (spatial_coord_id)
        references twod_spatial_cord (spatial_coord_id), 
    constraint fk_2d_data_opt_step
        foreign key (opt_step_id) references opt_step (opt_step_id)
)
partition by reference (fk_2d_data_opt_step);

create index idx_fk_2d_data_opt_step on twod_data (opt_step_id);

Indexes like idx_fk_2d_data_opt_step end up needing to be rebuilt after the DROP PARTITION clause.

The inserts into the data tables are uninteresting, simple INSERT INTO blah. The Case creation procedure now looks like:

-- Add the CASE
insert into opt_case (name) values (p_CaseName) returning case_id into v_CaseId;
commit; 

-- Add partition for the CASE
execute immediate 'alter table opt_run'
    || ' split partition default_case values (' || v_CaseId || ')'
    || ' into (partition case_' || v_CaseId ||', partition default_case)';

-- Because we split an empty partition, no index rebuild required

Answer 1

global indexes are made unusable when you drop or move a partition. This is because there are entries pointing to physical addresses in your partition, that are no longer valid. If you can, try to avoid global indexes. You can do this by making them local.

create index idx_fk_opt_run_case on opt_run (case_id) local;

Should do the trick. If you can use reference partitioning, you don't have to add the redundant data in the table because the relation is already defined in the partition definition.

an example:

RONR SQL>create table parent
(id number not null
, x varchar2(10)
)
partition by list (id) (
partition par_1 values (1),
partition par_2 values (2)
);  2    3    4    5    6    7    8  

Table created.

RONR SQL>create unique index pk_parent
on parent (id) local;
  2  
Index created.

RONR SQL>alter table parent
add CONSTRAINT pk_parent
PRIMARY KEY (id)
USING INDEX;  2    3    4  

Table altered.

RONR SQL>create table child
( parent number not null
, y      varchar2(10)
,
constraint fk_p_c 
foreign key (parent) references parent(id))
partition by reference (fk_p_c);  2    3    4    5    6    7  

Table created.

RONR SQL>insert into parent (id, x) values (1,'boe');
insert into parent (id, x) values (2,'oeps');
1 row created.

RONR SQL>

1 row created.

RONR SQL>select * from parent;

    ID X
---------- ----------
     1 boe
     2 oeps

RONR SQL>insert into child (parent, y) values (1,'ggg');

1 row created.

RONR SQL>insert into child (parent, y) values (1,'ggg');

1 row created.

RONR SQL>insert into child (parent, y) values (2,'ppp');

1 row created.

RONR SQL>select * from child;

    PARENT Y
---------- ----------
     1 ggg
     1 ggg
     2 ppp

RONR SQL>select table_name, partition_name from user_tab_partitions;

TABLE_NAME             PARTITION_NAME
------------------------------ ------------------------------
PARENT                 PAR_1
PARENT                 PAR_2
CHILD                  PAR_1
CHILD                  PAR_2

RONR SQL>select index_name, partition_name, status from user_ind_partitions;

INDEX_NAME             PARTITION_NAME             STATUS
------------------------------ ------------------------------ --------
PK_PARENT              PAR_1                  USABLE
PK_PARENT              PAR_2                  USABLE

RONR SQL>alter table parent drop partition par_1;

Table altered.

RONR SQL>select * from parent;

    ID X
---------- ----------
     2 oeps

RONR SQL>select * from child;

    PARENT Y
---------- ----------
     2 ppp

RONR SQL>select table_name, partition_name from user_tab_partitions;

TABLE_NAME             PARTITION_NAME
------------------------------ ------------------------------
PARENT                 PAR_2
CHILD                  PAR_2

RONR SQL>select index_name, partition_name, status from user_ind_partitions;

INDEX_NAME             PARTITION_NAME             STATUS
------------------------------ ------------------------------ --------
PK_PARENT              PAR_2                  USABLE

I see no unusable index. What I do see is that I can drop a parent partition and this cascades to the child partition. does this help?