Oracle_的分区技术7830.pptx

《Oracle_的分区技术7830.pptx》由会员分享，可在线阅读，更多相关《Oracle_的分区技术7830.pptx（24页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle 性能调整 Oracle Oracle的分区技术的分区技术的分区技术的分区技术 分区技术内容什么是分区什么是分区?分区的好分区的好处处？如何如何实实施分区？施分区？如何如何评评估分区的效果？估分区的效果？分区概述大数据大数据对对象（表、索引）被分成小物理段象（表、索引）被分成小物理段当分区表建立当分区表建立时时，记录记录基于分区字段基于分区字段值值被存被存储储到相到相应应分区分区分区字段分区字段值值可以修改可以修改.(row movement enabled).(row movement enabled)分区可以存分区可以存储储在不同的表空在不同的表空间间分区可以有不同的物理存分区

2、可以有不同的物理存储储参数参数分区支持分区支持IOTIOT表、表、对对象表、象表、LOBLOB字段字段VarraysVarrays等等性能性能l lSelectSelect和和DMLDML只只访问访问指定分区指定分区l l并行并行DMLDML操作操作l lPartitionPartitionwise joinwise join可管理性可管理性l l历历史数据清除史数据清除l l提高提高备备份性能份性能l l指定分区的数据指定分区的数据维护维护操作操作可用性可用性l l将故障局限在分区中将故障局限在分区中l l缩缩短恢复短恢复时间时间分区目分区目标优标优先先级级l l高性能高性能-数据数据维护维

3、护能力能力-实实施施难难度度-高可用性高可用性(故障屏蔽能力）故障屏蔽能力）分区技术的效益和目标分区方法分区方法分区方法l lRange partition -8Range partition -8l lHash partition -8iHash partition -8il lList partition -9iList partition -9il lComposite partition-8iComposite partition-8iRange 分区示例CREATE TABLE sales_range(salesman_id NUMBER(5),CREATE TABLE sales_

4、range(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_amount NUMBER(10),salesman_name VARCHAR2(30),sales_amount NUMBER(10),sales_date DATE)COMPRESS PARTITION BY RANGE(sales_date DATE)COMPRESS PARTITION BY RANGE(sales_datesales_date)()(PARTITION sales_jan2000 VALUES LESS PARTITION sales_jan200

5、0 VALUES LESS THAN(TO_DATE(02/01/2000,DD/MM/YYYY),PARTITION THAN(TO_DATE(02/01/2000,DD/MM/YYYY),PARTITION sales_feb2000 VALUES LESS sales_feb2000 VALUES LESS THAN(TO_DATE(03/01/2000,DD/MM/YYYY),PARTITION THAN(TO_DATE(03/01/2000,DD/MM/YYYY),PARTITION sales_mar2000 VALUES LESS sales_mar2000 VALUES LES

6、S THAN(TO_DATE(04/01/2000,DD/MM/YYYY),PARTITION THAN(TO_DATE(04/01/2000,DD/MM/YYYY),PARTITION sales_apr2000 VALUES LESS sales_apr2000 VALUES LESS THAN(TO_DATE(05/01/2000,DD/MM/YYYY);THAN(TO_DATE(05/01/2000,DD/MM/YYYY);分区字段分区字段Values less thanValues less than必必须须是确定是确定值值，最后一个可以是，最后一个可以是maxvaluemaxval

7、ue每个分区可以每个分区可以单单独指定物理属性独指定物理属性Range分区的特点最早、最最早、最经经典的分区方法典的分区方法RangeRange分区通分区通过对过对分区字段分区字段值值的范的范围进围进行分区行分区RangeRange分区特分区特别别适合于按适合于按时间时间周期周期进进行数据的存行数据的存储储：日、周、月、年：日、周、月、年等等数据管理能力数据管理能力强强l l数据迁移数据迁移l l数据数据备备份份l l数据交数据交换换范范围围分区的数据可能不均匀分区的数据可能不均匀范范围围分区与分区与记录值记录值有关，有关，实实施施难难度和可度和可维护维护性相性相对较对较差差Hash 分区CR

8、EATE TABLE sales_hash(salesman_id NUMBER(5),CREATE TABLE sales_hash(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_amount salesman_name VARCHAR2(30),sales_amount NUMBER(10),week_no NUMBER(2)NUMBER(10),week_no NUMBER(2)PARTITION BY HASH(salesman_id)PARTITION BY HASH(salesman_id)PARTITIONS 4;P

9、ARTITIONS 4;Hash分区的特点基于分区字段的基于分区字段的hashhash值值，自，自动动将将记录记录插入到相插入到相应应分区分区分区一般是分区一般是2 2的的幂幂易于易于实实施施总总体性能最佳体性能最佳适合于静适合于静态态数据数据Hash Hash 分区适合于数据的均匀存分区适合于数据的均匀存储储HashHash分区特分区特别别适合于适合于PDMLPDML和和Partition-wise joinPartition-wise join支持（支持（hashhash）local indexeslocal indexes9i9i不支持不支持 hash global indexeshas

10、h global indexes，10g10g开始支持开始支持数据管理能力数据管理能力强强HashHash分区分区对对数据数据值值无法控制无法控制List 分区CREATE TABLE sales_list(salesman_id NUMBER(5),CREATE TABLE sales_list(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_state salesman_name VARCHAR2(30),sales_state VARCHAR2(20),sales_amount NUMBER(10),VARCHAR2(20),

11、sales_amount NUMBER(10),sales_date DATE)sales_date DATE)PARTITION BY LIST(sales_state)PARTITION BY LIST(sales_state)(PARTITION sales_west VALUES(California,Hawaii),PARTITION sales_west VALUES(California,Hawaii),PARTITION sales_east VALUES(New York,Virginia,PARTITION sales_east VALUES(New York,Virgin

12、ia,Florida),Florida),PARTITION sales_central VALUES(Texas,Illinois),PARTITION sales_central VALUES(Texas,Illinois),PARTITION sales_other VALUES(DEFAULT);PARTITION sales_other VALUES(DEFAULT);List 分区的特点ListList分区通分区通过对过对分区字段的离散分区字段的离散值进值进行分区行分区ListList分区是不排序的，而且分区之分区是不排序的，而且分区之间间也没有关也没有关联联ListList分区适

13、合于分区适合于对对数据离散数据离散值进值进行控制行控制ListList分区只支持分区只支持单单个字段个字段ListList分区具有与分区具有与rangerange分区相似的分区相似的优优缺点：缺点：l l数据管理能力数据管理能力强强各分区的数据可能不均匀各分区的数据可能不均匀Composite 分区CREATE TABLE quarterly_regional_sales(deptno NUMBER,item_no VARCHAR2(20),CREATE TABLE quarterly_regional_sales(deptno NUMBER,item_no VARCHAR2(20),txn_

14、date DATE,txn_amount NUMBER,state VARCHAR2(2)txn_date DATE,txn_amount NUMBER,state VARCHAR2(2)PARTITION BY RANGE(txn_date)SUBPARTITION BY LIST(state)PARTITION BY RANGE(txn_date)SUBPARTITION BY LIST(state)(PARTITION q1_1999 VALUES LESS THAN(TO_DATE(1-APR-1999,DD-MON-YYYY)(PARTITION q1_1999 VALUES LES

15、S THAN(TO_DATE(1-APR-1999,DD-MON-YYYY)(SUBPARTITION q1_1999_northwest VALUES(OR,WA),(SUBPARTITION q1_1999_northwest VALUES(OR,WA),SUBPARTITION q1_1999_southwest VALUES(AZ,UT,NM),SUBPARTITION q1_1999_southwest VALUES(AZ,UT,NM),SUBPARTITION q1_1999_northeast VALUES(NY,VM,NJ),SUBPARTITION q1_1999_north

16、east VALUES(NY,VM,NJ),SUBPARTITION q1_1999_southeast VALUES(FL,GA),SUBPARTITION q1_1999_southeast VALUES(FL,GA),SUBPARTITION q1_1999_northcentral VALUES(SD,WI),SUBPARTITION q1_1999_northcentral VALUES(SD,WI),SUBPARTITION q1_1999_southcentral VALUES(NM,TX),SUBPARTITION q1_1999_southcentral VALUES(NM,

17、TX),PARTITION q2_1999 VALUES LESS THAN(TO_DATE(1-JUL-1999,DD-MON-YYYY)PARTITION q2_1999 VALUES LESS THAN(TO_DATE(1-JUL-1999,DD-MON-YYYY)(SUBPARTITION q2_1999_northwest VALUES(OR,WA),(SUBPARTITION q2_1999_northwest VALUES(OR,WA),SUBPARTITION q2_1999_southwest VALUES(AZ,UT,NM),SUBPARTITION q2_1999_sou

18、thwest VALUES(AZ,UT,NM),SUBPARTITION q2_1999_northeast VALUES(NY,VM,NJ),SUBPARTITION q2_1999_northeast VALUES(NY,VM,NJ),SUBPARTITION q2_1999_southeast VALUES(FL,GA),SUBPARTITION q2_1999_southeast VALUES(FL,GA),SUBPARTITION q2_1999_northcentral VALUES(SD,WI),SUBPARTITION q2_1999_northcentral VALUES(S

19、D,WI),SUBPARTITION q2_1999_southcentral VALUES(NM,TX),SUBPARTITION q2_1999_southcentral VALUES(NM,TX),PARTITION q3_1999 VALUES LESS THAN(TO_DATE(1-OCT-1999,DD-MON-YYYY)PARTITION q3_1999 VALUES LESS THAN(TO_DATE(1-OCT-1999,DD-MON-YYYY)(SUBPARTITION q3_1999_northwest VALUES(OR,WA),(SUBPARTITION q3_199

20、9_northwest VALUES(OR,WA),.SUBPARTITION q4_1999_northcentral VALUES(SD,WI),SUBPARTITION q4_1999_northcentral VALUES(SD,WI),SUBPARTITION q4_1999_southcentral VALUES(NM,TX);SUBPARTITION q4_1999_southcentral VALUES(NM,TX);Composite 分区的特点OracleOracle支持的支持的CompositeComposite分区：分区：range-hashrange-hash Ran

21、ge-listRange-list既适合于既适合于历历史数据，又适合于数据均匀分布史数据，又适合于数据均匀分布与范与范围围分区一分区一样样提高可用性和可管理性提高可用性和可管理性更好的更好的PDMLPDML和和partition-wise joinspartition-wise joins特性特性实现实现粒度更粒度更细细的操作的操作支持符合支持符合local indexeslocal indexes不支持符合不支持符合 global indexesglobal indexes分区索引分区表索引的分分区表索引的分类类l lLocal prefixed indexLocal prefixed in

22、dexl lLocal non-prefixed indexLocal non-prefixed indexl lGlobal prefixed indexGlobal prefixed indexl lNon partition indexNon partition indexGlobal index Global index 的分区不同于表分区的分区不同于表分区LocalLocal索引的分区与表分区相同索引的分区与表分区相同An index is prefixed if it is partitioned on a left prefix of An index is prefixed i

23、f it is partitioned on a left prefix of the index columnsthe index columns分区表上的非分区索引等同于分区表上的非分区索引等同于global global 索引索引分区索引Global indexGlobal index必必须须是范是范围围分区分区9i9i以前以前Global indexGlobal index可以是可以是hash hash 分区分区10g10g新特性新特性Global indexGlobal index不支持不支持bitmap indexbitmap indexUnique index Unique in

24、dex 必必须须是是prefixedprefixed，或者包含分区字段，或者包含分区字段Local indexLocal index（non-prefixed,non-unique)non-prefixed,non-unique)可以不包含分区字可以不包含分区字段段分区索引示例CREATE INDEX month_ix ON sales(sales_month)CREATE INDEX month_ix ON sales(sales_month)GLOBAL PARTITION BY RANGE(sales_month)GLOBAL PARTITION BY RANGE(sales_month

25、)(PARTITION pm1_ix VALUES LESS THAN(2)(PARTITION pm1_ix VALUES LESS THAN(2)PARTITION pm2_ix VALUES LESS THAN(3)PARTITION pm2_ix VALUES LESS THAN(3)PARTITION pm3_ix VALUES LESS THAN(4)PARTITION pm3_ix VALUES LESS THAN(4)PARTITION pm4_ix VALUES LESS THAN(5)PARTITION pm4_ix VALUES LESS THAN(5)PARTITION

26、 pm5_ix VALUES LESS THAN(6)PARTITION pm5_ix VALUES LESS THAN(6)PARTITION pm6_ix VALUES LESS THAN(7)PARTITION pm6_ix VALUES LESS THAN(7)PARTITION pm7_ix VALUES LESS THAN(8)PARTITION pm7_ix VALUES LESS THAN(8)PARTITION pm8_ix VALUES LESS THAN(9)PARTITION pm8_ix VALUES LESS THAN(9)PARTITION pm9_ix VALU

27、ES LESS THAN(10)PARTITION pm9_ix VALUES LESS THAN(10)PARTITION pm10_ix VALUES LESS THAN(11)PARTITION pm10_ix VALUES LESS THAN(11)PARTITION pm11_ix VALUES LESS THAN(12)PARTITION pm11_ix VALUES LESS THAN(12)PARTITION pm12_ix VALUES LESS THAN(MAXVALUE);PARTITION pm12_ix VALUES LESS THAN(MAXVALUE);分区表索引

28、的使用OLTPOLTP系系统统中的建中的建议议l lGlobalGlobal和和unique local indexunique local index性能性能优优于于nonunique local indexnonunique local indexl lLocal indexLocal index提供了更好的可用性提供了更好的可用性决策支持系决策支持系统统中的建中的建议议l lLocal indexLocal index更适合于数据装更适合于数据装载载和分区和分区维护维护l l在大量数据在大量数据统计时统计时，能充分利用，能充分利用local indexlocal index的并行的并行查

29、询查询能力能力在性能、高可用和可管理性之在性能、高可用和可管理性之间进间进行平衡行平衡分区索引策略选择索引字段是表分区字段的前缀？Local prefixedyes该非分区字段是唯一索引？global prefixedyes是否性能在可承受范围，而分区的可管理性、可用性更重要？Local non-prefixed是数据仓库/交易系统？yesLocal non-prefixedGlobal prefixedDSSOLTPNONONO分区表设计原则表的大小：当表的大小超表的大小：当表的大小超过过2GB2GB，或，或对对于于OLTPOLTP系系统统，表，表记录记录超超过过10001000万万时时，都

30、，都应该应该考考虑对虑对表表进进行分区行分区数据数据访问访问特性：基于表的大部分特性：基于表的大部分查询应查询应用，只用，只访问访问表中少量的数据。表中少量的数据。对对于于这样这样的表的表进进行分区，可充分利用分区排除无关数据行分区，可充分利用分区排除无关数据查询查询的特性的特性数据数据维护维护：按：按时间时间段段删删除成批的数据，除成批的数据，对对于于这样这样的表需要考的表需要考虑进虑进行分行分区，以区，以满满足足维护维护需要需要数据数据备备份和恢复：按份和恢复：按时间时间周期周期进进行表空行表空间备间备份份时时，将分区与表空，将分区与表空间间建建立立对应对应关系关系只只读读数据：如果一个表

31、中大部分数据都是只数据：如果一个表中大部分数据都是只读读，通，通过对过对表表进进行分区，行分区，可将只可将只读读数据存数据存储储在只在只读读表空表空间间，对对于数据的于数据的备备份有利份有利OLAPOLAP并行数据操作并行数据操作分区表的管理功能分区的增加分区的增加分区的分区的删删除除分区的合并分区的合并mergemerge分区的清空分区的清空truncatetruncate分区的交分区的交换换exchangeexchange分区的分区的压缩压缩coalesecoalese分区的移分区的移动动movemove分区的分离分区的分离splitsplit修改分区的物理属性修改分区的物理属性分区的更名

32、分区的更名renamerename分区交换功能通通过过交交换换数据段，数据段，实现实现分区和非分区表的数据交分区和非分区表的数据交换换，以及，以及子分区和分区表的数据交子分区和分区表的数据交换换非常快捷的数据移非常快捷的数据移动动方式。特方式。特别别是没有是没有validationvalidation和索引和索引维维护护操作操作时时Local Local 索引自索引自动维护动维护GlobalGlobal索引必索引必须须重建重建分区交换的应用ETLETLl l在源系在源系统统中，将需要抽取的数据以如下中，将需要抽取的数据以如下语语句形式抽取到建立在句形式抽取到建立在单单独表空独表空间间的中的中间

33、间表上：表上：Create table as select Create table as select insert insert/*+append*/as select/*+append*/as select l l以以TTSTTS方式将中方式将中间间表的表空表的表空间传输间传输到数据到数据仓库仓库中中 exp transportable_tablespace=yesexp transportable_tablespace=yes FTPFTP imp transportable_tablespace=yesimp transportable_tablespace=yesl l在数据在数据

34、仓库仓库中中对对中中间间表表进进行各种数据行各种数据归归并清洗工作并清洗工作l l通通过过exchangeexchange技技术术，将中，将中间间表数据及索引直接交表数据及索引直接交换换到分区表中到分区表中 Alter table exchange partition Alter table exchange partition with table including with table including indexes;indexes;现有系统实施分区的经验分区分区对对象的确定，存象的确定，存储储空空间间中最大的前中最大的前1010个表个表Select *from Select *fr

35、om (select segment_name from dba_segments where segment_type=TABLE (select segment_name from dba_segments where segment_type=TABLE order by bytes desc)order by bytes desc)where rownum=10;where rownum=10;分析大表的操作行分析大表的操作行为为 select *from(select sql_text,executions from v$sqlarea select *from(select sql

36、_text,executions from v$sqlarea where upper(sql_text)like%TABNAME%order by executions where upper(sql_text)like%TABNAME%order by executions desc)where rownum=20desc)where rownum=20综综合其他分区因素的考合其他分区因素的考虑虑：性能、数据迁移、：性能、数据迁移、备备份、高可份、高可用、可用、可维护维护性性分区的评估性能方面性能方面l l响响应应速度速度l l资资源消耗（源消耗（CPU/IO/MEMORY)CPU/IO/MEMORY)l l性能分析工具：性能分析工具：autotrace autotrace，sqltracesqltrace，tkproftkprof其他方面其他方面l l数据迁移能力数据迁移能力l l数据数据备备份和恢复份和恢复l l数据可数据可扩扩展性展性l l数据高可用性数据高可用性