rtition(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_