开发者社区> 樱桃味> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

分区表 自动添加

简介: 背景: 数据较多,切考虑到后期维护,希望能够自动添加分区 过程  间隔分区 oracle 11g 新的特性 CREATE T...
+关注继续查看
背景:

数据较多,切考虑到后期维护,希望能够自动添加分区

过程 


间隔分区 oracle 11g 新的特性


CREATE TABLE gh_interval_tab (
  id           NUMBER,
  created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd'))
);


insert into gh_interval_tab(id,created_date) values(1000,to_date('2015-01-01','yyyy-mm-dd'));
insert into gh_interval_tab(id,created_date) values(1000,to_date('2015-02-01','yyyy-mm-dd'));
insert into gh_interval_tab(id,created_date) values(1000,to_date('2015-03-01','yyyy-mm-dd'));
insert into gh_interval_tab(id,created_date) values(1000,to_date('2015-04-01','yyyy-mm-dd'));
insert into gh_interval_tab(id,created_date) values(1000,to_date('2015-05-01','yyyy-mm-dd'));

commit;


SQL> select t.segment_name,t.partition_name,t.segment_name,t.tablespace_name from user_segments t;
SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_NAME                                                                     TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------
GH_INTERVAL_TAB                                                                  PART_01                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA



SQL> select s.* from user_tab_partitions s;
TABLE_NAME                     COMPOSITE PARTITION_NAME                 SUBPARTITION_COUNT HIGH_VALUE                                                                       HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME                  PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT   MAX_SIZE PCT_INCREASE  FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION         INTERVAL SEGMENT_CREATED
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ----------- ---------------- ------------ ---------- --------- ------------------------------ -------- ---------------
GH_INTERVAL_TAB                NO        PART_01                                         0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  1 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       NO       YES




insert into gh_interval_tab(id,created_date) values(1000,to_date('2016-01-01','yyyy-mm-dd'));
insert into gh_interval_tab(id,created_date) values(1000,to_date('2016-02-01','yyyy-mm-dd'));
insert into gh_interval_tab(id,created_date) values(1000,to_date('2016-03-01','yyyy-mm-dd'));
insert into gh_interval_tab(id,created_date) values(1000,to_date('2016-04-01','yyyy-mm-dd'));
insert into gh_interval_tab(id,created_date) values(1000,to_date('2016-05-01','yyyy-mm-dd'));
commit;





SQL> select s.* from user_tab_partitions s;
TABLE_NAME                     COMPOSITE PARTITION_NAME                 SUBPARTITION_COUNT HIGH_VALUE                                                                       HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME                  PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT   MAX_SIZE PCT_INCREASE  FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION         INTERVAL SEGMENT_CREATED
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ----------- ---------------- ------------ ---------- --------- ------------------------------ -------- ---------------
GH_INTERVAL_TAB                NO        PART_01                                         0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  1 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       NO       YES
GH_INTERVAL_TAB                NO        SYS_P64                                         0 TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  2 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES
GH_INTERVAL_TAB                NO        SYS_P65                                         0 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  3 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES
GH_INTERVAL_TAB                NO        SYS_P66                                         0 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  4 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES
GH_INTERVAL_TAB                NO        SYS_P67                                         0 TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  5 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES
GH_INTERVAL_TAB                NO        SYS_P68                                         0 TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  6 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES
6 rows selected



过程中2015的数据插入了5条,都存储在oracle




CREATE TABLE gh_interval_tab_num (
  id           NUMBER,
  created_date DATE
)
PARTITION BY RANGE (id)
INTERVAL (10000)
(
   partition p_1w values less than (10001)
)




SQL> select s.* from user_tab_partitions s;
TABLE_NAME                     COMPOSITE PARTITION_NAME                 SUBPARTITION_COUNT HIGH_VALUE                                                                       HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME                  PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT   MAX_SIZE PCT_INCREASE  FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION         INTERVAL SEGMENT_CREATED
------------------------------ --------- ------------------------------ ------------------ -------------------------------------------------------------------------------- ----------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- -------------- ----------- ---------- ---------- ---------- ------------ ---------- --------------- ------- ----------- ------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- ------------- ----------- ----------- ---------------- ------------ ---------- --------- ------------------------------ -------- ---------------
GH_INTERVAL_TAB                NO        PART_01                                         0 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  1 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       NO       YES
GH_INTERVAL_TAB                NO        SYS_P64                                         0 TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  2 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES
GH_INTERVAL_TAB                NO        SYS_P65                                         0 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  3 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES
GH_INTERVAL_TAB                NO        SYS_P66                                         0 TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  4 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES
GH_INTERVAL_TAB                NO        SYS_P67                                         0 TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  5 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES
GH_INTERVAL_TAB                NO        SYS_P68                                         0 TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA                83                  6 TS_ZYK_DATA                            10                     1        255        8388608     1048576          1 2147483645 2147483645                                         YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       YES      YES
GH_INTERVAL_TAB_NUM            NO        P_1W                                            0 10001                                                                                            5                  1 TS_ZYK_DATA                            10                     1        255                                                                                                     YES     DISABLED                                                                                                                DEFAULT     DEFAULT     DEFAULT          NO           NO         NO                                       NO       NO
7 rows selected
SQL> select t.segment_name,t.partition_name,t.segment_name,t.tablespace_name from user_segments t;
SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_NAME                                                                     TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------
GH_INTERVAL_TAB                                                                  PART_01                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
GH_INTERVAL_TAB                                                                  SYS_P64                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
GH_INTERVAL_TAB                                                                  SYS_P65                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
GH_INTERVAL_TAB                                                                  SYS_P66                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
GH_INTERVAL_TAB                                                                  SYS_P67                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
GH_INTERVAL_TAB                                                                  SYS_P68                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
6 rows selected

可以发现一个情况在user_segment 中数据只有出现数据才会存在,这是oracle11g新的特性


insert into gh_interval_tab_num(id,created_date) values(1,to_date('2016-01-01','yyyy-mm-dd'));
commit;



SQL> select t.segment_name,t.partition_name,t.segment_name,t.tablespace_name from user_segments t;
SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_NAME                                                                     TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- ------------------------------
GH_INTERVAL_TAB                                                                  PART_01                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
GH_INTERVAL_TAB                                                                  SYS_P64                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
GH_INTERVAL_TAB                                                                  SYS_P65                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
GH_INTERVAL_TAB                                                                  SYS_P66                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
GH_INTERVAL_TAB                                                                  SYS_P67                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
GH_INTERVAL_TAB                                                                  SYS_P68                        GH_INTERVAL_TAB                                                                  TS_ZYK_DATA
GH_INTERVAL_TAB_NUM                                                              P_1W                           GH_INTERVAL_TAB_NUM        
出现了GH_INTERVAL_TAB_NUM信息










具体步骤:

CREATE TABLE gh_interval_tab (
  id           NUMBER,
  created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd'))
);


CREATE TABLE gh_interval_tab_num (
  id           NUMBER,
  created_date DATE
)
PARTITION BY RANGE (id)
INTERVAL (10000)
(
   partition p_1w values less than (10001)
)




部分资料摘自官网文档:

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【TP5.1】数据库添加的时候自动添加时间autoWriteTimestamp
【TP5.1】数据库添加的时候自动添加时间autoWriteTimestamp
85 0
AutoScaling 通过lifecycleHook自动添加Redis白名单
弹性伸缩核心数据库组件不包含Redis实例,想要自动化配置伸缩组实例到Redis白名单中,是一件相当麻烦的事情。弹性伸缩与OOS运维编排合作,通过lifecycleHook的方式为大家提供了一种简单,可靠的自动化添加Redis白名单的方案。
909 0
MySQL分区表
MySQL分区表 官方文档:https://dev.mysql.com/doc/refman/5.6/en/partitioning.html 在MySQL 5.6.1之前可以通过命令“show variables like '%have_partitioning%'”来查看MySQL是否支持分区。
1201 0
为jqgrid添加统计金额页脚
为jqgrid增加自定义用户数据 设计统计的金额总和 最后在jgrid组件中添加   最后的效果为:
1118 0
EDB分区表的又一个“坑”
下周有一个应用上线,其中涉及一个夜维删除逻辑的应用,大体功能是按照时间删除一张表的历史数据,这张表的主键是另外一张时间分区表的外键,使用的是EDB(9.2)数据库,这次测试就意外发现了一个说是隐藏,也不算隐藏,至少和Oracle分区表有很大不同的地方,或者可以称他为KENG,“坑”。
1003 0
HASH分区表的好处和注意点
一、好处   1、对于分区本生不需要定期的进行分区加入(范围分区和LIST分区需要定期的对新加入的值新建分区)   2、可以消除访问热点块及索引热点块,由于索引是排序后的结构,对于一列自增的列加入范围分区,可能对索引的高位块进行频繁的数据插入,导致频繁的写入和分裂      对于这样的索引如果加入散列分区索引即可消除。
971 0
+关注
樱桃味
一个要努力的人
47
文章
21
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载