您好,登錄后才能下訂單哦!
概述
范圍分區(qū)(Range partition):就是根據(jù)表的某個字段的值,以固定的一個范圍作為一個分區(qū)來劃分?jǐn)?shù)據(jù)。
實例:
創(chuàng)建銷售部門銷售數(shù)據(jù)清單表:
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','yyyy-mm-dd')),
partition sales_201402 values less than(to_date('2014-03-01','yyyy-mm-dd')),
partition sales_201403 values less than(to_date('2014-04-01','yyyy-mm-dd')),
partition sales_201404 values less than(to_date('2014-05-01','yyyy-mm-dd')),
partition sales_201405 values less than(to_date('2014-06-01','yyyy-mm-dd')),
partition sales_201406 values less than(to_date('2014-07-01','yyyy-mm-dd')),
partition sales_201407 values less than(to_date('2014-08-01','yyyy-mm-dd')),
partition sales_201408 values less than(to_date('2014-09-01','yyyy-mm-dd')),
partition sales_201409 values less than(to_date('2014-10-01','yyyy-mm-dd')),
partition sales_201410 values less than(to_date('2014-11-01','yyyy-mm-dd')),
partition sales_201411 values less than(to_date('2014-12-01','yyyy-mm-dd')),
partition sales_201412 values less than(to_date('2015-01-01','yyyy-mm-dd')),
partition sales_201501 values less than(to_date('2015-02-01','yyyy-mm-dd')),
partition sales_201502 values less than(to_date('2015-03-01','yyyy-mm-dd'))
);
創(chuàng)建了14個分區(qū),14個段
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.
創(chuàng)建過程用于刪除舊的分區(qū)并添加新的分區(qū):
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'; --找到當(dāng)前最早的分區(qū)
SELECT MAX(partition_name)
INTO v_next_name
FROM user_tab_partitions
WHERE table_name = 'SALES_LIST'; --找到當(dāng)前最晚的分區(qū)
SELECT substr(to_char(add_months(to_date(substr(v_next_name, 7, 6),
'yyyy-mm'),
1),
'yyyymmdd'),
1,
6)
INTO v_next_name
FROM dual; --拼接下一個新的分區(qū)的名稱串
SELECT round(months_between(SYSDATE,
to_date(substr(v_part_name, 7, 6), 'yyyy-mm')))
INTO v_over_time
FROM dual;
--計算當(dāng)前時間和最早分區(qū)的之間間隔的月數(shù)
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');
--拼接新的分區(qū)在創(chuàng)建的時候需要指定的截至?xí)r間點的串
IF v_over_time > 12
THEN
EXECUTE IMMEDIATE 'alter table sales_list drop partition ' ||
v_part_name; --刪除舊的分區(qū)
EXECUTE IMMEDIATE 'alter table sales_list add partition SALES_' ||
REPLACE(v_next_name, '-', '') ||
' values less than (''' || v_date || ''')'; --添加新的分區(qū)
END IF;
EXCEPTION
WHEN OTHERS THEN
--異常處理
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.
創(chuàng)建后臺作業(yè):
declare
job number;
begin
dbms_job.submit(job,'drop_add_partition;',sysdate,'sysdate+1'); --每天運行一次,創(chuàng)建這個job的時候就要執(zhí)行一次
commit;
end;
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。