oracle分区技术-大批量数据操作.pptx

《oracle分区技术-大批量数据操作.pptx》由会员分享，可在线阅读，更多相关《oracle分区技术-大批量数据操作.pptx（73页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、Copyright 2006,Oracle.All rights reserved.OracleOracle数据库高级技术交流数据库高级技术交流-大批量数据处理技术大批量数据处理技术Oracle(中国中国)顾问咨询部顾问咨询部罗罗 敏敏 资深技术顾问资深技术顾问电话：电话：13321161702eMail:MCopyright 2006,Oracle.All rights reserved.交流内容交流内容分区技术报表优化技术 并行处理应用经验Copyright 2006,Oracle.All rights reserved.OracleOracle的分区技术的分区技术Copyright 20

2、06,Oracle.All rights reserved.分区技术内容分区技术内容什么是分区?分区的好处?如何实施分区?如何评估分区的效果?Copyright 2006,Oracle.All rights reserved.OracleOracle的分区技术基本原理的分区技术基本原理分而治之分而治之SB_ZSXX按年度进行分区按年度进行分区2003200420052006Copyright 2006,Oracle.All rights reserved.分区概述分区概述大数据对象(表,索引)被分成小物理段当分区表建立时，记录基于分区字段值被存储到相应分区。分区字段值可以修改。(row mov

3、ement enabled)分区可以存储在不同的表空间分区可以有不同的物理存储参数分区支持IOT表，对象表，LOB字段，varrays等Copyright 2006,Oracle.All rights reserved.分区技术的效益和目标分区技术的效益和目标性能Select和DML操作只访问指定分区并行DML操作Partition-wise Join可管理性：数据删除，数据备份历史数据清除提高备份性能指定分区的数据维护操作可用性将故障局限在分区中缩短恢复时间分区目标优先级 高性能 数据维护能力-实施难度 高可用性（故障屏蔽能力）Copyright 2006,Oracle.All rights

4、 reserved.分区方法分区方法分区方法:范围 -8Hash -8i列表 -9i组合 -8iRangepartitioningHashpartitioningCompositepartitioningListpartitioningCopyright 2006,Oracle.All rights reserved.123CREATE TABLE sales (acct_no NUMBER(5),person VARCHAR2(30),sales_amount NUMBER(8),week_no NUMBER(2)PARTITION BY RANGE(week_no)(PARTITION P

5、1 VALUES LESS THAN(4)TABLESPACE data0,PARTITION P2 VALUES LESS THAN(8)TABLESPACE data1,.PARTITION P13 VALUES LESS THAN(53)TABLESPACE data12 );分区字段：分区字段：week_no.VALUES LESS THAN 必须是确定值必须是确定值每个分区可以单独指定物理属性每个分区可以单独指定物理属性123范围分区例范围分区例Copyright 2006,Oracle.All rights reserved.最早、最经典的分区算法Range分区通过对分区字段值的范

6、围进行分区Range分区特别适合于按时间周期进行数据的存储。日、周、月、年等。数据管理能力强数据迁移数据备份数据交换范围分区的数据可能不均匀范围分区与记录值相关，实施难度和可维护性相对较差范围分区特点范围分区特点Copyright 2006,Oracle.All rights reserved.Hash分区例分区例create table CUSTOMERS(.column definitions.)pctfree 0 nologgingstorage(initial 40m next 40m pctincrease 0)partition by hash(customer_no)partit

7、ions 8 store in(cust_data01,cust_data02)create table CUSTOMERS(.column definitions.)pctfree 0 nologgingstorage(initial 40m next 40m pctincrease 0)partition by hash(customer_no)(partition cust_p01 tablespace cust_data01,partition cust_p02 tablespace cust_data02,partition cust_p03 tablespace cust_data

8、03,partition cust_p04 tablespace cust_data04,partition cust_p05 tablespace cust_data05,partition cust_p06 tablespace cust_data06,partition cust_p07 tablespace cust_data07,partition cust_p08 tablespace cust_data08)Copyright 2006,Oracle.All rights reserved.Hash分区特点分区特点基于分区字段的HASH值，自动将记录插入到指定分区。分区数一般是2

9、的幂易于实施总体性能最佳适合于静态数据HASH分区适合于数据的均匀存储 HASHHASHHASHHASH分区特别适合于分区特别适合于分区特别适合于分区特别适合于PDMLPDMLPDMLPDML和和和和partition-wise joinspartition-wise joinspartition-wise joinspartition-wise joins。支持(hash)local indexes9i 不支持(hash)global indexes10g 支持(hash)global indexes HASH分区数据管理能力弱HASH分区对数据值无法控制Copyright 2006,Ora

10、cle.All rights reserved.列表分区例列表分区例create table addresses(.column definitions.)pctfree 0 nologgingstorage(initial 40m next 40m pctincrease 0)partition by list(city_name)(partition addr_p01 values(WELLINGTON)tablespace addr_data01,partition addr_p02 values(CHRISTCHURCH)tablespace addr_data02,partition

11、 addr_p03 values(DUNEDIN,INVERCARGILL)tablespace addr_data03,partition addr_p04 values(AUCKLAND)tablespace addr_data04,partition addr_p05 values(HAMILTON,ROTORUA,TAURANGA)tablespace addr_data05)Copyright 2006,Oracle.All rights reserved.列表分区特点列表分区特点ListList分区通过对分区字段的离散值进行分区。分区通过对分区字段的离散值进行分区。ListList

12、分区是不排序的，而且分区之间没有关联关系分区是不排序的，而且分区之间没有关联关系ListList分区适合于对数据离散值进行控制。分区适合于对数据离散值进行控制。ListList分区只支持单个字段。分区只支持单个字段。ListList分区具有与范围分区相似的优缺点分区具有与范围分区相似的优缺点数据管理能力强ListList分区的数据可能不均匀ListList分区与记录值相关，实施难度和可维护性相对较差Copyright 2006,Oracle.All rights reserved.复合分区例复合分区例create table daily_trans_data(.column definitio

13、ns.)partition by range(trans_datetime)subpartition by hash(customer_no)subpartitions 8 store in(dtd_data01,dtd_data02)(partition dtd_20010620 values less than(to_date(21-jun-2001,dd-mon-yyyy)(subpartition dtd_20010620_s01 ,subpartition dtd_20010620_s02 ,subpartition dtd_20010620_s03 tablespace dtd_d

14、ata03 ,subpartition dtd_20010620_s04 tablespace dtd_data04 ,subpartition dtd_20010620_s05 tablespace dtd_data05 ,subpartition dtd_20010620_s06 tablespace dtd_data06 ,subpartition dtd_20010620_s07 tablespace dtd_data07 ,subpartition dtd_20010620_s08 tablespace dtd_data08 ),partition dtd_20010621 valu

15、es less than(to_date(22-jun-2001,dd-mon-yyyy),partition dtd_20010622 values less than(to_date(23-jun-2001,dd-mon-yyyy)subpartitions 4 )Copyright 2006,Oracle.All rights reserved.复合分区图示复合分区图示Copyright 2006,Oracle.All rights reserved.复合分区特点复合分区特点OracleOracle支持的支持的CompositeComposite分区：分区：Range-Hash,Rang

16、e-List Range-Hash,Range-List既适合于历史数据，又适合于数据均匀分布与范围分区一样提供高可用性和管理性更好的PDML和partition-wise joins性能实现粒度更细的操作支持复合 local indexes不支持复合compositeglobal indexes？Copyright 2006,Oracle.All rights reserved.分区索引分区索引不分区分区不分区 分区表表索引索引Copyright 2006,Oracle.All rights reserved.GlobalNonpartitioned indexLocal partition

17、ed indexGlobal Partitioned Index不同的分区索引不同的分区索引绍兴绍兴杭州杭州温州温州03年年04年年08年年Copyright 2006,Oracle.All rights reserved.分区索引分区索引分区表索引的分类：Local Prefixed indexLocal Non-prefiexed indexGlobal Prefixed indexNon Partition IndexGlobal索引的分区不同与表分区Local索引的分区与表分区相同An index is prefixed if it is partitioned on a left p

18、refix of the index columns.分区表上的非分区索引等同于Global索引Copyright 2006,Oracle.All rights reserved.分区索引分区索引Global索引必须是范围分区-9i之前Global索引可以是HASH分区-10g新特性Global索引不支持Bitmap索引Unique索引必须是prefixed，或者包含分区字段Local索引（non-prefixed,non-unique）可以不包含分区字段Copyright 2006,Oracle.All rights reserved.create index cust_idx1 on cu

19、stomers(customer_name)global partition by range(customer_name)(partition cust_p01 values less than(H)tablespace cust_index01,partition cust_p02 values less than(N)tablespace cust_index02,partition cust_p03 values less than(T)tablespace cust_index03,partition cust_p04 values less than(MAXVALUE)tables

20、pace cust_index04)create index cust_idx2 on customers(customer_no)local(partition cust_idx_p01 tablespace cust_index01,partition cust_idx_p02 tablespace cust_index02,partition cust_idx_p03 tablespace cust_index03,partition cust_idx_p04 tablespace cust_index04,partition cust_idx_p05 tablespace cust_i

21、ndex05,partition cust_idx_p06 tablespace cust_index06,partition cust_idx_p07 tablespace cust_index07,partition cust_idx_p08 tablespace cust_index08)create index cust_idx3 on customers(customer_type)local;分区索引举例分区索引举例Copyright 2006,Oracle.All rights reserved.分区表索引的使用分区表索引的使用OLTP系统中的建议Global和unique lo

22、cal index性能优于nonunique local indexLocal index提供了更好的可用性数据仓库系统中的建议Local index更适合于数据装载和分区维护在大量数据统计时，能充分利用Local index并行查询能力在性能、高可用性和可管理性之间进行平衡Copyright 2006,Oracle.All rights reserved.分区索引选择策略分区索引选择策略Copyright 2006,Oracle.All rights reserved.分区裁剪功能分区裁剪功能Partition pruning:Only the relevant partitions are

23、 accessed.99-May99-Apr99-Feb99-Jan99-Mar99-JunsalesSQL SELECT SUM(sales_amount)2 FROM sales 3 WHERE sales_date BETWEEN 4 TO_DATE(01-MAR-1999,5 DD-MON-YYYY)AND 6 TO_DATE(31-MAY-1999,7 DD-MON-YYYY);Copyright 2006,Oracle.All rights reserved.分区裁剪举例分区裁剪举例1 select*from daily_trans_summ 2*where trans_datet

24、ime between to_date(25-jun-2001 08,DD-mon-yyyy hh24)and to_date(28-jun-2001 18,DD-mon-yyyy hh24)Partition Partition Operation Options Object Name Start Stop -SELECT STATEMENT PARTITION RANGE ITERATOR 231 234 TABLE ACCESS FULL DAILY_TRANS_SUMM 231 234 1 select*from daily_trans_summ 2*where trans_date

25、time in(25-jun-2001,28-jun-2001)Partition Partition Operation Options Object Name Start Stop -SELECT STATEMENT PARTITION RANGE INLIST KEY(INLIST)KEY(INLIST)TABLE ACCESS FULL DAILY_TRANS_SUMM KEY(INLIST)KEY(INLIST)Copyright 2006,Oracle.All rights reserved.Nonpartition-wise joinFull partition-wise joi

26、nPartial partition-wise joinQuery slavePartitionPartitioned table123Partition-Wise JoinCopyright 2006,Oracle.All rights reserved.Partition-wise JoinsTables and indexes that are partitioned identically are equi-partitioned.A full partition-wise join occurs when joining two equi-partitioned tables tha

27、t are partitioned on the join key.Oracle splits the join into joins of pairs of partitions.A partial partition-wise join occurs when only one of the tables is partitioned on the join key.Partition-wise joins occur when joining a hash partition table to a composite partition table if the hash partiti

28、oning and sub-partitioning is on the join key.Oracle assigns parallel query slaves to process the partition joins.Copyright 2006,Oracle.All rights reserved.Partition-wise Joins举例举例1 select/*+full(c)*/c.customer_no,count(*)2 from customers c,daily_trans_data d3 where c.customer_no=d.customer_no4 and

29、d.trans_datetime between to_date(25-jun-2001,dd-mon-yyyy)5 and to_date(28-jun-2001,dd-mon-yyyy)6*group by c.customer_no Partition Partition Operation Options Object Name Start Stop -SELECT STATEMENT PARTITION HASH ALL 1 8 SORT GROUP BY HASH JOIN PARTITION RANGE ITERATOR 50 53 TABLE ACCESS FULL DAILY

30、_TRANS_DATA 393 424 TABLE ACCESS FULL CUSTOMERS 1 8 Copyright 2006,Oracle.All rights reserved.分区表设计原则分区表设计原则表的大小：当表的大小超过表的大小：当表的大小超过1.5GB2GB，或对于或对于OLTP系统，表系统，表的记录超过的记录超过1000万，都应考虑对表进行分区。万，都应考虑对表进行分区。数据访问特性：基于表的大部分查询应用，只访问表中少量的数数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询的据。对于这样表进行分区，可充分利用

31、分区排除无关数据查询的特性。特性。数据维护：按时间段删除成批的数据，例如按月删除历史数据。数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。对于这样的表需要考虑进行分区，以满足维护的需要。数据备份和恢复：数据备份和恢复：按时间周期进行表空间的备份时，将分区与表按时间周期进行表空间的备份时，将分区与表空间建立对应关系。空间建立对应关系。只读数据：如果一个表中大部分数据都是只读数据，通过对表进只读数据：如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份行分区，可将只读数据存储在只读表空间中，对

32、于数据库的备份是非常有益的。是非常有益的。并行数据操作：对于经常执行并行操作（如并行数据操作：对于经常执行并行操作（如Parallel Parallel Insert,Parallel UpdateInsert,Parallel Update等）的表应考虑进行分区。等）的表应考虑进行分区。表的可用性：当对表的部分数据可用性要求很高时，应考虑进行表的可用性：当对表的部分数据可用性要求很高时，应考虑进行表分区。表分区。Copyright 2006,Oracle.All rights reserved.分区表的管理功能分区表的管理功能分区的增加（分区的增加（ADD）分区的删除（分区的删除（DROP）

33、分区的合并（分区的合并（MERGE）分区的清空分区的清空（TRUNCATE）分区的交换分区的交换（EXCHANGE）分区的压缩分区的压缩（COALESE）分区的移动分区的移动（MOVE）分区的分离分区的分离（SPLIT）修改分区的修改分区的Default Attribute分区的更名（分区的更名（RENAME）Copyright 2006,Oracle.All rights reserved.分区索引的管理功能分区索引的管理功能分区索引的删除（分区索引的删除（分区索引的删除（分区索引的删除（DROPDROP）分区索引的修改（分区索引的修改（分区索引的修改（分区索引的修改（MODIFYMODIF

34、Y）分区索引分区索引分区索引分区索引Default AttributeDefault Attribute的修改的修改的修改的修改分区索引的重建分区索引的重建分区索引的重建分区索引的重建（REBUILDREBUILD）分区索引的更名（分区索引的更名（分区索引的更名（分区索引的更名（RENAMERENAME）分区索引的分离分区索引的分离分区索引的分离分区索引的分离（SPLITSPLIT）分区索引的分区索引的分区索引的分区索引的UnusableUnusableCopyright 2006,Oracle.All rights reserved.分区表和分区表和Local索引索引OCTOCT200220

35、02SEPSEP20022002AUGAUG20022002NOVNOV 2001 2001OCTOCT20012001新月份数据的加载和索引的维护新月份数据的加载和索引的维护NOVNOV20022002NOVNOV20022002NOVNOV20022002NOVNOV20022002NOVNOV20022002NOVNOV20022002NOVNOV20022002NOVNOV20022002NOVNOV20022002NOVNOV20022002.“滚动窗口滚动窗口”操作操作-大量数据高速装大量数据高速装载载Copyright 2006,Oracle.All rights reserve

36、d.OCTOCT20022002SEPSEP20022002NOVNOV20022002NOVNOV 2001 2001NOVNOV 2001 2001OCTOCT20012001OCTOCT20022002SEPSEP20022002NOVNOV20022002DECDEC 2001 2001NOVNOV 2001 2001OCTOCT20012001OCTOCT20022002SEPSEP20022002NOVNOV20022002DECDEC 2001 2001NOVNOV 2001 2001OCTOCT20012001OCTOCT20022002SEPSEP20022002NOVNOV

37、20022002DECDEC 2001 2001NOVNOV 2001 2001OCTOCT20012001OCTOCT20022002SEPSEP20022002NOVNOV20022002DECDEC 2001 2001NOVNOV 2001 2001OCTOCT20012001OCTOCT20022002SEPSEP20022002NOVNOV20022002DECDEC 2001 2001NOVNOV 2001 2001OCTOCT20012001OCTOCT20022002SEPSEP20022002NOVNOV20022002DECDEC 2001 2001NOVNOV 2001

38、2001OCTOCT20012001OCTOCT20022002SEPSEP20022002NOVNOV20022002DECDEC 2001 2001NOVNOV 2001 2001OCTOCT20012001NOVNOV20022002删除或归档最老月份的数据删除或归档最老月份的数据OCTOCT20012001OCT2001新月份数据的加载和索引的维护新月份数据的加载和索引的维护.分区表和分区表和Local索引索引“滚动窗口滚动窗口”操作操作-大量数据高速装大量数据高速装载载Copyright 2006,Oracle.All rights reserved.分区交换功能分区交换功能通过交换

39、数据段，实现分区和非分区表的数据交换。以及子分区和分区表的数据交换非常快捷的数据移动方式。特别是没有validation和索引维护操作时Local 索引自动维护Global索引必须重建Copyright 2006,Oracle.All rights reserved.分区交换的应用分区交换的应用-全文检索全文检索（1）1：00数数据的加载据的加载（2）建立建立context 索引索引（3）partition的的exchangeBF_DXX表表*初始化工作初始化工作*整理工作整理工作Copyright 2006,Oracle.All rights reserved.分区交换的应用分区交换的应用-

40、全文检索全文检索第一步：1：00数据的加载insert into BF_DXX_stage(SJ,TEXT3)values(to_date(2004.03.02,YYYY.MM.DD),大撒反对撒);第二步：建立context 索引CREATE INDEX IDX_ BF_DXX _STAGE ON BF_DXX_stage(text3)INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS(LEXER MYLEXER STORAGE MYSTORE FILTER CTXSYS.NULL_FILTER MEMORY 100M)parallel 4;第三步：partitio

41、n的交换alter table BF_DXX exchange partition p2 with table BF_DXX_stage including indexes;Copyright 2006,Oracle.All rights reserved.迁移表空间迁移表空间(Transportable Transportable Tablespace)Tablespace)技术简介技术简介 第一步：exp transport_tablespace=yes第二步：FTP 数据文件和dmp文件第三步：imp transport_tablespace=yes地市系统地市系统地市系统地市系统imp

42、卸载文件卸载文件省级系统省级系统省级系统省级系统FTPFTP数据文件数据文件卸载文件卸载文件数据文件数据文件expCopyright 2006,Oracle.All rights reserved.迁移表空间技术的作用迁移表空间技术的作用业务系统数据向数据仓库系统的迁移 对业务系统和数据仓库系统的数据进行定期归档 数据仓库向数据集市的数据迁移 数据对外发布 按表空间进行时间点的数据恢复(TSPITR)Copyright 2006,Oracle.All rights reserved.迁移表空间技术的优点迁移表空间技术的优点性能大大高于export/import或PL/SQL编写的程序 由于Dm

43、p文件只包含表空间的结构信息，因此该技术的真正开销在于数据文件的传输。对源系统的影响非常小 只需要将被迁移的表空间设置为只读方式 可同时传输索引数据，避免在目的数据库中重建索引 Copyright 2006,Oracle.All rights reserved.分区交换的应用分区交换的应用-ETL在源系统中，将需要抽取的数据以如下语句形式，抽取到建立在单独表空间上的中间表中：CREATE TABLE.AS SELECT INSERT/*+APPEND*/AS SELECT 以TTS方式将中间表的表空间传输到数据仓库之中。exp transportable_tablespace=Yes FTP

44、中间表表空间的数据文件imp transportable_tablespace=Yes 在数据仓库中对中间表进行各种数据归并等清洗工作，并建立需要的各种索引。通过exchange技术，将中间表数据及索引直接交换到分区表中。Alter table exchange partition with table including indexes;Copyright 2006,Oracle.All rights reserved.分区交换的应用分区交换的应用-重复记录删除重复记录删除问题描述：在使用SQL*Loader进行数据加载sor_acct_dcc_saamt_c表时，由于操作失误，重复加载，导

45、致分区ETL_LOAD_DATE_0606出现重复记录，也使得两个唯一索引：IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2的ETL_LOAD_DATE_0606分区不可用（UNUSABLE）。用户在试图重新创建该分区索引时，出现如下错误：SQL alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606;alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606*ORA-01452:cannot CREATE UNIQUE INDE

46、X;duplicate keys found Copyright 2006,Oracle.All rights reserved.分区交换的应用分区交换的应用-重复记录删除重复记录删除在试图删除该分区的重复记录时，又出现如下错误：SQL delete from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0606)where rowid not in(select min(rowid)from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0606)group by ETL_LOAD_DATE,CUST_

47、ACCT_NO,SA_CURR_COD,SA_CURR_IDEN);*ORA-01502:index GYFX.IDX_SAACNAMT_C_1 or partition of such index is in unusable stateCopyright 2006,Oracle.All rights reserved.分区交换的应用分区交换的应用-重复记录删除重复记录删除简单办法是彻底删除这两个唯一索引，重新创建。数据量大，时间太长。影响系统的可用性。更完备的解决方式创建一个与sor_acct_dcc_saamt_c结构一样的临时表test。SQL create table test as

48、 select*from sor_acct_dcc_saamt_c where 1=2;将sor_acct_dcc_saamt_c表分区ETL_LOAD_DATE_0606数据交换到临时表test。SQL alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 with table test;Copyright 2006,Oracle.All rights reserved.分区交换的应用分区交换的应用-重复记录删除重复记录删除更完备的解决方式删除test中的重复记录 delete from test whe

49、re rowid not in(select min(rowid)from test group by ETL_LOAD_DATE,CUST_ACCT_NO,SA_CURR_COD,SA_CURR_IDEN);因为test表没有任何索引，可避免上述ORA-01502错误。将临时表test数据交换回sor_acct_dcc_saamt_c表分区ETL_LOAD_DATE_0606。alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 with table test;Copyright 2006,Oracle.

50、All rights reserved.分区交换的应用分区交换的应用-重复记录删除重复记录删除更完备的解决方式重新创建创建该分区索引IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2 alter index IDX_SAACNAMT_C_1 rebuild partition ETL_LOAD_DATE_0606 tablespace ETL0_R_LOAD_IDX_200606;alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606 tablespace ETL0_R_LOAD_IDX_200606;