Replacing truncate with partitions rotation

[gala22]

PARTITION ROTATION

PROBLEM CASE
- Once a day application pupulates table which is used during the day. Next day the data are purged and replaced with the fresh set of rows. The table is relitevly 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)
a)

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
);

b ) Populate 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;

c) reset grants (select, insert, etc.. on a new table) to any other users if needed.
d) check the database for any kind of invalid objects appeared due to table drop and fix them.
e) 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.
a) 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;

b ) 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 <URL SNIPPED> forums under Oracle DBA corner. You can also visit <URL SNIPPED>