Oracle分区技术

<Insert Picture Here>Oracle技术交流----分区技术内容分区技术概述分区表和分区索引技术如何运用分区技术11g 分区新特性分区实施经验及案例分区实施服务性能海量数据库设计的关键技术---分区••Select和DML操作只访问指定分区•并行DML操作•Partition-wise Join•可管理性：数据删除，数据备份•2005历史数据清除•提高备份性能•指定分区的数据维护操作按年度进行分区2006•可用性•将故障局限在分区中•2007缩短恢复时间•分区目标优先级•高性能帐户交易明细表2008•数据维护能力•实施难度•高可用性（故障屏蔽能力）Oracle支持多种分区方法•分区方法•范围、hash、列表、复合分区删除添加分区分区帐户交易明细表05may05jan05feb05mar05apr05工行建行may05jan05feb05mar05apr05may05jan05feb05mar05apr05中行本地索引大表访问性能的提高分区对应用是透明的查询并统计工行上海分行5月份的可疑交易情况select* 自动忽略与访问无关的分区 实现分区间的并行查询select From 交易明细表Where branch=‘上海’and 日期between 01-May-2007and 31-May-2007显著提高访问大表时的性能帐户交易明细表（3600个分区）分区忽略机制系统自动根据分区情况优化数据BJ-05SH-05GZ-05访问，忽略无关的数据分区（只查询1/3600的数据）BJ-04SH-04GZ-04帐户交易明细表may05 jan05feb05mar05apr05may05 jan05feb05mar05apr05保单信息表may05jan05feb05mar05apr05内容分区技术概述分区表和分区索引技术如何运用分区技术11g 分区新特性分区实施经验及案例分区实施服务Oracle 分区技术的发展Basic maintenanceStatic partition Range partitioning Oracle8ManageabilityPerformanceCore functionalityMerge operation Partitionwise joins D i iHash and composite range hash partitioning Oracle8i operations: add, drop, exchange pruningGlobal range indexes Global index maintenanceList partitioning Oracle9i Dynamic pruning range-hash partitioning Local Index Global hash indexes Oracle10g Fast partition splitComposite range-list partitioningOracle9i R2Fast drop table Multidimensional pruning1M partitions per tableOracle10g R2maintenanceInterval Partitioning Partition AdvisorMore composite choices REF PartitioningVirtual Column PartitioningOracleDatabase 11g范围分区例CREATE TABLE sales (acct_noNUMBER(5),person VARCHAR2(30),sales_amount NUMBER(8),week_noNUMBER(2))PARTITION BY RANGE (week_no)(PARTITION P1 VALUES LESS THAN (4) TABLESPACE data0,123PARTITION P2 VALUES LESS THAN (8) TABLESPACE data1,...…PARTITION P13 VALUES LESS THAN (53)TABLESPACE data12 1);分区字段：week_no .23_VALUES LESS THAN 必须是确定值每个分区可以单独指定物理属性范围分区特点•最早、最经典的分区算法•Range分区通过对分区字段值的范围进行分区•Range分区特别适合于按时间周期进行数据的存储。

日、周、月、年等。

•数据管理能力强–数据迁移–数据备份–数据交换•范围分区的数据可能不均匀•范围分区与记录值相关，实施难度和可维护性相对较范围分区与记录值相关实施难度和可维护性相对较差Hash分区例create table CUSTOMERS (... column definitions ...) pctfree 0 nologgingg(p)storage ( initial 40m next 40m pctincrease 0 )partition 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,partition cust_p03 tablespace cust_data03,partition cust_p04 tablespace cust_data04p_p p_,partition cust p05 tablespace cust data05,partition cust_p06 tablespace cust_data06,partition cust_p07 tablespace cust_data07,partition cust p08tablespace cust data08),partition cust_p08 tablespace cust_data08)Hash分区特点•基于分区字段的HASH值，自动将记录插入到指定分区。

分区数般是•分区数一般是2的幂•易于实施•总体性能最佳•适合于静态数据•HASH分区适合于数据的均匀存储partition--wise joins和partition。

wise joins。

分区特别适合于PDML•HASHHASH分区特别适合于PDML和•支持(hash) local indexes•9i 不支持(hash) global indexes•10g 支持(hash) global indexes HASH分区•数据管理能力弱•HASH分区对数据值无法控制列表分区例create table addresses (... column definitions ...)create table addresses(column definitions)pctfree 0 nologgingstorage ( initial 40m next 40m pctincrease 0 )g ppartition by list(city_name)(partition addr_p01 values ('WELLINGTON') tablespace addr_data01dd d t01,partition addr_p02 values ('CHRISTCHURCH') tablespace addr_data02,partition addr_p03 values ('DUNEDIN','INVERCARGILL') tablespace addr_data03,partition addr_p04 values (AUCKLAND) tablespacepartition addr p04values('AUCKLAND')tablespaceaddr_data04,partition addr_p05 values ('HAMILTON','ROTORUA','TAURANGA') tablespace addr_data05)tablespace addr data05)列表分区特点•List分区通过对分区字段的离散值进行分区。

•List分区是不排序的，而且分区之间没有关联关系•List分区适合于对数据离散值进行控制。

Li t分区适合于对数据离散值进行控制•List分区只支持单个字段。

•List分区具有与范围分区相似的优缺点–数据管理能力强–List分区的数据可能不均匀–List分区与记录值相关，实施难度和可维护性相Li t分区与记录值相关实施难度和可维护性相对较差复合分区例create table daily_trans_data (...column definitions ...)partition by range(trans_datetime)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 dtd20010620s01(subpartition dtd_20010620_s01,subpartition dtd_20010620_s02,subpartition dtd_20010620_s03 tablespace dtd_data03,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 values 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)复合分区特点•Oracle支持的Composite分区：•Range-Hash,Range-ListRange-Hash Range-List•既适合于历史数据，又适合于数据均匀分布•与范围分区一样提供高可用性和管理性partition wise joins •更好的PDML和partition-wise joins性能•实现粒度更细的操作•支持复合local indexes•不支持复合compositeglobal indexesl b l i d分区索引不分区分区表索引不分区√√分区√√GlobalNonpartitioned indexGlobal Partitioned IndexGlobal Partitioned Index 杭州嘉兴湖州03年04年…08年03年04年08年03年04年…08年杭州–丽水杭州–丽水杭州–丽水Local non-Prefixed Partitioned IndexTXN_CURRENTTXN CURRENT03年04年…08年exchangestageexchange03年02年…01年TXN_HISTORY•分区表索引的分类：–Local Prefixed indexL l N n pr fi d ind–Local Non-prefiexed index–Global Prefixed index–Non Partition Indexy Global索引的分区不同与表分区y Local索引的分区与表分区相同y An index is prefixed if it is partitioned on a left prefix of the index columns.l ft fi f th i d ly分区表上的非分区索引等同于Global索引•Global索引必须是范围分区---9i之前•Global索引可以是HASH分区--10g新特性•Global索引不支持Bitmap索引Unique索引必须是prefixed 或者包含分•Unique索引必须是prefixed，或者包含分区字段索引）•Local索引（non-prefixed, non-unique）可以不包含分区字段分区索引举例create index cust_idx1 on customers(customer_name)global partition by range (customer_name)(p_p()p_(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_index03partition cust p04values less than(MAXVALUE)tablespace cust index04) ,partition cust_p04 values less than (MAXVALUE) tablespace 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_index05,partition cust_idx_p06 tablespace cust_index06,partition cust_idx_p07 tablespace cust_index07,partition cust idx p07tablespace cust index07,partition cust_idx_p08 tablespace cust_index08)create index cust_idx3 on customers(customer_type) local;4-23分区表索引的使用•OLTP系统中的建议–Global和unique local index性能优于nonunique local indexnonunique local index–Local index提供了更好的可用性•数据仓库系统中的建议–Local index更适合于数据装载和分区维护–在大量数据统计时，能充分利用Localindex并行查询能力•在性能、高可用性和可管理性之间进行平衡分区索引选择策略表分区字段是索引字段的前缀？该非分区字段是唯一索引？Local Prefixed Y esNo该非分区字段是唯索引Global Prefixed Y esNo是否性能在可承受范围，而分区的可管理性、可用性更重要？Local Non prefixed Y esLocal Non-prefixed是数据仓库/交易系统？NoOL TP DSS Local Non-prefixed Global Prefixed内容分区技术概述分区表和分区索引技术如何运用分区技术11g 分区新特性分区实施经验及案例分区实施服务99J Partition pruning: Only the relevant partitions are accessed.99-Feb 99-Janp 99Feb99-MarSQL> SELECT SUM(sales_amount)2FROM sales 99-Apr2 FROM sales3 WHERE sales_date BETWEEN4 TO DATE(‘01-MAR-1999’,99-May_5 ‘DD-MON-YYYY’) AND 6 TO_DATE(‘31-MAY-1999’,99-Jun 7 ‘DD-MON-YYYY’);sales1 select * from daily_trans_summ2* where trans_datetime between to_date('25-jun-2001 08','DD-mon-yyyy hh24') and to_date('28-jun2001 18,DD mon yyyy hh24)jun-200118','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 2341 select * from daily_trans_summ2* 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)TABLE ACCESS FULL DAILY TRANS SUMM KEY(INLIST)KEY(INLIST)Partition-Wise Join312Full partition-wise join Nonpartition-wise joinP ti l titi i j iPartial partition-wise joinQ yQuery slave Partition Partitioned tablePartition-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-2001','dd-mon-yyyy')6* group by c.customer_no6*bPartition PartitionPartition PartitionOperation Options Object Name Start Stop -----------------------------------------------------------------------------SELECT STATEMENT PARTITION HASH ALL 1 8 SORT GROUP BY HASH JOIN PARTITION RANGE ITERATOR 50 53 PARTITION RANGE ITERATOR5053TABLE ACCESS FULL DAILY_TRANS_DATA 393 424 TABLE ACCESS FULL CUSTOMERS 1 8分区表的管理功能• 分区的增加（ADD） • 分区的删除（DROP） • 分区的合并（MERGE） • 分区的清空（TRUNCATE） • 分区的交换（EXCHANGE） • 分区的压缩（COALESE） • 分区的移动（MOVE） • 分区的分离（SPLIT） • 修改分区的Default Attribute • 分区的更名（RENAME）分区索引的管理功能• • • • • • • 分区索引的删除（DROP） 分区索引的修改（MODIFY） 分区索引Default D f lt Att Attribute ib t 的修改 分区索引的重建（REBUILD） 分区索引的更名（RENAME） 分区索引的分离（SPLIT） 分区索引的 索 的Unusable“滚动窗口”操作 - 大量数据高速装载NOV 2002 NOV 2002 NOV 2002 NOV 2002 NOV 2002 NOV 2002 NOV 2002 NOV 2002分区表和Local索引 新月份数据的加载和索引的维护OCT 2002SEP 2002AUG 2002...NOV 2001OCT 2001“滚动窗口”操作 - 大量数据高速装载分区表和Local索引 新月份数据的加载和索引的维护 删除或归档最老月份的数据OCT SEP OCT OCT OCT OCT OCT SEP OCT SEP SEP SEP SEP SEP DEC NOV OCT NOV DEC DEC DEC DEC NOV DEC NOV NOV NOV NOV OCT NOV OCT OCT OCT OCT OCT NOV NOV NOV NOV NOV NOV NOV OCT 2002 2002 2002 2002 2002 2002 2002 2002 2002 2002 2002 2002 2002 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2001 2002 2002 2002 2002 2002 2002 2002 2001...分区交换功能• 通过交换数据段 通过交换数据段，实现分区和非分区表的数据交换。