DROP PARTITION strategy using LIST-REF composite partitioning

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

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?

StackExchange Database Administrators Q#2427, answer score: 2