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

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

分區(qū)的優(yōu)點：

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)性能。

分區(qū)的缺點：

1.已經(jīng)存在的表不能直接轉(zhuǎn)化為分區(qū)表。不過 Oracle 提供了在線重定義表的功能。

分區(qū)的特殊性：

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

使用分區(qū)的場景：

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

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

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

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

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

6.經(jīng)常執(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); 

提示報錯：

經(jīng)過分析：提示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ū)內(nèi)插入數(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轉(zhuǎn)換。原表取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ū)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論