Oracle的分区表有什么用

数据库 2024年05月09日 19:49

这篇文章主要讲解了“Oracle的分区表有什么用”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Oracle的分区表有什么用”吧！

下面对分区表进行简单的实验：

先查询所需要进行分区表的表结构：

sys@FX1TEST>descfqtbbNameNull?Type------------------------------------------------------------------------------------------------------------------------------------IDNUMBERYXTIMEDATEHASHVARCHAR2(50)GOODLOOKVARCHAR2(20)

查询表当前的数据：

sys@FX1TEST>select*fromfqtbb;IDYXTIMEHASHGOODLOOK-----------------------------------------------------------12011-01-0200:00:00java111no12011-04-0200:00:00java121no12011-05-0200:00:00java131no12012-02-0200:00:00java141no12013-01-0200:00:00java1dsno12012-06-0400:00:00java1dano12013-12-2100:00:00java771no12013-02-0200:00:00java771no12014-06-2500:00:00java661no12014-01-0200:00:00java156no12015-05-2200:00:00java132no12015-06-0700:00:00java134no12016-04-2100:00:00java152yes12016-11-1100:00:00java761yes12016-01-2100:00:00java171yes12017-04-2100:00:00java711yes12017-05-0500:00:00java717yes12017-01-0200:00:00java174yes12018-03-0100:00:00java172yes12018-01-2300:00:00java176yes12018-12-2200:00:00java166yes21rowsselected.

下面便是一些常用的分区方法

范围分区：利用分区键定义范围，根据实际取值，进行分区选择，这种范围一般以数字形式存储，例如时间，分数，工资等等，所以不可避免的这种分区方法做不到均匀分区。

这里我们用时间范围进行划分 :

createtablefqtbb_part(idnumber,yxtimedate,hashvarchar(50),goodlookvarchar(20))PARTITIONBYRANGE(yxtime)(PARTITIONfqtbb_part1VALUESLESSTHAN(TO_DATE('2011-01-01','yyyy-mm-dd'))TABLESPACEtest,PARTITIONfqtbb_part2VALUESLESSTHAN(TO_DATE('2014-01-02','yyyy-mm-dd'))TABLESPACEtest1,PARTITIONfqtbb_part3VALUESLESSTHAN(MAXVALUE)TABLESPACEtest2);Tablecreated.

将大表中的数据插到新建表中：

sys@FX1TEST>insertintofqtbb_partselect*fromfqtbb;21rowscreated.sys@FX1TEST>commit;Commitcomplete.

接下来查询单独分区的数据信息

sys@FX1TEST>select*fromfqtbb_partpartition(fqtbb_part3);IDYXTIMEHASHGOODLOOK-----------------------------------------------------------12014-06-2500:00:00java661no12014-01-0200:00:00java156no12015-05-2200:00:00java132no12015-06-0700:00:00java134no12016-04-2100:00:00java152yes12016-11-1100:00:00java761yes12016-01-2100:00:00java171yes12017-04-2100:00:00java711yes12017-05-0500:00:00java717yes12017-01-0200:00:00java174yes12018-03-0100:00:00java172yes12018-01-2300:00:00java176yes12018-12-2200:00:00java166yes13rowsselected.sys@FX1TEST>select*fromfqtbb_partpartition(fqtbb_part2);IDYXTIMEHASHGOODLOOK-----------------------------------------------------------12011-01-0200:00:00java111no12011-04-0200:00:00java121no12011-05-0200:00:00java131no12012-02-0200:00:00java141no12013-01-0200:00:00java1dsno12012-06-0400:00:00java1dano12013-12-2100:00:00java771no12013-02-0200:00:00java771no8rowsselected.

当然如果查询分区表的信息。也有相对应的表供用户查询。例如：

sys@FX1TEST>selectTABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,STATUSfromdba_part_tableswheretable_name='FQTBB_PART';TABLE_NAMEPARTITIONING_TYPEPARTITION_COUNTSTATUS-------------------------------------------------------------------------------FQTBB_PARTRANGE3VALID

查询表分区所处的表空间：

sys@FX1TEST>selectPARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATEDfromdba_tab_partitionswheretable_name='FQTBB_PART';PARTITION_NAMENUM_ROWSTABLESPACESEGMENT_------------------------------------------------FQTBB_PART1TESTYESFQTBB_PART2TEST1YESFQTBB_PART3TEST2YES

列分区

列分区根据分区键指定的取值列表进行分区，但是这种分区方法适合的比较少，例如部门列表、所属项目组名称等等

这里根据goodlook键进行定义：

createtablefqtbb_parta(idnumber,yxtimedate,hashvarchar(50),goodlookvarchar(20))PARTITIONBYlist(goodlook)(PARTITIONfqtbb_part1VALUES('yes')TABLESPACEtest,PARTITIONfqtbb_part2VALUES('no')TABLESPACEtest1,PARTITIONfqtbb_part3VALUES(default)TABLESPACEtest2);Tablecreated.

插入数据

sys@FX1TEST>insertintofqtbb_partaselect*fromfqtbb;21rowscreated.sys@FX1TEST>commit;Commitcomplete.

分区表创建完成之后进行查询验证：

sys@FX1TEST>select*fromfqtbb_partapartition(fqtbb_part2);IDYXTIMEHASHGOODLOOK-----------------------------------------------------------12011-01-0200:00:00java111no12011-04-0200:00:00java121no12011-05-0200:00:00java131no12012-02-0200:00:00java141no12013-01-0200:00:00java1dsno12012-06-0400:00:00java1dano12013-12-2100:00:00java771no12013-02-0200:00:00java771no12014-06-2500:00:00java661no12014-01-0200:00:00java156no12015-05-2200:00:00java132no12015-06-0700:00:00java134no12rowsselected.sys@FX1TEST>select*fromfqtbb_partapartition(fqtbb_part1);IDYXTIMEHASHGOODLOOK-----------------------------------------------------------12016-04-2100:00:00java152yes12016-11-1100:00:00java761yes12016-01-2100:00:00java171yes12017-04-2100:00:00java711yes12017-05-0500:00:00java717yes12017-01-0200:00:00java174yes12018-03-0100:00:00java172yes12018-01-2300:00:00java176yes12018-12-2200:00:00java166yes9rowsselected.sys@FX1TEST>select*fromfqtbb_partapartition(fqtbb_part3);norowsselected

下面是oracle 分区表常用的管理操作，如下：

由于我们之前再范围分区时，设置了default值，所以无法增加分区，所以这里先演示删除分区

sys@FX1TEST>altertablefqtbb_partdroppartitionfqtbb_part3;Tablealtered.sys@FX1TEST>selectPARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATEDfromdba_tab_partitionswheretable_name='FQTBB_PART';PARTITION_NAMENUM_ROWSTABLESPACE_NAMESEGMENT_------------------------------------------------------------------------------------------------------------------------------------------FQTBB_PART10TESTYESFQTBB_PART28TEST1YES

添加分区

sys@FX1TEST>altertablefqtbb_partaddpartitionp3valueslessthan(to_date('2019-09-09','yyyy-mm-dd'));Tablealtered.

截断分区：截断分区是指清楚数据，保留分区结构，如下

sys@FX1TEST>altertablefqtbb_parttruncatepartitionp3;Tabletruncated.

合并分区：指将相邻的分区合并成一个分区，例如2019、2018 这两个分区，合并之后，分区将采用2019这个界限，并且合并是要合并到2019这个分区里；如下

sys@FX1TEST>altertablefqtbb_partmergepartitionsfqtbb_part1,fqtbb_part2intopartitionfqtbb_part2;Tablealtered.sys@FX1TEST>selectPARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATEDfromdba_tab_partitionswheretable_name='FQTBB_PART';PARTITION_NAMENUM_ROWSTABLESPACE_NAMESEGMENT_------------------------------------------------------------------------------------------------------------------------------------------FQTBB_PART2SYSTEMYESP3SYSTEMYES

拆分分区：有合并当然就有拆分，意思就是将一个分区拆成两个分区，而之前的分区将不存在；如下

sys@FX1TEST>altertablefqtbb_partsplitpartitionfqtbb_part2at(to_date('2014-01-01','yyyy-mm-dd'))into(partitionp1,partitionp2);Tablealtered.sys@FX1TEST>selectPARTITION_NAME,NUM_ROWS,TABLESPACE_NAME,SEGMENT_CREATEDfromdba_tab_partitionswheretable_name='FQTBB_PART';PARTITION_NAMENUM_ROWSTABLESPACE_NAMESEGMENT_------------------------------------------------------------------------------------------------------------------------------------------P1SYSTEMYESP2SYSTEMYESP3SYSTEMYES

重命名分区：顾名思义，就是让分区换一个名字

sys@FX1TEST>altertablefqtbb_partrenamepartitionp1top11;Tablealtered.

感谢各位的阅读，以上就是“Oracle的分区表有什么用”的内容了，经过本文的学习后，相信大家对Oracle的分区表有什么用这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！