一、实验目的
采用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 L