834993') 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 pa