Oracle的分区表有什么用
短信预约 -IT技能 免费直播动态提醒
这篇文章主要讲解了“Oracle的分区表有什么用”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Oracle的分区表有什么用”吧！
下面对分区表进行简单的实验：
先查询所需要进行分区表的表结构：
sys@FX1TEST>desc fqtbb
Name Null? Type
-------------------------------------------------------------------------- -------- --------------------------------------------------
ID NUMBER
YXTIME DATE
HASH VARCHAR2(50)
GOODLOOK VARCHAR2(20)查询表当前的数据：
sys@FX1TEST>select * from fqtbb;
ID YXTIME HASH GOODLOOK
---------- ------------------- -------------------- ----------
1 2011-01-02 00:00:00 java111 no
1 2011-04-02 00:00:00 java121 no
1 2011-05-02 00:00:00 java131 no
1 2012-02-02 00:00:00 java141 no
1 2013-01-02 00:00:00 java1ds no
1 2012-06-04 00:00:00 java1da no
1 2013-12-21 00:00:00 java771 no
1 2013-02-02 00:00:00 java771 no
1 2014-06-25 00:00:00 java661 no
1 2014-01-02 00:00:00 java156 no
1 2015-05-22 00:00:00 java132 no
1 2015-06-07 00:00:00 java134 no
1 2016-04-21 00:00:00 java152 yes
1 2016-11-11 00:00:00 java761 yes
1 2016-01-21 00:00:00 java171 yes
1 2017-04-21 00:00:00 java711 yes
1 2017-05-05 00:00:00 java717 yes
1 2017-01-02 00:00:00 java174 yes
1 2018-03-01 00:00:00 java172 yes
1 2018-01-23 00:00:00 java176 yes
1 2018-12-22 00:00:00 java166 yes
21 rows selected.下面便是一些常用的分区方法
范围分区：利用分区键定义范围，根据实际取值，进行分区选择，这种范围一般以数字形式存储，例如时间，分数，工资等等，所以不可避免的这种分区方法做不到均匀分区。
这里我们用时间范围进行划分 :
create table fqtbb_part (
id number,
yxtime date,
hash varchar(50),
goodlook varchar(20))
PARTITION BY RANGE (yxtime)
(
PARTITION fqtbb_part1 VALUES LESS THAN (TO_DATE('2011-01-01','yyyy-mm-dd'))
TABLESPACE test,
PARTITION fqtbb_part2 VALUES LESS THAN (TO_DATE('2014-01-02','yyyy-mm-dd'))
TABLESPACE test1,
PARTITION fqtbb_part3 VALUES LESS THAN (MAXVALUE)
TABLESPACE test2
);
Table created.将大表中的数据插到新建表中：
sys@FX1TEST>insert into fqtbb_part select * from fqtbb;
21 rows created.
sys@FX1TEST>commit;
Commit complete.接下来查询单独分区的数据信息
sys@FX1TEST>select * from fqtbb_part partition(fqtbb_part3);
ID YXTIME HASH GOODLOOK
---------- ------------------- -------------------- ----------
1 2014-06-25 00:00:00 java661 no
1 2014-01-02 00:00:00 java156 no
1 2015-05-22 00:00:00 java132 no
1 2015-06-07 00:00:00 java134 no
1 2016-04-21 00:00:00 java152 yes
1 2016-11-11 00:00:00 java761 yes
1 2016-01-21 00:00:00 java171 yes
1 2017-04-21 00:00:00 java711 yes
1 2017-05-05 00:00:00 java717 yes
1 2017-01-02 00:00:00 java174 yes
1 2018-03-01 00:00:00 java172 yes
1 2018-01-23 00:00:00 java176 yes
1 2018-12-22 00:00:00 java166 yes
13 rows selected.sys@FX1TEST>select * from fqtbb_part partition(fqtbb_part2);
ID YXTIME HASH GOODLOOK
---------- ------------------- -------------------- ----------
1 2011-01-02 00:00:00 java111 no
1 2011-04-02 00:00:00 java121 no
1 2011-05-02 00:00:00 java131 no
1 2012-02-02 00:00:00 java141 no
1 2013-01-02 00:00:00 java1ds no
1 2012-06-04 00:00:00 java1da no
1 2013-12-21 00:00:00 java771 no
1 2013-02-02 00:00:00 java771 no
8 rows selected.当然如果查询分区表的信息。也有相对应的表供用户查询。例如：
sys@FX1TEST>select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUS from dba_part_tables where table_name='FQTBB_PART';
TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT STATUS
------------------------------ ------------------ --------------- ----------------
FQTBB_PART RANGE3 VALID查询表分区所处的表空间：
sys@FX1TEST>select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART';
PARTITION_NAME NUM_ROWS TABLESPACE SEGMENT_
-------------------- ---------- ---------- --------
FQTBB_PART1TEST YES
FQTBB_PART2TEST1 YES
FQTBB_PART3TEST2 YES列分区
列分区根据分区键指定的取值列表进行分区，但是这种分区方法适合的比较少，例如部门列表、所属项目组名称等等
这里根据goodlook键进行定义：
create table fqtbb_parta (
id number,
yxtime date,
hash varchar(50),
goodlook varchar(20))
PARTITION BY list(goodlook)
(
PARTITION fqtbb_part1 VALUES ('yes')
TABLESPACE test,
PARTITION fqtbb_part2 VALUES ('no')
TABLESPACE test1,
PARTITION fqtbb_part3 VALUES (default)
TABLESPACE test2
);
Table created.插入数据
sys@FX1TEST>insert into fqtbb_parta select * from fqtbb;
21 rows created.
sys@FX1TEST>commit;
Commit complete.分区表创建完成之后进行查询验证：
sys@FX1TEST>select * from fqtbb_parta partition(fqtbb_part2);
ID YXTIME HASH GOODLOOK
---------- ------------------- -------------------- ----------
1 2011-01-02 00:00:00 java111 no
1 2011-04-02 00:00:00 java121 no
1 2011-05-02 00:00:00 java131 no
1 2012-02-02 00:00:00 java141 no
1 2013-01-02 00:00:00 java1ds no
1 2012-06-04 00:00:00 java1da no
1 2013-12-21 00:00:00 java771 no
1 2013-02-02 00:00:00 java771 no
1 2014-06-25 00:00:00 java661 no
1 2014-01-02 00:00:00 java156 no
1 2015-05-22 00:00:00 java132 no
1 2015-06-07 00:00:00 java134 no
12 rows selected.
sys@FX1TEST>select * from fqtbb_parta partition(fqtbb_part1);
ID YXTIME HASH GOODLOOK
---------- ------------------- -------------------- ----------
1 2016-04-21 00:00:00 java152 yes
1 2016-11-11 00:00:00 java761 yes
1 2016-01-21 00:00:00 java171 yes
1 2017-04-21 00:00:00 java711 yes
1 2017-05-05 00:00:00 java717 yes
1 2017-01-02 00:00:00 java174 yes
1 2018-03-01 00:00:00 java172 yes
1 2018-01-23 00:00:00 java176 yes
1 2018-12-22 00:00:00 java166 yes
9 rows selected.
sys@FX1TEST>select * from fqtbb_parta partition(fqtbb_part3);
no rows selected下面是oracle 分区表常用的管理操作，如下：
由于我们之前再范围分区时，设置了default值，所以无法增加分区，所以这里先演示删除分区
sys@FX1TEST>alter table fqtbb_part drop partition fqtbb_part3;
Table altered.
sys@FX1TEST>select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGMENT_
------------------------------------------------------------ ---------- ------------------------------------------------------------ --------
FQTBB_PART1 0 TEST YES
FQTBB_PART2 8 TEST1 YES添加分区
sys@FX1TEST>alter table fqtbb_part add partition p3 values less than (to_date('2019-09-09','yyyy-mm-dd'));
Table altered.截断分区：截断分区是指清楚数据，保留分区结构，如下
sys@FX1TEST>alter table fqtbb_part truncate partition p3;
Table truncated.合并分区：指将相邻的分区合并成一个分区，例如2019、2018 这两个分区，合并之后，分区将采用2019这个界限，并且合并是要合并到2019这个分区里；如下
sys@FX1TEST>alter table fqtbb_part merge partitions fqtbb_part1,fqtbb_part2 into partition fqtbb_part2;
Table altered.
sys@FX1TEST>select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGMENT_
------------------------------------------------------------ ---------- ------------------------------------------------------------ --------
FQTBB_PART2SYSTEM YES
P3SYSTEM YES拆分分区：有合并当然就有拆分，意思就是将一个分区拆成两个分区，而之前的分区将不存在；如下
sys@FX1TEST>alter table fqtbb_part split partition fqtbb_part2 at(to_date('2014-01-01','yyyy-mm-dd')) into (partition p1,partition p2);
Table altered.
sys@FX1TEST>select PARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATED from dba_tab_partitions where table_name='FQTBB_PART';
PARTITION_NAME NUM_ROWS TABLESPACE_NAME SEGMENT_
------------------------------------------------------------ ---------- ------------------------------------------------------------ --------
P1SYSTEM YES
P2SYSTEM YES
P3SYSTEM YES重命名分区：顾名思义，就是让分区换一个名字
sys@FX1TEST>alter table fqtbb_part rename partition p1 to p11;
Table altered.感谢各位的阅读，以上就是“Oracle的分区表有什么用”的内容了，经过本文的学习后，相信大家对Oracle的分区表有什么用这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/49.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
