聊聊oracle的表分区-1

数据库之美 2021-04-08

956

作为关系型数据库领头羊，oracle之所以能够很好的支撑百亿大表，其对表进行分区的能力功不可没。

非分区表

分区表

从图片可以形象的看出，分区表把一张大表分成了一个个小表，从而提高查询效率。

问题来了，什么时候考虑对表进行分区操作？

oracle官方给出了3种情况：

1 超过2gb 的表应考虑进行分区；

2 含有历史数据的表，尤其是历史数据很少更新，新数据插入新的分区，历史数据存放在老分区这种情况；

3 当表的内容需要分散在不同存储设备上时；

那么，oracle里面如何进行表分区呢？

1 表分区的类型

从类型上说，主要有range, hash, list 这几种分区方式。

案例展示:

-- 先建表
SQL> create table table1
  2  (
  3     id varchar(12) not null,
  4     time_ date not null,
  5     primary key (id)
  6  )
  7  partition by range (time_)
  8  (
  9  partition p_table1_2020 values less than ( to_date('2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN' ) ),
 10  partition p_table1_2021 values less than ( to_date('2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN' ) ),
 11  partition p_table1_2022 values less than ( to_date('2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN' ) )
 12  );


Table created.
复制

-- 插入数据
SQL> insert into table1 values(1000,to_date('2020-07-01 10:31:01','SYYYY-MM-DD HH24:MI:SS'));




1 row created.


SQL> SQL> insert into table1 values(1001,to_date('2021-09-01 10:31:01','SYYYY-MM-DD HH24:MI:SS'));


1 row created.


SQL> select * from table1;


ID           TIME_
------------ ---------
1000         01-JUL-20
1001         01-SEP-21


复制

-- 查看各分区数据
SQL> select * from table1 partition (p_table1_2020);


ID           TIME_
------------ ---------
1000         01-JUL-20


SQL> select * from table1 partition (p_table1_2021);


ID           TIME_
------------ ---------
1001         01-SEP-21


SQL> select * from table1 partition (p_table1_2022);


no rows selected
复制

前面插入的2条数据按照我们制定的分区规则分别进入了分区p_table1_2020 和分区p_table1_2021，而p_table1_2022分区则无数据，符合预期。

2 表分区相关操作

 ADD PARTITION | SUBPARTITION
 COALESCE PARTITION | SUBPARTITION
 DROP PARTITION | SUBPARTITION
 EXCHANGE PARTITION | SUBPARTITION
 MERGE PARTITION | SUBPARTITION
 MOVE PARTITION | SUBPARTITION
 SPLIT PARTITION | SUBPARTITION
 TRUNCATE PARTITION | SUBPARTITION
复制

已add partition 为例，操作如下：

SQL>  alter table table1 add partition p_table1_max values less than ( (to_date('2099-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS') ) );


Table altered.
复制

这样，一个新的分区就添加好了。

我们验证一下：

SQL> select table_owner,table_name,partition_name,high_value,partition_position
  2      from dba_TAB_partitions where table_owner='HR' and table_name ='TABLE1';


TABLE_OWNER     TABLE_NAME   PARTITION_NAME  HIGH_VALUE                                         PARTITION_POSITION
--------------- ------------ --------------- -------------------------------------------------- ------------------
HR              TABLE1       P_TABLE1_2020   TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:                  1
                                             MI:SS', 'NLS_CALENDAR=GREGORIA


HR              TABLE1       P_TABLE1_2021   TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:                  2
                                             MI:SS', 'NLS_CALENDAR=GREGORIA


HR              TABLE1       P_TABLE1_2022   TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:                  3
                                             MI:SS', 'NLS_CALENDAR=GREGORIA


HR              TABLE1       P_TABLE1_MAX    TO_DATE(' 2099-01-01 00:00:00', 'SYYYY-MM-DD HH24:                  4
                                             MI:SS', 'NLS_CALENDAR=GREGORIA
复制

P_TABLE1_MAX 分区已经显示出来了。

当然，从plsql 里面也可以查看

好了，今天的分享就到这里了，亲，你学会了么？

Jan.3, 2021

oracle

文章转载自数据库之美，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

聊聊oracle的表分区-1

评论

相关阅读