oracle分区技术-大批量数据操作ppt课件

1、Oracle数据库高级技术交流-大批量数据处置技术Oracle(中国)顾问咨询部罗 敏 资深技术顾问 Michael.luooracle交流内容分区技术报表优化技术 并行处置运用阅历Oracle的分区技术 分区技术内容什么是分区?分区的益处?如何实施分区?如何评价分区的效果? Oracle的分区技术根本原理分而治之SB_ZSXX按年度进展分区2003200420052006分区概述大数据对象 (表, 索引)被分成小物理段当分区表建立时，记录基于分区字段值被存储到相应分区。分区字段值可以修正。(row movement enabled)分区可以存储在不同的表空间分区可以

2、有不同的物理存储参数分区支持IOT表，对象表，LOB字段，varrays等分区技术的效益和目的性能Select和DML操作只访问指定分区并行DML操作Partition-wise Join可管理性：数据删除，数据备份历史数据去除提高备份性能指定分区的数据维护操作可用性将缺点局限在分区中缩短恢复时间分区目的优先级 高性能 数据维护才干-实施难度 高可用性缺点屏蔽才干分区方法分区方法:范围 - 8Hash - 8i列表 - 9i组合 - 8iRangepartitioningHashpartitioningCompositepartitioningListpartitioning123CREATE

3、 TABLE sales (acct_no NUMBER(5), person VARCHAR2(30), sales_amount NUMBER(8), week_no NUMBER(2) PARTITION BY RANGE (week_no) (PARTITION P1 VALUES LESS THAN (4) TABLESPACE data0, PARTITION P2 VALUES LESS THAN (8) TABLESPACE data1, . PARTITION P13 VALUES LESS THAN (53)TABLESPACE data12 );分区字段：week_no.

4、VALUES LESS THAN 必需是确定值每个分区可以单独指定物理属性123范围分区例最早、最经典的分区算法Range分区经过对分区字段值的范围进展分区Range分区特别适宜于按时间周期进展数据的存储。日、周、月、年等。数据管理才干强数据迁移数据备份数据交换范围分区的数据能够不均匀范围分区与记录值相关，实施难度和可维护性相对较差范围分区特点Hash分区例create table CUSTOMERS (. column definitions .)pctfree 0 nologgingstorage ( initial 40m next 40m pctincrease 0 )partitio

5、n by hash(customer_no) partitions 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,pa

6、rtition cust_p03 tablespace cust_data03,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)Hash分区特点基于分区字段的HASH值，自动将记录插入到指定分区。分区数普通是2的幂易于实施总体性

7、能最正确适宜于静态数据HASH分区适宜于数据的均匀存储HASH分区特别适宜于PDML和partition-wise joins。支持 (hash) local indexes9i 不支持 (hash) global indexes10g 支持(hash) global indexes HASH分区数据管理才干弱HASH分区对数据值无法控制列表分区例create table addresses (. column definitions .)pctfree 0 nologgingstorage ( initial 40m next 40m pctincrease 0 )partition by

8、list(city_name) (partition addr_p01 values (WELLINGTON) tablespace addr_data01,partition addr_p02 values (CHRISTCHURCH) tablespace addr_data02,partition addr_p03 values (DUNEDIN,INVERCARGILL) tablespace addr_data03,partition addr_p04 values (AUCKLAND) tablespace addr_data04,partition addr_p05 values

9、 (HAMILTON,ROTORUA,TAURANGA) tablespace addr_data05)列表分区特点List分区经过对分区字段的离散值进展分区。List分区是不排序的，而且分区之间没有关联关系List分区适宜于对数据离散值进展控制。List分区只支持单个字段。List分区具有与范围分区类似的优缺陷数据管理才干强List分区的数据能够不均匀List分区与记录值相关，实施难度和可维护性相对较差复合分区例create table daily_trans_data (.column definitions .)partition by range(trans_datetime)subp

10、artition 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_data03 ,subpartition dtd_20010620_s04

11、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 values less than (to_date(22-jun-2001,dd

12、-mon-yyyy) ,partition dtd_20010622 values less than (to_date(23-jun-2001,dd-mon-yyyy) subpartitions 4 )复合分区图示复合分区特点Oracle支持的Composite分区： Range-Hash,Range-List既适宜于历史数据，又适宜于数据均匀分布与范围分区一样提供高可用性和管理性更好的PDML和partition-wise joins性能实现粒度更细的操作支持复合 local indexes不支持复合compositeglobal indexes？分区索引不分区分区不分区 分区表索引Gl

13、obalNonpartitioned indexLocal partitioned indexGlobal Partitioned Index不同的分区索引绍兴杭州温州03年04年08年分区索引分区表索引的分类：Local Prefixed indexLocal Non-prefiexed indexGlobal Prefixed indexNon Partition IndexGlobal索引的分区不同与表分区Local索引的分区与表分区一样An index is prefixed if it is partitioned on a left prefix of the index colu

14、mns.分区表上的非分区索引等同于Global索引分区索引Global索引必需是范围分区 - 9i之前Global索引可以是HASH分区 - 10g新特性Global索引不支持Bitmap索引Unique索引必需是prefixed，或者包含分区字段Local索引non-prefixed, non-unique可以不包含分区字段create index cust_idx1 on customers(customer_name)global partition by range (customer_name)(partition cust_p01 values less than (H) tabl

15、espace 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) tablespace cust_index04)create index cust_idx2 on customers(customer_no) local(partition cust_idx_p01 tab

16、lespace 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_index05,partition cust_idx_p06 tablespace cust_index06,partition cust_idx_p07 tablespace cust_index

17、07,partition cust_idx_p08 tablespace cust_index08)create index cust_idx3 on customers(customer_type) local;分区索引举例分区表索引的运用OLTP系统中的建议Global和unique local index性能优于nonunique local indexLocal index提供了更好的可用性数据仓库系统中的建议Local index更适宜于数据装载和分区维护在大量数据统计时，能充分利用Local index并行查询才干在性能、高可用性和可管理性之间进展平衡分区索引选择战略分区裁剪功能P

18、artition pruning: Only the relevant partitions are 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);分区裁剪举例1 select * from daily_trans_summ 2* where tr

19、ans_datetime 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_s

20、umm 2* where trans_datetime 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) Nonpartition-wise joinFull partition-wise joinPar

21、tial partition-wise joinQuery slavePartitionPartitioned table123Partition-Wise JoinPartition-wise JoinsTables and indexes that are partitioned identically are equi-partitioned. A full partition-wise join occurs when joining two equi-partitioned tables that are partitioned on the join key. Oracle spl

22、its 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 partitioning and sub-partitioning is on the join ke

23、y.Oracle assigns parallel query slaves to process the partition joins.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 d.trans_datetime between to_date(25-jun-2001,dd-mon-yyyy)5 and to_date(28-jun-20

24、01,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_TRANS_DATA 393 424 TABLE ACCESS FULL CUSTOMERS 1 8 分区表设计原那么表的大小：当表的大小

25、超越1.5GB2GB，或对于OLTP系统，表的记录超越1000万，都应思索对表进展分区。 数据访问特性：基于表的大部分查询运用，只访问表中少量的数据。对于这样表进展分区，可充分利用分区排除无关数据查询的特性。 数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需求思索进展分区，以满足维护的需求。数据备份和恢复： 按时间周期进展表空间的备份时，将分区与表空间建立对应关系。只读数据：假设一个表中大部分数据都是只读数据，经过对表进展分区，可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。 并行数据操作：对于经常执行并行操作如Parallel Insert,Paralle

26、l Update等的表应思索进展分区。 表的可用性：当对表的部分数据可用性要求很高时，应思索进展表分区。 分区表的管理功能分区的添加ADD分区的删除DROP分区的合并MERGE分区的清空TRUNCATE分区的交换EXCHANGE分区的紧缩COALESE分区的挪动MOVE分区的分别SPLIT修正分区的Default Attribute分区的更名RENAME分区索引的管理功能分区索引的删除DROP分区索引的修正MODIFY分区索引Default Attribute的修正分区索引的重建REBUILD分区索引的更名RENAME分区索引的分别SPLIT分区索引的Unusable分区表和Local索引OC

27、T2002SEP2002AUG2002NOV 2001OCT2001新月份数据的加载和索引的维护NOV2002NOV2002NOV2002NOV2002NOV2002NOV2002NOV2002NOV2002NOV2002NOV2002.“滚动窗口操作 - 大量数据高速装载OCT2002SEP2002NOV2002NOV 2001NOV 2001OCT2001OCT2002SEP2002NOV2002DEC 2001NOV 2001OCT2001OCT2002SEP2002NOV2002DEC 2001NOV 2001OCT2001OCT2002SEP2002NOV2002DEC 2001NO

28、V 2001OCT2001OCT2002SEP2002NOV2002DEC 2001NOV 2001OCT2001OCT2002SEP2002NOV2002DEC 2001NOV 2001OCT2001OCT2002SEP2002NOV2002DEC 2001NOV 2001OCT2001OCT2002SEP2002NOV2002DEC 2001NOV 2001OCT2001NOV2002删除或归档最老月份的数据OCT2001OCT2001新月份数据的加载和索引的维护.分区表和Local索引“滚动窗口操作 - 大量数据高速装载分区交换功能经过交换数据段，实现分区和非分区表的数据交换。以及子分区

29、和分区表的数据交换非常快捷的数据挪动方式。特别是没有validation和索引维护操作时Local 索引自动维护Global索引必需重建分区交换的运用- 全文检索12：00分区 BF_DXX_stage中间表11：00数据的加载 2建立context 索引 3partition的exchangeBF_DXX表* 初始化任务* 整理任务 2：00分区1：00分区 分区交换的运用- 全文检索第一步：1：00数据的加载insert into BF_DXX_stage(SJ,TEXT3) values(to_date(2004.03.02,YYYY.MM.DD),大撒反对撒);第二步：建立contex

30、t 索引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;第三步：partition的交换alter table BF_DXX exchange partition p2 with table BF_DXX_stage including indexes;迁移表空间(Transportable Tab

31、lespace)技术简介 第一步：exp transport_tablespace=yes第二步：FTP 数据文件和dmp文件第三步：imp transport_tablespace=yes地市系统imp卸载文件省级系统FTP数据文件卸载文件数据文件exp迁移表空间技术的作用业务系统数据向数据仓库系统的迁移 对业务系统和数据仓库系统的数据进展定期归档 数据仓库向数据集市的数据迁移 数据对外发布 按表空间进展时间点的数据恢复(TSPITR) 迁移表空间技术的优点性能大大高于export/import或PL/SQL编写的程序 由于Dmp文件只包含表空间的构造信息，因此该技术的真正开销在于数据文件的

32、传输。 对源系统的影响非常小 只需求将被迁移的表空间设置为只读方式 可同时传输索引数据，防止在目的数据库中重建索引 分区交换的运用- ETL在源系统中，将需求抽取的数据以如下语句方式，抽取到建立在单独表空间上的中间表中： CREATE TABLE . AS SELECT INSERT /*+ APPEND */ AS SELECT 以TTS方式将中间表的表空间传输到数据仓库之中。 exp transportable_tablespace=Yes FTP 中间表表空间的数据文件imp transportable_tablespace=Yes 在数据仓库中对中间表进展各种数据归并等清洗任务，并建立

33、需求的各种索引。 经过exchange技术，将中间表数据及索引直接交换到分区表中。 Alter table exchange partition with table including indexes; 分区交换的运用-反复记录删除问题描画： 在运用SQL*Loader进展数据加载sor_acct_dcc_saamt_c表时，由于操作失误，反复加载，导致分区ETL_LOAD_DATE_0606出现反复记录，也使得两个独一索引：IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2的ETL_LOAD_DATE_0606分区不可用UNUSABLE。用户在试图重新创建该分区索引时，出现

34、如下错误：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 INDEX; duplicate keys found 分区交换的运用-反复记录删除在试图删除该分区的反复记录时，又出现如下错误：SQL delete from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0

35、606)where rowid not in (select min(rowid) from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0606) group by ETL_LOAD_DATE, CUST_ACCT_NO, SA_CURR_COD, SA_CURR_IDEN);*ORA-01502: index GYFX.IDX_SAACNAMT_C_1 or partition of such index is in unusable state分区交换的运用-反复记录删除简一方法是彻底删除这两个独一索引，重新创建。 数据量大，时间太长。影响系

36、统的可用性。 更完备的处理方式创建一个与sor_acct_dcc_saamt_c构造一样的暂时表test。 SQL create table test as 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; 分区交换的运用-反复记录删除更完备的处理方式删除tes

37、t中的反复记录 delete from test where 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 w

38、ith table test; 分区交换的运用-反复记录删除更完备的处理方式重新创建创建该分区索引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; 此时反复记录曾经删除，可防止上

39、述ORA-01452错误 现有系统实施分区的阅历分区对象确实定：存储空间最大的前20个表Select * from (Select * from dba_segments order by bytes desc) where rownum = 20;分析大表的操作行为Select * from (Select sql_text,executions from v$sqlarea where upper(sql_text) like %SB_ZSXX% order by executions desc) where rownum CREATE MATERIALIZED VIEW sales_summary 2 TABLESPACE users 3 PARALLEL (DEGREE 4) 4 BUILD IMMEDIATE 5 ENABLE QUERY REWRITE 6 AS 7 SELECT d_name, 8 SUM (s.quantity_sold), 8 SUM (s.amount_sold) 9 FROM sales s, products p 10 WHERE d_id = d_id 11 GROUP BY d_name; 物化视图创建和查询重写例SQL SELECT d_name,SUM (s.quantity_sold), 2 SUM (s.amou