oracle表分区的几种方法以及维护

表分区有以下优点：1、数据查询：数据被存储到多个文件上，减少了I/O负载，查询速度提高。2、数据修剪：保存历史数据非常的理想。3、备份：将大表的数据分成多个文件，方便备份和恢复。4、并行性：可以同时向表中进行DML操作，并行性性能提高。当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。Oracle中提供了以下几种表分区：一、范围分区：这种类型的分区是使用列的一组值，通常将该列成为分区键。示例1：假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：CREATETABLECUSTOMER(CUSTOMER_IDNUMBERNOTNULLPRIMARYKEY,FIRST_NAMEVARCHAR2(30)NOTNULL,LAST_NAMEVARCHAR2(30)NOTNULL,PHONEVARCHAR2(15)NOTNULL,EMAILVARCHAR2(80),STATUSCHAR(1))PARTITIONBYRANGE(CUSTOMER_ID)(PARTITIONCUS_PART1VALUESLESSTHAN(100000)TABLESPACECUS_TS01,PARTITIONCUS_PART2VALUESLESSTHAN(200000)TABLESPACECUS_TS02)在创建表进行分区时，表空间必须先存在，而且建议将不同的分区放入不同的表空间中。示例2：假设有ORDER_ACTIVITIES表，每6个月对订单进行清理，我们可以按月份对表进行分区，分区代码如下：CREATETABLEORDER_ACTIVITIES(ORDER_IDNUMBER(7)NOTNULL,ORDER_DATEDATE,TOTAL_AMOUNTNUMBER,CUSTOTMER_IDNUMBER(7),PAIDCHAR(1))PARTITIONBYRANGE(ORDER_DATE)(PARTITIONORD_ACT_PART01VALUESLESSTHAN(TO_DATE('01-MAY-2003','DD-MON-YYYY'))TABLESPACEORD_TS01,PARTITIONORD_ACT_PART02VALUESLESSTHAN(TO_DATE('01-JUN-2003','DD-MON-YYYY'))TABLESPACEORD_TS02,PARTITIONORD_ACT_PART02VALUESLESSTHAN(TO_DATE('01-JUL-2003','DD-MON-YYYY'))TABLESPACEORD_TS03)二、列表分区：该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。示例1：CREATETABLEPROBLEM_TICKETS(PROBLEM_IDNUMBER(7)NOTNULLPRIMARYKEY,DESCRIPTIONVARCHAR2(2000),CUSTOMER_IDNUMBER(7)NOTNULL,DATE_ENTEREDDATENOTNULL,STATUSVARCHAR2(20))PARTITIONBYLIST(STATUS)(PARTITIONPROB_ACTIVEVALUES('ACTIVE')TABLESPACEPROB_TS01,PARTITIONPROB_INACTIVEVALUES('INACTIVE')TABLESPACEPROB_TS02)三、散列分区：这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。请看下列示例：示例1：CREATETABLEHASH_TABLE(COLNUMBER(8),INFVARCHAR2(100))PARTITIONBYHASH(COL)(PARTITIONPART01TABLESPACEHASH_TS01,PARTITIONPART02TABLESPACEHASH_TS02,PARTITIONPART03TABLESPACEHASH_TS03)四、复合范围列表分区：这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。示例1：CREATETABLESALES(PRODUCT_IDVARCHAR2(5),SALES_DATEDATE,SALES_COSTNUMBER(10),STATUSVARCHAR2(20))PARTITIONBYRANGE(SALES_DATE)SUBPARTITIONBYLIST(STATUS)(PARTITIONP1VALUESLESSTHAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACEP1_TS(SUBPARTITIONP1SUB1VALUES('ACTIVE')TABLESPACESUBP1_TS1,SUBPARTITIONP1SUB2VALUES('INACTIVE')TABLESPACESUBP1_TS2),PARTITIONP2VALUESLESSTHAN(TO_DATE('2003-03-01','YYYY-MM-DD'))TABLESPACEP2_TS(SUBPARTITIONP2SUB1VALUES('ACTIVE')TABLESPACESUBP2_TS1,SUBPARTITIONP2SUB2VALUES('INACTIVE')TABLESPACESUBP2_TS2))示例2：使用TEMPLATE模板CREATETABLESALES(PRODUCT_IDVARCHAR2(5),SALES_DATEDATE,SALES_COSTNUMBER(10),STATUSVARCHAR2(20))PARTITIONBYRANGE(SALES_DATE)SUBPARTITIONBYLIST(STATUS)SUBPARTITIONTEMPLATE(SUBPARTITIONSUB1VALUES('ACTIVE')TABLESPACESUBP1_TS1,SUBPARTITIONSUB2VALUES('INACTIVE')TABLESPACESUBP2_TS2)(PARTITIONP1VALUESLESSTHAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACEP1_TS,PARTITIONP2VALUESLESSTHAN(TO_DATE('2003-03-01','YYYY-MM-DD'))TABLESPACEP2_TS)五、复合范围散列分区：这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。与上面的定义方式非常的类似，在此不单独举例。表分区对于用户来说是透明的，在插入数据时Oracle会自动判断插入的数据，然后放入相应的表分区中。但有时想单独查询某个分区中的数据时，就必须手工指定分区的名称。示例1：(此示例基于：四、复合范围列表分区的示例一)向SALES表插入记录，不必指定表分区。INSERTINTOSALESVALUES('00001','01-1月-02',100,'ACTIVE')/INSERTINTOSALESVALUES('00002','01-1月-01',200,'ACTIVE')/INSERTINTOSALESVALUES('00003','01-2月-03',300,'INACTIVE')/INSERTINTOSALESVALUES('00004','04-2月-03',300,'INACTIVE')/INSERTINTOSALESVALUES('00005','04-2月-02',300,'INACTIVE')/不指定表分区查看SALES表信息：SELECT*FROMSALES;结果如下所示：指定P1表分区查询SALES表信息：SELECT*FROMSALESPARTITION(P1);结果如下所示：指定P1SUB1子分区查询SALES表信息:SELECT*FROMSALESSUBPARTITION(P1SUB1);结果如下所示：示例2：(此示例基于：四、复合范围列表分区的示例二)示例2基于TEMPLATE模板的表分区，查询稍稍烦琐一点。指定P1表分区查询SALES表信息：SELECT*FROMSALESPARTITION(P1);结果如下所示,和刚才查询一致。指定SUB1子分区查询SALES表信息:SELECT*FROMSALESSUBPARTITION(SUB1);出现如下错误信息：怎么解决以上问题呢？我们通过sys模式查看分区信息的数据字典,如下：可以看出子分区不叫SUB1，而是P1_SUB1,重新查询信息，如下图所示：有关表分区的一些维护性操作：一、添加分区以下代码给SALES表添加了一个P3分区ALTERTABLESALESADDPARTITIONP3VALUESLESSTHAN(TO_DATE('2003-06-01','YYYY-MM-DD'));注意：以上添加的分区界限应该高于最后一个分区界限。以下代码给SALES表的P3分区添加了一个P3SUB1子分区ALTERTABLESALESMODIFYPARTITIONP3ADDSUBPARTITIONP3SUB1VALUES('COMPLETE');二、删除分区以下代码删除了P3表分区：ALTERTABLESALESDROPPARTITIONP3;在以下代码删除了P4SUB1子分区：ALTERTABLESALESDROPSUBPARTITIONP4SUB1;注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。三、截断分区截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：ALTERTABLESALESTRUNCATEPARTITIONP2;通过以下代码截断子分区：ALTERTABLESALESTRUNCATESUBPARTITIONP2SUB2;四、合并分区合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1P2分区的合并：ALTERTABLESALESMERGEPARTITIONSP1,P2INTOPARTITIONP2;五、拆分分区拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。ALTERTABLESALESSBLITPARTITIONP2AT(TO_DATE('2003-02-01','YYYY-MM-DD'))INTO(PARTITIONP21,PARTITIONP22);六、接合分区(coalesca)结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：ALTERTABLESALESCOALESCAPARTITION;七、重命名表分区以下代码将P21更改为P2ALTERTABLESALESRENAMEPARTITIONP21TOP2;九、跨分区查询selectsum(*)from((selectcount(*)cnfromt_table_SSPARTITION(P200709_1)unionallselectcount(*)cnfromt_table_SSPARTITION(P200709_2));十、查询表上有多少分区SELECT*FROMuse