PARTITION ROTATION

Remote Database Administration, Monitoring and Support

  • SATS Remote DBA is dedicated to optimizing database uptime and performance, and to reducing the cost of IT ownership. Our Like Clockwork RDBA service saves you time, recourses, continuity, and helps you to ally IT tasks with the business goals of your organization. Many other companies chose SATS for our dependability, cost-effectiveness, and expert technological support. SATS RDBA advantages help our customers achieve costs savings of up to 75% while increasing the quality of DBA coverage.
  • SATS Remote DBA could be a one stop service provider for all this platforms:
  • Oracle, PostgreSQL, Hadoop, MS SQL, MySQL, Mongo DB

For specific inquiries please contact SATS RDBA Services or call 650 889-5533.

PARITION ROTATION

PROBLEM CASE

- Once a day application populates table which is used during the day. Next day the data are purged and replaced with the fresh set of rows. The table is relatively big - about 1GB of data. Application does truncate the table and repopulates it anew. Once in a while the database dead-locks because truncate gets stuck - locked by multiple selects from the web on top of on-going truncate.

SOLUTION

- While there are multiple solutions to this problem, including the best - application logic change to work with two different tables (odd an even, for example) below we look into the DBA solution - assuming that no changes to web application are possible. DBA solutions are also multiple, but we will look into the one which implements the partitioning with partition rotation in order to avoid the possibility of any locking.

Current table structure is


                    PRODUCTS_LIST1 (
                    PRODUCT_ID     VARCHAR2(30) NOT NULL,
                    <..other_columns_lst..>

                    STORY          VARCHAR2(4000))

MACRO STEPS

  1. Add to the table a new column LIST_DAY DATE DEFAULT SYSDATE
  2. Create a partitioned table instead of a non-partitioned, partition by range of LIST_DAY - one partition per day. Make partition for today and for tomorrow. So total two partitions only.
  3. Once day we add a new partition for tomorrow and drop partition for yesterday.

DETAILS

  1. Step. Add a new column to existing table: 
    
                            alter table PRODUCTS_LIST add LIST_DAY DATE default SYSDATE;

  2. Create future partitioned table (One time DDL only, and assuming that today is March 10 2009 - which is important as partition name convention is hard-coded)

    1. create a new table

      
                                    CREATE table PRODUCTS_LIST1 (
      
                                    PRODUCT_ID    VARCHAR2(30) NOT NULL,
      
                                    <..other_columns_lst..>
      
                                    LIST_DAY      DATE DEFAULT SYSDATE
      
                                    )
      
                                    partition by range ( list_day) 
      
                                    ( PARTITION  P_031009 VALUES LESS THAN (to_date('11-MAR-2009','DD-MON-YYYY')) tablespace DATA,
      
                                    PARTITION  P_031109 VALUES LESS THAN (to_date('12-MAR-2009','DD-MON-YYYY')) tablespace DATA
      
                                    );

    2. new partitioned table with the data from the old, existing table and rename: 

      
                                   insert into PRODUCTS_LIST1 select * from PRODUCTS_LIST;
      
                                   rename PRODUCTS_LIST to PRODUCTS_LIST_OLD;
      
                                   rename PRODUCTS_LIST1 to PRODUCTS_LIST;
      
                                   drop table PRODUCTS_LIST_OLD;
    3. reset grants (select, insert, etc.. on a new table) to any other users if needed.
    4. check the database for any kind of invalid objects appeared due to table drop and fix them.
    5. recreate original indexes - for simplicity we keep them global - though we might do local indexes if needed. Note doing local indexes may violate the requirement of NOT TOUCHING the application code, because of the CBO behavior. If doing local indexes - we must than add HINTs to most of the SQL statements - hence introduce the change to the application code.
      
                                    CREATE INDEX IND4 ON "PRODUCTS_LIST" ("PRODUCT_LINE" , "CODE" , "PRODUCT_ID" )  TABLESPACE "INDX";
      
                                    CREATE INDEX IND1 ON "PRODUCTS_LIST" (LOWER("PRODUCT_ID_NORM") ) TABLESPACE "INDX";
      
                                    create index AV_PRDLST_LIST_DAY on PRODUCTS_LIST (list_day) tablespace INDX;
    Here ends a one time DDL. Now we have a table operational for today, may want to rebuild table stats manualy and next 24 hours application will be happy camper.

  3. Develop and implement the logic to automatically rotate partitions - by dropping yesterday partition and adding tomorrow partition (empty). Note, that today partition is not touched, hence operations of the application are not effected.

    1. create a PLSQL procedure:
      
    create or replace procedure RebuildPRODUCTSLIST  (p_today in varchar2 default null)
      
    AS
      
    v_yesterday             varchar2(6);
      
    v_today                 date;
      
    v_tomorrow              varchar2(6);
      
    v_after_tomorrow        varchar2(11);
      
    v_errmsg                varchar2(512);

      

    BEGIN

      -- set the operational date: Note if passing non-default date it must be in MMDDYY format!)

      
    if p_today is null then
      
    v_today := sysdate;
      
    else
      
    select to_date(p_today,'MMDDYY')+1/24 into v_today from dual;
      
    end if;

      -- Populate table from the fresh soruce of data.

      
    insert into PRODUCTS_LIST ( 
      
    PRODUCT_ID, <..other_columns_lst..>, LIST_DAY)
      
    SELECT a.product_id, 
      
    <..other_columns_lst..>
      
    FROM PRODUCT_PRICING a;
      -- (select could be much more complex, of course, it is irrelevant for our case study)
      -- Now we do partition rotation and other relevant maintenance
      -- set variables to be used in partition naming and rotation
      
    select to_char(v_today-1,'MMDDYY'), to_char(v_today+1,'MMDDYY'), 
      
    to_char(v_today+2,'DD-MON-YYYY')
      
    into v_yesterday, v_tomorrow, v_after_tomorrow
      
    from dual;

      1. add a new after tomorrow partition to PRODUCTS_LIST - naming convention P_MMDDYY

        
                                           execute immediate 'alter table PRODUCTS_LIST add partition P_'||v_tomorrow||
        
                                           ' VALUES LESS THAN (to_date('||chr(39)||v_after_tomorrow||
        
                                           chr(39)||','||chr(39)||'DD-MON-YYYY'||chr(39)||'))'||' tablespace DATA';

      2. drop yesterdays partition:

        
    execute immediate 'alter table PRODUCTS_LIST drop partition P_'||v_yesterday;
        
    execute immediate 'alter tablespace DATA coalesce';
        
    commit;

      3. Rebuild relevant global indexes


        
    for li in ( select p.index_name from user_indexes p
        
    where p.table_name= 'PRODUCTS_LIST'
        
    and p.status = 'UNUSABLE') loop
        
    execute immediate 'alter index '||li.index_name||' rebuild tablespace INDX';
        
    commit;
        
    end loop;    
        
    EXCEPTION 
        
    WHEN OTHERS THEN     
        
    v_errmsg := SQLERRM;
        
    ROLLBACK; 
        
    print('REbuildPRODUCTSLIST FAILED with ERROR: '||v_errmsg);  
        

    END RebuildPRODUCTSLIST;
        
    show errors;
    2. Schedule and run this procedure via crontab once a day. (need a shell script and a driving sql script).

Questions/Comments? - feel free to contact Cerberus (which is me) on RDBA.net forums under Oracle DBA corner.