An application example of Oracle range partition
Overview
Range Range partition: data is divided into a fixed range as a partition based on the value of a field in the table.
Example:
Create a sales department sales data inventory table:
Create table sales_list
(order_id number (5)
Sales_name varchar2 (20)
Order_amount number (10)
Order_date date
)
Partition by range (order_date)
(partition sales_201401 values less than (to_date ('2014-02-01)
Partition sales_201402 values less than (to_date ('2014-03-01)
Partition sales_201403 values less than (to_date ('2014-04-01)
Partition sales_201404 values less than (to_date ('2014-05-01)
Partition sales_201405 values less than (to_date ('2014-06-01)
Partition sales_201406 values less than (to_date ('2014-07-01)
Partition sales_201407 values less than (to_date ('2014-08-01)
Partition sales_201408 values less than (to_date ('2014-09-01)
Partition sales_201409 values less than (to_date ('2014-10-01)
Partition sales_201410 values less than (to_date ('2014-11-01)
Partition sales_201411 values less than (to_date ('2014-12-01)
Partition sales_201412 values less than (to_date ('2015-01-01-01-01))
Partition sales_201501 values less than (to_date ('2015-02-01)
Partition sales_201502 values less than (to_date ('2015-03-01)
);
Created 14 partitions, 14 segments
SQL > col partition_name format A20
SQL > col segment_name format A20
SQL > select segment_name,partition_name from user_segments where segment_name='SALES_LIST'
SEGMENT_NAME PARTITION_NAME
--
SALES_LIST SALES_201401
SALES_LIST SALES_201402
SALES_LIST SALES_201403
SALES_LIST SALES_201404
SALES_LIST SALES_201405
SALES_LIST SALES_201406
SALES_LIST SALES_201407
SALES_LIST SALES_201408
SALES_LIST SALES_201409
SALES_LIST SALES_201410
SALES_LIST SALES_201411
SALES_LIST SALES_201412
SALES_LIST SALES_201501
SALES_LIST SALES_201502
14 rows selected.
The creation process is used to delete old partitions and add new ones:
CREATE OR REPLACE PROCEDURE drop_add_partition AS
V_part_name VARCHAR2 (100)
V_next_name VARCHAR2 (100)
V_over_time NUMBER
V_string VARCHAR2 (10)
V_date DATE
BEGIN
SELECT MIN (partition_name)
INTO v_part_name
FROM user_tab_partitions
WHERE table_name = 'SALES_LIST';-- find the current oldest partition
SELECT MAX (partition_name)
INTO v_next_name
FROM user_tab_partitions
WHERE table_name = 'SALES_LIST';-- find the latest partition currently
SELECT substr (to_char (add_months (to_date) (substr (v_next_name, 7,6))
'yyyy-mm')
1),
'yyyymmdd')
one,
6)
INTO v_next_name
FROM dual;-concatenate the name string of the next new partition
SELECT round (months_between (SYSDATE)
To_date (substr (v_part_name, 7,6), 'yyyy-mm')
INTO v_over_time
FROM dual
-- calculate the number of months between the current time and the oldest partition
V_string: = to_char (add_months (to_date (v_next_name, 'yyyy-mm'), 1)
'yyyy-mm')
V_date: = to_date (substr (v_string, 1,4) | |'-'| |
Substr (v_string, 6,2) | |'- 01'
'yyyy-mm-dd')
Stitching a new partition requires a specified time-to-point string at the time of creation
IF v_over_time > 12
THEN
EXECUTE IMMEDIATE 'alter table sales_list drop partition' | |
Delete the old partition
EXECUTE IMMEDIATE 'alter table sales_list add partition SALES_' | |
REPLACE (v_next_name,'-',') | |
'values less than (''| | v_date | |'')';-add a new partition
END IF
EXCEPTION
WHEN OTHERS THEN
-- exception handling
Dbms_output.put_line (to_char (SQLCODE))
Dbms_output.put_line (SQLERRM)
END
SQL > set serveroutput on
SQL > exec drop_add_partition
PL/SQL procedure successfully completed.
SQL > select segment_name,partition_name from user_segments where segment_name='SALES_LIST'
SEGMENT_NAME PARTITION_NAME
--
SALES_LIST SALES_201402
SALES_LIST SALES_201403
SALES_LIST SALES_201404
SALES_LIST SALES_201405
SALES_LIST SALES_201406
SALES_LIST SALES_201407
SALES_LIST SALES_201408
SALES_LIST SALES_201503
SALES_LIST SALES_201409
SALES_LIST SALES_201410
SALES_LIST SALES_201411
SALES_LIST SALES_201412
SALES_LIST SALES_201501
SALES_LIST SALES_201502
14 rows selected.
Create a background job:
Declare
Job number
Begin
Dbms_job.submit (job,'drop_add_partition;',sysdate,'sysdate+1');-- run once a day, and execute once when the job is created
Commit
End
