Oracle分区表详细讲解.ppt

Oracle数据库的表分区,分区表的由来,当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。 从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。,分区表概述,大数据对象（表、索引）被分成小物理段 当分区表建立时，记录基于分区字段值被存储到相应分区 分区字段值可以修改.(row movement enabled) 分区可以存储在不同的表空间 分区可以有不同的物理存储参数 分区支持IOT表、对象表、LOB字段Varrays等,什么时候使用分区表,表的大小：当表的大小超过2GB，或对于OLTP系统，表记录超过1000万时，都应该考虑对表进行分区 数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样的表进行分区，可充分利用分区排除无关数据查询的特性 数据维护：按时间段删除成批的数据，对于这样的表需要考虑进行分区，以满足维护需要,性能 Select和DML只访问指定分区,提高检索速度 并行DML操作 Partitionwise join 可管理性 历史数据清除 提高备份性能 指定分区的数据维护操作 可用性 将故障局限在分区中 缩短恢复时间 分区目标优先级 高性能-数据维护能力-实施难度-高可用性(故障屏蔽能力）,使用分区表的好处,分区表的分类,1、范围分区 (Range) 2、列表分区 (List) 3、散列分区 (Hash) 4、组合范围散列分区 5、复合范围散列分区,范围分区,范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。如：将销售数据按照月份进行分区,范围分区时的规则,使用范围分区时，需要注意以下几个规则 1、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。 2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。 3、在最高的分区中定义了MAXVALUE。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。,范围分区例1,假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘,CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY 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 ),按时间划分 CREATE 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(01-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_tb, PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb );,范围分区的特点,最早、最经典的分区方法 Range分区通过对分区字段值的范围进行分区 Range分区特别适合于按时间周期进行数据的存储：日、周、月、年等 数据管理能力强 数据迁移 数据备份 数据交换 范围分区的数据可能不均匀 范围分区与记录值有关，实施难度和可维护性相对较差,列表(List)分区,如果某列的值只有几个，可以采用列表分区 CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_state VARCHAR2(20), sales_amount NUMBER(10), sales_date DATE) PARTITION BY LIST(sales_state) ( PARTITION sales_west VALUES(California, Hawaii), PARTITION sales_east VALUES (New York, Virginia, Florida), PARTITION sales_central VALUES(Texas, Illinois), PARTITION sales_other VALUES(DEFAULT);,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) TABLESPACE Part2_tb ); ),列表分区的特点,List分区通过对分区字段的离散值进行分区 List分区是不排序的，而且分区之间也没有关联 List分区适合于对数据离散值进行控制 List分区只支持单个字段 List分区具有与range分区相似的优缺点： 数据管理能力强 各分区的数据可能不均匀,散列(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, PARTITION 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次方，这样可以使得各个分区间数据分布更加均匀。,Hash分区的特点,基于分区字段的hash值，自动将记录插入到相应分区 分区一般是2的幂 易于实施 总体性能最佳 适合于静态数据 Hash 分区适合于数据的均匀存储 Hash分区特别适合于PDML和Partition-wise join 支持（hash）local/global indexes 数据管理能力强 Hash分区对数据值无法控制,组合(Composite)分区,CREATE TABLE quarterly_regional_sales (deptno NUMBER, item_no VARCHAR2(20), txn_date DATE, txn_amount NUMBER, state VARCHAR2(2) PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) ( PARTITION q1_1999 VALUES LESS THAN(TO_DATE(1-APR-1999,DD-MON-YYYY) (SUBPARTITION q1_1999_northwest VALUES (OR, WA), SUBPARTITION q1_1999_southwest VALUES (AZ, UT, NM), SUBPARTITION q1_1999_northeast VALUES (NY, VM, NJ), SUBPARTITION q1_1999_southeast VALUES (FL, GA), SUBPARTITION q1_1999_northcentral VALUES (SD, WI), SUBPARTITION q1_1999_southcentral VALUES (NM, TX), PARTITION q2_1999 VALUES LESS THAN(TO_DATE(1-JUL-1999,DD-MON-YYYY) (SUBPARTITION q2_1999_northwest VALUES (OR, WA), SUBPARTITION q2_1999_southwest VALUES (AZ, UT, NM), SUBPARTITION q2_1999_northeast VALUES (NY, VM, NJ), SUBPARTITION q2_1999_southeast VALUES (FL, GA), SUBPARTITION q2_1999_northcentral VALUES (SD, WI), SUBPARTITION q2_1999_southcentral VALUES (NM, TX), PARTITION q3_1999 VALUES LESS THAN (TO_DATE(1-OCT-1999,DD-MON-YYYY) (SUBPARTITION q3_1999_northwest VALUES (OR, WA), . SUBPARTITION q4_1999_northcentral VALUES (SD, WI), SUBPARTITION q4_1999_southcentral VALUES (NM, TX);,组合分区的特点,Oracle支持的Composite分区： range-hash Range-list 既适合于历史数据，又适合于数据均匀分布 与范围分区一样提高可用性和可管理性 更好的PDML和partition-wise joins特性 实现粒度更细的操作 支持符合local indexes 不支持符合 global indexes,组合范围散列分区,这种分区是基于范围分区和散列分区，表首先按某列进行范围分区， 然后再按某列进行散列分区， 分区之中的分区被称为子分区。,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)subpartition 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) );,组合范围列表分区,这种分区是基于范围分区和列表分区，表首先按某列进行范围分区， 然后再按某列进行列表分区,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 (ACTIVE) 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 ) ),如何选定不同类型的分区表,何时选择范围分区必须可以将表的记录按照某一列值的范围进行划分。你想处理一些数据，这些数据经常是属于某一个范围内，例如月份。如果数据能够按照分区的范围均匀分布的话，那会获得最佳性能。如果数据分布很不均匀的话，你可能不得不选择其他分区方式。,何时选择HASH分区,如果数据不容易用范围分区，但你想提升性能和表的易管理性。 Hash分区提供了一个在指定数量的分区内交叉均匀分布数据的方法。行根据分区键的hash值映射到相应分区中。 创建和使用hash分区你可以灵活放置数据，可以通过交叉访问在不同I/O设备上的分区提升性能。 CREATE TABLE scubagear (id NUMBER, name VARCHAR2 (60)PARTITION BY HASH (id)PARTITIONS 4 STORE IN (gear1, gear2, gear3, gear4);,何时选择列表分区,使用LIST分区你可以直接控制某些数据映射到某些分区。你可以为某个分区指定不连续的分区键值。这不同于RANGE分区（用键值的范围划分分区），也不同于HASH分区（不能控制某行映射到哪个分区）。,不同类型的分区表对性能的影响,关于范围分区Range： 要想将行映射到基于列值范围的分区，就使用范围分区方法。当数据可以被划分成逻辑范围时如年度中的月份，这种类型的分区就有用了。当数据在整个范围中能被均等地划分时性能最好。如果靠范围的分区会由于不均等的划分而导致分区在大小上明显不同时，就需要考虑其他的分区方法。,不同类型的分区表对性能的影响,关于散列分区Hash： 如果数据不那么容易进行范围分区，但为了性能和管理的原因又想分区时，就使用散列分区方法。散列分区提供了一种在指定数量的分区中均等地划分数据的方法。基于分区键的散列值将行映射到分区中。创建和使用散列分区会给你提供了一种很灵活的放置数据的方法，因为你可以通过在I/O 驱动器之间播撒(摘掉)这些均等定量的分区，来影响可用性和性能。,关于列表分区List： 当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。可以在每个分区的描述中为该分区列指定一列离散值，这不同于范围分区，在那里一个范围与一个分区相关，这也不同于散列分区，在那里用户不能控制如何将行映射到分区。列表分区方法是特意为遵从离散值的模块化数据划分而设计的。范围分区或散列分区不那么容易做到这一点。进一步说列表分区可以非常自然地将无序的和不相关的数据集进行分组和组织到一起。 与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。,不同类型的分区表对性能的影响,关于组合范围-散列分区： 范围和散列技术的组合，首先对表进行范围分区，然后用散列技术对每个范围分区再次分区。给定的范围分区的所有子分区加在一起表示数据的逻辑子集。,不同类型的分区表对性能的影响,关于组合范围-列表分区： 范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。 创建或更改分区表时可以指定行移动子句，即ENABLE ROW MOVEMENT 或者DISABLE ROW MOVEMENT ，当其键被更改时，该子句启用或停用将行迁移到一个新的分区。默认值为DISABLE ROW MOVEMENT。,分区索引,分区表索引的分类 Local prefixed index Local non-prefixed index Global prefixed index Non partition index Global index 的分区不同于表分区 Local索引的分区与表分区相同 An index is prefixed if it is partitioned on a left prefix of the index columns 分区表上的非分区索引等同于global 索引,分区索引,Global index必须是范围分区9i以前 Global index可以是hash 分区10g新特性 Global index不支持bitmap index Unique index 必须是prefixed，或者包含分区字段 Local index（non-prefixed,non-unique)可以不包含分区字段,分区索引示例,CREATE INDEX month_ix ON sales(sales_month) GLOBAL PARTITION BY RANGE(sales_month) (PARTITION pm1_ix VALUES LESS THAN (2) PARTITION pm2_ix VALUES LESS THAN (3) PARTITION pm9_ix VALUES LESS THAN (10) PARTITION pm10_ix VALUES LESS THAN (11) PARTITION pm11_ix VALUES LESS THAN (12) PARTITION pm12_ix VALUES LESS THAN (MAXVALUE);,分区表索引的使用,OLTP系统中的建议 Global和unique local index性能优于nonunique local index Local index提供了更好的可用性 决策支持系统中的建议 Local index更适合于数据装载和分区维护 在大量数据统计时，能充分利用local index的并行查询能力 在性能、高可用和可管理性之间进行平衡,分区索引策略选择,索引字段是表分区字段的前缀？,Local prefixed,yes,该非分区字段是唯一索引？,global prefixed,yes,是否性能在可承受范围，而分区的可管理性、可用性更重要？,Local non-prefixed,是数据仓库/交易系统？,yes,Local non-prefixed,Global prefixed,DSS,OLTP,NO,NO,NO,分区表设计原则,表的大小：当表的大小超过2GB，或对于OLTP系统，表记录超过1000万时，都应该考虑对表进行分区 数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样的表进行分区，可充分利用分区排除无关数据查询的特性 数据维护：按时间段删除成批的数据，对于这样的表需要考虑进行分区，以满足维护需要 数据备份和恢复：按时间周期进行表空间备份时，将分区与表空间建立对应关系 只读数据：如果一个表中大部分数据都是只读，通过对表进行分区，可将只读数据存储在只读表空间，对于数据的备份有利 OLAP并行数据操作,分区表的管理功能,分区的增加 分区的删除 分区的合并merge 分区的清空truncate 分区的交换exchange 分区的压缩coalese 分区的移动move 分区的分离split 修改分区的物理属性 分区的更名rename,分区交换功能,通过交换数据段，实现分区和非分区表的数据交换，以及子分区和分区表的数据交换 非常快捷的数据移动方式。特别是没有validation和索引维护操作时 Local 索引自动维护 Global索引必须重建,分区交换的应用,ETL 在源系统中，将需要抽取的数据以如下语句形式抽取到建立在单独表空间的中间表上： Create table as select insert /*+ append */ as select 以TTS方式将中间表的表空间传输到数据仓库中 exp transportable_tablespace=yes FTP imp transportable_tablespace=yes 在数据仓库中对中间表进行各种数据归并清洗工作 通过exchange技术，将中间表数据及索引直接交换到分区表中 Alter table exchange partition with table including indexes;,现有系统实施分区的经验,分区对象的确定，存储空间中最大的前10个表 Select * from (select segment_name from dba_segments where segment_type=TABLE order by bytes desc) where rownum<=10; 分析大表的操作行为 select * from (select sql_text ,executions from v$sqlarea where upper(sql_text) like %TABNAME% order by executions desc) where rownum<=20 综合其他分区因素的考虑：性能、数据迁移、备份、高可用、可维护性,分区的评估,性能方面 响应速度 资源消耗（CPU/IO/MEMORY) 性能分析工具：autotrace ，sqltrace，tkprof 其他方面 数据迁移能力 数据备份和恢复 数据可扩展性 数据高可用性,