mysql建表分区按天_ORACLE创建按月和按天的自动递增分区

一、建按月自增分区表：

1.1建表

SQL> create table month_interval_partition_table (id number,time_col date) partition by range(time_col)

2  interval (numtoyminterval(1,'month'))

3  (

4   partition p_month_1 values less than (to_date('2014-01-01','yyyy-mm-dd'))

5  );

Table created

1.2 查看现在分区

SQL> select table_name,partition_name from user_tab_partitions where table_name='MONTH_INTERVAL_PARTITION_TABLE';

TABLE_NAME                     PARTITION_NAME

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

MONTH_INTERVAL_PARTITION_TABLE P_MONTH_1

1.3、插入数据

SQL> begin

2  for i in 0..11 loop

3   insert into MONTH_INTERVAL_PARTITION_TABLE values(i,add_months(to_date('2014-01-01','yyyy-mm-dd'),i));

4  end loop;

5  commit;

6  end;

7  /

PL/SQL procedure successfully completed

1.4、查看分区

SQL> select table_name,partition_name from user_tab_partitions where table_name='MONTH_INTERVAL_PARTITION_TABLE';

TABLE_NAME                     PARTITION_NAME

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

MONTH_INTERVAL_PARTITION_TABLE P_MONTH_1

MONTH_INTERVAL_PARTITION_TABLE SYS_P22

MONTH_INTERVAL_PARTITION_TABLE SYS_P23

MONTH_INTERVAL_PARTITION_TABLE SYS_P24

MONTH_INTERVAL_PARTITION_TABLE SYS_P25

MONTH_INTERVAL_PARTITION_TABLE SYS_P26

MONTH_INTERVAL_PARTITION_TABLE SYS_P27

MONTH_INTERVAL_PARTITION_TABLE SYS_P28

MONTH_INTERVAL_PARTITION_TABLE SYS_P29

MONTH_INTERVAL_PARTITION_TABLE SYS_P30

MONTH_INTERVAL_PARTITION_TABLE SYS_P31

MONTH_INTERVAL_PARTITION_TABLE SYS_P32

MONTH_INTERVAL_PARTITION_TABLE SYS_P33

13 rows selected

1.5查看其中一个分区数据

SQL> select * From MONTH_INTERVAL_PARTITION_TABLE partition(SYS_P22);

ID TIME_COL

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

0 2014/1/1

二、创建按天的分区表

2.1建表

SQL> create table day_interval_partition_table (id number,time_col date) partition by range(time_col)

2  interval (numtodsinterval(1,'day'))

3  (

4   partition p_day_1 values less than (to_date('2014-01-01','yyyy-mm-dd'))

5  );

Table created

2.2、查看现在分区

SQL> select table_name,partition_name from user_tab_partitions where table_name='DAY_INTERVAL_PARTITION_TABLE';

TABLE_NAME                     PARTITION_NAME

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

DAY_INTERVAL_PARTITION_TABLE   P_DAY_1

2.3、插入数据

SQL> begin

2  for i in 1..12 loop

3   insert into DAY_INTERVAL_PARTITION_TABLE values(i,trunc(to_date('2014-01-01','yyyy-mm-dd')+i));

4  end loop;

5  commit;

6  end;

7  /

PL/SQL procedure successfully completed

2.4查看分区

SQL> select table_name,partition_name from user_tab_partitions where table_name='DAY_INTERVAL_PARTITION_TABLE';

TABLE_NAME                     PARTITION_NAME

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

DAY_INTERVAL_PARTITION_TABLE   P_DAY_1

DAY_INTERVAL_PARTITION_TABLE   SYS_P34

DAY_INTERVAL_PARTITION_TABLE   SYS_P35

DAY_INTERVAL_PARTITION_TABLE   SYS_P36

DAY_INTERVAL_PARTITION_TABLE   SYS_P37

DAY_INTERVAL_PARTITION_TABLE   SYS_P38

DAY_INTERVAL_PARTITION_TABLE   SYS_P39

DAY_INTERVAL_PARTITION_TABLE   SYS_P40

DAY_INTERVAL_PARTITION_TABLE   SYS_P41

DAY_INTERVAL_PARTITION_TABLE   SYS_P42

DAY_INTERVAL_PARTITION_TABLE   SYS_P43

DAY_INTERVAL_PARTITION_TABLE   SYS_P44

DAY_INTERVAL_PARTITION_TABLE   SYS_P45

2.5查看数据

SQL> select table_name,partition_name from user_tab_partitions where table_name='DAY_INTERVAL_PARTITION_TABLE';

TABLE_NAME                     PARTITION_NAME

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

DAY_INTERVAL_PARTITION_TABLE   P_DAY_1

DAY_INTERVAL_PARTITION_TABLE   SYS_P34

DAY_INTERVAL_PARTITION_TABLE   SYS_P35

DAY_INTERVAL_PARTITION_TABLE   SYS_P36

DAY_INTERVAL_PARTITION_TABLE   SYS_P37

DAY_INTERVAL_PARTITION_TABLE   SYS_P38

DAY_INTERVAL_PARTITION_TABLE   SYS_P39

DAY_INTERVAL_PARTITION_TABLE   SYS_P40

DAY_INTERVAL_PARTITION_TABLE   SYS_P41

DAY_INTERVAL_PARTITION_TABLE   SYS_P42

DAY_INTERVAL_PARTITION_TABLE   SYS_P43

DAY_INTERVAL_PARTITION_TABLE   SYS_P44

DAY_INTERVAL_PARTITION_TABLE   SYS_P45

13 rows selected

SQL> select * From DAY_INTERVAL_PARTITION_TABLE;

ID TIME_COL

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

1 2014/1/2

2 2014/1/3

3 2014/1/4

4 2014/1/5

5 2014/1/6

6 2014/1/7

7 2014/1/8

8 2014/1/9

9 2014/1/10

10 2014/1/11

11 2014/1/12

12 2014/1/13

12 rows selected

SQL> select * From DAY_INTERVAL_PARTITION_TABLE partition(SYS_P39);

ID TIME_COL

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

6 2014/1/7