Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
What kind of error? –  Vincent Malgrat Apr 28 '11 at 16:01
    
I think it was ORA-01502: index ‘%s.%s’ or partition of such index is in unusable state. This was not during the ALTER or the DELETE, but rather subsequent queries that ended up using the indexes. Note that none of these are partition local indexes. –  user7116 Apr 28 '11 at 16:27
    
The documentation states that the UPDATE INDEXES clause updates all indexes so they should not be in an unusable state afterwards. Have you tried with UPDATE GLOBAL INDEXES instead? Also are the unusable indexes on the OptimizationRun table or on its children tables? the UPDATE INDEX clause might work only for the main table (and not those that were affected by the drop partition cascade). –  Vincent Malgrat Apr 28 '11 at 16:51
    
These are on the Data tables (like FourDData has another index on it, not part of the partitioning scheme). –  user7116 Apr 28 '11 at 17:04
    
Could you list ddl for the tables, indexes and some inserts for data? This makes answering your questions a bit easier. Now we all have to invent the same wheel first. In my case, it would certainly be a red one .... –  ik_zelf Apr 29 '11 at 7:28
show 1 more comment

1 Answer 1

up vote 2 down vote accepted

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?

share|improve this answer
    
I have read that if I use a local index I will still have to rebuild each index if I do not delete all of the data in the table referencing those rows first. –  user7116 May 2 '11 at 14:47
    
I added an example to my answer. I does it help? –  ik_zelf May 2 '11 at 16:25
    
Interesting, I will see if I can make that work! –  user7116 May 2 '11 at 18:38
    
I believe the issue will be that I cannot create "local" indexes for the other indexable items on the tables. And THOSE indexes will be marked as unusable. –  user7116 May 2 '11 at 19:00
    
In the example I gave, I can create local index on parent(x), child(parent), child(y) without any problems, drop the par_1 partition from the parent and have all indexes usable, without rebuild. Did you try? Did you test? What error did you get? What definitions did you use? If you leave the 'local' part of create index, it's global and hurt by partition management. –  ik_zelf May 2 '11 at 20:05
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.