0) { obj.className = "imgborder_on"; } } } function hideImage(obj) { if (obj.className.substr(0, 10) == "imgborder_") { obj.src = eyeglass.src; obj.className = "imgborder_off"; } } function showAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { showImage(imgs[i]); } } function hideAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { hideImage(imgs[i]); } } //-->

高速データ・ロードおよびOracle Partitioningによるローリング・ウィンドウ操作

目的

このチュートリアルでは、Oracle Databaseを使用した高速データ・ロードとOracle Partitioningを使用したローリング・ウィンドウ操作について学習します。

所要時間

約2時間

トピック

このチュートリアルでは、以下のトピックについて説明します。

概要
シナリオ
前提条件
売上履歴スキーマのスキーマ変更の実装
外部表を使用したデータ・ロード

外部表のロードおよび変換プロセスとSQL*Loaderの比較

表圧縮を活用したディスク領域の節約とTCOの削減
Oracle Partitioningによるローリング・ウィンドウ操作の実行
まとめ

スクリーンショットの表示

このアイコンの上にカーソルを置くと、すべてのスクリーンショットがロードし、表示されます。 （警告：すべてのスクリーンショットが同時にロードされるため、ご使用のインターネット接続によってはレスポンス・タイムが遅くなる場合があります。）

注：各手順に関連したスクリーンショットのみを表示する場合は、それぞれの手順にあるアイコンの上にカーソルを置いてください。 スクリーンショットをクリックすると、非表示になります。

概要

データ・ウェアハウスを提供するオンライン・トランザクション処理（OLTP）ソース・システムは、多くの場合、データ・ウェアハウス・システムに直接接続して新しいデータを抽出することはありません。 一般的に、OLTPシステムは、外部ファイルの形式でデータ・フィードを送信します。 このデータをデータ・ウェアハウスにロードする必要があります（可能であれば、パラレル処理にします）。これによって、既存のリソースが活用されます。

たとえば、このチュートリアルで使用されるサンプルの企業（MyCompany）のビジネス・ニーズとディスク領域の制約によって、過去3年のデータだけが分析に関連します。 つまり、新しいデータを挿入する場合、古いデータの消去またはOracle Databaseの表圧縮でディスク領域を解放する必要があります。 このローリング・ウィンドウ操作のメンテナンスは、Oracle Partitioningによって実行されます。

トピック・リストに戻る

前提条件

このチュートリアルを始める前に、次のことを確認してください。

1.

Oracle Database 11gのインストール。

2.

wkdirディレクトリの作成。 wkdirディレクトリへの etl.zip のダウンロードおよび解凍。

トピック・リストに戻る

シナリオ

外部表

外部ファイルをデータ・ウェアハウスにロードする場合、MyCompanyは、通常のデータベース表と同じようにフラット・ファイルなどの外部データをデータベース内に公開できるOracle Database外部表機能を使用します。 外部表は、SQLによってアクセスできます。このため、SQL、PL/SQL、およびJavaのすべての機能を使用して、外部ファイルの直接およびパラレルの問合せを実行できます。 一般的に、外部表は、抽出、変換、およびロード（ETL）プロセスで使用され、SQLを使用したデータ変換とデータ・ロードを単一の手順に統合します。 外部表は、ETLで使用できる多くのアプリケーションとフラット・ファイルを処理する他のデータベース環境において非常に強力な機能です。 外部表は、SQL*Loaderの代替機能です。

パラレル実行

パラレル実行によって、意思決定支援システム（DSS）とデータ・ウェアハウスで一般的に使用されている大規模なデータベースのデータ集約処理の応答時間が大幅に削減されます。 パラレル実行は、特定のOLTPシステムおよびハイブリッド・システムでも実装できます。 簡潔に説明すると、パラレル処理とは、問合せのすべての作業を1つのプロセスで実行する代わりに、タスクを分割して、作業の各部を多くのプロセスで同時に実行するという概念です。 たとえば、パラレル実行を使用して、1つのプロセスで4つの四半期を処理する代わりに4つのプロセスで4つの四半期を処理できます。

Oracle Partitioningによるローリング・ウィンドウ操作

データ・ウェアハウスのバック・オフィスの非常に重要なタスクとして、OLTP（ソース）システムで行われるさまざまな変更に対するデータの同期があります。 また、分析の観点から、データの使用期間は、一般的に非常に制限されています。このため、古いデータをターゲット・システムから消去して、新しいデータをロードする必要があります。この操作は、一般的にローリング・ウィンドウ操作と呼ばれます。 データ・ウェアハウス・システムの同時オンライン・アクセスを実行しないで、この操作をできるだけ迅速に実行する必要があります。

トピック・リストに戻る

売上履歴スキーマのスキーマ変更の実装

このOracle by Example（OBE）のタスクを開始する前に、既存の売上履歴（ SH）スキーマに変更を実装する必要があります。 SHスキーマに追加のオブジェクトを作成します。 また、追加のシステム権限を SHユーザーに付与する必要があります。 これらの変更を行うSQLファイルは、 modifySH_11g.sqlです。 以下の手順を実行します。

1.

端末ウィンドウを開きます。 端末セッションから以下のコマンドを実行して、作業ディレクトリを /home/oracle/wkdirに変更します。

cd wkdir

( 注： このチュートリアルでは、 /home/oracle/wkdirフォルダが作成されていることを前提とします。 作成されていない場合は、フォルダを作成し、 etl.zipの内容をこのフォルダに解凍する必要があります。）

 
2.

SQL*Plusセッションを開始し、 SHユーザーとしてログインします（パスワードに SHを使用します）。

次の modifySH_11g.sql スクリプトをSQL*Plusセッションで実行します。 

                                           
@modifySH_11g.sql

出力結果の最後と下に示すイメージが一致する必要があります。

 

トピック・リストに戻る

外部表を使用したデータ・ロード

ここでは、外部表を使用してデータをデータ・ウェアハウスにロードします。

外部表を構築し使用するには、以下の手順を実行します。

1.

必要なディレクトリ・オブジェクトの作成

2. 外部表の作成
3. 外部表からの選択
4. 外部表の透過的な高速パラレル・アクセスの提供
5. オラクルのパラレル挿入機能の確認
6.

パラレル挿入の実行

トピック・リストに戻る

1. 必要なディレクトリ・オブジェクトの作成

外部表を作成する前に、データファイルが存在するファイル・システムのディレクトリを示すデータベースのディレクトリ・オブジェクトを作成する必要があります。 オプションで、データファイルの場所と log、 bad、および discardファイルの場所を分離できます。 ディレクトリを作成するには、以下の手順を実行します。

SHユーザーとしてログオンしたSQL*Plusセッションで、 create_directory.sql スクリプトを実行するか、以下のSQL文をSQL*Plusセッションにコピーします。 

                                           
DROP DIRECTORY data_dir;
DROP DIRECTORY log_dir;
CREATE DIRECTORY data_dir AS '/home/oracle/wkdir';
                                            


CREATE DIRECTORY log_dir AS '/home/oracle/wkdir';

このアイコンの上にカーソルを置くと、イメージが表示されます。

スクリプトはLinuxシステム用に設定されているので、ファイルが /home/oracle/wkdirに抽出されると仮定します。 セキュリティ上の理由で、シンボリック・リンクは、データベース内の DIRECTORYオブジェクトとしてサポートされません。


リストへ戻る

2. 外部表の作成

外部表を作成すると、次の情報が定義されます。

1.

データベース内の表を表現するメタデータ情報
2. 外部ファイルからデータを抽出する HOW アクセス・パラメータ定義

このメタ情報を作成した後、初期ロードを実行しなくても、外部データにデータベース内からアクセスできます。

外部表を構築するには、以下の手順を実行します。

SHユーザーとしてログインしたSQL*Plusセッションで、 create_external_table.sql スクリプトを実行するか、以下のコマンドをコピーします。 

                                           
DROP TABLE sales_delta_XT;
                                        
                                           
CREATE TABLE sales_delta_XT
               (
   PROD_ID NUMBER,
   CUST_ID NUMBER,
   TIME_ID DATE,
   CHANNEL_ID CHAR(2),
   PROMO_ID NUMBER,
   QUANTITY_SOLD NUMBER(3),
   AMOUNT_SOLD NUMBER(10,2)
   )
   ORGANIZATION external
   (
   TYPE oracle_loader
   DEFAULT DIRECTORY data_dir
   ACCESS PARAMETERS
   (
   RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
   BADFILE log_dir:'sh_sales.bad'
   LOGFILE log_dir:'sh_sales.log_xt'
   FIELDS TERMINATED BY "|" LDRTRIM
   (prod_id, cust_id,
   time_id CHAR(11) DATE_FORMAT DATE MASK "DD-MON-YYYY",
   channel_id, promo_id, quantity_sold, amount_sold
   )
   )
   location
   (
   'salesDec01.dat'
   )
   )REJECT LIMIT UNLIMITED NOPARALLEL

このアイコンの上にカーソルを置くと、イメージが表示されます。

以下のデータ・ディクショナリ・ビューを通じて外部表の情報を参照できます。

        - [USER | ALL| DBA]_EXTERNAL_TABLES
        - [ALL| DBA]_DIRECTORIES
        - [USER | ALL| DBA]_EXTERNAL_LOCATIONS


リストへ戻る

3. 外部表からの選択

以下のSQLコマンドに示されているように、追加アクションを実行しないで外部ファイルのデータにアクセスできます。

SHユーザーとしてログインしたSQL*Plusセッションで、以下の問合せまたは select_et.sql ファイルを実行します。 

                                           
SELECT COUNT(*) FROM sales_delta_xt; 
SELECT MAX(time_id) FROM sales_delta_xt;

ファイルを正しくコピーした場合、最大の TIME_IDは、2001年12月の末日です。

このアイコンの上にカーソルを置くと、イメージが表示されます。


リストへ戻る

4. 外部表の透過的な高速パラレル・アクセスの提供

外部表のアクセスは、SQL*Loaderとは異なり、外部ファイルの数に関係なくパラレル実行できます。 SQL*Loaderは、ファイル単位の操作だけを実行できます。 つまり、パラレル処理を実行するには、大規模なソース・ファイルを手動で分割する必要があります。 外部表を使用すると、通常の表と同じように、パラレル処理度が正確に制御されます。 この場合、デフォルトで NOPARALLEL外部表を定義します。 次の項では、ヒントを使用した文レベルのパラレル処理度を制御する方法について説明します。

1.

parallel_select_from_ET.sql スクリプトには、次の3つの手順のSQL文が含まれます。

SHユーザーとしてログオンしたSQL*Plusセッションで、次の問合せまたは parallel_select_from_ET.sqlファイルを実行して、現在のパラレル・セッション統計を確認します。 

                                           
SELECT * 
FROM   v$pq_sesstat 
WHERE  statistic in ('Queries Parallelized', 
                     'Allocation Height');

このアイコンの上にカーソルを置くと、イメージが表示されます。

 
2.

以前に使用した問合せをそのまま実行して、ヒントで制御されるパラレル処理度4の外部表にアクセスします。 以下のコマンドまたは parallel_select_from_ET_2.sql スクリプトを使用できます。 

                                           
SELECT /*+ parallel(a,4) */ COUNT(*) 
FROM   sales_delta_XT a;

このアイコンの上にカーソルを置くと、イメージが表示されます。

外部表は1つの入力ソース・ファイルだけを示しますが、外部表からパラレルに選択しています。 または、次の ALTER TABLEコマンドを使用して、外部表の PARALLELプロパティを変更できます。 

                                           
ALTER TABLE sales_delta_XT PARALLEL 4;

 
3.

セッション統計を再表示して、違いを確認します。 以下のコマンドまたは parallel_select_from_ET.sql スクリプトを実行します。 パラレル・セッション統計は変更されています。 最後の問合せがパラレル処理され、そのパラレル処理度が表示されます。 

                                           
SELECT * 
FROM   v$pq_sesstat 
WHERE  statistic in 
       ('Queries Parallelized', 'Allocation Height');

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

5. オラクルのパラレル挿入機能の確認

Oracle Databaseは、各パーティション内に無制限のパラレル・ダイレクト・パスの INSERT機能を提供します。 実行計画を使用して、 INSERTのパラレル処理を実行するかどうかを決定できます。 または、 EXPLAIN PLANコマンドを実行しないで、SQLキャッシュで操作の実行計画を確認できます。

次のシリアル計画を検証します。 パラレルで定義されるオブジェクトがないため、オブジェクトのデフォルトのパラレル処理度を変更する場合およびヒントを使用する場合を除いて、自動的にシリアル実行されます。

1.

SERIAL INSERT処理の実行計画を表示するには、 show_serial_exec_plan.sql を実行するか、以下のSQL文をSQL*Plusセッションにコピーします。 

                                           
EXPLAIN PLAN FOR INSERT /*+ APPEND */ INTO sales
               (
   PROD_ID,
   CUST_ID,
   TIME_ID,
   CHANNEL_ID,
   PROMO_ID,
   QUANTITY_SOLD,
   AMOUNT_SOLD
   )
   SELECT
   PROD_ID,
   CUST_ID,
   TIME_ID,
   case CHANNEL_ID
   when 'S' then 3
   when 'T' then 9
   when 'C' then 5
   when 'I' then 4
   when 'P' then 2
   else 99
   end,
   PROMO_ID,
   sum(QUANTITY_SOLD),
   sum(AMOUNT_SOLD)
   FROM SALES_DELTA_XT
   GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;
                                        
                                           
set linesize 140
set pagesize 40
SELECT * FROM TABLE(dbms_xplan.display);


                                           このアイコンの上にカーソルを置くと、イメージが表示されます。
2.

PARALLEL INSERT実行計画を表示するには、 SHユーザーとしてログインして、以下のコマンドまたは show_parallel_exec_plan.sql スクリプトを実行します。 パラレルDMLコマンドは、常にトランザクションの最初の文に配置する必要があります。 また、主キーおよび外部キーの制約がある場合、DML操作は実行できません。 このため、パラレルDML操作の前に制約を無効にする必要があります。 

                                           
                                             
                                                                                    
ALTER TABLE sales DISABLE CONSTRAINT sales_product_fk;
                                            


ALTER TABLE sales DISABLE CONSTRAINT sales_customer_fk;
                                            


ALTER TABLE sales DISABLE CONSTRAINT sales_time_fk;
                                            


ALTER TABLE sales DISABLE CONSTRAINT sales_channel_fk;
                                            


ALTER TABLE sales DISABLE CONSTRAINT sales_promo_fk;
                                          
                                        
                                           
COMMIT;


ALTER SESSION ENABLE PARALLEL DML;
                                        
                                           
EXPLAIN PLAN FOR INSERT /*+ APPEND PARALLEL(SALES,4) */ INTO sales
                                          
                                          
(
   PROD_ID,
   CUST_ID,
   TIME_ID,
   CHANNEL_ID,
   PROMO_ID,
   QUANTITY_SOLD,
   AMOUNT_SOLD
   )
   SELECT /*+ parallel (sales_delta_XT,4) */
   PROD_ID,
   CUST_ID,
   TIME_ID,
   case CHANNEL_ID
                                          
                                             
 
                                            
                                          
when 'S' then 3
   when 'T' then 9
   when 'C' then 5
   when 'I' then 4
   when 'P' then 2
   else 99
   end,
   PROMO_ID,
   sum(QUANTITY_SOLD),
   sum(AMOUNT_SOLD)
   FROM SALES_DELTA_XT
   GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;
                                        
                                           
set linesize 140
set pagesize 40
SELECT * FROM TABLE(dbms_xplan.display);


このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

6. パラレル挿入の実行

ここでは、前述したパラレル INSERT（挿入）を実行します。 挿入前、外部表からデータを SELECT（選択）するだけではなく、 SELECTの一部として集計を行うことに注意してください。 変換と実際のロード・プロセスを統合しています。 この操作は、SQL*Loaderユーティリティだけでは実行できません。

1.

以下のSQL文または parallel_insert_file.sql ファイルを実行して、パラレル INSERT（挿入）を行います。 タイミングを ONに設定します。 

                                           


set timing on
COMMIT;
                                        
ALTER SESSION ENABLE PARALLEL DML;

                                           
                                             


INSERT /*+ APPEND PARALLEL(SALES,4) */ INTO sales
                                            


(
                                            


  PROD_ID,
                                            


  CUST_ID,
                                            


  TIME_ID,
                                            


  CHANNEL_ID,
                                            


  PROMO_ID,
                                            


  QUANTITY_SOLD,
                                            


  AMOUNT_SOLD
                                            


)
                                            


SELECT /*+ PARALLEL (sales_delta_XT,4) */
                                            


  PROD_ID,
                                            


  CUST_ID,
                                            


  TIME_ID,
                                            


  case CHANNEL_ID
                                            


  when 'S' then 3
                                            


  when 'T' then 9
                                            


  when 'C' then 5
                                            


  when 'I' then 4
                                            


  when 'P' then 2
                                            


  else 99
                                            


  end,
                                            


  PROMO_ID,
                                            


  sum(QUANTITY_SOLD),
                                            


  sum(AMOUNT_SOLD)
                                            


FROM SALES_DELTA_XT
                                            


GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;
set timing off

SELECT * FROM TABLE(dbms_xplan.display_cursor);

                                             


SELECT * 
FROM   v$pq_sesstat 
WHERE  statistic in ('DML Parallelized','Allocation Height');

この文の実行時間を記録し、SQL*Loaderと後続の挿入で必要な合計時間と比較します。 1つのディスクを使用した単一のCPUマシンで非常に少量のデータにパラレル・アクセスしているため、外部表アクセスのパラレル処理を実行して変換とロードを統合する利点をすべて確認することはできません。

このアイコンの上にカーソルを置くと、イメージが表示されます。

 
2.

ROLLBACKを実行して、データを以前の状態に戻します。 （次の例で、SQL*Loaderを使用して、同じデータを挿入します。） 

                                           
                                             
ROLLBACK;
                                          

                                           このアイコンの上にカーソルを置くと、イメージが表示されます。
3.

ROLLBACKを発行した後、制約を再度有効にする必要があります。 以下のコマンドまたは enable_cons.sql スクリプトを実行します。 

                                           
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_product_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_customer_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_time_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_channel_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_promo_fk ENABLE NOVALIDATE;
                                          

                                             このアイコンの上にカーソルを置くと、イメージが表示されます。


単一の手順でデータをロードおよび変換しました。 SQL*Loaderを使用してデータをロードおよび変換するには、2つのプロセスが必要です。つまり、より多くの作業が必要で、プロセス完了のパフォーマンスが低下します。

リストへ戻る

トピック・リストに戻る

外部表のロードおよび変換プロセスとSQL*Loaderの比較

以前に実行した外部表の手順は、データのロードおよび変換に適した手順です。 外部表の利点を表すため、SQL*Loaderを使用して、データのロードおよび変換に必要なタスクを比較できます。

SQL*Loaderを使用してデータをロードおよび変換するには、以下の手順を実行します。

1.

ステージング表の作成

2.

SQL*Loaderによるステージング表へのデータのロード

3. ターゲット・データベースへのステージング表のロード
4. ステージング表の削除

1. ステージング表の作成

2つ目の手順でデータベース内の変換を実行するには、データをロードするステージング表が必要です。

SHユーザーとして接続したSQL*Plusセッションで、以下のコマンドまたは create_stage.sql スクリプトを実行して、ステージング表を作成します。 

                                           

                                           
CREATE TABLE sales_dec01 AS
                                            


  SELECT *
                                            


   FROM   sales
                                            


   WHERE  1=0;

 ALTER TABLE sales_dec01 MODIFY (channel_id CHAR(2) null);
                                          
                                           
                                             このアイコンの上にカーソルを置くと、イメージが表示されます。

リストへ戻る

2. SQL*Loaderによるステージング表へのデータのロード

注： スクリプトはLinuxシステム用に設定されているので、ファイルが /home/oracle/wkdir ディレクトリに 抽出されると仮定します。

以下の手順を実行して、 sales_dec01.ctlファイルからステージング表にデータファイルをロードします。

1.

OSコマンドラインから以下のコマンドを実行します。

                                           
                                             
cd /home/oracle/wkdir
sqlldr  
                                              
sh/sh control=sales_dec01.ctl direct=true
                                                                                                                                
                                             このアイコンの上にカーソルを置くと、イメージが表示されます。

注： SQL*Loaderを使用して接続する場合、データベース・エイリアスの指定が必要な場合があります。 以下の文とともにSQL*Loaderを起動します。 

                                           
sqlldr  
                                            
sh/sh@<database alias> control=sales_dec01.ctl direct=true
2.

このタスクのパラレル処理は実行できません。 SQL*Loaderの sales_dec01.logログ・ファイルを確認し、ロード・プロセスの実行時間を記録します。

任意のエディタを使用して、 sales_dec01.logファイルを確認できます。 ファイルは、 /home/oracle/wkdirディレクトリにあります。

このアイコンの上にカーソルを置くと、イメージが表示されます。

外部表とは異なり、データベース内からデータにアクセスできるように、領域がデータベースで使用されます。 ステージング表によって使用される領域は、追加の変換用にロードされるデータ量と線形従属の関係にあります。

また、いくつかの外部ファイルを使用することなくSQL*Loaderでロードのパラレル処理を実行することはできません。 いくつかのSQL*Loaderプロセスの SKIPオプションを使用して同じファイルにアクセスできます。 ただし、このオプションは、各SQL*Loaderプロセスを実施して、全体の外部ファイルをスキャンします。 これは、全体のパフォーマンスに悪影響を与えます。

オブジェクトの領域使用量の情報は、以下のデータ・ディクショナリ・ビューを通じてアクセスできます。
[USER | ALL| DBA]_SEGMENTS
[USER | ALL| DBA]_EXTENTS

リストへ戻る

3. ターゲット・データベースへのステージング表のロード

外部データをロードしてデータベースでアクセス可能にした後、変換を実行できます。

 

SHユーザーとしてSQL*Plusにログインします。 以下のコマンドまたは load_stage_table.sql スクリプトを実行して、データベースにすでにステージングされている外部データを SALESファクト表に変換および挿入するSQL文を実行します。 

                                           
set timing on
                                            


INSERT /*+ APPEND */ INTO sales
                                            


  ( PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID,
                                            


    PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD )
                                            


  SELECT
                                            


    PROD_ID, CUST_ID, TIME_ID,
                                            


    case CHANNEL_ID
                                            


    when 'S' then 3
                                            


    when 'T' then 9
                                            


    when 'C' then 5
                                            


    when 'I' then 4
                                            


    when 'P' then 2
                                            


    else 99
                                            


    end,
                                            


    PROMO_ID,
                                            


    sum(QUANTITY_SOLD),
                                            


    sum(AMOUNT_SOLD)
                                            


  FROM sales_dec01
                                            


  GROUP BY prod_id,time_id,cust_id,channel_id,promo_id;

set timing off

このアイコンの上にカーソルを置くと、イメージが表示されます。

リストへ戻る

4. ステージング表の削除

ステージング表の削除または切捨てを実行して、使用されている領域を解放できます。

 

以下のコマンドまたは drop_sales_dec01.sql スクリプトを使用して、ステージング表の情報を削除します。 

  
DROP TABLE sales_dec01;   
                                          
                                          
                                             このアイコンの上にカーソルを置くと、イメージが表示されます。

このシンプルなロードおよび変換プロセスの外部表を使用すると、ロードおよび変換を統合できます。これによって、プロセスが簡素化および高速化されます。 また、データベース内のデータのステージングは、外部表では必要ありません。 外部データの量が多くなるほど、SQL*Loaderの代わりに外部表を使用することでステージング領域および処理時間がさらに節約されます。

リストへ戻る

トピック・リストに戻る

表圧縮を活用したディスク領域の節約とTCOの削減

12月のデータを SALESファクト表の第4四半期パーティションに正常にロードすると、このパーティションは、最小限のDML操作以外何も発生しません。 したがって、このパーティションは、オラクルの表圧縮機能を使用して、ストレージの最適な候補になります。 ビジネスで必要とされる情報が増えるにつれ、リレーショナル・データベースに格納されるデータも増加します。 大量のデータを保持するコストの大部分は、ディスク・システムとデータを管理するために使用されるリソースのコストです。 Oracle Databaseは、一意な方法を使用して、このコストに対処します。データの問合せ時間にマイナスの影響をほとんど与えることなく、リレーショナル表に格納されたデータを圧縮し、大幅なコスト削減を実現します。

市販のリレーショナル・データベース・システムは、リレーショナル表に格納されたデータの圧縮技術をあまり利用しません。 1つの理由として、圧縮の時間と領域のトレードオフがリレーショナル・データベースで必ずしも魅力的ではない点があります。 通常の圧縮技術によって領域を節約できるかもしれませんが、データの問合せ時間が大幅に増加します。 また、多くの標準的な技術では、圧縮後にデータ・サイズが増加しないことを保証していません。

Oracle Databaseは、大規模なデータ・ウェアハウスに非常に役立つ一意な圧縮技術を提供します。 Oracle Databaseは、多くの面で一意です。 リレーショナル・データ用に最適化されているため、ディスク領域の削減量は、標準的な圧縮アルゴリズムよりも大幅に多くなります。 圧縮データの問合せのパフォーマンスにマイナスの影響を与えることはほとんどありません。実際、バックアップおよびリカバリなどのデータ管理操作と同様に、大量のデータにアクセスする問合せに大きなプラスの影響を与える場合があります。 また、圧縮データが非圧縮データより大きくならないようにします。

1. 最新のパーティションの圧縮

表圧縮の利点を測定するには、最新のパーティションに有効な圧縮がないことを確認します。 また、その大きさを決定します。

1.

part_before_compression.sql スクリプトを実行するか、次のSQL文をSQL*Plusセッションにコピーします。 

                                           

COLUMN partition_name FORMAT a50
                                            


COLUMN segment_name FORMAT a50

SELECT partition_name, compression FROM user_tab_partitions
WHERE partition_name='SALES_Q4_2001';


SELECT segment_name, bytes/(1024*1024) MB FROM user_segments
WHERE segment_name='SALES' AND partition_name='SALES_Q4_2001';
                                          

                                           
                                             このアイコンの上にカーソルを置くと、イメージが表示されます。
2.

パーティションを圧縮し、既存のすべての索引を透過的に保存します。 すべてのローカル索引およびグローバル索引は、このSQL文の一部として保存されます。 パーティション保存操作のオンラインの索引メンテナンス機能は、このチュートリアルの後半で取り上げます。

パーティションの圧縮は、インプレース圧縮ではありません。 新しく圧縮したセグメントを作成し、操作の最後に古い非圧縮のセグメントを削除します。

SHユーザーとしてログインしたSQL*Plusセッションで、 compress_salesQ4_2001.sqlスクリプトまたは次のSQL文を実行します。 

                                           
ALTER TABLE sales MOVE PARTITION sales_q4_2001 COMPRESS UPDATE INDEXES;

                                             このアイコンの上にカーソルを置くと、イメージが表示されます。


3.

以下のコマンドまたは part_after_compression.sqlスクリプトを実行して、新しく圧縮したパーティションが割り当てられる領域を確認し、非圧縮のパーティションのサイズと比較します。 

                                           
                                          
SELECT partition_name, compression FROM user_tab_partitions
                                            


WHERE partition_name='SALES_Q4_2001';
                                             


SELECT segment_name, bytes/(1024*1024) MB FROM user_segments
                                            


WHERE segment_name='SALES' AND partition_name='SALES_Q4_2001';
                                          


                                           
                                             このアイコンの上にカーソルを置くと、イメージが表示されます。

通常、実際のデータの圧縮比率は、売上履歴スキーマの圧縮比率よりも高くなります。 SALESファクト表のデータは、人工的に生成され、 INSERTの前にデータがクレンジング、統合、または集計されたデータ・ウェアハウス環境の通常の"自然なソート"で表示されません。

トピック・リストに戻る

Oracle Partitioningによるローリング・ウィンドウ操作の実行

多くのデータ・ウェアハウスは、データのローリング・ウィンドウを保存します。 たとえば、このデータ・ウェアハウスは、最近12ヶ月の売上データを保存します。 新しいパーティションを SALES表に追加できるように、古いパーティションを SALES表から迅速に1つずつ削除できます。 Oracle Partitioningは、これらの操作の理想的なフレームワークを提供します。 2つの利点（リソース利用の削減とエンド・ユーザーの影響の最小化）は、パーティションの追加と同様にパーティションの削除にも効果的です。

ローリング・ウィンドウ操作の手順の実行

ローリング・ウィンドウ操作の実行手順

1.

新しいデータを使用したスタンドアロン表の準備

2.

ファクト表への新しいデータの追加

3. ファクト表からの古いデータの削除

1. 新しいデータを使用したスタンドアロン表の準備

ローリング・ウィンドウ操作を実行するには、以下の手順を実行して、新しいデータを使用したスタンドアロン表の作成およびロードを行う必要があります。 以前に定義した外部表を使用しますが、外部表を異なる外部ファイルに指定します。

1.1

外部表の変更による第1四半期の売上データの使用

1.2

新しい第1四半期の売上データ表の作成

1.3 この表のロード
1.4

この表のビットマップ索引の作成

1.5

この表の制約の作成

1.1 外部表の変更による第1四半期の売上データの使用

この項では、すでに定義した外部表を使用します。 ただし、今回は異なる外部ファイルの sales_Q1_dataを使用します。 このため、外部表の位置属性を変更して、新しいデータファイルを指定する必要があります。

1.

最初に、 select_et.sql スクリプト・ファイルまたは以下のSQL文を実行して、現在の外部表の行数を確認します。 

                                           
SELECT COUNT(*) FROM sales_delta_xt;
SELECT MAX(time_id) FROM sales_delta_xt;

このアイコンの上にカーソルを置くと、イメージが表示されます。

2001年12月のすべての売上トランザクションを含むファイルは、2001年12月の末日の値を示します。OSレベルで外部ファイルを変更した後、行数と MAX(time_id) が異なっていることがわかります。

 
2.

LOCATION属性を変更します。 以下のコマンドまたは alter_loc_attrib.sql スクリプトを実行して、 LOCATION属性を変更します。 

ALTER TABLE sales_delta_xt location ( 'salesQ1.dat' );

                                           
                                             このアイコンの上にカーソルを置くと、イメージが表示されます。

新しいデータを確認するには、以下のコマンドまたは select_et.sql スクリプトを実行します。 

                                           

SELECT COUNT(*) FROM sales_delta_xt;
SELECT MAX(time_id) FROM sales_delta_xt;

行数と 最大の TIME_IDが変更されます。 外部表ファイルが正しい場合、最大の TIME_IDは 2002年3月の末日です。

このアイコンの上にカーソルを置くと、イメージが表示されます。

リストへ戻る

1.2 新しい第1四半期の売上データ表の作成

ここでは、新しい第1四半期の売上データの空の表を作成します。 この表は、パーティション化された既存の SALES表に後で追加されます。

 

以下のコマンドまたは create_stage_table.sql スクリプトを実行して表を作成します。 

DROP TABLE sales_delta;
                                           


CREATE TABLE sales_delta NOLOGGING NOCOMPRESS 
  AS SELECT * FROM sales WHERE 1=0;

このアイコンの上にカーソルを置くと、イメージが表示されます。

リストへ戻る

1.3 この表のロード

この表をロードするには、以下の手順を実行します。

1.

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは load_stage_table2.sql スクリプトを実行して表をロードします。 

INSERT /*+ APPEND */ INTO sales_delta
                                          


SELECT /*+ PARALLEL (SALES_DELTA_XT,4) */
                                          


  PROD_ID,
                                          


  CUST_ID,
                                          


  TIME_ID,
                                          


  case CHANNEL_ID
                                          


  when 'S' then 3
                                          


  when 'T' then 9
                                          


  when 'C' then 5
                                          


  when 'I' then 4
                                          


  when 'P' then 2
                                          


  else 99
                                          


  end,
                                          


  PROMO_ID,
                                          


  sum(QUANTITY_SOLD) quantity_sold,
                                          


  sum(AMOUNT_SOLD) amount_sold
                                          


FROM SALES_DELTA_XT
                                          


GROUP BY prod_id,time_id,cust_id,channel_id,promo_id;

COMMIT;

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

 
2.

SALES_DELTA表をロードした後、この新しく作成した表の統計を収集します。 SHユーザーとしてログインしたSQL*Plusセッションで、以下のコマンドまたは gather_stat_stage_table.sql スクリプトを実行して、表の統計を収集します。 

                                           
exec dbms_stats.gather_table_stats('SH','sales_delta',estimate_percent=>20);

このアイコンの上にカーソルを置くと、イメージが表示されます。

リストへ戻る

1.4 この表のビットマップ索引の作成

このスタンドアロン表と空のパーティションの SALES表を後で交換するので、交換した後に使用できるように、既存の SALES表とまったく同じ索引構造を構築して、この表のローカル索引構造を保存する必要があります。

1.

ビットマップ索引を作成する前に、新しく作成した表を実際にデータを圧縮することなく圧縮された表に変更する必要があります。 この操作は、圧縮されたパーティションがすでに含まれるパーティション化された表に交換できるようにするために必要です。 以下のコマンドまたは alter_sales_delta.sql スクリプトを実行して表を変更します。 

ALTER TABLE sales_delta COMPRESS;
ALTER TABLE sales_delta NOCOMPRESS;

                                           
                                             このアイコンの上にカーソルを置くと、イメージが表示されます。
2.

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは create_static_bitmap_index.sql スクリプトを実行して、 SALES_DELTA表のビットマップ索引を作成します。 

                                           
                                          
CREATE BITMAP INDEX sales_prod_local_bix
  ON sales_delta (prod_id)
  NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_cust_local_bix
  ON sales_delta (cust_id)
  NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_time_local_bix
  ON sales_delta (time_id)
  NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_channel_local_bix
   ON sales_delta (channel_id)
   NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_promo_local_bix
   ON sales_delta (promo_id)
   NOLOGGING COMPUTE STATISTICS ;

索引作成の一部として、これらの索引の統計が作成されます。

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

1.5 この表の制約の作成

 

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは create_constraints.sql スクリプトを実行して、 SALES表の制約を変更します。 

                                           
ALTER TABLE channels MODIFY CONSTRAINT CHANNELS_PK RELY;
                                            


ALTER TABLE countries MODIFY CONSTRAINT COUNTRIES_PK RELY;
                                            


ALTER TABLE customers MODIFY CONSTRAINT CUSTOMERS_PK RELY;
                                            


ALTER TABLE products MODIFY CONSTRAINT PRODUCTS_PK RELY;
                                            


ALTER TABLE promotions MODIFY CONSTRAINT PROMO_PK RELY;
                                            


ALTER TABLE times MODIFY CONSTRAINT TIMES_PK RELY;

                                          ALTER TABLE sales_delta
               ADD ( CONSTRAINT sales_product_delta_fk
   FOREIGN KEY (prod_id)
   REFERENCES products RELY ENABLE NOVALIDATE
   , CONSTRAINT sales_customer_delta_fk
   FOREIGN KEY (cust_id)
   REFERENCES customers RELY ENABLE NOVALIDATE
   , CONSTRAINT sales_time_delta_fk
   FOREIGN KEY (time_id)
   REFERENCES times RELY ENABLE NOVALIDATE
   , CONSTRAINT sales_channel_delta_fk
   FOREIGN KEY (channel_id)
   REFERENCES channels RELY ENABLE NOVALIDATE
   , CONSTRAINT sales_promo_delta_fk
   FOREIGN KEY (promo_id)
   REFERENCES promotions RELY ENABLE NOVALIDATE
   ) ;

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

トピックに戻る

2. ファクト表への新しいデータの追加

ローリング・ウィンドウ操作の次のタスクでは、新しくロードおよび索引付けされたデータをファクト表に追加します。 追加するには、以下の手順に従います。

2.1

新しいパーティションの作成（パーティションが作成されていない場合）

2.2

パーティションの交換（データ・ディクショナリ操作のみでデータは操作しません）.

2.3 パーティションからの選択による妥当性の管理
2.4 最新のパーティションの分割による（ビジネス）データ整合性の確保

2.1 新しいパーティションの作成

新しい空のパーティションを作成する必要があります。 個別の上限を持つ新しいパーティションを作成するか、 MAXVALUEキーワードを使用できます。 キーワードを使用すると、上限の条件に違反する可能性のあるレコードが拒否されずに、 INSERT操作が正常に実行されます。

このビジネス・シナリオでは、ロード操作の完了後に SPLIT PARTITIONを発行して、潜在的な違反を識別します。 上限に違反するすべてのレコードが追加のパーティションに"分離"されます。

新しい空のパーティションを作成する必要があります。 作成するには、以下の手順に従います。

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは create_partition_for_sales_etl.sql スクリプトを実行して、パーティションを SALES表に追加します。 

                                           
                                          
COLUMN partition_name FORMAT a20
                 
select partition_name, high_value 
from   user_tab_partitions 
where  table_name='SALES'
order by partition_position;

ALTER TABLE sales 
ADD PARTITION sales_q1_2002
VALUES LESS THAN (MAXVALUE);

SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_2002);

このアイコンの上にカーソルを置くと、イメージが表示されます。

リストへ戻る

2.2 パーティションの交換

PARTITION EXCHANGEコマンドを実行して、新しくロードおよび索引付けしたデータを実際の SALESファクト表に追加できます。 これはDDLコマンドだけで、実際のデータは操作しません。 追加するには、以下の手順に従います。

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは exchange_partition_wo_gim.sql スクリプトを実行して、 SALES表を ALTER（変更）します。これによって、パーティションが交換されます。 

                                           
ALTER TABLE sales EXCHANGE PARTITION sales_q1_2002
                                            


     WITH TABLE sales_delta INCLUDING INDEXES;

このアイコンの上にカーソルを置くと、イメージが表示されます。

リストへ戻る

2.3 パーティションからの選択

新しく追加および交換したパーティションから選択できます。

パーティション化されたファクト表に追加するデータが多いほど、メタデータのみの操作の時間がさらに節約されます。

レンジ・パーティション化などの論理パーティション操作を行う必要があります。 ハッシュ・パーティション化は、非常に一般的なローリング・ウィンドウ操作には使用できません。

SALES表のすべての索引が保存され使用できます。

1.

SHユーザーとしてログオンしたSQL*Plusセッションで、以下の問合せまたは select_count.sqlスクリプトを実行します。 交換したパーティションの行数とスタンドアロン表（現在は空になっている）が表示されます。 

SELECT COUNT(*)
                                          


FROM   sales PARTITION (sales_q1_2002);

SELECT COUNT(*)
                                          


FROM   sales_delta;
                                          

                                             
                                               このアイコンの上にカーソルを置くと、イメージが表示されます。

 
2.

SALES表のすべてのローカル索引は有効になっています。 以下のコマンドまたは show_sales_idx_status.sql スクリプトを実行して、索引の状態を参照します。 

SELECT ui.index_name, DECODE(uip.status,null,ui.status,uip.status)
                                          


FROM user_ind_partitions uip, user_indexes ui
                                          


WHERE ui.index_name=uip.index_name(+)
                                          


AND ui.table_name='SALES'
                                          


GROUP BY ui.index_name, DECODE(uip.status,null,ui.status,uip.status);

このアイコンの上にカーソルを置くと、イメージが表示されます。

また、 PARTITION EXCHANGEコマンドの一部として、 WITHOUT VALIDATION句も使用できます。 これによって、交換される表の妥当性の確認がOracle Databaseサーバーで行われなくなります。 使用しない場合、Oracle Databaseサーバーは、パーティション化キーのすべての値がパーティション境界内で一致することを保証します。

リストへ戻る

2.4 最新のパーティションの分割による（ビジネス）データ整合性の確保

すでに説明したとおり、上限が固定されていないパーティションにデータをロードして、潜在的なエラーを回避しました。 潜在的な違反を識別するには、最新のパーティションを分割します。これによって、2つのパーティションが作成されます。1つのパーティションは、上限が固定されています。

Oracle Databaseは、 SPLIT操作後の2つの新しいパーティションの1つが空であるかどうかを検出するため、強化された高速の分割操作を使用します。 この場合、Oracle Databaseサーバーは、2つの新しいセグメントを作成しません。 空の新しいパーティションに DBMS_STATSを使用して、1つのセグメントだけを作成します。また、すべてのデータを含む新しいパーティションとして既存のセグメントを使用します。

この最適化は、完全に透過的です。 SPLIT操作の実行時間が向上し、システム・リソースが節約されます。また、索引のメンテナンスが必要ありません。

1.

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは fast_split_sales.sql スクリプトを実行して、 SALES表の変更と索引状態の参照を行います。 

                                           
                                          
ALTER TABLE sales SPLIT PARTITION sales_q1_2002
                                            


   AT (TO_DATE('01-APR-2002','DD-MON-YYYY'))
                                            


   INTO (PARTITION sales_q1_2002, PARTITION sales_beyond_q1_2002);
                                          
                                        
SELECT COUNT(*) FROM sales PARTITION (sales_beyond_q1_2002);

SELECT ui.index_name, DECODE(uip.status,null,ui.status,uip.status)
FROM user_ind_partitions uip, user_indexes ui
WHERE ui.index_name=uip.index_name(+)
AND ui.table_name='SALES'
GROUP BY ui.index_name, DECODE(uip.status,null,ui.status,uip.status);

ALTER TABLE sales DROP PARTITION sales_beyond_q1_2002;

このアイコンの上にカーソルを置くと、イメージが表示されます。

 
2.

SALES表のすべてのローカル索引は有効になっています。 show_sales_idx_status.sql スクリプトを実行して、 SALES表のローカル索引の状態を参照します。 

                                           
@show_sales_idx_status.sql

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

トピックに戻る

3. ファクト表からの古いデータの削除

ローリング・ウィンドウ操作の次のタスクでは、ファクト表から古いデータを削除します。 過去3年の最新のデータだけを分析します。 2002年の第1四半期を追加したので、1998年の第1四半期のデータを削除する必要があります。

レンジ・パーティション化を使用しないで、表のDML操作を実行する必要があります。 パーティション化を使用する場合、 PARTITION EXCHANGEコマンドを再利用して、ファクト表からデータを削除できます。 この場合、新しいデータの追加に類似しているハッシュ・パーティション化は役立ちません。

データを 削除するわけではありません。 代わりに、 SALESファクト表のこのデータを含むパーティションと同じ論理構造を持つ空のスタンドアロン表を交換します（論理的に置き換えます）。 ビジネス・ニーズに応じて、このデータをアーカイブするか、交換したパーティションを削除できます。

3.1

空のスタンドアロン表の作成

3.2

この表のビットマップ索引の作成

3.3 この表の制約の作成
3.4 交換前のパーティションのデータ表示
3.5 空の新しい表と既存の1998年第1四半期のパーティションの交換
3.6 交換後のパーティションのデータ表示

3.1 空のスタンドアロン表の作成

古い1998年のデータを保存する空の表を作成する必要があります。

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは create_empty_sat.sql スクリプトを実行して、古い1998年のデータを保存する空の表を作成します。 

                                           
DROP TABLE sales_old_q1_1998;

CREATE TABLE sales_old_q1_1998 NOLOGGING COMPRESS
                                          


  AS SELECT * FROM sales WHERE 1=0;

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

3.2 この表のビットマップ索引の作成

ローカル索引を作成します。

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは create_ndx.sql スクリプトを実行して、ローカル索引を作成します。 

                                           
CREATE BITMAP INDEX sales_prod_old_bix
                                            


       ON sales_old_q1_1998 (prod_id)
                                            


       NOLOGGING COMPUTE STATISTICS ;
                                            


CREATE BITMAP INDEX sales_cust_old_bix
                                            


       ON sales_old_q1_1998 (cust_id)
                                            


       NOLOGGING COMPUTE STATISTICS ;
                                            


CREATE BITMAP INDEX sales_time_old_bix
                                            


       ON sales_old_q1_1998 (time_id)
                                            


       NOLOGGING COMPUTE STATISTICS ;
                                            


CREATE BITMAP INDEX sales_channel_old_bix
                                            


       ON sales_old_q1_1998 (channel_id)
                                            


       NOLOGGING COMPUTE STATISTICS ;
                                            


CREATE BITMAP INDEX sales_promo_old_bix
                                            


       ON sales_old_q1_1998 (promo_id)
                                            


       NOLOGGING COMPUTE STATISTICS ;

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

3.3 この表の制約の作成

制約を作成します。

 

SHスキーマでログオンしたSQL*Plusセッションで、以下のコマンドまたは create_constraints_old.sql スクリプトを実行して、制約を変更および作成します。 

                                           
                                             
                                                                                    
ALTER TABLE channels MODIFY CONSTRAINT CHANNELS_PK RELY;
                                            


ALTER TABLE countries MODIFY CONSTRAINT COUNTRIES_PK RELY;
                                            


ALTER TABLE customers MODIFY CONSTRAINT CUSTOMERS_PK RELY;
                                            


ALTER TABLE products MODIFY CONSTRAINT PRODUCTS_PK RELY;
                                            


ALTER TABLE promotions MODIFY CONSTRAINT PROMO_PK RELY;
                                            


ALTER TABLE times MODIFY CONSTRAINT TIMES_PK RELY; 

                                          ALTER TABLE sales_old_q1_1998
                                          


  ADD ( CONSTRAINT sales_product_old_fk
                                          


  FOREIGN KEY (prod_id)
                                          


  REFERENCES products RELY ENABLE NOVALIDATE
                                          


  , CONSTRAINT sales_customer_old_fk
                                          


  FOREIGN KEY (cust_id)
                                          


  REFERENCES customers RELY ENABLE NOVALIDATE
                                          


  , CONSTRAINT sales_time_old_fk
                                          


  FOREIGN KEY (time_id)
                                          


  REFERENCES times RELY ENABLE NOVALIDATE
                                          


  , CONSTRAINT sales_channel_old_fk
                                          


  FOREIGN KEY (channel_id)
                                          


  REFERENCES channels RELY ENABLE NOVALIDATE
                                          


  , CONSTRAINT sales_promo_old_fk
                                          


  FOREIGN KEY (promo_id)
                                          


  REFERENCES promotions RELY ENABLE NOVALIDATE
                                          


  ) ;

このアイコンの上にカーソルを置くと、イメージが表示されます。

リストへ戻る

3.4 交換前のパーティションのデータ表示

交換を実行する前に、パーティションの古い1998年第1四半期のデータを参照します。

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは show_partition.sql スクリプトを実行して、パーティションの古いデータを参照します。 

                                           
SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_1998);

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

3.5 パーティションの交換

空の表と既存の1998年第1四半期のパーティションを交換します。 交換するには、以下の手順に従います。

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは exchange_old_partition.sql スクリプトを実行して、パーティションを交換します。 

ALTER TABLE sales 
   EXCHANGE PARTITION sales_q1_1998 
   WITH TABLE sales_old_q1_1998 
   INCLUDING INDEXES;

このアイコンの上にカーソルを置くと、イメージが表示されます。

代わりに DROP PARTITION文を使用できます。 SALES_OLD_Q1_1998表に1998年第1四半期のデータがすべて格納されます。この表を削除して、システムから完全にデータを削除できます。

リストへ戻る

3.6 交換後のパーティションのデータ表示

交換を実行した後に、パーティションのデータを参照します。

1.

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは count_sales.sql スクリプトを実行して、パーティションのデータを参照します。 

                                           
SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_1998);

SELECT COUNT(*) 
FROM   sales_old_q1_1998;

このアイコンの上にカーソルを置くと、イメージが表示されます。

EXCHANGEコマンドの実行前とは異なり、 SALES表の該当パーティションが空になって、スタンドアロン表に数千の行が格納されます。

 
2.

ローカル索引は、交換の影響を受けません。 以下のコマンドまたは show_sales_idx_status.sql スクリプトを実行して、索引の情報を参照します。 

                                           
SELECT ui.index_name, DECODE(uip.status,null,ui.status,uip.status)
                                            


FROM   user_ind_partitions uip, user_indexes ui
                                            


WHERE  ui.index_name=uip.index_name(+)
                                            


AND    ui.table_name='SALES'
                                            


GROUP BY ui.index_name, DECODE(uip.status,null,ui.status,uip.status);

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

トピックに戻る

Oracle Database 10g拡張機能のローカル索引メンテナンス

Oracle Database 10 g のローカル索引メンテナンスの拡張機能を学習するには、オンラインのローカル索引メンテナンスを使用して、最新の四半期パーティションを月パーティションに分割します。 これは、Oracle Database 10gの新機能です。 Oracle9iで導入されたグローバル索引メンテナンス機能も使用します。

1.

Oracle Database 10 g 拡張機能のローカル索引メンテナンスの利用

2.

オラクルのグローバル索引メンテナンスの利用

1. ローカル索引メンテナンスの拡張機能

Oracle Database 10 g 以降、すべてのパーティション・メンテナンス操作は、可用性に影響を与えることなく実行できます。 ローカル索引メンテナンスによって、アトミックなパーティション・メンテナンス操作の一部として、パーティション化された表のローカル索引を最新の状態に保てます。
オラクルは、パーティション・メンテナンス操作のSQL構文を拡張して、影響するすべてのローカル索引構造に対する索引配置などの物理属性を制御しました。

手順

1.1

デフォルトの配置ルールを使用した最新のパーティションの分割

1.2

ローカル索引メンテナンス用の拡張SQL構文を使用したパーティションの分割

1.3 クリーンアップ

このシナリオを検証します。 2002年の第1四半期のデータを正常にロードした後、ビジネス要件の変更によって問合せパターンが変更されたことに気づきます。 四半期ごとの分析に焦点を当てる代わりに、多くのビジネス・ユーザーは、月ごとのレポートおよび分析に依存し始めました。 変更されたこのビジネス要件に対処して問合せのパフォーマンスを最適化するため、Oracle Partitioningを使用して、最新の四半期を月ごとのパーティションに分割できます。

ローカル索引メンテナンスのオンラインの可用性は、この例では取り上げません。 オンラインの可用性は、グローバル索引メンテナンスで示され、ローカル索引においてもまったく同じように機能します。

1.1 最新のパーティションの分割

新しい空のパーティションを作成する必要があります。 個別の上限を持つ新しいパーティションを作成するか、 MAXVALUEキーワードを選択できます。 キーワードを使用すると、上限の条件に違反する可能性のあるレコードが拒否されずに、 INSERT操作が正常に実行されます。

このビジネス・シナリオでは、ロード操作の完了後に SPLIT PARTITIONコマンドを発行して、潜在的な違反を識別します。 上限に違反するすべてのレコードが追加のパーティションに"分離"されます。

以下の手順を実行して、空の新しいパーティションを作成します。

1.

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のSQL文を実行して、四半期パーティションから最新の月（2002年3月）を分離します（ローカル索引メンテナンスを含む）。 以下のコマンドまたは split1_10g.sql スクリプトを実行して、このタスクを実行できます。 

ALTER TABLE sales SPLIT PARTITION sales_q1_2002
AT (TO_DATE('01-MAR-2002','DD-MON-YYYY'))
INTO (PARTITION sales_1_2_2002 TABLESPACE example, 
PARTITION sales_MAR_2002 TABLESPACE example NOCOMPRESS)
UPDATE INDEXES;

このアイコンの上にカーソルを置くと、イメージが表示されます。

 
2.

新しい索引パーティションと表パーティションが同じ場所に配置され、索引パーティションにパーティションのネーミングが継承されていることを確認できます。 以下のコマンドまたは see_split.sql スクリプトを実行して、パーティションの情報を参照します。 

COL segment_name format a25
COL partition_name format a25
COL tablespace_name format a25

SELECT segment_name, partition_name, tablespace_name 
FROM user_segments
WHERE segment_type='INDEX PARTITION'
AND segment_name IN 
   (SELECT index_name 
    FROM   user_indexes 
    WHERE  table_name='SALES');

このアイコンの上にカーソルを置くと、イメージが表示されます。

...

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

1.2 拡張SQL構文を使用したパーティションの分割

前の四半期パーティションの残りを1月および2月のパーティションに分割します。 デモ用に、 SYSAUX表領域に新しいパーティションを作成して、一部の索引に明示的に名前を付けます。

1.

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のSQL文を実行して、残りのパーティションを分割します（ローカル索引メンテナンスを含む）。 以下のコマンドまたは split2_10g.sql スクリプトを実行できます。 

ALTER TABLE sales SPLIT PARTITION sales_1_2_2002
                                          


  AT (TO_DATE('01-FEB-2002','DD-MON-YYYY'))
                                          


  INTO
                                          


   (PARTITION sales_JAN_2002 TABLESPACE sysaux COMPRESS,
                                          


    PARTITION sales_FEB_2002 TABLESPACE example NOCOMPRESS)
                                          


  UPDATE INDEXES (sales_time_bix
                                          


   (PARTITION jan_02 TABLESPACE example,
                                          


    PARTITION feb_02 TABLESPACE system));

このアイコンの上にカーソルを置くと、イメージが表示されます。

 
2.

新しい索引パーティションと表パーティションが同じ場所に配置され、索引パーティションの名前がパーティションから継承されていることを確認できます。 以下のコマンドまたは see_split2.sql スクリプトを実行して、パーティションとセグメントの情報を参照します。 

SELECT segment_name, partition_name, tablespace_name
                                          


FROM   user_segments
                                          


WHERE  segment_type='INDEX PARTITION'
                                          


  AND  tablespace_name <>'EXAMPLE'
                                          


  AND  segment_name IN
                                          


    (SELECT index_name
                                          


     FROM   user_indexes
                                          


     WHERE  table_name='SALES');

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

1.3 クリーンアップ

SYSAUX表領域から EXAMPLE表領域にパーティションを移動して、クリーンアップ操作を実行します。 標準的なネーミング規則を使用します。

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは cleanup_split_10g.sql スクリプトを実行して、パーティションの移動および索引の更新を行います。 

ALTER TABLE sales MOVE PARTITION sales_JAN_2002 TABLESPACE example COMPRESS
                                          


UPDATE INDEXES (sales_time_bix    (PARTITION sales_jan_2002 TABLESPACE example),
                                          


                sales_cust_bix    (PARTITION sales_jan_2002 TABLESPACE example),
                                          


                sales_channel_bix (PARTITION sales_jan_2002 TABLESPACE example),
                                          


                sales_prod_bix    (PARTITION sales_jan_2002 TABLESPACE example),
                                          


                sales_promo_bix   (PARTITION sales_jan_2002 TABLESPACE example))
                                          


;
                                        
ALTER INDEX sales_time_bix 
  REBUILD PARTITION feb_02 TABLESPACE example;

SELECT segment_name, partition_name, tablespace_name 
FROM   user_segments
WHERE  segment_type='INDEX PARTITION'
  AND  segment_name IN (SELECT index_name 
                        FROM   user_indexes 
                        WHERE  table_name='SALES')
  AND  tablespace_name <> 'EXAMPLE';

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

2. オラクルのグローバル索引メンテナンスの利用

グローバル索引メンテナンスによって、アトミックなパーティション・メンテナンス操作の一部として、パーティション化された表のグローバル索引を最新の状態に保てます。 これによって、グローバル索引が使用不可になることが防止されるので、メンテナンス操作が実行される場合のグローバル索引の使用に影響しません。

手順

2.1

グローバル索引メンテナンスの準備

2.2

グローバル索引の構築

2.3 グローバル索引メンテナンスを使用したパーティションの交換とグローバル索引への影響
2.4 グローバル索引メンテナンスを使用しないパーティションの交換とグローバル索引への影響
2.5 グローバル索引の削除と交換（クリーンアップ）

2.1 グローバル索引メンテナンスの準備

グローバル索引が存在する場合に、3月のデータとパーティション化された表を交換します。 最初に、必要なインフラストラクチャを構築する必要があります。

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは prep4_global_index.sql スクリプトを実行して、グローバル索引メンテナンスの準備をします。 

CREATE TABLE sales_mar_2002_temp
                                          


  NOLOGGING AS SELECT * FROM sales
                                          


    PARTITION (sales_MAR_2002);
                                        
ALTER TABLE sales TRUNCATE PARTITION sales_MAR_2002;

SELECT COUNT(*) 
               FROM sales PARTITION (sales_MAR_2002);
               ALTER TABLE sales_mar_2002_temp COMPRESS;
               ALTER TABLE sales_mar_2002_temp NOCOMPRESS;

CREATE BITMAP INDEX sales_prod_mar_2002_bix
               ON sales_mar_2002_temp (prod_id)
               NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_cust_mar_2002_bix
               ON sales_mar_2002_temp (cust_id)
               NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_time_mar_2002_bix
               ON sales_mar_2002_temp (time_id)
               NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_channel_mar_2002_bix
               ON sales_mar_2002_temp (channel_id)
               NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_promo_mar_2002_bix
               ON sales_mar_2002_temp (promo_id)
               NOLOGGING COMPUTE STATISTICS ;

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

2.2 グローバル索引の構築

グローバル索引メンテナンス機能を表すには、最初にグローバル索引を作成する必要があります。 作成するには、以下の手順に従います。

1.

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは create_global_index.sql スクリプトを実行して、 SALES表に連結された一意な索引を作成します。 

CREATE UNIQUE INDEX sales_pk
   ON sales (prod_id, cust_id, promo_id, channel_id, time_id)
   NOLOGGING COMPUTE STATISTICS;

これには1分ほどかかります。

このアイコンの上にカーソルを置くと、イメージが表示されます。

 
2.

以下のコマンドまたは add_sales_pk.sql スクリプトを実行して、この索引を利用する制約を作成します。 

ALTER TABLE sales ADD CONSTRAINT sales_pk
PRIMARY KEY (prod_id, cust_id, promo_id, channel_id, time_id) USING INDEX;

                                           
                                             このアイコンの上にカーソルを置くと、イメージが表示されます。
3.

グローバル索引を使用して制約が定義される場合、交換される表にも同じ制約が定義される必要があります。 以下のコマンドまたは add_salestemp_pk.sql スクリプトを実行して、このタスクを実行します。 

ALTER TABLE sales_mar_2002_temp
                                          


  ADD CONSTRAINT sales_mar_2002_temp_pk
                                          


  PRIMARY KEY (prod_id, cust_id, promo_id, channel_id, time_id)
                                          


  DISABLE VALIDATE;
                                          


                                             このアイコンの上にカーソルを置くと、イメージが表示されます。

リストへ戻る

2.3 グローバル索引メンテナンスを使用したパーティションの交換

同時オンライン・アクセスのパーティション・メンテナンス操作の影響を示すには、2つのセッション（2つのウィンドウ）が必要です。 先に進む前に、次の項を 注意深く お読みください。

1つ目のウィンドウで、以下の手順を実行します。

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下のコマンドまたは use_global_index.sql スクリプトを実行して、EXPLAIN PLANの作成と情報の参照を行います。 

                                           
EXPLAIN PLAN FOR
   SELECT /*+ INDEX(sales, sales_pk) */ count(*)
   FROM   sales
   WHERE  prod_id BETWEEN 100 AND 500;

set linesize 140
SELECT * 
FROM TABLE(dbms_xplan.display);

                                             このアイコンの上にカーソルを置くと、イメージが表示されます。

計画とグローバル索引の使用を検証した後、以下の文または run_select.sql ファイルを繰り返し実行します。 最後に実行された文を再実行する場合、SQL*Plus機能の"r"または"/"を使用できます。 

                                           
SELECT /*+ INDEX(sales, sales_pk) */ count(*)
FROM   sales
WHERE  prod_id BETWEEN 100 AND 500;

 

このアイコンの上にカーソルを置くと、イメージが表示されます。

問合せを実行している間、2つ目のウィンドウで以下の手順を実行します。 パーティション・メンテナンス操作が実行される場合のグローバル索引を使用した同時問合せアクセスで影響がないことがわかります。

問合せは失敗 しません。 パーティション交換コマンドが成功するとすぐに問合せの結果が変更されることがわかります。 Oracle Databaseサーバーは、この状況での READ CONSISTENCY（読取り一貫性）を保証します。また、オンライン使用を制限することなく、最も効率的なパーティション表と索引メンテナンス操作を提供します。

2つ目のウィンドウで、以下の手順を実行します。

1.

SHユーザーとしてログオンしたSQL*Plusセッションで、以下の問合せまたは exchange_partition_w_gim.sql スクリプトを実行します。 

                                           
ALTER TABLE sales 
  EXCHANGE PARTITION sales_mar_2002
                                            


  WITH TABLE sales_mar_2002_temp
                                            


  INCLUDING INDEXES UPDATE GLOBAL INDEXES;

このアイコンの上にカーソルを置くと、イメージが表示されます。

DDLコマンドですが、 PARTITION EXCHANGEアトミック・コマンドの一部としてグローバル索引が保存されるので、時間がかかる場合があります。

 
2.

パーティション・メンテナンス操作の後もすべての索引が有効なことがわかります。 以下のコマンドまたは show_sales_idx_status.sql スクリプトを実行します。 

                                           
                                          
SELECT   ui.index_name,
         DECODE(uip.status,null,ui.status,uip.status) status, 
         count(*) num_of_part
FROM     user_ind_partitions uip, user_indexes ui
WHERE    ui.index_name=uip.index_name(+)
AND      ui.table_name='SALES'
GROUP BY ui.index_name, 
         DECODE(uip.status,null,ui.status,uip.status);

                                             このアイコンの上にカーソルを置くと、イメージが表示されます。
3.

交換されたパーティションとスタンドアロン表の情報を参照します。 以下のコマンドまたは count_mar_sales.sql スクリプトを実行します。 

SELECT COUNT(*) 
FROM   sales PARTITION (sales_mar_2002);


SELECT COUNT(*) 
FROM   sales_mar_2002_temp;

このアイコンの上にカーソルを置くと、イメージが表示されます。

このコマンドによって、数千の行がパーティション化された表に追加されました。スタンドアロン表は空になります。

2001年の第1四半期の新しい売上データが再交換されます。 オンラインの使用に影響を与えることなく、 PARTITION EXCHANGEコマンドの一部としてグローバル索引が保存されました。

次に、グローバル索引メンテナンスを使用しない処理を検証します。

リストへ戻る

2.4 グローバル索引メンテナンスを使用しないパーティションの交換

この機能を示すには、 2つのウィンドウが必要です。 先に進む前に、次の項を 注意深くお読みください。

1つ目のウィンドウで、以下の手順を実行します。

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下の問合せまたは use_global_index.sql スクリプトを実行します。 

                                           
explain plan for
  SELECT /*+ INDEX(sales, sales_pk) */ count(*)
  FROM  sales 
  WHERE prod_id BETWEEN 100 AND 500;

set linesize 140

SELECT * 
FROM   table(dbms_xplan.display);

SELECT /*+ INDEX(sales, sales_pk) */ count(*)
FROM   sales 
WHERE prod_id BETWEEN 100 AND 500;

2つ目のウィンドウで以下の手順を実行します。次に、上記の問合せを実行して、違いを確認します。

パーティション・メンテナンス・コマンドが処理されるとすぐに失敗します。

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

2つ目のウィンドウで、以下の手順を実行します。

1.

SHユーザーとしてログオンしたSQL*Plusセッションで、以下の問合せまたは exchange_partition_wo_gim2.sql スクリプトを実行します。 

                                           
ALTER TABLE sales 
    EXCHANGE PARTITION sales_mar_2002 
    WITH TABLE sales_mar_2002_temp
                                            


    INCLUDING INDEXES;

このアイコンの上にカーソルを置くと、イメージが表示されます。

 
2.

グローバル索引は、使用不可とマークされます。 以下のコマンドまたは show_sales_idx_status.sql スクリプトを実行して、この情報を参照します。 

SELECT   ui.index_name,
         DECODE(uip.status,null,ui.status,uip.status) status, 
         count(*) num_of_part
FROM     user_ind_partitions uip, user_indexes ui
WHERE    ui.index_name=uip.index_name(+)
AND      ui.table_name='SALES'
GROUP BY ui.index_name, DECODE(uip.status,null,ui.status,uip.status);

このアイコンの上にカーソルを置くと、イメージが表示されます。

 

リストへ戻る

2.5 グローバル索引の削除と交換

環境をクリーンアップするには、以下の手順を実行します。

 

SHユーザーとしてログオンしたSQL*Plusセッションで、以下の文または cleanup_mod1.sql スクリプトを実行して、Oracle by Example（OBE）固有の変更をクリーンアップします。 

                                           
                                             


ALTER TABLE sales DROP CONSTRAINT sales_pk;
                                            


DROP INDEX sales_pk;
                                          
                                        
ALTER TABLE sales 
  EXCHANGE PARTITION sales_q1_1998 
  WITH TABLE sales_old_q1_1998 INCLUDING INDEXES;


ALTER TABLE sales DROP PARTITION sales_jan_2002;


ALTER TABLE sales DROP PARTITION sales_feb_2002;


ALTER TABLE sales DROP PARTITION sales_mar_2002;

DROP TABLE sales_mar_2002_temp;

DROP TABLE sales_delta;

DROP TABLE sales_old_q1_1998;
         
set serveroutput on
exec dw_handsOn.cleanup_modules

SELECT * FROM TABLE(dw_handsOn.verify_env)

 

リストへ戻る

トピックに戻る

まとめ

このチュートリアルで学習した内容は、次のとおりです。

外部表を使用したデータ・ロード
SQL*Loaderと外部表を使用した比較
表圧縮によるディスク領域の節約
Oracle Partitioningによるローリング・ウィンドウ操作の実行

トピック・リストに戻る

このアイコンの上にカーソルを置くと、すべてのスクリーンショットが非表示になります。

Copyright (c) Oracle Corporation 2007 All Rights Reserved
Oracle Corporation発行の「Using High-Speed Data Loading and Rolling Window Operations with Partitioning」の翻訳版です。

この文書はあくまで参考資料であり、 掲載されている情報は予告なしに変更されることがあります。 万一、誤植などにお気づきの場合は、オラクル社までお知らせください。 オラクル社は本書の内容に関していかなる保証もしません。 また、本書の内容に関連したいかなる損害についても責任を負いかねます。

Oracleはオラクル社の登録商標です。
その他の会社名および製品名は、 あくまでその製品および会社を識別する目的にのみ使用されており、 それぞれの所有者の商標または登録商標です。