Oracle Illustrated

Jun 25 2010   6:06AM GMT

Implement ROLLING Partition using INTERVAL partition (11g)



Posted by: Lakshmi Venkatesh
Tags:
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                                        

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: