For specific inquiries please contact SATS RDBA Services or call 650 288-1701.
- 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.
- 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..>
alter table PRODUCTS_LIST add LIST_DAY DATE default SYSDATE;
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)
create a new table
CREATE table PRODUCTS_LIST1 (
PRODUCT_ID VARCHAR2(30) NOT NULL,
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
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;
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;
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.
create or replace procedure RebuildPRODUCTSLIST (p_today in varchar2 default null)
-- 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;
select to_date(p_today,'MMDDYY')+1/24 into v_today from dual;
-- Populate table from the fresh soruce of data.
-- (select could be much more complex, of course, it is irrelevant for our case study)
insert into PRODUCTS_LIST (
PRODUCT_ID, <..other_columns_lst..>, LIST_DAY)
FROM PRODUCT_PRICING a;
select to_char(v_today-1,'MMDDYY'), to_char(v_today+1,'MMDDYY'),
into v_yesterday, v_tomorrow, v_after_tomorrow
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';
drop yesterdays partition:
execute immediate 'alter table PRODUCTS_LIST drop partition P_'||v_yesterday;
execute immediate 'alter tablespace DATA coalesce';
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';
WHEN OTHERS THEN
v_errmsg := SQLERRM;
print('REbuildPRODUCTSLIST FAILED with ERROR: '||v_errmsg);
Questions/Comments? - feel free to contact Cerberus (which is me) on
RDBA.net forums under Oracle DBA corner.