Shulou(Shulou.com)06/01 Report--

11G partition table automatic partitioning

Create interval partition table

create table test_range (idnumber,test_date date)

partition by range(test_date)interval(numtodsinterval(1,'day'))

(partition p_20160612 values less than(to_date('20160613','yyyymmdd')));

SQL> selecttable_name,partitioning_type,partition_count,interval from user_part_tableswhere table_name='TEST_RANGE';

TABLE_NAME PARTITION PARTITION_COUNT INTERVAL

-------------------- ------------------------ ---------------------------------------------

TEST_RANGE RANGE 1048575 (1024k with 10G) NUMTODSINTERVAL(1,'DAY')

Insert test data (partition exists)

SQL> insert into TEST_RANGE values(1,to_date('20160612','yyyymmdd'));

1 row created.

SQL> commit;

Commit complete.

SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';

TABLE_NAME PARTITION_NAME

--------------------------------------------------

TEST_RANGE P_20160612

Insert test data (no partitions exist)

SQL> insert into TEST_RANGE values(1,to_date('20160613','yyyymmdd'));

1 row created.

SQL> commit;

Commit complete.

SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';

TABLE_NAME PARTITION_NAME

--------------------------------------------------

TEST_RANGE P_20160612

TEST_RANGE SYS_P122

SQL> insert into TEST_RANGE values (1,to_date ('20160615 ',' yymmdd')); --insert larger values first

1 row created.

SQL> commit;

Commit complete.

SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';

TABLE_NAME PARTITION_NAME

--------------------------------------------------

TEST_RANGE P_20160612

TEST_RANGE SYS_P122

TEST_RANGE SYS_P123 --New partition

SQL> insert into TEST_RANGE values (1,to_date ('20160614 ',' yymmdd')); --insert middle values first

1 row created.

SQL> commit;

Commit complete.

SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';

TABLE_NAME PARTITION_NAME

--------------------------------------------------

TEST_RANGE P_20160612

TEST_RANGE SYS_P122

TEST_RANGE SYS_P123

TEST_RANGE SYS_P124 --New partition

Description: For interval partition table, when inserting the value corresponding to "partition does not exist", the corresponding partition will be generated automatically according to interval; if inserting a larger value first and then inserting a smaller value, the partition will be generated in turn according to interval. For example, test_range only has partition 20160612. When inserting the value of 20160615, partition 20160615 will be generated, and when inserting the value of 20160614, partition 20160614 will be generated again.

Create template partition table

drop table test_range purge;

create table test_range (idnumber,test_date date)

partition by range(test_date) interval(numtodsinterval(1,'day'))

subpartition by hash(id)

subpartition template

(subpartition a,

subpartition b,

subpartition c)

(partitionp_20160612 values less than (to_date('20160613','yyyymmdd')));

insert test data

SQL> insert into test_rangevalues(1,sysdate+2);

1 row created.

SQL> commit;

Commit complete.

SQL> select table_name,subpartition_namefrom user_tab_subpartitions where table_name ='TEST_RANGE';

TABLE_NAME SUBPARTITION_NAME

------------------------------------------------------------

TEST_RANGE P_20160612_A

TEST_RANGE P_20160612_B

TEST_RANGE P_20160612_C

TEST_RANGE SYS_SUBP125

TEST_RANGE SYS_SUBP126

TEST_RANGE SYS_SUBP127

Found that the newly generated partition is not in template form

SQL> alter table test_range addpartition P_20160615 values less than(to_date('20160616','yyyymmdd'));

alter table test_range add partition P_20160615values less than(to_date('20160616','yyyymmdd'))

*

ERROR at line 1:

ORA-14760: ADDPARTITION is not permitted on Interval partitioned objects

partition table created by interval keyword does not support self add partition

Do not take interval to create template partition table

drop table test_range purge;

create table test_range (idnumber,test_date date)

partition by range(test_date)

subpartition by hash(id)

subpartition template

(subpartition a,

subpartition b,

subpartition c)

(partitionp_20160612 values less than (to_date('20160613','yyyymmdd')));

add partition

SQL> alter table test_range addpartition P_20160615 values less than(to_date('20160616','yyyymmdd'));

Table altered.

SQL>

SQL> select table_name,subpartition_namefrom user_tab_subpartitions where table_name ='TEST_RANGE';

TABLE_NAME SUBPARTITION_NAME

------------------------------------------------------------

TEST_RANGE P_20160612_A

TEST_RANGE P_20160612_B

TEST_RANGE P_20160612_C

TEST_RANGE P_20160615_A

TEST_RANGE P_20160615_B

TEST_RANGE P_20160615_C

Description: For partition tables created with partition interval & subpartition template, subpartitions are named according to system customization, subpartitions are not named according to subpartition template, and partitions are not added by themselves; for partition tables created only with subpartition template, subpartitions are named according to subpartitiontemplate.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.