oracle分區(qū)技術-大批量數據操作ppt課件

1、Oracle數據庫高級技術交流-大批量數據處置技術Oracle(中國)顧問咨詢部羅 敏 資深技術顧問 Michael.luooracle交流內容分區(qū)技術報表優(yōu)化技術 并行處置運用閱歷Oracle的分區(qū)技術 分區(qū)技術內容什么是分區(qū)?分區(qū)的益處?如何實施分區(qū)?如何評價分區(qū)的效果? Oracle的分區(qū)技術根本原理分而治之SB_ZSXX按年度進展分區(qū)2003200420052006分區(qū)概述大數據對象 (表, 索引)被分成小物理段當分區(qū)表建立時，記錄基于分區(qū)字段值被存儲到相應分區(qū)。分區(qū)字段值可以修正。(row movement enabled)分區(qū)可以存儲在不同的表空間分區(qū)可以

2、有不同的物理存儲參數分區(qū)支持IOT表，對象表，LOB字段，varrays等分區(qū)技術的效益和目的性能Select和DML操作只訪問指定分區(qū)并行DML操作Partition-wise Join可管理性：數據刪除，數據備份歷史數據去除提高備份性能指定分區(qū)的數據維護操作可用性將缺點局限在分區(qū)中縮短恢復時間分區(qū)目的優(yōu)先級 高性能 數據維護才干-實施難度 高可用性缺點屏蔽才干分區(qū)方法分區(qū)方法:范圍 - 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 );分區(qū)字段：week_no.

4、VALUES LESS THAN 必需是確定值每個分區(qū)可以單獨指定物理屬性123范圍分區(qū)例最早、最經典的分區(qū)算法Range分區(qū)經過對分區(qū)字段值的范圍進展分區(qū)Range分區(qū)特別適宜于按時間周期進展數據的存儲。日、周、月、年等。數據管理才干強數據遷移數據備份數據交換范圍分區(qū)的數據能夠不均勻范圍分區(qū)與記錄值相關，實施難度和可維護性相對較差范圍分區(qū)特點Hash分區(qū)例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分區(qū)特點基于分區(qū)字段的HASH值，自動將記錄插入到指定分區(qū)。分區(qū)數普通是2的冪易于實施總體性

7、能最正確適宜于靜態(tài)數據HASH分區(qū)適宜于數據的均勻存儲HASH分區(qū)特別適宜于PDML和partition-wise joins。支持 (hash) local indexes9i 不支持 (hash) global indexes10g 支持(hash) global indexes HASH分區(qū)數據管理才干弱HASH分區(qū)對數據值無法控制列表分區(qū)例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)列表分區(qū)特點List分區(qū)經過對分區(qū)字段的離散值進展分區(qū)。List分區(qū)是不排序的，而且分區(qū)之間沒有關聯關系List分區(qū)適宜于對數據離散值進展控制。List分區(qū)只支持單個字段。List分區(qū)具有與范圍分區(qū)類似的優(yōu)缺陷數據管理才干強List分區(qū)的數據能夠不均勻List分區(qū)與記錄值相關，實施難度和可維護性相對較差復合分區(qū)例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 )復合分區(qū)圖示復合分區(qū)特點Oracle支持的Composite分區(qū)： Range-Hash,Range-List既適宜于歷史數據，又適宜于數據均勻分布與范圍分區(qū)一樣提供高可用性和管理性更好的PDML和partition-wise joins性能實現粒度更細的操作支持復合 local indexes不支持復合compositeglobal indexes？分區(qū)索引不分區(qū)分區(qū)不分區(qū) 分區(qū)表索引Gl

13、obalNonpartitioned indexLocal partitioned indexGlobal Partitioned Index不同的分區(qū)索引紹興杭州溫州03年04年08年分區(qū)索引分區(qū)表索引的分類：Local Prefixed indexLocal Non-prefiexed indexGlobal Prefixed indexNon Partition IndexGlobal索引的分區(qū)不同與表分區(qū)Local索引的分區(qū)與表分區(qū)一樣An index is prefixed if it is partitioned on a left prefix of the index colu

14、mns.分區(qū)表上的非分區(qū)索引等同于Global索引分區(qū)索引Global索引必需是范圍分區(qū) - 9i之前Global索引可以是HASH分區(qū) - 10g新特性Global索引不支持Bitmap索引Unique索引必需是prefixed，或者包含分區(qū)字段Local索引non-prefixed, non-unique可以不包含分區(qū)字段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;分區(qū)索引舉例分區(qū)表索引的運用OLTP系統中的建議Global和unique local index性能優(yōu)于nonunique local indexLocal index提供了更好的可用性數據倉庫系統中的建議Local index更適宜于數據裝載和分區(qū)維護在大量數據統計時，能充分利用Local index并行查詢才干在性能、高可用性和可管理性之間進展平衡分區(qū)索引選擇戰(zhàn)略分區(qū)裁剪功能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);分區(qū)裁剪舉例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 分區(qū)表設計原那么表的大小：當表的大小

25、超越1.5GB2GB，或對于OLTP系統，表的記錄超越1000萬，都應思索對表進展分區(qū)。 數據訪問特性：基于表的大部分查詢運用，只訪問表中少量的數據。對于這樣表進展分區(qū)，可充分利用分區(qū)排除無關數據查詢的特性。 數據維護：按時間段刪除成批的數據，例如按月刪除歷史數據。對于這樣的表需求思索進展分區(qū)，以滿足維護的需求。數據備份和恢復： 按時間周期進展表空間的備份時，將分區(qū)與表空間建立對應關系。只讀數據：假設一個表中大部分數據都是只讀數據，經過對表進展分區(qū)，可將只讀數據存儲在只讀表空間中，對于數據庫的備份是非常有益的。 并行數據操作：對于經常執(zhí)行并行操作如Parallel Insert,Paralle

26、l Update等的表應思索進展分區(qū)。 表的可用性：當對表的部分數據可用性要求很高時，應思索進展表分區(qū)。 分區(qū)表的管理功能分區(qū)的添加ADD分區(qū)的刪除DROP分區(qū)的合并MERGE分區(qū)的清空TRUNCATE分區(qū)的交換EXCHANGE分區(qū)的緊縮COALESE分區(qū)的挪動MOVE分區(qū)的分別SPLIT修正分區(qū)的Default Attribute分區(qū)的更名RENAME分區(qū)索引的管理功能分區(qū)索引的刪除DROP分區(qū)索引的修正MODIFY分區(qū)索引Default Attribute的修正分區(qū)索引的重建REBUILD分區(qū)索引的更名RENAME分區(qū)索引的分別SPLIT分區(qū)索引的Unusable分區(qū)表和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新月份數據的加載和索引的維護.分區(qū)表和Local索引“滾動窗口操作 - 大量數據高速裝載分區(qū)交換功能經過交換數據段，實現分區(qū)和非分區(qū)表的數據交換。以及子分區(qū)

29、和分區(qū)表的數據交換非常快捷的數據挪動方式。特別是沒有validation和索引維護操作時Local 索引自動維護Global索引必需重建分區(qū)交換的運用- 全文檢索12：00分區(qū) BF_DXX_stage中間表11：00數據的加載 2建立context 索引 3partition的exchangeBF_DXX表* 初始化任務* 整理任務 2：00分區(qū)1：00分區(qū) 分區(qū)交換的運用- 全文檢索第一步：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遷移表空間技術的作用業(yè)務系統數據向數據倉庫系統的遷移 對業(yè)務系統和數據倉庫系統的數據進展定期歸檔 數據倉庫向數據集市的數據遷移 數據對外發(fā)布 按表空間進展時間點的數據恢復(TSPITR) 遷移表空間技術的優(yōu)點性能大大高于export/import或PL/SQL編寫的程序 由于Dmp文件只包含表空間的構造信息，因此該技術的真正開銷在于數據文件的

32、傳輸。 對源系統的影響非常小 只需求將被遷移的表空間設置為只讀方式 可同時傳輸索引數據，防止在目的數據庫中重建索引 分區(qū)交換的運用- ETL在源系統中，將需求抽取的數據以如下語句方式，抽取到建立在單獨表空間上的中間表中： CREATE TABLE . AS SELECT INSERT /*+ APPEND */ AS SELECT 以TTS方式將中間表的表空間傳輸到數據倉庫之中。 exp transportable_tablespace=Yes FTP 中間表表空間的數據文件imp transportable_tablespace=Yes 在數據倉庫中對中間表進展各種數據歸并等清洗任務，并建立

33、需求的各種索引。 經過exchange技術，將中間表數據及索引直接交換到分區(qū)表中。 Alter table exchange partition with table including indexes; 分區(qū)交換的運用-反復記錄刪除問題描畫： 在運用SQL*Loader進展數據加載sor_acct_dcc_saamt_c表時，由于操作失誤，反復加載，導致分區(qū)ETL_LOAD_DATE_0606出現反復記錄，也使得兩個獨一索引：IDX_SAACNAMT_C_1，IDX_SAACNAMT_C_2的ETL_LOAD_DATE_0606分區(qū)不可用UNUSABLE。用戶在試圖重新創(chuàng)建該分區(qū)索引時，出現

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 分區(qū)交換的運用-反復記錄刪除在試圖刪除該分區(qū)的反復記錄時，又出現如下錯誤：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分區(qū)交換的運用-反復記錄刪除簡一方法是徹底刪除這兩個獨一索引，重新創(chuàng)建。 數據量大，時間太長。影響系

36、統的可用性。 更完備的處理方式創(chuàng)建一個與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表分區(qū)ETL_LOAD_DATE_0606數據交換到暫時表test。 SQL alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 with table test; 分區(qū)交換的運用-反復記錄刪除更完備的處理方式刪除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表分區(qū)ETL_LOAD_DATE_0606 。 alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 w

38、ith table test; 分區(qū)交換的運用-反復記錄刪除更完備的處理方式重新創(chuàng)建創(chuàng)建該分區(qū)索引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錯誤 現有系統實施分區(qū)的閱歷分區(qū)對象確實定：存儲空間最大的前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; 物化視圖創(chuàng)建和查詢重寫例SQL SELECT d_name,SUM (s.quantity_sold), 2 SUM (s.amou