一、實驗目的
采用List-Range分區,對主分區指定表空間或者對子分區指定表空的不同情況,測試如下內容:
1、對List主分區不指定表空間,對Range子分區指定表空間,數據實際存儲在哪個表空間;追加List主分區不指定Range子分區和指定Range子分區時,Oracle如何創建相關子分區;
2、對List主分區指定表空間,對Range子分區不指定表空間,數據實際存儲在哪個表空間;追加List主分區不指定Range子分區和指定Range子分區時,Oracle如何創建相關子分區;
二、實驗環境
操作系統:Window 7 旗艦版 x64
Cpu:Intel i5-2520M 2.50GHz X 2
內存:10G
Oracle版本:Release 11.2.0.1.0三、List指定表空間測試
3.1、創建表空間
CREATE TABLESPACE "TS_3512860010" DATAFILE 'D:\APP\ORADATA\ORCL\TS_3512860010.dbf' SIZE 50M AUTOEXTEND ON NEXT16K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENTAUTO;
CREATE TABLESPACE "TS_3512860005" DATAFILE 'D:\APP\ORADATA\ORCL\TS_3512860005.dbf' SIZE 50M AUTOEXTEND ON NEXT16K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENTAUTO;
CREATE TABLESPACE "TS_3512834993" DATAFILE 'D:\APP\ORADATA\ORCL\TS_3512834993.dbf' SIZE 50M AUTOEXTEND ON NEXT16K MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENTAUTO;
3.2、兩個List兩個Range測試
3.2.1、 創建分區
drop table LST_RNG_LIST cascade constraints; /*==============================================================*/ /* Table: LST_RNG_LIST */ /*==============================================================*/ create table LST_RNG_LIST ( AUTO_ID VARCHAR2(36) not null, SALE_NO VARCHAR2(36) not null, POS_CODE VARCHAR2(10), POS_NAME VARCHAR2(30), TOTAL_AMOUNT NUMBER(18,2), SALE_DATE DATE, REMARK VARCHAR2(500), constraint PK_LST_RNG_LISTprimary key (AUTO_ID) ) partition by list (POS_CODE) subpartition by range (SALE_DATE) subpartition template ( subpartition SP_20150726 values less than (TO_DATE('2015-07-26','YYYY-MM-DD')), subpartition SP_20150802 values less than (TO_DATE('2015-08-02','YYYY-MM-DD')) ) ( partition P_3512860010 values ('3512860010') tablespace TS_3512860010, partition P_3512860005 values ('3512860005') tablespace TS_3512860005 ); comment on column LST_RNG_LIST.AUTO_ID is '自動編號'; comment on column LST_RNG_LIST.SALE_NO is '銷售單號'; comment on column LST_RNG_LIST.POS_CODE is '商戶代碼'; comment on column LST_RNG_LIST.POS_NAME is '商戶名稱'; comment on column LST_RNG_LIST.TOTAL_AMOUNTis '銷售總額'; comment on column LST_RNG_LIST.SALE_DATE is '銷售日期'; comment on column LST_RNG_LIST.REMARK is '備注';
3.2.2、查看分區
查看主分組
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_LIST';
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_LIST';
Range子分區所屬表空間自動歸入List分區所屬表空間
3.2.3、插入數據
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507240001','3512860010','全味食品商貿有限公司','100.78',TO_DATE('2015-07-24','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507250001','3512860010','全味食品商貿有限公司','100.78',TO_DATE('2015-07-25','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507260001','3512860010','全味食品商貿有限公司','1132.23',TO_DATE('2015-07-26','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507290001','3512860010','全味食品商貿有限公司','1132.23',TO_DATE('2015-07-29','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201508010001','3512860010','全味食品商貿有限公司','1132.23',TO_DATE('2015-08-01','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507240001','3512860005','新干線貿易有限公司','100.78',TO_DATE('2015-07-24','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507250001','3512860005','新干線貿易有限公司','100.78',TO_DATE('2015-07-25','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507260001','3512860005','新干線貿易有限公司','1132.23',TO_DATE('2015-07-26','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507290001','3512860005','新干線貿易有限公司','1132.23',TO_DATE('2015-07-29','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201508010001','3512860005','新干線貿易有限公司','1132.23',TO_DATE('2015-08-01','YYYY-MM-DD'),NULL);
commit;
3.2.4、查看數據
查看分區數據
select * from LST_RNG_LIST partition(P_3512860010); select * from LST_RNG_LIST partition(P_3512860005); select * from LST_RNG_LIST subpartition(P_3512860010_SP_20150726); select * from LST_RNG_LIST subpartition(P_3512834993_SP_20150802);
收集分區統計信息
begin
dbms_stats.gather_table_stats(ownname=>'WKOD_VERIFY',granularity =>'all',tabname=>'LST_RNG_LIST',cascade=>true);
end;
查看主分組
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_LIST';num_rows=5,LST_PNG_LIST表分別在兩個表空間中有5條數據
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_LIST';3.2.5、追加分區
方式一:追加主分區
alter table LST_RNG_LIST add partitionP_3512834993 values ('3512834993') tablespace TS_3512834993
查看分區
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_LIST';
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_LIST';默認按照表創建時子分區的的分區規則,自動生成兩個子分區(紅色框內)
刪除添加的List分區
alter table LST_RNG_LIST drop partitionP_3512834993;
對應的子分區會自動被drop掉。
方式二:追加主分區及其子分區
alter table LST_RNG_LIST add partition P_3512834993 values ('3512834993') tablespace TS_3512834993
(subpartition P_3512834993_SP_20150726 values less than (TO_DATE('2015-07-26','YYYY-MM-DD')))
查看分區
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_LIST';
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_LIST';
按照設定的子分區創建range子分區
3.3、兩個List一個Range測試
3.3.1、創建分區
drop table LST_RNG_LIST cascade constraints; /*==============================================================*/ /* Table: LST_RNG_LIST */ /*==============================================================*/ create table LST_RNG_LIST ( AUTO_ID VARCHAR2(36) not null, SALE_NO VARCHAR2(36) not null, POS_CODE VARCHAR2(10), POS_NAME VARCHAR2(30), TOTAL_AMOUNT NUMBER(18,2), SALE_DATE DATE, REMARK VARCHAR2(500), constraint PK_LST_RNG_LISTprimary key (AUTO_ID) ) partition by list (POS_CODE) subpartition by range (SALE_DATE) subpartition template ( subpartition SP_20150726 values less than (TO_DATE('2015-07-26','YYYY-MM-DD')) ) ( partition P_3512860010 values ('3512860010') tablespace TS_3512860010, partition P_3512860005 values ('3512860005') tablespace TS_3512860005 ); comment on column LST_RNG_LIST.AUTO_ID is '自動編號'; comment on column LST_RNG_LIST.SALE_NO is '銷售單號'; comment on column LST_RNG_LIST.POS_CODE is '商戶代碼'; comment on column LST_RNG_LIST.POS_NAME is '商戶名稱'; comment on column LST_RNG_LIST.TOTAL_AMOUNTis '銷售總額'; comment on column LST_RNG_LIST.SALE_DATE is '銷售日期'; comment on column LST_RNG_LIST.REMARK is '備注';
3.3.2、查看分區
查看主分組
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_LIST';
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_LIST';
Range子分區所屬表空間自動歸入List分區所屬表空間3.3.3、插入數據
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507240001','3512860010','全味食品商貿有限公司','100.78',TO_DATE('2015-07-24','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507250001','3512860010','全味食品商貿有限公司','100.78',TO_DATE('2015-07-25','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507240001','3512860005','新干線貿易有限公司','100.78',TO_DATE('2015-07-24','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507250001','3512860005','新干線貿易有限公司','100.78',TO_DATE('2015-07-25','YYYY-MM-DD'),NULL);
commit;
3.3.4、查看數據
查看分區數據
select * from LST_RNG_LIST partition(P_3512860010);
select * from LST_RNG_LIST partition(P_3512860005);
select * from LST_RNG_LIST subpartition(P_3512860010_SP_20150726);
--select * from LST_RNG_LIST subpartition(P_3512834993_SP_20150802);
收集分區統計信息
begin
dbms_stats.gather_table_stats(ownname=>'WKOD_VERIFY',granularity =>'all',tabname=>'LST_RNG_LIST',cascade=>true);
end;
查看主分組
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_LIST';
num_rows=2,LST_PNG_LIST表分別在兩個表空間中有2條數據
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_LIST';3.3.5、追加分區
方式一:追加主分區
alter table LST_RNG_LIST add partition P_3512834993 values ('3512834993') tablespace TS_3512834993
查看分區
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_LIST';
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_LIST';
默認按照表創建時子分區的的分區規則,自動生成一個子分區(紅色框內)
刪除添加的List分區
alter table LST_RNG_LIST drop partitionP_3512834993;
對應的子分區會自動被drop掉。
方式二:追加主分區及其子分區
alter table LST_RNG_LIST add partition P_3512834993 values ('3512834993') tablespace TS_3512834993
(subpartition P_3512834993_SP_20150802values less than (TO_DATE('2015-08-02','YYYY-MM-DD')))
查看分區
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_LIST';
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_LIST';
按照設定的子分區創建range子分區
四、Range指定表空間測試
4.1、創建表空間
CREATE TABLESPACE "TS_20150726" DATAFILE 'D:\APP\ORADATA\ORCL\TS_20150726.dbf' SIZE 50M AUTOEXTEND ON NEXT 16KMAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENTAUTO;
CREATE TABLESPACE "TS_20150802" DATAFILE 'D:\APP\ORADATA\ORCL\TS_20150802.dbf' SIZE 50M AUTOEXTEND ON NEXT 16KMAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENTAUTO;
CREATE TABLESPACE "TS_20150809" DATAFILE 'D:\APP\ORADATA\ORCL\TS_20150809.dbf' SIZE 50M AUTOEXTEND ON NEXT 16KMAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENTAUTO;4.2、兩個List兩個Range測試
4.2.1、創建分區表
drop table LST_RNG_RANGE cascade constraints; /*==============================================================*/ /* Table:LST_RNG_RANGE */ /*==============================================================*/ create table LST_RNG_RANGE ( AUTO_ID VARCHAR2(36) not null, SALE_NO VARCHAR2(36) not null, POS_CODE VARCHAR2(10), POS_NAME VARCHAR2(30), TOTAL_AMOUNT NUMBER(18,2), SALE_DATE DATE, REMARK VARCHAR2(500), constraint PK_LST_RNG_RANGEprimary key (AUTO_ID) ) partition by list (POS_CODE) subpartition by range (SALE_DATE) subpartition template ( subpartition SP_20150726 values less than (TO_DATE('2015-07-26','YYYY-MM-DD')) tablespace TS_20150726, subpartition SP_20150802 values less than (TO_DATE('2015-08-02','YYYY-MM-DD')) tablespace TS_20150802 ) ( partition P_3512860010 values ('3512860010'), partition P_3512860005 values ('3512860005') ); comment on column LST_RNG_RANGE.AUTO_ID is '自動編號'; comment on column LST_RNG_RANGE.SALE_NO is '銷售單號'; comment on column LST_RNG_RANGE.POS_CODE is '商戶代碼'; comment on column LST_RNG_RANGE.POS_NAME is '商戶名稱'; comment on column LST_RNG_RANGE.TOTAL_AMOUNT is '銷售總額'; comment on column LST_RNG_RANGE.SALE_DATE is '銷售日期'; comment on column LST_RNG_RANGE.REMARK is '備注';
4.2.2、查看分區
查看主分組
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_RANGE';
未指定表空間的主分區,默認使用當前用戶所在的表空間;
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_RANGE';
Range子分區存放於指定的表空間中
4.2.3、插入數據
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507240001','3512860010','全味食品商貿有限公司','100.78',TO_DATE('2015-07-24','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507250001','3512860010','全味食品商貿有限公司','100.78',TO_DATE('2015-07-25','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507260001','3512860010','全味食品商貿有限公司','1132.23',TO_DATE('2015-07-26','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507290001','3512860010','全味食品商貿有限公司','1132.23',TO_DATE('2015-07-29','YYYY-MM-DD'),NULL);
INSERT INTOLST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201508010001','3512860010','全味食品商貿有限公司','1132.23',TO_DATE('2015-08-01','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507240001','3512860005','新干線貿易有限公司','100.78',TO_DATE('2015-07-24','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507250001','3512860005','新干線貿易有限公司','100.78',TO_DATE('2015-07-25','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507260001','3512860005','新干線貿易有限公司','1132.23',TO_DATE('2015-07-26','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507290001','3512860005','新干線貿易有限公司','1132.23',TO_DATE('2015-07-29','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201508010001','3512860005','新干線貿易有限公司','1132.23',TO_DATE('2015-08-01','YYYY-MM-DD'),NULL);
commit;4.2.4、查看數據
查看分區數據
select * from LST_RNG_RANGE partition(P_3512860010); select * from LST_RNG_RANGE partition(P_3512860005); select * from LST_RNG_RANGE subpartition(P_3512860010_SP_20150726); select * from LST_RNG_RANGE subpartition(P_3512860005_SP_20150726); select * from LST_RNG_RANGE subpartition(P_3512860010_SP_20150802); select * from LST_RNG_RANGE subpartition(P_3512860005_SP_20150802);
收集分區統計信息
begin
dbms_stats.gather_table_stats(ownname=>'WKOD_VERIFY',granularity =>'all',tabname=>'LST_RNG_RANGE',cascade=>true);
end;
查看主分組
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_RANGE';
num_rows=5,LST_PNG_RANGE表的兩個主分區在當前用戶所在的表空間中有各5條數據
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_RANGE';
4.2.5、追加分區
方式一:追加主分區
alter table LST_RNG_RANGE add partition P_3512834993 values ('3512834993')
查看分區
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_RANGE';
主分區所屬表空間為當前用戶所在的表空間
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_RANGE';
默認按照表創建時子分區的的分區規則,自動生成兩個子分區(紅色框內),且分區對應表空間與原來一致
刪除添加的List分區
alter table LST_RNG_RANGE drop partition P_3512834993;
對應的子分區會自動被drop掉。
方式二:追加主分區及其子分區
alter table LST_RNG_RANGE add partition P_3512834993 values ('3512834993')
(subpartition P_3512834993_SP_20150802 values less than (TO_DATE('2015-08-02','YYYY-MM-DD')) tablespace TS_20150809)
查看分區
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_RANGE';
主分區所屬表空間為當前用戶所在的表空間
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_RANGE';
按照設定的子分區所屬的表空間創建range子分區4.3、兩個List一個Range測試
drop table LST_RNG_RANGE cascade constraints; /*==============================================================*/ /* Table:LST_RNG_RANGE */ /*==============================================================*/ create table LST_RNG_RANGE ( AUTO_ID VARCHAR2(36) not null, SALE_NO VARCHAR2(36) not null, POS_CODE VARCHAR2(10), POS_NAME VARCHAR2(30), TOTAL_AMOUNT NUMBER(18,2), SALE_DATE DATE, REMARK VARCHAR2(500), constraint PK_LST_RNG_RANGEprimary key (AUTO_ID) ) partition by list (POS_CODE) subpartition by range (SALE_DATE) subpartition template ( subpartition SP_20150726 values less than (TO_DATE('2015-07-26','YYYY-MM-DD')) tablespace TS_20150726 ) ( partition P_3512860010 values ('3512860010'), partition P_3512860005 values ('3512860005') ); comment on column LST_RNG_RANGE.AUTO_ID is '自動編號'; comment on column LST_RNG_RANGE.SALE_NO is '銷售單號'; comment on column LST_RNG_RANGE.POS_CODE is '商戶代碼'; comment on column LST_RNG_RANGE.POS_NAME is '商戶名稱'; comment on column LST_RNG_RANGE.TOTAL_AMOUNT is '銷售總額'; comment on column LST_RNG_RANGE.SALE_DATE is '銷售日期'; comment on column LST_RNG_RANGE.REMARK is '備注'; 4.3.2、查看分區
查看主分組
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_RANGE';
未指定表空間的主分區,默認使用當前用戶所在的表空間;
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_RANGE';
Range子分區存放於指定的表空間中4.3.3、插入數據
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507240001','3512860005','新干線貿易有限公司','100.78',TO_DATE('2015-07-24','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507250001','3512860005','新干線貿易有限公司','100.78',TO_DATE('2015-07-25','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507240001','3512860010','全味食品商貿有限公司','100.78',TO_DATE('2015-07-24','YYYY-MM-DD'),NULL);
INSERT INTO LST_RNG_RANGE(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507250001','3512860010','全味食品商貿有限公司','100.78',TO_DATE('2015-07-25','YYYY-MM-DD'),NULL);
commit;
4.3.4、查看數據
查看分區數據
select * from LST_RNG_RANGE partition(P_3512860010);
select * from LST_RNG_RANGE partition(P_3512860005);
select * from LST_RNG_RANGE subpartition(P_3512860010_SP_20150726);
select * from LST_RNG_RANGE subpartition(P_3512860005_SP_20150726);
收集分區統計信息
begin
dbms_stats.gather_table_stats(ownname=>'WKOD_VERIFY',granularity =>'all',tabname=>'LST_RNG_RANGE',cascade=>true);
end;
查看主分組
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_RANGE';
num_rows=2,LST_PNG_RANGE表的兩個主分區在當前用戶所在的表空間中各有2條數據
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_RANGE';
兩個分區在同一個表空間中,每個分區中都存在兩條數據
4.3.5、追加分區
方式一:追加主分區
alter table LST_RNG_RANGE add partition P_3512834993 values ('3512834993')
查看分區
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_RANGE';
主分區所屬表空間為當前用戶所在的表空間
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_RANGE';
默認按照表創建時子分區的的分區規則,自動生成一個子分區(紅色框內),且分區對應表空間與原來一致
刪除添加的List分區
alter table LST_RNG_RANGE drop partition P_3512834993;
對應的子分區會自動被drop掉。
方式二:追加主分區及其子分區
alter table LST_RNG_RANGE add partition P_3512834993 values ('3512834993')
(subpartition P_3512834993_SP_20150802 values less than (TO_DATE('2015-08-02','YYYY-MM-DD')) tablespace TS_20150809)
查看分區
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_RANGE';
主分區所屬表空間為當前用戶所在的表空間
查看子分區
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_RANGE';
按照設定的子分區所屬的表空間創建range子分區
五、實驗結論
一、List-Range組合分區下,在List指定表空間,Range不指定表空間,Oracle自動將Range子分區存放於對應的List表空間中;
二、追加分區時候,在不指定子分區的情況下,Oracle默認按照表創建時的分區方式,對新追加的List分區下的子分區進行分區;
三、List-Range組合分區,所有表的數據實際存放在子分區所在的表空間;
附:
1、DROP TABLE
drop table LST_RNG_RANGE;
--並非真刪,而是置DROP標志,相關分區也依然存在
select * from user_recyclebin;
實際清空
purge table LST_RNG_RANGE;2、查看數據記錄所在表空間
SELECT B.TABLESPACE_NAME
FROM DBA_DATA_FILES B
WHERE B.FILE_ID = (SELECTDBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE_ID
FROM LST_RNG_RANGE A
WHERE A.SALE_NO='SN201507260001');3、常用分區表相關語句
--顯示數據庫所有分區表的信息:
select * from DBA_PART_TABLES;
--顯示當前用戶可訪問的所有分區表信息:
select * from ALL_PART_TABLES;
--顯示當前用戶所有分區表的信息:
select * from USER_PART_TABLES;
--顯示表分區信息 顯示數據庫所有分區表的詳細分區信息:
select * from DBA_TAB_PARTITIONS;
--顯示當前用戶可訪問的所有分區表的詳細分區信息:
select * from ALL_TAB_PARTITIONS;
--顯示當前用戶所有分區表的詳細分區信息:
select * from USER_TAB_PARTITIONS;
--顯示子分區信息 顯示數據庫所有組合分區表的子分區信息:
select * from DBA_TAB_SUBPARTITIONS;
--顯示當前用戶可訪問的所有組合分區表的子分區信息:
select * from ALL_TAB_SUBPARTITIONS;
--顯示當前用戶所有組合分區表的子分區信息:
select * from USER_TAB_SUBPARTITIONS;
--顯示分區列 顯示數據庫所有分區表的分區列信息:
select * from DBA_PART_KEY_COLUMNS;
--顯示當前用戶可訪問的所有分區表的分區列信息:
select * from ALL_PART_KEY_COLUMNS;
--顯示當前用戶所有分區表的分區列信息:
select * from USER_PART_KEY_COLUMNS;
--顯示子分區列 顯示數據庫所有分區表的子分區列信息:
select * from DBA_SUBPART_KEY_COLUMNS;
--顯示當前用戶可訪問的所有分區表的子分區列信息:
select * from ALL_SUBPART_KEY_COLUMNS;
--顯示當前用戶所有分區表的子分區列信息:
select * from USER_SUBPART_KEY_COLUMNS;
--怎樣查詢出oracle數據庫中所有的的分區表
select * from user_tables a where a.partitioned='YES';
--刪除一個表的數據是
truncate table table_name;
--刪除分區表一個分區的數據是
alter table table_name truncate partition p5;