Oracle分区表详细讲解

《Oracle分区表详细讲解》由会员分享，可在线阅读，更多相关《Oracle分区表详细讲解（47页珍藏版）》请在装配图网上搜索。

1、分区表的由来 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。分区表概述什么时候使用分区表 表的大小：当表的大小超过2GB，或对于OLTP系统，表记录超过1000万时，都应该考虑对表进行分区 数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样的表进行分区，可充分利用分区排除无关数据

2、查询的特性 数据维护：按时间段删除成批的数据，对于这样的表需要考虑进行分区，以满足维护需要分区表的分类1、范围分区()2、列表分区()3、散列分区()4、组合范围散列分区5、复合范围散列分区范围分区范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。如：将销售数据按照月份进行分区范围分区时的规则使用范围分区时，需要注意以下几个规则1、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。2、所有

3、分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。3、在最高的分区中定义了MAXVALUE。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。范围分区例1假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘 CREATE TABLE CUSTOMER(CUSTOMER_ID NUMBER NOT NULL PRI

4、MARY KEY,FIRST_NAME VARCHAR2(30)NOT NULL,LAST_NAME VARCHAR2(30)NOT NULL,PHONE VARCHAR2(15)NOT NULL,EMAIL VARCHAR2(80),STATUS CHAR(1)PARTITION BY RANGE(CUSTOMER_ID)(PARTITION CUS_PART1 VALUES LESS THAN(100000)TABLESPACE CUS_TS01,PARTITION CUS_PART2 VALUES LESS THAN(200000)TABLESPACE CUS_TS02)按时间划分CRE

5、ATE TABLE ORDER_ACTIVITIES(ORDER_ID NUMBER(7)NOT NULL,ORDER_DATE DATE,TOTAL_AMOUNT NUMBER,CUSTOTMER_ID NUMBER(7),PAID CHAR(1)PARTITION BY RANGE(ORDER_DATE)(PARTITION ORD_ACT_PART01 VALUES LESS THAN(TO_DATE(01-MAY-2003,DD-MON-YYYY)TABLESPACEORD_TS01,PARTITION ORD_ACT_PART02 VALUES LESS THAN(TO_DATE(0

6、1-JUN-2003,DD-MON-YYYY)TABLESPACE ORD_TS02,PARTITION ORD_ACT_PART02 VALUES LESS THAN(TO_DATE(01-JUL-2003,DD-MON-YYYY)TABLESPACE ORD_TS03)MAXVALUE CREATE TABLE RangeTable(idd INT PRIMARY KEY,iNAME VARCHAR(10),grade INT )PARTITION BY RANGE(grade)(PARTITION part1 VALUES LESS THEN(1000)TABLESPACE Part1_

7、tb,PARTITION part2 VALUES LESS THEN(MAXVALUE)TABLESPACE Part2_tb);列表(List)分区 如果某列的值只有几个，可以采用列表分区 CREATE TABLE ListTable(id INT PRIMARY KEY,name VARCHAR(20),area VARCHAR(10)PARTITION BY LIST(area)(PARTITION part1 VALUES(guangdong,beijing)TABLESPACE Part1_tb,PARTITION part2 VALUES(shanghai,nanjing)TAB

8、LESPACE Part2_tb);)散列(hash)分区这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。例一：CREATE TABLE HASH_TABLE(COL NUMBER(8),INF VARCHAR2(100)PARTITION BY HASH(COL)(PARTITION PART01 TABLESPACE HASH_TS01,PARTITION PART02 TABLESPACE HASH_TS02,PARTIT

9、ION PART03 TABLESPACE HASH_TS03)简写：CREATE TABLE emp(empno NUMBER(4),ename VARCHAR2(30),sal NUMBER)PARTITION BY HASH(empno)PARTITIONS 8STORE IN(emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分

10、区间数据分布更加均匀。SUBPARTITION q4_1999_southcentral VALUES(NM,TX);组合范围散列分区这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区，分区之中的分区被称为子分区。create table dinya_test (transaction_id number primary key,item_id number(8)not null,item_description varchar2(300),transaction_date date )partition by range(transaction_date)s

11、ubpartition by hash(transaction_id)subpartitions 3 store in(dinya_space01,dinya_space02,dinya_space03)(partition part_01 values less than(to_date(2006-01-01,yyyy-mm-dd),partition part_02 values less than(to_date(2010-01-01,yyyy-mm-dd),partition part_03 values less than(maxvalue);组合范围列表分区这种分区是基于范围分区和

12、列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区CREATE TABLE SALES(PRODUCT_ID VARCHAR2(5),SALES_DATE DATE,SALES_COST NUMBER(10),STATUS VARCHAR2(20)PARTITION BY RANGE(SALES_DATE)SUBPARTITION BY LIST(STATUS)(PARTITION P1 VALUES LESS THAN(TO_DATE(2003-01-01,YYYY-MM-DD)TABLESPACE rptfact2009 (SUBPARTITION P1SUB1 VALUES(A

13、CTIVE)TABLESPACE rptfact2009,SUBPARTITION P1SUB2 VALUES(INACTIVE)TABLESPACE rptfact2009 ),PARTITION P2 VALUES LESS THAN(TO_DATE(2003-03-01,YYYY-MM-DD)TABLESPACE rptfact2009 (SUBPARTITION P2SUB1 VALUES(ACTIVE)TABLESPACE rptfact2009,SUBPARTITION P2SUB2 VALUES(INACTIVE)TABLESPACE rptfact2009 )如何选定不同类型的

14、分区表何时选择范围分区何时选择范围分区 必须可以将表的记录按照某一列值的范围进行划分。你想处理一些数据，这些数据经常是属于某一个范围内，例如月份。如果数据能够按照分区的范围均匀分布的话，那会获得最佳性能。如果数据分布很不均匀的话，你可能不得不选择其他分区方式。何时选择HASH分区 如果数据不容易用范围分区，但你想提升性能和表的易管理性。Hash分区提供了一个在指定数量的分区内交叉均匀分布数据的方法。行根据分区键的hash值映射到相应分区中。创建和使用hash分区你可以灵活放置数据，可以通过交叉访问在不同I/O设备上的分区提升性能。CREATE TABLE scubagear(id NUMBER

15、,name VARCHAR2(60)PARTITION BY HASH(id)PARTITIONS 4 STORE IN(gear1,gear2,gear3,gear4);何时选择列表分区使用LIST分区你可以直接控制某些数据映射到某些分区。你可以为某个分区指定不连续的分区键值。这不同于RANGE分区（用键值的范围划分分区），也不同于HASH分区（不能控制某行映射到哪个分区）。不同类型的分区表对性能的影响 关于范围分区Range：要想将行映射到基于列值范围的分区，就使用范围分区方法。当数据可以被划分成逻辑范围时如年度中的月份，这种类型的分区就有用了。当数据在整个范围中能被均等地划分时性能最好。

16、如果靠范围的分区会由于不均等的划分而导致分区在大小上明显不同时，就需要考虑其他的分区方法。不同类型的分区表对性能的影响 关于散列分区Hash：如果数据不那么容易进行范围分区，但为了性能和管理的原因又想分区时，就使用散列分区方法。散列分区提供了一种在指定数量的分区中均等地划分数据的方法。基于分区键的散列值将行映射到分区中。创建和使用散列分区会给你提供了一种很灵活的放置数据的方法，因为你可以通过在I/O 驱动器之间播撒(摘掉)这些均等定量的分区，来影响可用性和性能。关于列表分区List：当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。可以在每个分区的描述中为该分区列指定一列离散值，这不

17、同于范围分区，在那里一个范围与一个分区相关，这也不同于散列分区，在那里用户不能控制如何将行映射到分区。列表分区方法是特意为遵从离散值的模块化数据划分而设计的。范围分区或散列分区不那么容易做到这一点。进一步说列表分区可以非常自然地将无序的和不相关的数据集进行分组和组织到一起。与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。不同类型的分区表对性能的影响关于组合范围-散列分区：范围和散列技术的组合，首先对表进行范围分区，然后用散列技术对每个范围分区再次分区

18、。给定的范围分区的所有子分区加在一起表示数据的逻辑子集。不同类型的分区表对性能的影响 关于组合范围-列表分区：范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。创建或更改分区表时可以指定行移动子句，即ENABLE ROW MOVEMENT 或者DISABLE ROW MOVEMENT，当其键被更改时，该子句启用或停用将行迁移到一个新的分区。默认值为DISABLE ROW MOVEMENT。索引字段是表分区字段的前缀？Local prefixedyes该非分区字段是唯一索引？global prefixedyes是否性能在可承受范围，而分区的可管理性、可用性更重要？Local non-prefixed是数据仓库/交易系统？yesLocal non-prefixedGlobal prefixedDSSOLTPNONONO