使用Oracle 10g進(jìn)行數(shù)據(jù)倉庫的抽取、轉(zhuǎn)換、裝載ETL

總覽

ETL過程中發(fā)生了什么？

首先從多個(gè)不同的數(shù)據(jù)源中抽取數(shù)據(jù)（Extraction），然后傳輸（transpoting）到中間臨時(shí)系統(tǒng)或目標(biāo)系統(tǒng)中，上面兩個(gè)過程中都可能會(huì)有部分轉(zhuǎn)換工作（transform）發(fā)生。
抽取盒傳輸完成之后就是最重要的轉(zhuǎn)換和裝載工作，包含：

  • 復(fù)雜的過濾器的使用
  • 保證與目標(biāo)數(shù)據(jù)庫表中的數(shù)據(jù)一致
  • 檢查數(shù)據(jù)是否需要更新或插入
  • 統(tǒng)一數(shù)據(jù)可能需要插入多次，如作為細(xì)節(jié)數(shù)據(jù)或者聚集信息。

先決條件

Sales History 銷售記錄模式改變的實(shí)現(xiàn)

procedure代碼end;之后加斜杠是執(zhí)行存儲(chǔ)過程。
這部分主要代碼在modifySH_10gR2.sql中，代碼運(yùn)行了3分鐘。

多表插入

公司從合作伙伴公司接收到的數(shù)據(jù)記錄了每個(gè)客戶每周的信息。
轉(zhuǎn)換（transformation）的一部分工作就是從每周一條記錄轉(zhuǎn)換為7條記錄。另外，數(shù)據(jù)倉庫需要分開記錄所有信用高于某一界限的新用戶。
這一節(jié)，將使用Oracle的多表插入來實(shí)現(xiàn)上述業(yè)務(wù)轉(zhuǎn)換。

使用多表插入實(shí)現(xiàn)pivoting旋轉(zhuǎn)

Oracle RDBMS執(zhí)行每一條SQL語句，都必須經(jīng)過Oracle優(yōu)化器的評(píng)估。所以，了解優(yōu)化器是如何選擇(搜索)路徑以及索引是如何被使用的，對(duì)優(yōu)化SQL語句有很大的幫助。Explain可以用來迅速方便地查出對(duì)于給定SQL語句中的查詢數(shù)據(jù)是如何得到的即搜索路徑(我們通常稱為Access Path)。

這里我們用explain plan發(fā)現(xiàn)使用insert all into sales value into sales[or other table] values輸入源表只掃描了一次，因?yàn)槎啾聿迦胪ㄟ^多個(gè)Insert into分支實(shí)現(xiàn)數(shù)據(jù)反向規(guī)格化（分解），避免了多趟掃描。
對(duì)比而言INSERT INTO sales [select_statement1] union all [select_statement1]的方法需要多變掃描。

使用多表插入實(shí)現(xiàn)條件插入

    INSERT /*+ APPEND NOLOGGING */FIRST
      WHEN cust_credit_limit >= 4500 THEN
        INTO customers
        INTO customers_special VALUES (cust_id, cust_credit_limit)
      ELSE
        INTO customers
    SELECT * FROM customers_new;

使用upsert（update or insert）功能，即SQL merge關(guān)鍵字

公司需要定期根據(jù)產(chǎn)品數(shù)據(jù)庫更新數(shù)據(jù)倉庫。不幸的是信息是否新舊需要在數(shù)據(jù)倉庫端來確定。下面步驟介紹了sql MERGE操作：

  1. 為外部產(chǎn)品信息建立external table外部表（和directories目錄）。
    外部表作用:加載和卸載數(shù)據(jù)
    外部表都是只讀表 , 不能進(jìn)行 update,insert,delete 操作 .
    Oracle 提供兩種訪問驅(qū)動(dòng) :
    ORACLE_LOADER ： 它利用 oracle loader 技術(shù)從外部表讀取數(shù)據(jù) . 它還具有類似 SQL*Loader 工具控制文件語法的數(shù)據(jù)映射能力 .
    ORACLE_DATAPUMP ：它提供卸載數(shù)據(jù)的能力 , 即把數(shù)據(jù)從數(shù)據(jù)庫里導(dǎo)入一個(gè)外部表 , 再由一個(gè)或幾個(gè)外部表導(dǎo)入到數(shù)據(jù)庫里 . 對(duì)于 ASsubquery 的子句必須使用 ORACLE_DATAPUMP . 創(chuàng)建用于卸載數(shù)據(jù)的外部表時(shí)，不能制定列名。同時(shí)，不支持 badfile 、 discardfile 參數(shù)，因?yàn)槠錈o效或者丟棄數(shù)據(jù)將不予以記錄為操作系統(tǒng)文件 , 沒有 fields erminated by( 或者 missing field values are null) 參數(shù)。
    語法為：

     CREATE TABLE products_delta
 (
 PROD_ID NUMBER(6),
 PROD_VALID CHAR(1)
 )
 ORGANIZATION external
 (
 TYPE oracle_loader
 DEFAULT DIRECTORY data_dir
 ACCESS PARAMETERS
 (
 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
 NOBADFILE
 NOLOGFILE
 FIELDS TERMINATED BY "|" LDRTRIM
 )
 location
 ('prodDelta.dat')
 REJECT LIMIT UNLIMITED NOPARALLEL;

  2. 使用SQL MERGE命令行實(shí)現(xiàn)更新或插入。

  3. 顯示Merge命令的執(zhí)行計(jì)劃。

  4. 不用MERGE操作，使用兩段分開的代碼實(shí)現(xiàn)upsert。

對(duì)比結(jié)論:

To leverage the updatable join view functionality, the external table needs to be copied into a real database table and a unique index on the join column needs to be created. In short, this requires more operations, more space requirements, and more processing time.

學(xué)習(xí)DML錯(cuò)誤日志

    --調(diào)用模塊
    exec dbms_errlog.create_error_log('sales_overall');
    INSERT /*+ APPEND NOLOGGING PARALLEL */ INTO sales_overall
    SELECT * FROM sales_activity_direct
    LOG ERRORS INTO err$_sales_overall ( 'load_test1' ) REJECT LIMIT 10;--記錄錯(cuò)誤，最多容錯(cuò)十條
    commit;
    --檢查錯(cuò)誤的方法，這里可以看出相對(duì)于文件方法的優(yōu)勢(shì)
    select distinct ora_err_number$ from err$_sales_overall;
    select distinct ora_err_number$, ora_err_mesg$ from err$_sales_overall;

體會(huì)基本的表函數(shù)

在ETL過程中，數(shù)據(jù)從原系統(tǒng)中抽?。‥xtraction）出來，經(jīng)過一系列的轉(zhuǎn)換（Transformation），最后被裝載（Load）到目標(biāo)系統(tǒng)中。復(fù)雜的轉(zhuǎn)換通常以過程（procedure）的形式實(shí)現(xiàn)。有時(shí)，轉(zhuǎn)換的結(jié)果太大，內(nèi)存容不下，這些結(jié)果必須以數(shù)據(jù)庫表或falt files(普通文件)的方式臨時(shí)地實(shí)體化。然后再被讀入和作為下一轉(zhuǎn)換的輸入被處理。Oracle提供了對(duì)這些轉(zhuǎn)換的管道化和并行處理的支持，實(shí)現(xiàn)的語言有PL/SQL，C和java。
表函數(shù)可接受查詢語句或游標(biāo)作為輸入?yún)?shù)，并可輸出多行數(shù)據(jù)。該函數(shù)可以平行執(zhí)行，并可持續(xù)輸出數(shù)據(jù)流，被稱作管道式輸出。應(yīng)用表函數(shù)可將數(shù)據(jù)轉(zhuǎn)換分階段處理，并省去中間結(jié)果的存儲(chǔ)和緩沖表。

自從 Oracle9i 之后，提供了一個(gè)叫做"管道化表函數(shù)"的概念。來解決此類問題，這種類型的函數(shù)，必須返回一個(gè)集合類型，且標(biāo)明 PIPELINED。這個(gè)函數(shù)不能返回具體變量，必須以一個(gè)空的RETURN 返回。這個(gè)函數(shù)中，通過 PIPE ROW() 語句來送出要返回的表中的每一行。調(diào)用這個(gè)函數(shù)的時(shí)候，通過 TABLE() 關(guān)鍵字把管道流仿真為一個(gè)數(shù)據(jù)集。

下面是學(xué)習(xí)使用表函數(shù)的基本步驟：

  1. 設(shè)立表函數(shù)的基本對(duì)象

  • 定義對(duì)象（記錄）類型
    CREATE TYPE product_t AS OBJECT (
    prod_id NUMBER(6)
    , prod_name VARCHAR2(50)
    );
    /

  • 定義對(duì)象（集合）類型
    CREATE TYPE product_t_table AS TABLE OF product_t;
    /

  • 記錄集合的結(jié)構(gòu)，定義ref cursor引用游標(biāo)類型的包
    ref cursor：動(dòng)態(tài)關(guān)聯(lián)結(jié)果集的臨時(shí)對(duì)象。即在運(yùn)行的時(shí)候動(dòng)態(tài)決定執(zhí)行查詢。實(shí)現(xiàn)在程序間傳遞結(jié)果集的功能，能作為參數(shù)傳遞。
    CREATE OR REPLACE PACKAGE cursor_PKG as
    TYPE product_t_rec IS RECORD (prod_id NUMBER(6)
    , prod_name VARCHAR2(50)
    );

      TYPE product_t_rectab IS TABLE OF product_t_rec;
  TYPE strong_refcur_t IS REF CURSOR RETURN       product_t_rec;
 TYPE refcur_t IS REF CURSOR;
  END;

  • 為表函數(shù)創(chuàng)建日志表
    CREATE TABLE obsolete_products_errors
    (prod_id NUMBER, msg VARCHAR2(2000));

  1. 實(shí)現(xiàn)非管道化的表函數(shù)，返回記錄數(shù)組
    Rem uses weakly typed cursor as input
    CREATE OR REPLACE FUNCTION obsolete_products(cur cursor_pkg.refcur_t) RETURN
    product_t_table
    IS
    prod_id NUMBER(6);
    prod_name VARCHAR2(50);
    sales NUMBER:=0;
    objset product_t_table := product_t_table();
    i NUMBER := 0;
    BEGIN
    LOOP
    -- Fetch from cursor variable
    FETCH cur INTO prod_id, prod_name;
    EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched
    -- Category Electronics is not meant to be obsolete and will be suppressed
    IF prod_status='obsolete' AND prod_category != 'Electronics' THEN
    -- append to collection
    i:=i+1;
    objset.extend;
    objset(i):=product_t( prod_id, prod_name);
    END IF;
    END LOOP;
    CLOSE cur;
    RETURN objset;
    END;
    /
    調(diào)用及結(jié)果：
調(diào)用obsolete_products

  1. 實(shí)現(xiàn)管道化表函數(shù)
    CREATE OR REPLACE FUNCTION obsolete_products_pipe(cur cursor_pkg.strong_refcur_t)
    RETURN product_t_table
    PIPELINED
    PARALLEL_ENABLE (PARTITION cur BY ANY) IS
    prod_id NUMBER(6);
    prod_name VARCHAR2(50);
    sales NUMBER:=0;
    BEGIN
    LOOP
    -- Fetch from cursor variable
    FETCH cur INTO prod_id, prod_name;
    EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched
    IF prod_status='obsolete' AND prod_category !='Electronics' THEN
    PIPE ROW (product_t( prod_id, prod_name));
    END IF;
    END LOOP;
    CLOSE cur;
    RETURN;
    END;
    /
    區(qū)別在于RETURN product_t_table PIPELINED和使用PIPE ROW(添加數(shù)據(jù))。
    調(diào)用obsolete_product與前類似。

  2. 實(shí)現(xiàn)透明并行執(zhí)行表函數(shù)
    有時(shí)表函數(shù)需要對(duì)一些產(chǎn)品在類別屬性上處理復(fù)雜的聚合操作。若要將其并行化，需要保證具有統(tǒng)一產(chǎn)品類別屬性的所有記錄被統(tǒng)一并行子進(jìn)程處理，使得聚集函數(shù)覆蓋了同一group中的所有記錄。因此，需要使用對(duì)分布規(guī)則，在表函數(shù)頭部添加PARALLEL_ENABLE (PARTITION cur BY ANY) IS。
    通過對(duì)ref cursor進(jìn)行hint提示/+ PARALLEL(a,4)/來強(qiáng)制進(jìn)行并行化。

  3. 帶自治的DML語句的表函數(shù)
    表函數(shù)提供在其自治事務(wù)的域內(nèi)扇出數(shù)據(jù)到其它表中。
    例子中進(jìn)行判斷并向錯(cuò)誤日志記錄表中輸出數(shù)據(jù)。
    FETCH cur INTO prod_id, prod_name;
    EXIT WHEN cur%NOTFOUND; -- exit when last row is fetched
    IF prod_status='obsolete' THEN
    IF prod_category=prod_cat THEN
    INSERT INTO obsolete_products_errors VALUES
    (prod_id, 'correction: category '||UPPER(prod_cat)||' still available');
    COMMIT;
    ELSE
    PIPE ROW (product_t( prod_id, prod_name));
    END IF;
    END IF;

  1. 通過多個(gè)表函數(shù)實(shí)現(xiàn)無縫流式處理
    除了表函數(shù)在sql語句中的透明使用和其并行處理能力，表函數(shù)的另一個(gè)優(yōu)勢(shì)是可以相互之間調(diào)用。更進(jìn)一步，表函數(shù)可以被任何sql語句使用，可以成為任何DML語句的輸入。

  • 嵌套使用使用兩個(gè)表函數(shù)
    SELECT DISTINCT prod_category, prod_status
    FROM TABLE(obsolete_products_dml(CURSOR(SELECT *
    FROM TABLE(obsolete_products_pipe(
    CURSOR(SELECT prod_id, prod_name
    FROM products))))));

  • 使用create table as select命令將表函數(shù)用作輸入。
    CREATE TABLE PIPE_THROUGH AS

      SELECT DISTINCT prod_category, prod_status
  FROM TABLE(obsolete_products_dml(CURSOR(SELECT *
  FROM TABLE(obsolete_products_pipe(
  CURSOR(SELECT prod_id, prod_name
  FROM products))))));

使用同步Change Data Capture（CDC增量抽取解決方案）來記錄抽取增量數(shù)據(jù)變化

Change Data Capture快速找到并處理那些改變了的數(shù)據(jù)，而不是整張表，并使得這些改變的數(shù)據(jù)可以供后續(xù)之用。
CDC不依賴于在關(guān)系數(shù)據(jù)庫外使用中間文件臨時(shí)存儲(chǔ)數(shù)據(jù)。它能捕獲由對(duì)用戶表進(jìn)行insert、update、delete操作而引起的改變數(shù)據(jù)。這些改變數(shù)據(jù)被存儲(chǔ)在一個(gè)叫做change table的表中，并可被應(yīng)用程序方便地控制與操作。

publish 和 subscribe模型

publish 和 subscribe模型

大多數(shù)CDC系統(tǒng)有一個(gè)publisher發(fā)布者負(fù)責(zé)從多個(gè)數(shù)據(jù)源捕獲和發(fā)布這些改變數(shù)據(jù)。可以有多個(gè)subscriber訂閱用戶訪問數(shù)據(jù)。CDC提供了PL/SQL包來完成發(fā)布、訂閱任務(wù)。

  • publisher發(fā)布者
    通常是負(fù)責(zé)創(chuàng)建、維護(hù)組成CDC系統(tǒng)模式對(duì)象的DBA。主要任務(wù)有：

  • 定義數(shù)據(jù)倉庫可能感興趣的關(guān)系表（源表）。

  • 使用oracle提供的DBMS_LOGMNR_CDC_PUBLISH包來啟動(dòng)系統(tǒng)從多個(gè)源表中捕獲數(shù)據(jù)。

  • 以change table的形式發(fā)布這些變化數(shù)據(jù)。

  • 使用SQL的Grant和Revoke語句來對(duì)用戶和角色賦予對(duì)change table改變表的select權(quán)限。

  • subscriber訂閱用戶
    使用發(fā)布的改變數(shù)據(jù)的應(yīng)用。

  • 使用Oracle提供的DBMS_LOGMNR_CDC_SUBSCRIBE包來訂閱源表，以獲取對(duì)所發(fā)布的改變數(shù)據(jù)的訪問權(quán)限。

  • 展開訂閱窗口，當(dāng)可以接收數(shù)據(jù)時(shí)，創(chuàng)造一個(gè)新的subscriber view訂閱著視圖。

  • 使用SELECT語句來從subscriber view訂閱著視圖遍歷改變數(shù)據(jù)。

  • 刪除subscriber view訂閱著視圖，清楚訂閱窗口。

  • 當(dāng)不再需要其改變數(shù)據(jù)時(shí)，刪除subscription訂閱。

這一主題的學(xué)習(xí)步驟如下：

  1. 使用同步CDC來記錄所有增量數(shù)據(jù)。
  • 創(chuàng)建一個(gè)intermediate table中間表來進(jìn)行操作。
    CREATE TABLE my_price_change_Electronics
    (prod_id number, prod_min_price number, prod_list_price number, when date);
  1. 創(chuàng)建一個(gè)改變表。
    使用DBMS_CDC_PUBLISH包創(chuàng)建改變表。
    begin
    DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE (OWNER => 'SH', -
    CHANGE_TABLE_NAME => 'PROD_price_CT',
    CHANGE_SET_NAME => 'SYNC_SET',
    SOURCE_SCHEMA => 'SH',
    SOURCE_TABLE => 'PRODUCTS',
    COLUMN_TYPE_LIST => 'prod_id number(6), prod_min_price number(8,2),
    prod_list_price number(8,2)',
    CAPTURE_VALUES => 'both',
    RS_ID => 'y',
    ROW_ID => 'n',
    USER_ID => 'n',
    TIMESTAMP => 'n',
    OBJECT_ID => 'n',
    SOURCE_COLMAP => 'y',
    TARGET_COLMAP => 'y',
    OPTIONS_STRING => null);
    end;
    /
    這段代碼創(chuàng)建了一個(gè)叫PROD_PRICE_CT的改變表，以及跟蹤所有在product上的后續(xù)改變的必須的觸發(fā)器。
change table的結(jié)構(gòu)

表中除了源表PRODUCTS中的列之外還有多個(gè)必須（如OPPERATION$）和可選（SOURCE_COLMAP$）的metadata元數(shù)據(jù)列。
可通過SELECT * FROM change_tables;等查詢change table相關(guān)信息。

  1. 訂閱到一個(gè)change set改變集和所有感興趣的源表列。
    訂閱處理的邏輯實(shí)體是change set而不是change table。一個(gè)chage set可以包含多個(gè)change table，并能保證其間的邏輯一致。在訂閱一個(gè)change set后，你就可以訂閱所有感興趣的源表列。方法為DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION()；

  • 首先獲得獨(dú)一的將在整個(gè)對(duì)話中使用的 subscription handle訂閱句柄，并告訴系統(tǒng)你感興趣的列。

      variable subname varchar2(30)
  begin
  :subname := 'my_subscription_no_1';
  DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION (
  CHANGE_SET_NAME => 'SYNC_SET',
  DESCRIPTION => 'Change data PRODUCTS for ELECTRONICS',
  SUBSCRIPTION_name => :subname);
  END;
  /

訂閱是一種連接。

  • 然后就可以啟動(dòng)一個(gè)該subscription的change view改變視圖。

      BEGIN
  :view_name := 'my_prod_price_change_view';
  DBMS_CDC_SUBSCRIBE.SUBSCRIBE (
  SUBSCRIPTION_NAME => :subname,
  SOURCE_SCHEMA => 'sh',
  SOURCE_TABLE => 'products',
  COLUMN_LIST => 'prod_id, prod_min_price, prod_list_price',
  subscriber_view => :view_name );
  END;
  /

訂閱者視圖主要是為了添加感興趣的列。

  1. 激活一個(gè)訂閱，并展開訂閱窗口。
    EXEC DBMS_LOGMNR_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION -
    (SUBSCRIPTION_name => 'my_subscription_no_1')
    至此，所有對(duì)PRODUCT源表的改變都反映在change table中。這些改變是通過在源表上的trigger透明地實(shí)現(xiàn)的。
對(duì)source table的操作會(huì)改變change table

源表中每條記錄的改變，在change table中會(huì)添加對(duì)應(yīng)的兩條記錄：舊、新。
然而，建議不要直接使用change table來找到源表中的改變數(shù)據(jù)，而是使用安全有保障的subscriber view訂閱者視角。
展開訂閱窗口后，就可以在subscriber view訂閱者視角中看到一段時(shí)間窗口內(nèi)改變的數(shù)據(jù)。

  • 現(xiàn)在就可以在目標(biāo)系統(tǒng)上使用這些改變數(shù)據(jù)
    可以將subscriber view訂閱者視角my_prod_price_change_view 當(dāng)做表來處理。
    INSERT into my_price_change_electronics
    SELECT p1.prod_id, p1.prod_min_price, p1.prod_list_price, commit_timestamp$
    FROM my_prod_price_change_view p1, products p2
    WHERE p1.prod_id=p2.prod_id
    AND p2.prod_category='Electronics' AND operation$='UN';

      COMMIT;

  SELECT prod_id, prod_min_price, prod_list_price,
  to_char(when,'dd-mon-yyyy hh24:mi:ss')
  FROM my_price_change_electronics;
  1. 考察如何隨時(shí)間變化處理新的環(huán)境。
purge_window清理subscriber view中的數(shù)據(jù)
extend_window后可以繼續(xù)獲取數(shù)據(jù)

然后就可以使用所獲取的數(shù)據(jù)。如用merge指令將數(shù)據(jù)導(dǎo)入已存在的表中。

merge，將subscriber view中的數(shù)據(jù)當(dāng)做“普通”表進(jìn)行處理

  1. 運(yùn)行發(fā)布者。
    發(fā)布者需清理不需要的數(shù)據(jù)。
    exec DBMS_CDC_PUBLISH.purge_change_table('sh','prod_price_ct')

  2. 刪除使用過的改變視圖并清理訂閱窗口。

  3. 清理CDC環(huán)境。
    exec DBMS_CDC_SUBSCRIBE.drop_subscription -
    (subscription_name=> 'my_subscription_no_1');
    exec DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE (OWNER => 'sh', -
    CHANGE_TABLE_NAME => 'prod_price_CT', -
    FORCE_FLAG => 'Y');

為數(shù)據(jù)集市進(jìn)行信息傳播

除了中心的數(shù)據(jù)倉庫，公司還可能運(yùn)行多個(gè)小的data mart數(shù)據(jù)集市。比如，產(chǎn)品部門想要接收所有的事務(wù)型SALES銷售數(shù)據(jù)來進(jìn)行市場(chǎng)營銷分析，這些數(shù)據(jù)根據(jù)其主要的產(chǎn)品類別而劃分，且只有2000年的數(shù)據(jù)相關(guān)。
我們講使用Oracle的transportable tablespace可傳輸表空間功能和list partition列表劃分來解決這些問題。另外，為了保障新表生成過程的成功完成，我們將在resumable可恢復(fù)模式下運(yùn)行語句，以保證不會(huì)因?yàn)榭臻g問題而導(dǎo)致創(chuàng)建表失敗。
從數(shù)據(jù)倉庫傳播數(shù)據(jù)到數(shù)據(jù)集市的學(xué)習(xí)步驟：

  1. 開啟一個(gè)resumable session可恢復(fù)對(duì)話。
    當(dāng)遇到空間不足或超出空間的錯(cuò)誤后，resumable session會(huì)suspend掛起，待錯(cuò)誤解決后再自動(dòng)地resume繼續(xù)執(zhí)行。如果錯(cuò)誤不能在一定的時(shí)間范圍內(nèi)被解決，語句終究還是失敗。本例中，我們手工地解決錯(cuò)誤。

     ALTER SESSION ENABLE RESUMABLE TIMEOUT 1200 NAME 
 'create list partitioning';

  2. 創(chuàng)建一個(gè)新的表空間（將用作可傳輸表空間）。
    現(xiàn)在創(chuàng)建一個(gè)額外的表空間來存儲(chǔ)我們的List patitioned事實(shí)表。
    CREATE TABLESPACE my_obe_transfer DATAFILE 'c:\obetemp' SIZE 2M REUSE autoextend off;

  3. 在新的表空間中創(chuàng)建List partitioned table列表分離表。
    在窗口1中，利用CREATE TABLE AS SELECT創(chuàng)建List patitioned table列表分割表。
    DROP TABLE sales_prod_dept;
    PROMPT create table in new TS that is too small
    CREATE TABLE sales_prod_dept
    (prod_category, prod_subcategory,cust_id,
    time_id,channel_id,promo_id, quantity_sold, amount_sold
    ) NOLOGGING TABLESPACE my_obe_transfer
    PARTITION BY LIST (prod_category)
    (PARTITION electronic_sales values ('Electronics'),
    PARTITION hardware_sales values ('Hardware'),
    PARTITION sw_other_sales values ('Software/Other'),
    PARTITION p_and_a values ('Peripherals and Accessories'),
    PARTITION photo_sales values ('Photo')
    )
    AS
    SELECT p.prod_category, p.prod_subcategory, s.cust_id, s.time_id,s.channel_id,
    s.promo_id, SUM(s.amount_sold) amount_sold, SUM(s.quantity_sold) quantity_sold
    FROM sales s, products p, times t
    WHERE p.prod_id=s.prod_id
    AND s.time_id = t.time_id
    AND t.fiscal_year=2000
    GROUP BY prod_category, prod_subcategory,cust_id, s.time_id,channel_id, promo_id
    ;
    上面代碼中，partition相當(dāng)于切片，select pks [] group by plks相當(dāng)于建立了事實(shí)表。
    但由于初始劃分的空間2m太小，會(huì)報(bào)錯(cuò)誤并掛起，加入下一步。

  4. 使用新的resumable statement可恢復(fù)語句功能來進(jìn)行高效地錯(cuò)誤檢測(cè)和處理。
    SELECT NAME, STATUS, ERROR_MSG FROM dba_resumable;
    顯示不能擴(kuò)展表空間my_obe_transfer中的臨時(shí)段。
    我們手工解決這個(gè)問題，執(zhí)行：
    ALTER DATABASE DATAFILE 'c:\obetemp' AUTOEXTEND ON NEXT 5M;
    錯(cuò)誤修正后，掛起的窗口1自動(dòng)resume繼續(xù)運(yùn)行。

  5. 創(chuàng)建一個(gè)新的range_list partitioned table（oracle 9i功能）。
    一個(gè)使用Range-List分割的典型例子是全球零售環(huán)境，使用time range partitions時(shí)間范圍分割（滑動(dòng)窗口）和一個(gè)底層的region-oriented list partition基于地域的列表分割，這樣就可以為每個(gè)地域維護(hù)所有時(shí)間窗。
    oracle提供了subpartition子分割模板技術(shù)，來實(shí)現(xiàn)range-hash 和 range-list組合分割，可類比于下鉆。
    CREATE TABLE sales_rlp
    COMPRESS
    TABLESPACE MY_OBE_TRANSFER
    PARTITION BY RANGE (time_id)
    SUBPARTITION BY LIST (channel_id)
    SUBPARTITION TEMPLATE
    ( SUBPARTITION direct values (3),
    SUBPARTITION internet values (4),
    SUBPARTITION partner values (2),
    SUBPARTITION other values (DEFAULT)
    )
    (PARTITION SALES_before_1999 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
    PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
    PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
    PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
    PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
    PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
    PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),
    PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
    PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE) NOCOMPRESS)
    AS
    SELECT * FROM sales sample(10);

  6. 為可傳輸表空間準(zhǔn)備元數(shù)據(jù)導(dǎo)出
    CREATE DIRECTORY my_obe_dump_dir as 'c:\wkdir';
    expdp '/ as sysdba' DIRECTORY=my_obe_dump_dir DUMPFILE= meta_MY_OBE_TRANSFER.dmp TRANSPORT_TABLESPACES=MY_OBE_TRANSFER.dmp
    -原網(wǎng)站上的代碼有誤。

清理

    SET SERVEROUTPUT ON\
    EXEC dw_handsOn.cleanup_modules

總結(jié)

  • 多表插入
  • merge實(shí)現(xiàn)upsert
  • 使用表函數(shù)
  • 使用同步CDC來捕獲和使用增量數(shù)據(jù)
  • 從數(shù)據(jù)倉庫向數(shù)據(jù)集市傳播數(shù)據(jù)
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容