柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) oracle表分區(qū)

http://yzkb.51969.com/

1.范圍分區(qū)

比如：根據(jù)時(shí)間進(jìn)行分區(qū) 注意：分區(qū)語(yǔ)句中只能使用less，不能使用more，也不能使用less than (xxx) and more than (xxx)

1.1.創(chuàng)建分區(qū)

創(chuàng)建分區(qū) create table worker_202301( id varchar2(32) not null, name varchar2(200), technology varchar2(100), save_date date )

partition by range (save_date) ( partition worker202301 values less than (to_date(‘20230201’,‘YYYYMMDD’)), partition worker202302 values less than (to_date(‘20230301’,‘YYYYMMDD’)), partition worker202303 values less than (maxvalue) ); –添加數(shù)據(jù) insert into worker_202301 (id,name,technology,save_date) values (sys_guid(),‘ddd’,‘c++’,to_date(‘20221231’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (sys_guid(),‘a(chǎn)aa’,‘java’,to_date(‘20230128’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (sys_guid(),‘bbb’,‘c++’,to_date(‘20230228’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (sys_guid(),‘ccc’,‘c++’,to_date(‘20230328’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (sys_guid(),‘ddd’,‘c++’,to_date(‘20230428’,‘YYYYMMDD’));

1.2.查詢分區(qū)數(shù)據(jù)

查詢當(dāng)前表有哪些分區(qū) select * from user_tab_partitions where table_name = ‘WORKER_202301’; –查詢指定分區(qū)的數(shù)據(jù) select * from worker_202301 partition (worker202301); select * from worker_202301 partition (worker202302); select * from worker_202301 partition (worker202303);

1.3.添加分區(qū)

添加分區(qū) alter table worker_202301 add partition worker202305 values less than (to_date(‘20230501’,‘YYYYMMDD’));

直接添加分區(qū)會(huì)報(bào)錯(cuò)，因?yàn)樘砑拥姆謪^(qū)要比最大的分區(qū)要比最大的分區(qū)范圍要大。

1.3.1.解決方案（會(huì)刪除數(shù)據(jù)）

： 刪除最大的分區(qū)之后，再添加分區(qū) –刪除分區(qū) alter table worker_202301 drop partition worker202303; –添加分區(qū) alter table worker_202301 add partition worker202305 values less than (to_date(‘20230401’,‘YYYYMMDD’));

1.4.刪除分區(qū)

刪除分區(qū)會(huì)刪除該分區(qū)下的數(shù)據(jù) –刪除分區(qū) alter table worker_202301 drop partition worker202303;

2.列表分區(qū)

該分區(qū)方式適用于一個(gè)字段有多個(gè)固定的值，比如：產(chǎn)品、月份、類型等。

2.1.創(chuàng)建分區(qū)

創(chuàng)建分區(qū) create table worker_202301( id varchar2(32) not null, name varchar2(200), technology varchar2(100), save_date date )

partition by list (technology) ( partition technology_java values (‘java’), partition technology_python values (‘python’), partition technology_c values (‘c’) ); –添加數(shù)據(jù) insert into worker_202301 (id,name,technology,save_date) values (sys_guid(),‘ddd’,‘java’,to_date(‘20221231’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (sys_guid(),‘a(chǎn)aa’,‘java’,to_date(‘20230128’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (sys_guid(),‘bbb’,‘c’,to_date(‘20230228’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (sys_guid(),‘ccc’,‘python’,to_date(‘20230328’,‘YYYYMMDD’)); 如果添加的數(shù)據(jù)中，分區(qū)字段的內(nèi)容不在分區(qū)中，會(huì)報(bào)錯(cuò) insert into worker_202301 (id,name,technology,save_date) values (sys_guid(),‘ddd’,‘c++’,to_date(‘20230428’,‘YYYYMMDD’));

2.2.查詢分區(qū)數(shù)據(jù)

查詢當(dāng)前表有哪些分區(qū) select * from user_tab_partitions where table_name = ‘WORKER_202301’; –查詢指定分區(qū)的數(shù)據(jù) select * from worker_202301; select * from worker_202301 partition (technology_java); select * from worker_202301 partition (technology_python); select * from worker_202301 partition (technology_c);

2.3.刪除分區(qū)

刪除分區(qū)會(huì)刪除該分區(qū)下的數(shù)據(jù) alter table worker_202301 drop partition technology_c;

2.4.添加分區(qū)

alter table worker_202301 add partition technology_c values (‘c’);

2.5.當(dāng)數(shù)據(jù)量不大時(shí)，可以合并進(jìn)行分區(qū)

create table worker_202301( id varchar2(32) not null, name varchar2(200), technology varchar2(100), save_date date )

partition by list (technology) ( partition technology_java values (‘java’,’c’), partition technology_python values (‘python’) );

3.hash分區(qū)

范圍分區(qū)和列分區(qū)都是根據(jù)某個(gè)字段進(jìn)行分區(qū)，會(huì)存在分布不均勻的情況；Hash分區(qū)相比較前兩種分區(qū)會(huì)更加均勻。 注意事項(xiàng)：分區(qū)的鍵最好是連續(xù)的； 分區(qū)的鍵最好是2的n次方，對(duì)hash運(yùn)算更加友好； Hash分區(qū)是不能刪除的。

3.1. 創(chuàng)建分區(qū)

創(chuàng)建分區(qū) create table worker_202301( id varchar2(32) not null, name varchar2(200), technology varchar2(100), save_date date )

partition by hash (id) ( partition worker_id_1, partition worker_id_2, partition worker_id_3, partition worker_id_4 ); –添加數(shù)據(jù) insert into worker_202301 (id,name,technology,save_date) values (‘101’,‘ddd’,‘c++’,to_date(‘20221231’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘102’,‘a(chǎn)aa’,‘java’,to_date(‘20230128’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘103’,‘bbb’,‘c++’,to_date(‘20230228’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘104’,‘ccc’,‘c++’,to_date(‘20230328’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘105’,‘ddd’,‘c++’,to_date(‘20230428’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘106’,‘ddd’,‘c++’,to_date(‘20230428’,‘YYYYMMDD’));

3.2.查詢分區(qū)數(shù)據(jù)

select * from worker_202301;

select * from worker_202301 partition (worker_id_1); select * from worker_202301 partition (worker_id_2); select * from worker_202301 partition (worker_id_3); select * from worker_202301 partition (worker_id_4);

3.3.刪除分區(qū)–hash分區(qū)不能刪除

alter table worker_202301 drop partition worker_id_4;

3.4.添加分區(qū)

在添加分區(qū)時(shí)，所有數(shù)據(jù)都會(huì)重新計(jì)算hash值進(jìn)行分配。 alter table worker_202301 add partition worker_id_5;

4.范圍分區(qū)+列表分區(qū)的組合分區(qū)

4.1.創(chuàng)建分區(qū)

創(chuàng)建分區(qū) create table worker_202301( id varchar2(32) not null, name varchar2(200), technology varchar2(100), save_date date )

partition by range (save_date) subpartition by list (technology) ( partition worker1 values less than (to_date(‘20230201’,‘YYYYMMDD’)) ( subpartition technology_java_1 values (‘java’), subpartition technology_c_1 values (‘c’), subpartition technology_python_1 values (‘python’) ), partition worker2 values less than (to_date(‘20230301’,‘YYYYMMDD’)) ( subpartition technology_java_2 values (‘java’), subpartition technology_c_2 values (‘c’), subpartition technology_python_2 values (‘python’) ) ); –添加數(shù)據(jù) insert into worker_202301 (id,name,technology,save_date) values (‘101’,‘ddd’,‘c’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘102’,‘a(chǎn)aa’,‘java’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘103’,‘bbb’,‘java’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘104’,‘ccc’,‘python’,to_date(‘20230225’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘105’,‘ddd’,‘java’,to_date(‘20230225’,‘YYYYMMDD’));

4.2.查詢分區(qū)數(shù)據(jù)

查詢?nèi)繑?shù)據(jù) select * from worker_202301; –查詢分區(qū)數(shù)據(jù) select * from worker_202301 partition (worker1); select * from worker_202301 partition (worker2); –查詢子分區(qū)數(shù)據(jù) select * from worker_202301 subpartition (technology_java_1); select * from worker_202301 subpartition (technology_c_1); select * from worker_202301 subpartition (technology_python_1); select * from worker_202301 subpartition (technology_java_2); select * from worker_202301 subpartition (technology_c_2); select * from worker_202301 subpartition (technology_python_2);

4.3.刪除分區(qū)

刪除主分區(qū)，會(huì)刪除該分區(qū)下所有的數(shù)據(jù)；刪除子分區(qū)只會(huì)刪除子分區(qū)下的數(shù)據(jù) –刪除分區(qū) alter table worker_202301 drop partition worker3;

–刪除子分區(qū) alter table worker_202301 drop subpartition technology_python_3;

4.4.添加分區(qū)

在添加主分區(qū)時(shí)最好新增一個(gè)子分區(qū)，如果不新增子分區(qū)，oracle會(huì)自動(dòng)生成一個(gè)默認(rèn)的子分區(qū)；如果在給這個(gè)主分區(qū)添加子分區(qū)時(shí)會(huì)報(bào)錯(cuò)，也無(wú)法刪除這個(gè)默認(rèn)的子分區(qū)。 –添加主分區(qū) alter table worker_202301 add partition worker3 values less than (to_date(‘20230401’,‘YYYYMMDD’)) ( subpartition technology_java_3 values (‘java’), subpartition technology_c_3 values (‘c’) );

–添加子分區(qū) alter table worker_202301 modify partition worker3 add subpartition technology_python_3 values (‘python’);

5.范圍分區(qū)+hash分區(qū)的組合分區(qū)

因?yàn)閔ash分區(qū)的存在，可能分區(qū)會(huì)使數(shù)據(jù)不均勻。 當(dāng)hash分區(qū)作為主分區(qū)時(shí)，oracle中看不到子分區(qū)，但是能根據(jù)子分區(qū)進(jìn)行查詢。

5.1.創(chuàng)建分區(qū)

創(chuàng)建分區(qū) create table worker_202301( id varchar2(32) not null, name varchar2(200), technology varchar2(100), save_date date )

partition by range (save_date) subpartition by hash (id) ( partition worker1 values less than (to_date(‘20230201’,‘YYYYMMDD’)) ( subpartition worker_id_1, subpartition worker_id_2 ), partition worker2 values less than (to_date(‘20230301’,‘YYYYMMDD’)) ( subpartition worker_id_3, subpartition worker_id_4 ) ); –添加數(shù)據(jù) insert into worker_202301 (id,name,technology,save_date) values (‘101’,‘ddd’,‘c’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘102’,‘a(chǎn)aa’,‘java’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘103’,‘bbb’,‘java’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘104’,‘ccc’,‘python’,to_date(‘20230225’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘105’,‘ddd’,‘java’,to_date(‘20230225’,‘YYYYMMDD’));

5.2.查詢分區(qū)數(shù)據(jù)

查詢?nèi)繑?shù)據(jù) select * from worker_202301; –查詢分區(qū)數(shù)據(jù) select * from worker_202301 partition (worker1); select * from worker_202301 partition (worker2); –查詢子分區(qū)數(shù)據(jù) select * from worker_202301 subpartition (worker_id_1); select * from worker_202301 subpartition (worker_id_2); select * from worker_202301 subpartition (worker_id_3); select * from worker_202301 subpartition (worker_id_4);

5.3.刪除分區(qū)

刪除主分區(qū)，會(huì)刪除該分區(qū)下所有的數(shù)據(jù)；刪除子分區(qū)只會(huì)刪除子分區(qū)下的數(shù)據(jù) 1.當(dāng)hash分區(qū)為子分區(qū)時(shí)，不能刪除子分區(qū)；可以刪除主分區(qū) –刪除分區(qū) alter table worker_202301 drop partition worker3;

–刪除子分區(qū) alter table worker_202301 drop subpartition worker_id_7; 刪除子分區(qū)報(bào)錯(cuò)

2.當(dāng)hash分區(qū)為主分區(qū)時(shí)，不能刪除主分區(qū)；可以刪除子分區(qū)

5.4.添加分區(qū)

添加主分區(qū) alter table worker_202301 add partition worker3 values less than (to_date(‘20230401’,‘YYYYMMDD’)) ( subpartition worker_id_5, subpartition worker_id_6 );

–添加子分區(qū) alter table worker_202301 modify partition worker3 add subpartition worker_id_7;

6.列表分區(qū)+hash分區(qū)

因?yàn)閔ash分區(qū)的存在，可能會(huì)使數(shù)據(jù)分布不均勻。

6.1.創(chuàng)建分區(qū)

創(chuàng)建分區(qū) create table worker_202301( id varchar2(32) not null, name varchar2(200), technology varchar2(100), save_date date )

partition by list (technology) subpartition by hash (id) ( partition technology_java values (‘java’) ( subpartition worker_id_1, subpartition worker_id_2 ), partition technology_python values (‘python’) ( subpartition worker_id_3, subpartition worker_id_4 ) ); –添加數(shù)據(jù) insert into worker_202301 (id,name,technology,save_date) values (‘101’,‘ddd’,‘java’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘102’,‘a(chǎn)aa’,‘java’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘103’,‘bbb’,‘java’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘104’,‘ccc’,‘python’,to_date(‘20230225’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘105’,‘ddd’,‘java’,to_date(‘20230225’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘106’,‘eee’,‘java’,to_date(‘20230325’,‘YYYYMMDD’));

6.2.查詢分區(qū)數(shù)據(jù)

查詢?nèi)繑?shù)據(jù) select * from worker_202301; –查詢分區(qū)數(shù)據(jù) select * from worker_202301 partition (technology_java); select * from worker_202301 partition (technology_python); –查詢子分區(qū)數(shù)據(jù) select * from worker_202301 subpartition (worker_id_1); select * from worker_202301 subpartition (worker_id_2); select * from worker_202301 subpartition (worker_id_3); select * from worker_202301 subpartition (worker_id_4);

6.3.刪除分區(qū)

刪除主分區(qū)，會(huì)刪除該分區(qū)下所有的數(shù)據(jù)；刪除子分區(qū)只會(huì)刪除子分區(qū)下的數(shù)據(jù) 1.當(dāng)列表分區(qū)是主分區(qū)時(shí)，可以刪除主分區(qū) –刪除分區(qū) alter table worker_202301 drop partition technology_c; 2.當(dāng)hash分區(qū)是子分區(qū)時(shí)，刪除子分區(qū)會(huì)報(bào)錯(cuò) –刪除子分區(qū) alter table worker_202301 drop subpartition worker_id_6;

3.當(dāng)列表分區(qū)是子分區(qū)，hash分區(qū)是主分區(qū)時(shí)；可以刪除子分區(qū)，不能刪除主分區(qū)。

6.4.添加分區(qū)

添加主分區(qū) alter table worker_202301 add partition technology_c values (‘c’) ( subpartition worker_id_5 );

–添加子分區(qū) alter table worker_202301 modify partition technology_c add subpartition worker_id_6;

7.對(duì)已有表進(jìn)行分區(qū)

在對(duì)已有表進(jìn)行分區(qū)時(shí)，由于未分區(qū)表不能直接轉(zhuǎn)換為分區(qū)表；所以需要重建一個(gè)相同表結(jié)構(gòu)且有分區(qū)的表將原表數(shù)據(jù)轉(zhuǎn)移到分區(qū)表中，然后再將原表刪除，將分區(qū)表表名修改為原表的表名。 注意：分區(qū)表最好是分區(qū)好之后再進(jìn)行數(shù)據(jù)的轉(zhuǎn)移。

7.1.創(chuàng)建一張沒(méi)有分區(qū)的表并添加數(shù)據(jù)

創(chuàng)建一張普通表插入數(shù)據(jù) create table worker_202301( id varchar2(32) not null, name varchar2(200), technology varchar2(100), save_date date ); –添加數(shù)據(jù) insert into worker_202301 (id,name,technology,save_date) values (‘101’,‘ddd’,‘c’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘102’,‘a(chǎn)aa’,‘java’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘103’,‘bbb’,‘java’,to_date(‘20230125’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘104’,‘ccc’,‘python’,to_date(‘20230225’,‘YYYYMMDD’));

insert into worker_202301 (id,name,technology,save_date) values (‘105’,‘ddd’,‘java’,to_date(‘20230225’,‘YYYYMMDD’));

7.2.創(chuàng)建一張相同表結(jié)構(gòu)的表，并添加分區(qū)

創(chuàng)建一張相同表結(jié)構(gòu)的分區(qū)表 create table worker_202301tmp( id varchar2(32) not null, name varchar2(200), technology varchar2(100), save_date date )

partition by range (save_date) ( partition worker01 values less than (to_date(‘20230201’,‘YYYYMMDD’)), partition worker02 values less than (to_date(‘20230301’,‘YYYYMMDD’)) );

7.3.將沒(méi)有分區(qū)的表數(shù)據(jù)插入到有分區(qū)的表中

將沒(méi)有分區(qū)的表中數(shù)據(jù)插入到有分區(qū)的表中 insert into worker_202301tmp (select * from worker_202301);

7.4.檢查分區(qū)表中的分區(qū)是否生效

檢查分區(qū)是否生效 select * from worker_202301tmp; –查詢當(dāng)前表的分區(qū) select * from user_tab_partitions where table_name = ‘WORKER_202301TMP’; –根據(jù)分區(qū)查詢數(shù)據(jù) select * from worker_202301tmp partition (worker01); select * from worker_202301tmp partition (worker02);

7.5.刪除普通表，修改分區(qū)表名

刪除原表，將分區(qū)表表名進(jìn)行修改 drop table worker_202301; rename worker_202301tmp to worker_202301;

7.6.將一個(gè)分區(qū)拆分成多個(gè)分區(qū)

7.6.1.錯(cuò)誤示范

截?cái)喾謪^(qū)的at關(guān)鍵子不能寫字段的名稱。 alter table worker_202301 split partition worker01 at (id) into (partition worker02,partition worker03);

7.6.2.正確示范

1.范圍分區(qū)，一個(gè)分區(qū)拆分成多個(gè)分區(qū)示例 alter table worker_202301 split partition worker01 at (to_date(‘20230101’,‘YYYYMMDD’)) into (partition worker03,partition worker04); 2.列表分區(qū)，一個(gè)分區(qū)拆分成多個(gè)分區(qū)示例

alter table worker_202301 split partition technology_java into (partition technology_c values (‘c’),partition technology_java); 或 alter table worker_202301 split partition technology_java into (partition technology_java values (‘java’),partition technology_c);

3.hash分區(qū)，一個(gè)分區(qū)拆分成多個(gè)分區(qū)的示例 一個(gè)hash分區(qū)不能被再次拆分

7.7.截?cái)喾謪^(qū)

截?cái)喾謪^(qū)是指刪除某個(gè)分區(qū)的中的數(shù)據(jù)，不會(huì)刪除分區(qū)。也不會(huì)刪除其他分區(qū)。 alter table worker_202301 truncate partition worker03;

7.8.合并分區(qū)

合并分區(qū)是將多個(gè)分區(qū)的數(shù)據(jù)合并到一起，其他分區(qū)的數(shù)據(jù)會(huì)被刪除。 –將worker02和worker03分區(qū)中的數(shù)據(jù)合并到worker03分區(qū)中，同時(shí)刪除worker02分區(qū) alter table worker_202301 merge partitions worker02,worker03 into partition worker03; 注意事項(xiàng)：

7.8.1.范圍分區(qū)

1.大范圍的分區(qū)要寫在后面，小范圍的分區(qū)要放在前面（worker02放在前，worker03放在后）。下圖就是一個(gè)錯(cuò)誤的示范，應(yīng)該寫成 alter table worker_202301 merge partitions worker02,worker03 into partition worker02;

2.合并分區(qū)時(shí)，應(yīng)將多個(gè)分區(qū)合并到范圍最大的分區(qū)中 錯(cuò)誤示范：

應(yīng)該將worker02,worker03合并到worker03中，因?yàn)閣orker02和worker03中worker03分區(qū)的范圍最大。

7.8.2.列表分區(qū)

1.列表分區(qū)沒(méi)有范圍大小的限制，會(huì)將多個(gè)分區(qū)合并到一個(gè)分區(qū)中；如下圖，此時(shí)不能再添加單獨(dú)的java或者c的分區(qū)

alter table worker_202301 merge partitions technology_java ,technology_c into partition technology_java;

7.8.3.不能合并子分區(qū)

8.根據(jù)日期自動(dòng)分區(qū)

注意：分區(qū)字段類型必須為date類型，使用自動(dòng)分區(qū)時(shí)分區(qū)的名字是自動(dòng)生成的。 create table worker_202301( id varchar2(32) not null, name varchar2(200), technology varchar2(100), save_date date )

partition by list (save_date) interval (numtoyminterval(1,‘month’))( partition worker_20230903 values less than (to_date(‘20231001’,‘YYYYMMDD’)) );

interval (numtoyminterval(1,‘year’))–按年分區(qū) interval (numtoyminterval(1,‘month’))–按月分區(qū) interval (numtoyminterval(1,‘day’))–按天分區(qū)

9.更改已有分區(qū)的名稱

修改主分區(qū)名稱 alter table worker_202301 rename partition worker03 to technology_go; 修改子分區(qū)名稱 alter table worker_202301 rename subpartition technology03_c to technology033_c;

柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) oracle表分區(qū)

http://yzkb.51969.com/

推薦鏈接