oracle数据库分区

旧城等待， 2022-04-25 09:00 253阅读 0赞

**最近有一个分表的操作：  
1.消息中心5个月数据1亿条数据，【数据量巨大】；  
2.【消息中心表】+【消息中心跳转参数表】，消息中心有6个模块；  
3.消息中心表：以每个月时间围分区，用户的id索引；  
4.分页查询时关联【消息中心跳转参数表】，时间>用户注册时间；  
分表：  
1.按照消息中心类型模块分表；  
2.【消息中心表】增加是否跳转字段  
3.分页查询消息不关联【消息中心跳转参数表】，时间>用户注册时间；  
4.用户点跳转查询【消息中心跳转参数表】跳转信息；**

顺便看了一下分区：网上一个很好的分区资源

[https://blog.csdn.net/tonyzhou\_cn/article/details/10295247][https_blog.csdn.net_tonyzhou_cn_article_details_10295247]

[https://www.linuxidc.com/Linux/2017-06/144788.htm][https_www.linuxidc.com_Linux_2017-06_144788.htm]

--1.建表
    --删除已有表
    drop table BI_PARTITION_DEMO_T;
    --新建表
    create table BI_PARTITION_DEMO_T
    (
      USERID      VARCHAR2(32),
      MSGID       VARCHAR2(128),
      ACCEPT_DATE DATE,
      STATUS      NUMBER(5)
    )
    tablespace USERS
    
    partition by range (ACCEPT_DATE)
    (
      partition P201905 values less than (TO_DATE(' 2019-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace USERS,
      partition P201906 values less than (TO_DATE(' 2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
        tablespace USERS,
      partition PMAX values less than (MAXVALUE)
        tablespace USERS
    );
    
    --2建索引
    -- Create/Recreate indexes --
    create index BI_MESSAGE_IFREAD_INFO_UH_TEST on BI_PARTITION_DEMO_T (ACCEPT_DATE) local (partition P201905,partition P201906,partition PMAX)
    tablespace USERS;--分区索引
    create index BI_MESSAGE_IFREAD_INFO_UI_TEST on BI_PARTITION_DEMO_T (USERID) local 
    tablespace USERS;--分区索引
    create index BI_MESSAGE_IFREAD_INFO_UM_TEST on BI_PARTITION_DEMO_T (MSGID) global 
    tablespace USERS;--全局索引
    create index BI_MESSAGE_IFREAD_INFO_UL_TEST on BI_PARTITION_DEMO_T (STATUS) 
    tablespace USERS;--默认全局索引
    --ORACLE自动维护局部索引的分区，当表分区被合并，分裂或删除时，关联的索引分区也会被合并，分裂或删除；
    --对分区表执行管理操作时会使其上的全局索引失效；
    
    -- Grant/Revoke object privileges 
    --grant select, insert, update, delete on BI_PARTITION_DEMO_T to USER_NAME;
    
    --3.删除与增加一个分区
      Ø 删除分区：alter table [tbname] drop partition [ptname] UPDATE GLOBAL INDEXES  ;  
      Ø 删除子分区：alter table [tbname] drop subpartition [ptname]  UPDATE GLOBAL INDEXES; 
      除hash分区和hash子分区外，其它的分区格式都可以支持这项操作。
    alter table BI_PARTITION_DEMO_T drop partition P201906 UPDATE GLOBAL INDEXES  ;
    --增加分区
    alter table BI_PARTITION_DEMO_T add partition P201906 values less than (TO_DATE(' 2019-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
    
    异常：ORA-14074: partition bound must collate higher than that of the last partition
    解决：因为表上已经有了pmaxvalue分区,这个时候oracle是不直接允许增加分区的. 而是通过拆分分区实现,只要只用下列语句增加分区就不会发生该错误.
    --ALTER TABLE 【table_name 表名】 SPLIT PARTITION 【pmaxvalue -- 需要拆分的分区名】 at (to_date('2010-01-01','yyyy-mm-dd')) INTO (PARTITION p201001, PARTITION pmaxvalue);
    ALTER TABLE BI_PARTITION_DEMO_T SPLIT PARTITION PMAX at (TO_DATE(' 2019-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (PARTITION P201907, PARTITION pmaxvalue);
    
    --4.查看索引状态
    状态分4种：
        N/A说明这个是分区索引;
        VAILD说明这个索引可用；
        UNUSABLE说明这个索引不可用；
        USABLE 说明这个索引的分区是可用的。
    
    user_indexes/ dba_indexes--查看莫一个表中用到索引:索引状态字段status
    dba_ind_partitions/ user_ind_partitions/ user_ind_subpartitions--查看分区索引状态
    
    --分区相关表
    user_part_indexes/ DBA_PART_INDEXES--本地分区索引的整体状态
    user_part_tables
    user_partial_drop_tabs
    user_part_indexes
    user_part_key_columns

**希望对你有帮助，祝你有一个好心情，加油！**

**若有错误、不全、可优化的点，欢迎纠正与补充；转载请注明出处！**

[https_blog.csdn.net_tonyzhou_cn_article_details_10295247]: https://blog.csdn.net/tonyzhou_cn/article/details/10295247
[https_www.linuxidc.com_Linux_2017-06_144788.htm]: https://www.linuxidc.com/Linux/2017-06/144788.htm