分区表之按季度存数据(不考虑年份)

4.不创建新分区，实现按季度存数据

----每年的第一季度的数据放在第一个分区，第二季度放第二个分区.....共四个分区。如何实现？

----按照下面的方法可以实现，但是是否有bug呢？如果是12c中可以把分区表指定的列隐藏了，因为实际上该列不希望被人为手动插入数据，需要使用默认的参数，并且不需要被查询

 

4.1 方法一 range分区

----创建分区表

create table t_auto

 ( seq  number not null,

  update_mon  int  default (to_char(sysdate,'mm')),

  constraint t_auto_seq primary key(seq)

  )

  partition by range(update_mon)

  ( partition t_auto1 values less than(4) tablespace users,

   partition t_auto2 values less than(7) tablespace users,

   partition t_auto3 values less than(10) tablespace users,

   partition t_auto4 values less than(13) tablespace users

);

 

 

----查看T_AUTO表的分区情况

set linesize 999

set pagesize 999

col TABLE_NAME for a20

col PARTITION_NAME for a20

col HIGH_VALUE for a10

col TABLESPACE_NAME for a10

 

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_AUTO';

TABLE_NAME     PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

T_AUTO               T_AUTO1              4                          1 USERS

T_AUTO               T_AUTO2              7                          1 USERS

T_AUTO               T_AUTO3              10                         2 USERS

T_AUTO               T_AUTO4              13                         2 USERS

 

----批量添加数据，查看分区表添加情况

----我们在插入数据的时候，指定列插入，不要插入update_mon列值，让它一直为默认参数

begin

 for i in 1..30 loop

  insert into t_auto(seq) values(i);

 end loop;

commit;

end;

/

 

----因为当前是7月，所以新插入的30条数据都在第3分区

SQL> select sysdate from dual;

SYSDATE

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

2016-07-19 23:29:28

SQL> select count(*) from t_auto partition(t_auto3);

  COUNT(*)

----------

        30

 

 

----下面模拟每个月插入数据：

begin

 for i in 1..30 loop

  insert into t_auto(seq, update_mon) values(i+30, to_number(to_char(add_months(sysdate,i),'mm')));

 end loop;

commit;

end;

/

 

SQL> select count(*) from t_auto;

  COUNT(*)

----------

        60

 

 

----查看分区表数据情况

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_AUTO';

 

SQL> select * from t_auto partition(t_auto1);

       SEQ UPDATE_MON

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

        36          1

        37          2

        38          3

        48          1

        49          2

        50          3

        60          1

7 rows selected.

 

SQL> select * from t_auto partition(t_auto4);

       SEQ UPDATE_MON

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

        33         10

        34         11

        35         12

        45         10

        46         11

        47         12

        57         10

        58         11

        59         12

9 rows selected.

 

SQL> select count(*) from t_auto partition(t_auto3);

  COUNT(*)

----------

        38

 

 

----索引状态

select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='T_AUTO';

INDEX_NAME      INDEX_TYPE                  TABLESPACE TABLE_TYPE  STATUS

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

T_AUTO_SEQ       NORMAL                SYSTEM     TABLE       VALID

 

 

4.2 方法二 list分区

----创建分区表

create table t_auto_list

 ( seq  number not null,

  update_mon  int  default (to_char(sysdate,'mm')),

  constraint t_auto_list_seq primary key(seq)

  )

  partition by list(update_mon)

  ( partition t_auto_list1 values (1,2,3) tablespace users,

   partition t_auto_list2 values (4,5,6) tablespace users,

   partition t_auto_list3 values (7,8,9) tablespace users,

   partition t_auto_list4 values (10,11,12) tablespace users

);

 

 

----查看T_AUTO_LIST表的分区情况

set linesize 999

set pagesize 999

col TABLE_NAME for a20

col PARTITION_NAME for a20

col HIGH_VALUE for a10

col TABLESPACE_NAME for a10

 

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_AUTO_LIST';

TABLE_NAME    PARTITION_NAME       HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE

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

T_AUTO_LIST          T_AUTO_LIST1         1, 2, 3                    7 USERS

T_AUTO_LIST          T_AUTO_LIST2         4, 5, 6                    7 USERS

T_AUTO_LIST          T_AUTO_LIST3         7, 8, 9                    7 USERS

T_AUTO_LIST          T_AUTO_LIST4         10, 11, 12                10 USERS

 

 

----批量添加数据，查看分区表添加情况

----我们在插入数据的时候，指定列插入，不要插入update_mon列值，让它一直为默认参数

begin

 for i in 1..30 loop

  insert into t_auto_list(seq) values(i);

 end loop;

commit;

end;

/

 

----因为当前是7月，所以新插入的30条数据都在第3分区

SQL> select sysdate from dual;

SYSDATE

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

2016-07-19 23:29:28

SQL> select count(*) from t_auto_list partition(t_auto_list3);

  COUNT(*)

----------

        30

 

----下面模拟每个月插入数据：

begin

 for i in 1..30 loop

  insert into t_auto_list(seq, update_mon) values(i+30, to_number(to_char(add_months(sysdate,i),'mm')));

 end loop;

commit;

end;

/

 

SQL> select count(*) from t_auto_list;

  COUNT(*)

----------

        60

 

 

----查看分区表数据情况

select  TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_AUTO';

 

SQL> select * from t_auto_list partition(t_auto_list1);

       SEQ UPDATE_MON

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

        36          1

        37          2

        38          3

        48          1

        49          2

        50          3

        60          1

7 rows selected.

 

SQL> select * from t_auto_list partition(t_auto_list4);

       SEQ UPDATE_MON

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

        33         10

        34         11

        35         12

        45         10

        46         11

        47         12

        57         10

        58         11

        59         12

9 rows selected.

 

SQL> select count(*) from t_auto_list partition(t_auto_list3);

  COUNT(*)

----------

        38

 

 

----索引状态

select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='T_AUTO_LIST';

INDEX_NAME       INDEX_TYPE                  TABLESPACE TABLE_TYPE  STATUS

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

T_AUTO_LIST_SEQ      NORMAL                      SYSTEM     TABLE       VALID

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2123158/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2123158/