Oracle Database、SQL、PL/SQLに関する実用的な知識を有していること。

データウェアハウスに精通していること。

Oracle Database 11gがインストールされていること。

wkdirという名前のディレクトリを作成し、このディレクトリにetl.zipをダウンロードして解凍していること。

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

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

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

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');

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

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

外部表は1つの入力ソース・ファイルだけを指しますが、実際は外部表に対してパラレルでSELECTが実行されています。 または、次のALTER TABLEコマンドを使用して、外部表のPARALLELプロパティを変更できます。

ALTER TABLE sales_delta_XT PARALLEL 4;

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

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

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);

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);

以下のSQL文またはparallel_insert_file.sqlファイルを実行して、パラレルINSERT（挿入）を実行します。

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マシン上で、非常に少量のデータにパラレル・アクセスしているため、外部表へのアクセスを並列化し、変換とロードを統合したことによるメリットは十分には分かりません。

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

ROLLBACK;

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;

1つの手順で、データのロードと変換の両方が実行されました。 SQL*Loaderを使用してデータをロードおよび変換するには、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); 

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

cd /home/oracle/wkdir/etl 

sqlldr sh/sh control=sales_dec01.ctl direct=true 

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

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

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

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

外部表とは異なり、データベース内からデータにアクセスできるようにするために、データベースで一定の領域が使用されています。 ステージング表によって使用される領域は、追加の変換用にロードされるデータ量に比例します。

また、SQL*Loaderのロード処理をパラレル化するには、複数の外部ファイルを使用する必要があります。 複数のSQL*LoaderプロセスにSKIPオプションを使用すると、同じファイルにアクセスできます。 ただしこの場合、すべてのSQL*Loaderプロセスが外部ファイル全体をスキャンするため、 全面的なパフォーマンスに弊害をもたらします。

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

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

DROP TABLE sales_dec01;  

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';

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

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

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

ALTER TABLE sales MOVE PARTITION sales_q4_2001 COMPRESS UPDATE INDEXES;

以下のコマンドまたは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の前にデータがクレンジング、統合、または集計されるデータウェアハウス環境で見られる通常の"自然なソート"にはなりません。

最初に、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)の値が異なることを確認します。

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月の末日になります。

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

DROP TABLE sales_delta;

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

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;

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

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

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

ALTER TABLE sales_delta COMPRESS;
ALTER TABLE sales_delta NOCOMPRESS; 

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 ;

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

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
   ) ;

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);

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

ALTER TABLE sales EXCHANGE PARTITION sales_q1_2002 
    WITH TABLE sales_delta INCLUDING INDEXES;

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

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

SELECT COUNT(*)
FROM   sales_delta;

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サーバーは、パーティション化キーのすべての値がパーティション境界内で一致することを保証します。

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;

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

@show_sales_idx_status.sql

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;

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 ;

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 ) ;

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

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

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四半期のデータがすべて格納されます。この表を削除すると、データがシステムから完全に削除されます。

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

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

SELECT COUNT(*) 
FROM   sales_old_q1_1998;

EXCHANGEコマンドの実行前とは異なり、スタンドアロン表に数千行が格納されているのに対し、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);

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;

新しい索引パーティションと表パーティションが同じロケーションに配置され、索引パーティションにパーティションのネーミングが継承されていることを確認できます。 以下のコマンドまたは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');

...

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));

新しい索引パーティションと表パーティションが同じロケーションに配置され、索引パーティションの名前がパーティションから継承されていることを確認できます。 以下のコマンドまたは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')
ORDER BY 2 DESC;

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';

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 ;

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分ほどかかります。

以下のコマンドまたは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; 

グローバル索引を使用して制約が定義される場合、交換される表にも同じ制約が定義される必要があります。 以下のコマンドまたは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; 

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（読取り一貫性）を保証します。また、オンライン使用を制限することなく、もっとも効率的なパーティション表と索引のメンテナンス操作を実現します。

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アトミック・コマンドの一部としてグローバル索引がメンテナンスされるため、時間がかかる場合があります。

パーティション・メンテナンス操作の後もすべての索引が有効であることが分かります。 以下のコマンドまたは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); 

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

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

SELECT COUNT(*) 
FROM   sales_mar_2002_temp;

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

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つ目のウィンドウで以下の手順を実行してから、上記の問合せを実行して違いを確認します。

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

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

ALTER TABLE sales 
    EXCHANGE PARTITION sales_mar_2002 
    WITH TABLE sales_mar_2002_temp
    INCLUDING INDEXES;

ここでは、グローバル索引に使用不可のマークが付けられています。 以下のコマンドまたは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);

SHユーザーとしてログオンしたSQL*Plusセッションで、以下の文またはcleanup_mod1.sqlスクリプトを実行して、この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)