oracle?range時間范圍自動分區(qū)的創(chuàng)建方式

oracle range時間范圍自動分區(qū)

oracle11g之前的版本，分區(qū)大概分為4種：范圍分區(qū)：range 、列表分區(qū)：list、哈希分區(qū)：hash、復合分區(qū)：range+list or hash)；

1.改善查詢性能:對分區(qū)對象的查詢可以僅搜索自己關心的分區(qū),提高檢索速度。

2.增強可用性:如果表的某個分區(qū)出現(xiàn)故障,表在其他分區(qū)的數(shù)據(jù)仍然可用;

3.維護方便:如果表的某個分區(qū)出現(xiàn)故障,需要修復數(shù)據(jù),只修復該分區(qū)即可;可單獨備份某分區(qū)；

4.均衡i/o:可以把不同的分區(qū)映射到磁盤以平衡i/o,改善整個系統(tǒng)性能。

1.已經存在的表不能直接轉化為分區(qū)表。不過 oracle 提供了在線重定義表的功能。

1.含有 long、longraw 數(shù)據(jù)類型的表不能進行分區(qū)，一般分區(qū)類型為varchar,varchar2,number,date
2.每個表的分區(qū)或子分區(qū)數(shù)的總數(shù)不能超過 1023 個。

1.單表過大，當表大小超過2g，或對于oltp（on-line transaction processing聯(lián)機事務處理過程(oltp)，也稱為面向交易的處理過程，其基本特征是前臺接收的用戶數(shù)據(jù)可以立即傳送到計算中心進行處理，并在很短的時間內給出處理結果，是對用戶操作快速響應的方式之一）系統(tǒng)，表的記錄超過1000萬。

2.歷史數(shù)據(jù)需要分離出來，新的數(shù)據(jù)被加到新的分區(qū)中。

3.表數(shù)據(jù)被使用時特征明顯，例如當年，整月之類。

4.基于這類表大部分的查詢都是只查詢其中一部分數(shù)據(jù)。

5.按時間段刪除成批的表數(shù)據(jù)。

6.經常執(zhí)行并行查詢的表

7.對其中一部分分區(qū)表數(shù)據(jù)可用性要求高

1.創(chuàng)建測試表. 分區(qū)字段cdat number()

---建表
create table fq_test 
(contract_no varchar2(20),
loan_st varchar2(8),
amt  decimal(20,6)
);
--創(chuàng)建自增序列contract_no
create sequence seq_fqtest_con 
minvalue 1 
maxvalue 999999   
increment by 1    
start with 1;
--創(chuàng)建觸發(fā)器
create or replace trigger trig_fqtest_con
before insert on fq_test /*觸發(fā)條件：當表fq_test執(zhí)行插入操作時觸發(fā)此觸發(fā)器*/
for each row   /*對每一行都檢測是否觸發(fā)*/
begin
/*觸發(fā)后執(zhí)行的動作，在此是取得序列seq_fqtest_con的下一個值插入到表bc_es_ik_hot_word中的id字段中*/
select seq_fqtest_con.nextval into :new.contract_no from dual;
end;

2.執(zhí)行增加分區(qū)語句

alter table fq_test add partition p1 values less than (20220131); 

提示報錯：

經過分析：提示ora-14501: object is not partitioned對象未分區(qū)。

3.創(chuàng)建新的分區(qū)測試空表

--drop  table fq_test_partition;
create table fq_test_partition
(contract_no varchar2(20),
loan_st varchar2(8),
amt  decimal(20,6)
)
partition by range (loan_st)
(
partition p1 values less than (20220131)
);

報錯：

分析：oracle未啟用partitioning功能，查詢當前版本是否支持

select value from v$option where upper(parameter)= 'partitioning';

值為false則不支持。注意標準版是不支持分區(qū)操作的，企業(yè)版才支持。

----------使用企業(yè)版oracle進行測試分區(qū)以及分區(qū)自增長---------

1.使用固定的分區(qū)，每月新增數(shù)據(jù)需要手工增加對應分區(qū)

1.1創(chuàng)建相應固定分區(qū)表

----創(chuàng)建分區(qū)表
drop table    rp_report_test_xy;
-- create table
create table rp_report_test_xy
(
contract_code           varchar2(300),
cdat                     number(8)
)
partition by range(cdat)-- interval (numtoyminterval(1,'month'))
(
partition p1 values less than('20150101'),
partition p2 values less than('20220201'),
partition p3 values less than('20220501')
);

1.2 將原有表中2022年底數(shù)據(jù)插入（原表有202201-202205的月底數(shù)據(jù)）

報錯原因：由于原表有20220531的數(shù)據(jù)不在已有的分區(qū)內插入數(shù)據(jù)報錯，將數(shù)據(jù)限制在20220501之前重新插入。

---查詢現(xiàn)有分區(qū)
select * from user_tab_partitions where table_name='rp_report_test_xy'

---查詢插入的數(shù)據(jù)具體分區(qū)
select distinct cdat from rp_report_test_xy  partition(p2);---20220131
select distinct cdat from rp_report_test_xy  partition(p3)---20220430,20220228,20220331

1.3 新增一份分區(qū)

alter table rp_report_test_xy add partition p4 values less than('20220531');

1.4 插入大于20220501的數(shù)據(jù)實際就是原表中cdat是20220531的數(shù)據(jù)

1.5 刪除新增的分區(qū)，再次新建分區(qū)日期是20220601

--刪除分區(qū)
alter table rp_report_test_xy drop partition p4;
---重新插入分區(qū)
alter table rp_report_test_xy add partition p4 values less than('20220601');

1.6 再次插入大于20220501的數(shù)據(jù)實際就是原表中cdat是20220531的數(shù)據(jù)

--查詢當前新分區(qū)數(shù)據(jù)
select distinct cdat from rp_report_test_xy  partition(p4)---20220531

2.自增長分區(qū)

2.1創(chuàng)建一張表，分區(qū)字段為cdat,字段類型為number;

原有未分區(qū)的表數(shù)據(jù)基數(shù)為3000多萬

執(zhí)行報錯：

將創(chuàng)建表語句中cdat由number改為date則創(chuàng)建成功。

2.2使用insert語句將原未分區(qū)表數(shù)據(jù)插入分區(qū)測試表。

---查詢現(xiàn)有分區(qū)
select * from user_tab_partitions where table_name='rp_report_test_xy'

插入數(shù)據(jù)：注意原number在插入時要to_date轉換。原表取cdat是2022年的所有數(shù)據(jù)插入。

--查看當前分區(qū)的數(shù)據(jù)
select * from  rp_report_test_xy partition(sys_p76);

發(fā)現(xiàn)20220131的數(shù)據(jù)劃分到了20220201的區(qū)間，20220228劃分到了20220301的區(qū)間。

--查看當前分區(qū)的數(shù)據(jù)
select * from  rp_report_test_xy partition(sys_p76);

結果 ：cdat是20221130的數(shù)據(jù)。

--刪除某分區(qū)
alter table  rp_report_test_xy drop partition sys_p76;

關于oracle分區(qū)（range時間范圍自動分區(qū)）的文章就介紹至此，更多相關oracle分區(qū)內容請搜索碩編程以前的文章，希望以后支持碩編程！