Oracle分区表详细讲解课件

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,Oracle,数据库的表分区,2024/8/29,1,Oracle数据库的表分区2023/9/41,分区表的由来,当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间,(,物理文件上,),，这样查询数据时，不至于每次都扫描整张表。,从应用程序的角度来看，分区后的表与非分区表完全相同，使用,SQL DML,命令访问分区后的表时，无需任何修改。,2024/8/29,2,分区表的由来当表中的数据量不断增大，查询数据的速度就会变慢，,分区表概述,大数据对象（表、索引）被分成小物理段,当分区表建立时，记录基于分区字段值被存储到相应分区,分区字段值可以修改,.(row movement enabled),分区可以存储在不同的表空间,分区可以有不同的物理存储参数,分区支持,IOT,表、对象表、,LOB,字段,Varrays,等,2024/8/29,3,分区表概述大数据对象（表、索引）被分成小物理段2023/9/,什么时候使用分区表,表的大小：当表的大小超过,2GB,，或对于,OLTP,系统，表记录超过,1000,万时，都应该考虑对表进行分区,数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样的表进行分区，可充分利用分区排除无关数据查询的特性,数据维护：按时间段删除成批的数据，对于这样的表需要考虑进行分区，以满足维护需要,2024/8/29,4,什么时候使用分区表表的大小：当表的大小超过2GB，或对于OL,性能,Select,和,DML,只访问指定分区,提高检索速度,并行,DML,操作,Partition,wise join,可管理性,历史数据清除,提高备份性能,指定分区的数据维护操作,可用性,将故障局限在分区中,缩短恢复时间,分区目标优先级,高性能,-,数据维护能力,-,实施难度,-,高可用性,(,故障屏蔽能力）,使用分区表的好处,2024/8/29,5,性能使用分区表的好处2023/9/45,分区表的分类,1,、范围分区,(,Range,),2,、列表分区,(,List,),3,、散列分区,(,Hash,),4,、组合范围散列分区,5,、复合范围散列分区,2024/8/29,6,分区表的分类1、范围分区 (Range)2023/9/46,范围分区,范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期。如：将销售数据按照月份进行分区,2024/8/29,7,范围分区范围分区将数据基于范围映射到每一个分区，这个范围是你,范围分区时的规则,使用范围分区时，需要注意以下几个规则,1,、每一个分区都必须有一个,VALUES LESS THEN,子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。,2,、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。,3,、在最高的分区中定义了,MAXVALUE,。,MAXVALUE,代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的,VALUE LESS THEN,的值，同时包括空值。,2024/8/29,8,范围分区时的规则使用范围分区时，需要注意以下几个规则2023,范围分区例,1,假设有一个,CUSTOMER,表，表中有数据,200000,行，我们将此表通过,CUSTOMER_ID,进行分区，每个分区存储,100000,行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘,2024/8/29,9,范围分区例1假设有一个CUSTOMER表，表中有数据2000,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 ),2024/8/29,10,2023/9/410,按时间划分,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,),2024/8/29,11,按时间划分2023/9/411,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 );,2024/8/29,12,MAXVALUE2023/9/412,范围分区的特点,最早、最经典的分区方法,Range,分区通过对分区字段值的范围进行分区,Range,分区特别适合于按时间周期进行数据的存储：日、周、月、年等,数据管理能力强,数据迁移,数据备份,数据交换,范围分区的数据可能不均匀,范围分区与记录值有关，实施难度和可维护性相对较差,2024/8/29,13,范围分区的特点最早、最经典的分区方法2023/9/413,列表,(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);,2024/8/29,14,列表(List)分区如果某列的值只有几个，可以采用列表分区2,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 );,),2024/8/29,15,CREATE TABLE ListTable(,列表分区的特点,List,分区通过对分区字段的离散值进行分区,List,分区是不排序的，而且分区之间也没有关联,List,分区适合于对数据离散值进行控制,List,分区只支持单个字段,List,分区具有与,range,分区相似的优缺点：,数据管理能力强,各分区的数据可能不均匀,2024/8/29,16,列表分区的特点List分区通过对分区字段的离散值进行分区20,散列,(hash),分区,这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。,散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在,I/O,设备上进行散列分区，使得这些分区大小一致。,2024/8/29,17,散列(hash)分区这类分区是在列值上使用散列算法，以确定将,例一：,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 ),2024/8/29,18,例一： 2023/9/418,简写：,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);,2024/8/29,19,简写：2023/9/419,hash,分区最主要的机制是根据,hash,算法来计算具体某条纪录应该插入到哪个分区中,hash,算法中最重要的是,hash,函数，,Oracle,中如果你要使用,hash,分区，只需指定分区的数量即可。建议分区的数量采用,2,的,n,次方，这样可以使得各个分区间数据分布更加均匀。,2024/8/29,20,hash分区最主要的机制是根据hash算法来计算具体某条纪录,Hash,分区的特点,基于分区字段的,hash,值，自动将记录插入到相应分区,分区一般是,2,的幂,易于实施,总体性能最佳,适合于静态数据,Hash,分区适合于数据的均匀存储,Hash,分区特别适合于,PDML,和,Partition-wise join,支持（,hash,）,local/global indexes,数据管理能力强,Hash,分区对数据值无法控制,2024/8/29,21,Hash分区的特点基于分区字段的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);,2024/8/29,22,组合(Composite)分区CREATE TABLE qu,组合分区的特点,Oracle,支持的,Composite,分区：,range-hash,Range-list,既适合于历史数据，又适合于数据均匀分布,与范围分区一样提高可用性和可管理性,更好的,PDML,和,partition-wise joins,特性,实现粒度更细的操作,支持符合,local indexes,不支持符合,global indexes,2024/8/29,23,组合分区的特点Oracle支持的Composite分区：20,组合范围散列分区,这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，,然后再按某列进行散列分区， 分区之中的分区被称为子分区。,2024/8/29,24,组合范围散列分区这种分区是基于范围分区和散列分区，表首先按某,2024/8/29,25,2023/9/425,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) );,2024/8/29,26,create table dinya_test (,组合范围列表分区,这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，,然后再按某列进行列表分区,2024/8/29,27,组合范围列表分区这种分区是基于范围分区和列表分区，表首先按某,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 ) ),2024/8/29,28,CREATE TABLE SALES ( 2023/9/4,如何选定不同类型的分区表,何时选择范围分区,必须可以将表的记录按照某一列值的范围进行划分。你想处理一些数据，这些数据经常是属于某一个范围内，例如月份。如果数据能够按照分区的范围均匀分布的话，那会获得最佳性能。如果数据分布很不均匀的话，你可能不得不选择其他分区方式。,2024/8/29,29,如何选定不同类型的分区表何时选择范围分区必须可以将表的记,何时选择,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);,2024/8/29,30,何时选择HASH分区如果数据不容易用范围分区，但你想提升性,何时选择列表分区,使用,LIST,分区你可以直接控制某些数据映射到某些分区。你可以为某个分区指定不连续的分区键值。这不同于,RANGE,分区（用键值的范围划分分区），也不同于,HASH,分区（不能控制某行映射到哪个分区）。,2024/8/29,31,何时选择列表分区使用LIST分区你可以直接控制某些数据映,不同类型的分区表对性能的影响,关于范围分区,Range,：,要想将行映射到基于列值范围的分区，就使用范围分区方法。当数据可以被划分成逻辑范围时如年度中的月份，这种类型的分区就有用了。当数据在整个范围中能被均等地划分时性能最好。如果靠范围的分区会由于不均等的划分而导致分区在大小上明显不同时，就需要考虑其他的分区方法。,2024/8/29,32,不同类型的分区表对性能的影响关于范围分区Range：2023,不同类型的分区表对性能的影响,关于散列分区,Hash,：,如果数据不那么容易进行范围分区，但为了性能和管理的原因又想分区时，就使用散列分区方法。散列分区提供了一种在指定数量的分区中均等地划分数据的方法。基于分区键的散列值将行映射到分区中。创建和使用散列分区会给你提供了一种很灵活的放置数据的方法，因为你可以通过在,I/O,驱动器之间播撒,(,摘掉,),这些均等定量的分区，来影响可用性和性能。,2024/8/29,33,不同类型的分区表对性能的影响关于散列分区Hash：2023/,关于列表分区,List,：,当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。可以在每个分区的描述中为该分区列指定一列离散值，这不同于范围分区，在那里一个范围与一个分区相关，这也不同于散列分区，在那里用户不能控制如何将行映射到分区。列表分区方法是特意为遵从离散值的模块化数据划分而设计的。范围分区或散列分区不那么容易做到这一点。进一步说列表分区可以非常自然地将无序的和不相关的数据集进行分组和组织到一起。,与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。,2024/8/29,34,关于列表分区List：2023/9/434,不同类型的分区表对性能的影响,关于组合范围,-,散列分区：,范围和散列技术的组合，首先对表进行范围分区，然后用散列技术对每个范围分区再次分区。给定的范围分区的所有子分区加在一起表示数据的逻辑子集。,2024/8/29,35,不同类型的分区表对性能的影响关于组合范围-散列分区：2023,不同类型的分区表对性能的影响,关于组合范围,-,列表分区：,范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围,-,散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。,创建或更改分区表时可以指定行移动子句，即,ENABLE ROW MOVEMENT,或者,DISABLE ROW MOVEMENT,，当其键被更改时，该子句启用或停用将行迁移到一个新的分区。默认值为,DISABLE ROW MOVEMENT,。,2024/8/29,36,不同类型的分区表对性能的影响关于组合范围-列表分区：2023,分区索引,分区表索引的分类,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,索引,2024/8/29,37,分区索引分区表索引的分类2023/9/437,分区索引,Global index,必须是范围分区,9i,以前,Global index,可以是,hash,分区,10g,新特性,Global index,不支持,bitmap index,Unique index,必须是,prefixed,，或者包含分区字段,Local index,（,non-prefixed,non-unique),可以不包含分区字段,2024/8/29,38,分区索引Global index必须是范围分区9i以前,分区索引示例,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);,2024/8/29,39,分区索引示例CREATE INDEX month_ix ON,分区表索引的使用,OLTP,系统中的建议,Global,和,unique local index,性能优于,nonunique local index,Local index,提供了更好的可用性,决策支持系统中的建议,Local index,更适合于数据装载和分区维护,在大量数据统计时，能充分利用,local index,的并行查询能力,在性能、高可用和可管理性之间进行平衡,2024/8/29,40,分区表索引的使用OLTP系统中的建议2023/9/440,分区索引策略选择,索引字段是表分区字段的前缀？,Local prefixed,yes,该非分区字段是唯一索引？,global prefixed,yes,是否性能在可承受范围，而分区的可管理性、可用性更重要？,Local non-prefixed,是数据仓库,/,交易系统？,yes,Local non-prefixed,Global prefixed,DSS,OLTP,NO,NO,NO,2024/8/29,41,分区索引策略选择索引字段是表分区字段的前缀？Local pr,分区表设计原则,表的大小：当表的大小超过,2GB,，或对于,OLTP,系统，表记录超过,1000,万时，都应该考虑对表进行分区,数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样的表进行分区，可充分利用分区排除无关数据查询的特性,数据维护：按时间段删除成批的数据，对于这样的表需要考虑进行分区，以满足维护需要,数据备份和恢复：按时间周期进行表空间备份时，将分区与表空间建立对应关系,只读数据：如果一个表中大部分数据都是只读，通过对表进行分区，可将只读数据存储在只读表空间，对于数据的备份有利,OLAP,并行数据操作,2024/8/29,42,分区表设计原则表的大小：当表的大小超过2GB，或对于OLTP,分区表的管理功能,分区的增加,分区的删除,分区的合并,merge,分区的清空,truncate,分区的交换,exchange,分区的压缩,coalese,分区的移动,move,分区的分离,split,修改分区的物理属性,分区的更名,rename,2024/8/29,43,分区表的管理功能分区的增加2023/9/443,分区交换功能,通过交换数据段，实现分区和非分区表的数据交换，以及子分区和分区表的数据交换,非常快捷的数据移动方式。特别是没有,validation,和索引维护操作时,Local,索引自动维护,Global,索引必须重建,2024/8/29,44,分区交换功能通过交换数据段，实现分区和非分区表的数据交换，以,分区交换的应用,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;,2024/8/29,45,分区交换的应用ETL2023/9/445,现有系统实施分区的经验,分区对象的确定，存储空间中最大的前,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,综合其他分区因素的考虑：性能、数据迁移、备份、高可用、可维护性,2024/8/29,46,现有系统实施分区的经验分区对象的确定，存储空间中最大的前10,分区的评估,性能方面,响应速度,资源消耗（,CPU/IO/MEMORY),性能分析工具：,autotrace,，,sqltrace,，,tkprof,其他方面,数据迁移能力,数据备份和恢复,数据可扩展性,数据高可用性,2024/8/29,47,分区的评估性能方面2023/9/447,2024/8/29,48,2023/9/448,