Home > IT Blogs > Oracle Illustrated > Implement ROLLING Partition using INTERVAL partition (11g)
>>VIEW ALL POSTS  

Oracle Illustrated

«
»
Jun 25 2010   6:06AM GMT

Implement ROLLING Partition using INTERVAL partition (11g)



Posted by: Lakshmi Venkatesh
Uncategorized
Object Creation scripts                    
                       
1 – Table creation script                    
                       
drop table rolling_test                    
                       
                       
CREATE TABLE rolling_test                    
(ID number,                      
joining_date DATE)                    
partition by range (joining_date)                    
INTERVAL (numtodsinterval (1, ‘DAY’))                  
(                      
PARTITION jun25 VALUES LESS                  
THAN(TO_DATE(’26-06-2010′,’dd-mm-yyyy’)),                  
PARTITION jun26 VALUES LESS                  
THAN(TO_DATE(’27-06-2010′,’dd-mm-yyyy’)),                  
PARTITION jun27 VALUES LESS                  
THAN(TO_DATE(’28-06-2010′,’dd-mm-yyyy’))                  
);                      
                       
                       
CREATE INDEX rolling_test_idx ON rolling_test (joining_date) LOCAL;                
                       
2 – Insert script                    
                       
insert into rolling_test values (1, trunc(sysdate))                  
                       
insert into rolling_test values (2, trunc(sysdate) +1)                  
                       
insert into rolling_test values (3, trunc(sysdate) + 2)                  
                       
insert into rolling_test values (4, trunc(sysdate) + 3)                  
                       
insert into rolling_test values (5, trunc(sysdate) + 4)                  
                       
insert into rolling_test values (5, trunc(sysdate) + 7)                  
                       
insert into rolling_test values (5, trunc(sysdate) + 8)                  
                       
insert into rolling_test values (5, trunc(sysdate) + 9)                  
                       
3 – Procedure to maintain ROLLING PARTITION logic                
                       
                       
/* Author – Lakshmi V                    
   Description – Parameters accepted P_COUNT - minimum number of partitions to be left for use; P_TABLE_NAME – Table name for which rolling partition to be maintained     
  and out parameter P_ERROR_TEXT                  
                         - Fetches the count of partitions from ALL_Tab_partitions table for the table name.            
   - Check if the total count of partitions in ALL_TAB_PARTITIONS is more than the REQUIRED MINIMUM NUMBER of partitions to be retained in the table    
   - Fetches the partition name to be dropped from ALL_TAB_PARTITIONS for the table. Always the FIRST PARTITION POSITION needs to be dropped    
   - Checks whether the fetched partition is the LAST FORMALLY CREATED PARTITION in RANGE partition list. Note: Oracle does not allow us to drop this partition   
  (in this case JUN27 cannot be dropped)                
   - If it’s the last partition in formally created partition – then, set the INTERVAL partition to NULL; DROP the partition; Then SET the INTERVAL partition back to its  
  original value from ALL_PART_TABLES                
   - If it’s a normal partition – then, drop the partition */                
                       
CREATE OR REPLACE PROCEDURE ROLLING_PART(p_count IN NUMBER, p_table_name IN VARCHAR2, p_error_text OUT VARCHAR2) AS        
    v_count NUMBER := 0;                    
    v_drop_partition VARCHAR2(100);                  
    v_error_lvl   NUMBER;                    
    v_error_text  VARCHAR2(1000);                  
    v_count1      NUMBER;                    
    v_in_count    NUMBER;                    
    v_interval    VARCHAR2(100);                    
BEGIN                      
    v_error_lvl := 1;                    
    v_error_text := ‘While selecting count from all_tab_partitions’;                
    SELECT COUNT(1) INTO v_count FROM all_tab_partitions                
        WHERE table_name = UPPER(p_table_name);                
                       
    IF v_count > p_count THEN                    
    /* Check whether its the last formally created partion in range partition list */              
    SELECT COUNT(1) INTO v_count1 FROM all_tab_partitions                
        WHERE table_name = UPPER(p_table_name)                
               AND INTERVAL = ‘NO’;                  
                       
    SELECT partition_name INTO v_drop_partition FROM all_tab_partitions               
            WHERE table_name = UPPER(p_table_name)                
              AND partition_position = 1;                  
                       
        IF ( v_count1 = 1 ) THEN                     
            select interval INTO v_interval from ALL_PART_TABLES where table_name = p_table_name;            
          DBMS_OUTPUT.PUT_LINE(‘v_interval ‘ || v_interval);                     
                       
         /* If its the last partition in the RANGE PARTITION then, set the INTERVAL to null, drop the partition and reset the interval again */          
            EXECUTE IMMEDIATE ‘alter table ‘ || p_table_name || ‘ set INTERVAL()’;              
          DBMS_OUTPUT.PUT_LINE(‘PARTITION to be dropped ‘ || v_drop_partition);              
            EXECUTE IMMEDIATE ‘ALTER TABLE ‘ || p_table_name || ‘ DROP PARTITION ‘ || v_drop_partition;             
         EXECUTE IMMEDIATE ‘alter table ‘ || p_table_name || ‘ SET INTERVAL( ‘|| v_interval ||’)';            
        ELSE                      
        IF LENGTH(v_drop_partition) >0 THEN                  
            v_error_lvl := 3;                    
            v_error_text := ‘While dropping partition’;                  
            DBMS_OUTPUT.PUT_LINE(‘PARTITION to be dropped ‘ || v_drop_partition);              
            EXECUTE IMMEDIATE ‘ALTER TABLE ‘ || p_table_name || ‘ DROP PARTITION ‘ || v_drop_partition;             
        END IF;                      
       END IF;                      
    END IF;                      
    EXCEPTION                    
    WHEN NO_DATA_FOUND THEN                  
        DBMS_OUTPUT.PUT_LINE(‘NOTHING TO PARTITION’);                
    WHEN OTHERS THEN                    
        DBMS_OUTPUT.PUT_LINE(‘Error at level ‘|| v_error_lvl ||’ text -’|| v_error_text);              
        v_error_text := p_error_Text;                  
END;                      
/                      

*************************************************************************************************************************

PROOF OF CONCEPT

Step 1 – Create table, index, populate table & create procedure                                      
                                             
Step 2 – select * from rolling_test                                          
                                             
ID JOINING_DATE                                          
1 25/06/2010                                          
2 26/06/2010                                          
3 27/06/2010                                          
4 28/06/2010                                          
5 29/06/2010                                          
5 02/07/2010                                          
5 03/07/2010                                          
5 04/07/2010                                          
                                             
                                             
select * from all_tab_partitions where table_name = ‘ROLLING_TEST’                                      
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION                                        
Jun25 TO_DATE(‘ 2010-06-26 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
Jun26 TO_DATE(‘ 2010-06-27 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
Jun27 TO_DATE(‘ 2010-06-28 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3   (Note: Jun27 is the LAST PARTITION in the range partition list – rest of them are automatically created)                  
SYS_P112384 TO_DATE(‘ 2010-06-29 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 4                                        
SYS_P112385 TO_DATE(‘ 2010-06-30 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 5                                        
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 6                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 7                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 8                                        
                                             
Step 3 – Run the procedure to maintain only the LATEST 3 days of data (POC)                                    
                                             
                                             
declare                                            
    v_error_text VARCHAR2(1000);                                          
begin                                            
 rolling_part(3, ‘ROLLING_TEST’, v_error_text);                                        
end;                                             
/                                            
                                             
After running for FIRST TIME lets check ALL_TAB_PARTITIONS table                                      
                                             
select partition_name, high_value, partition_position from all_tab_partitions where table_name = ‘ROLLING_TEST’                              
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION   Note: Jun25 partition (first partition) is gone                              
JUN26 TO_DATE(‘ 2010-06-27 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
JUN27 TO_DATE(‘ 2010-06-28 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112384 TO_DATE(‘ 2010-06-29 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        
SYS_P112385 TO_DATE(‘ 2010-06-30 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 4                                        
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 5                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 6                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 7                                        
                                             
                                             
Lets run this for 3 more times then check ALL_TAB_PARTITIONS table                                      
                                             
declare                                            
    v_error_text VARCHAR2(1000);                                          
begin                                            
 rolling_part(3, ‘ROLLING_TEST’, v_error_text);                                        
end;                                             
/                                            
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION                                        
SYS_P112385 TO_DATE(‘ 2010-06-30 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 4                                        
                                             
                                             
Run 1 more time                                            
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION                                        
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        
                                             
                                             
Final time – now it should not drop anything ! – lets check                                        
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION   Note: It has not dropped any data                              
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        
                                             
                                             
Lets try to insert and drop again                                          
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION                                        
SYS_P112386 TO_DATE(‘ 2010-07-03 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        
SYS_P112389 TO_DATE(‘ 2010-07-06 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 4    -> newly inserted record; partition created                              
                                             
                                             
Run the script again                                            
                                             
PARTITION_NAME HIGH_VALUE PARTITION_POSITION                                        
SYS_P112387 TO_DATE(‘ 2010-07-04 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 1   SYS_P112386 partition (first partition in previous list is dropped)                          
SYS_P112388 TO_DATE(‘ 2010-07-05 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 2                                        
SYS_P112389 TO_DATE(‘ 2010-07-06 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’) 3                                        
  Bookmark and Share     Comment     RSS Feed     Email a friend

Comment on this Post

Leave a comment: