ハイウォーターマークを知る

ハイウォーターマークを知る

THE

Database

はじめに

本 書 は 、Oracle の領域管理メカニズムのなかでも重要な「ハイウォーターマーク」と、 そ れ に ま つ わ る 注 意 点 を 解 説 し て い ま す 。 対 象 の 読 者 と し て は 、 デ ー タ ベ ー ス 管 理 者 や ア プ リ ケ ー シ ョ ン 開 発 者 を 想 定 し て い ま す 。 特 に ア プ リ ケ ー シ ョ ン 開 発 者 に と っ て 必 須 の 知 識 で す 。 前 提 知 識 と し て 、 ブ ロ ッ ク や エ ク ス テ ン ト な ど 基 本 的 な Oracle の領域管理を理解し て い る も の と し ま す 。 領 域 管 理 の 知 識 は 、 参 考 文 献 １ や 日 本 オ ラ ク ル が 開 催 す る 「Oracle7 デ ー タ ベ ー ス 管 理 Ⅰ コ ー ス 」 や 「Oracle8 デ ー タ ベ ー ス 管 理 コ ー ス 」 な ど の 研 修 コ ー ス で 得 る こ と が で き ま す 。

な お 本 書 で は Oracle8 Enterprise Edition R8.0.4 for Windows NT 、 Oracle7

Server R7.3.4 for Windows NT を使用しています。本文中で説明するほとんどのコマ

ン ド は 、 そ れ 以 外 の バ ー ジ ョ ン の Oracle7、Oracle8 でも使用できます。しかしバージ

ョ ン に よ っ て 使 用 で き な い も の に つ い て は 、 そ れ を 明 記 し て い ま す 。

目次

1. ハイウォーターマークを知る ... 1

1.1. 概要 ... 1

1.2. ハイウォーターマークとは ... 1

1.3. ハイウォーターマークを調べる ... 2

1.4. ハイウォーターマークを変更する ... 4

1.5. ハイウォーターマークで注意すること ... 5

1.5.1. フルテーブルスキャンの注意点 ... 6 1.5.2. SQL*Loader のダイレクトパスロードの注意点 ... 6 1.5.3. DEALLOCATE UNUSED の注意点 ... 7

2. 問題を検証する ... 9

2.1. 概要 ... 9

2.2. 検証のための準備をする ... 9

2.3. フルテーブルスキャンの検証 ... 11

2.4. SQL*Loader の検証 ... 13

2.5. DEALLOCATE UNUSED の検証 ... 14

3. ハイウォーターマークを下げる ... 15

3.1. 概要 ... 15

3.2. 検証のための準備をする ... 15

3.3. Import/Export を使う ... 16

3.4. CREATE TABLE AS SELECT でテーブルを再作成する ... 18

3.5. TRUNCATE してからデータを戻す ... 20

4. おわりに ... 21

1. ハ イ ウ ォ ー タ ー マ ー ク を 知 る

1.1. 概 要

ハ イ ウ ォ ー タ ー マ ー ク は 、 ア プ リ ケ ー シ ョ ン 開 発 者 や デ ー タ ベ ー ス 管 理 者 に と っ て 非 常 に 重 要 な 知 識 で す 。 こ の 章 で は 、 ハ イ ウ ォ ー タ ー マ ー ク の 概 念 や ハ イ ウ ォ ー タ ー マ ー ク の 位 置 を 調 べ る 方 法 、 そ し て 本 書 の 主 題 で あ る ハ イ ウ ォ ー タ ー マ ー ク に 関 す る 注 意 事 項 を 解 説 し ま す 。

1.2. ハ イ ウ ォ ー タ ー マ ー ク と は

ハ イ ウ ォ ー タ ー マ ー ク を 知 っ て い ま す か 。 き っ と 知 ら な い 人 の ほ う が 多 い と 思 い ま す 。 高 水 位 標 だ っ た ら 知 っ て い る か も し れ ま せ ん 。 マ ニ ュ ア ル に は 「 ハ イ ウ ォ ー タ ー マ ー ク 」 で は な く 「 高 水 位 標 」 と 書 か れ て い る か ら で す 。 し か し 本 書 で は 一 貫 し て ハ イ ウ ォ

ー タ ー マ ー ク(HWM:High Water Mark) と書くことにします。こちらのほうが実際の

イ メ ー ジ に 近 く 、 分 か り や す い と 思 う か ら で す 。 大 き い 川 の 河 原 に 立 っ て い る 目 盛 り の つ い た 白 い 杭 を 見 た こ と が あ り ま す か 。 こ の 杭 が ど こ ま で 汚 れ て い る か に よ っ て 、 今 ま で の 最 高 水 位 を 知 る こ と が で き ま す 。 こ の 最 高 水 位 を 示 す と こ ろ が 「 ハ イ ウ ォ ー タ ー マ ー ク 」 で す 。Oracle の ハ イ ウ ォ ー タ ー マ ー ク も ま っ た く 同 じ イ メ ー ジ で す 。 Oracle のハイウォーターマークとは、テーブルやクラスタに割り当てたブロックの中 で 、 今 ま で デ ー タ が 挿 入 さ れ た こ と が あ る 最 後 尾 の ブ ロ ッ ク の こ と で す 。 例 を あ げ て 説 明 し ま し ょ う 。 あ る テ ー ブ ル に 10,000 行のデータをインサートしたところ、このテー ブ ル に100 個のブロックが割り当てられ、90 番目のブロックまでデータが入りました。 こ の90 番目のブロックがハイウォーターマークになります。 ９０Block HWM 実際に格納されているデータと HWM は必ずしも一致しない。 図 1   ハ イ ウ ォ ー タ ー マ ー ク の 概 念 次 に す べ て の デ ー タ を 削 除 し ま す 。 ハ イ ウ ォ ー タ ー マ ー ク は ど こ に な っ た と 思 い ま す か ？ 答 え は 90 番目のブロックです。DELETE を使ってデータを削除しても、ハイウォ ー タ ー マ ー ク は 変 わ り ま せ ん 。 な ぜ な ら ば ハ イ ウ ォ ー タ ー マ ー ク は 「 今 ま で デ ー タ を 挿 入 さ れ た こ と の あ る 最 後 尾 の ブ ロ ッ ク 」 だ か ら で す 。 こ こ ま で が 基 本 知 識 で す 。 次 に ハ

1.3. ハ イ ウ ォ ー タ ー マ ー ク を 調 べ る

そ れ ぞ れ の テ ー ブ ル や ク ラ ス タ に 割 り 当 て ら れ て い る 領 域 は 、USER_SEGMENTS

や USER_EXTENTS といったデータディクショナリを見れば分かります。しかしこれ

ら の デ ー タ デ ィ ク シ ョ ナ リ か ら ハ イ ウ ォ ー タ ー マ ー ク を 知 る こ と は で き ま せ ん 。 ハ イ ウ

ォ ー タ ー マ ー ク を 知 る た め に はDBMS_SPACE パッケージを使います。

こ の DBMS_SPACE パッケージには、UNUSED_SPACE と FREE_BLOCKS の２

つ の プ ロ シ ー ジ ャ が 含 ま れ て い ま す 。 ハ イ ウ ォ ー タ ー マ ー ク を 調 べ る に は UNUSED_SPACE プロシージャを使用します。構文は次のとおりです。

構文 DBMS_SPACE.UNUSED_SPACE(segment_owner IN VARCHAR2,

segment_name IN VARCHAR2, segment_type IN VARCHAR2, total_blocks OUT NUMBER, total_bytes OUT NUMBER, unused_blocks OUT NUMBER, unused_bytes OUT NUMBER,

last_used_extent_file_id OUT NUMBER, last_used_extent_block_id OUT NUMBER, last_used_block OUT NUMBER,

partition_name IN VARCHAR2 DEFAULT NULL)

UNUSED_SPACE プロシージャのパラメータの内容は次のとおりです。 パ ラ メ ー タ モ ー ド 内 容 s e g m e n t _ o w n e r IN 分 析 対 象 の ス キ ー マ 名 。 s e g m e n t _ n a m e IN 分 析 対 象 の セ グ メ ン ト 名 。 s e g m e n t _ t y p e IN 分 析 対 象 の セ グ メ ン ト タ イ プ 。 'TABLE','INDEX','CLUSTER' のいずれかです。 t o t a l _ b l o c k s OUT セ グ メ ン ト に 割 り 当 て ら れ た す べ て の ブ ロ ッ ク 数 。 t o t a l _ b y t e s OUT total_blocks のバイト単位の表現。 u n u s e d _ b l o c k s OUT 割 り 当 て ら れ た ブ ロ ッ ク の 中 で 、 ま だ 一 度 も 使 わ れ て た こ と の な い ブ ロ ッ ク の 数 。 u n u s e d _ b y t e s OUT unused_blocks のバイト単位の表現。 l a s t _ u s e d _ e x t e n t _ f i l e _ i d OUT HWM を持つエクステントが格納されているデータファ イ ル の フ ァ イ ルID。 l a s t _ u s e d _ e x t e n t _ b l o c k _ i d OUT HWM を持つエクステントの第一ブロック ID。 l a s t _ u s e d _ b l o c k OUT HWM を持つエクステント内での HWM のブロック番 号 。 p a r t i t i o n _ n a m e IN パ ー テ ィ シ ョ ニ ン グ を 使 用 し て い る と き の パ ー テ ィ シ ョ ン 名 。 ヒ ン ト      セ グ メ ン ト と は 、 １ つ の オ ブ ジ ェ ク ト に 割 り 当 て ら れ た す べ て の エ ク ス テ ン ト の こ と で す 。

実 際 に 、 こ の UNUSED_SPACE プロシージャを使ってハイウォーターマークを調べ る 方 法 を 解 説 し ま す 。

１ ． ま ずSQL*Plus を起動します。そして USER_DATA 表領域に TEST テーブルを作

成 し ま す 。

SQL> CONNECT SCOTT/TIGER

SQL> CREATE TABLE test(COL1 CHAR(6)) 2 TABLESPACE user_data

3 STORAGE(INITIAL 100K NEXT 100K PCTINCREASE 0);

２ ． 次 に プ ロ シ ー ジ ャ か ら 戻 り 値 を 受 け 取 る た め に バ イ ン ド 変 数 を 定 義 し ま す 。

SQL> variable total_blocks number SQL> variable total_bytes number SQL> variable unused_blocks number SQL> variable unused_bytes number SQL> variable last_used_extent_file_id number SQL> variable last_used_extent_block_id number SQL> variable last_used_block number

４ ． バ イ ン ド 変 数 の 定 義 が 終 わ っ た らUNUSED_SPACE プロシージャを実行します。 こ れ でTEST テーブルのハイウォーターマークに関する情報がバインド変数に格納さ れ ま し た 。 SQL> EXECUTE dbms_space.unused_space('SCOTT','TEST','TABLE',:total_blocks, 2 :total_bytes,:unused_blocks,:unused_bytes,:last_used_extent_file_id, 3 :last_used_extent_block_id,:last_used_block) ５ ． バ イ ン ド 変 数 の 内 容 を 表 示 す る に は 「print バインド変数名」と入力します。バイ ン ド 変 数 名 を 省 略 す る と 、 定 義 済 み の す べ て の バ イ ン ド 変 数 が 表 示 さ れ ま す 。 SQL> print TOTAL_BLOCKS ① 50 TOTAL_BYTES 102400 UNUSED_BLOCKS ② 49 UNUSED_BYTES 102400 LAST_USED_EXTENT_FILE_ID ③ 11 LAST_USED_EXTENT_BLOCK_ID ④ 2 LAST_USED_BLOCK ⑤ 1

こ の 結 果 か ら 以 下 の こ と が 分 か り ま す 。 ① ② T O T A L _ B L O C K S , T O T A L _ B Y T E S  TEST テ ー ブ ル に 50 ブ ロ ッ ク (=50 × 2K=100K)の領域が割り当てられ、そのうち 49 ブロックはまったく未使用の状態にな っ て い ま す 。 つ ま り TEST テーブルの第一ブロックがハイウォーターマークです。ハイ ウ ォ ー タ ー マ ー ク を 調 べ る 上 で 、 こ のTOTAL_BLOCKS と UNUSED_BLOCKS の値 が 重 要 で す 。 ③ L A S T _ U S E D _ E X T E N T _ F I L E _ I D  ファイル番号 11 のデータファイルにハイウ ォ ー タ ー マ ー ク を 持 つ エ ク ス テ ン ト が 格 納 さ れ て い ま す 。 ④L A S T _ U S E D _ E X T E N T _ B L O C K _ I D  そのデータファイルの中で、ブロックID= ２ の ブ ロ ッ ク が 、 ハ イ ウ ォ ー タ ー マ ー ク を 持 つ エ ク ス テ ン ト の 先 頭 ブ ロ ッ ク に な っ て い ま す 。 ⑤ L A S T _ U S E D _ B L O C K  ハイウォーターマークを持つエクステントの中で、１番目 の ブ ロ ッ ク に ハ イ ウ ォ ー タ ー マ ー ク が あ り ま す 。 ヒ ン ト       ブ ロ ッ ク サ イ ズ の 大 き さ や 表 領 域 の 使 用 状 況 に よ っ て 、 表 示 結 果 が 異 な る 場 合 が あ り ま す 。

1.4. ハ イ ウ ォ ー タ ー マ ー ク を 変 更 す る

ハ イ ウ ォ ー タ ー マ ー ク を 下 げ る 唯 一 の コ マ ン ド はTRUNCATE です。TRUNCATE は、 行 デ ー タ を す べ て 削 除 し 、 テ ー ブ ル 作 成 後 に 追 加 さ れ た エ ク ス テ ン ト を 開 放 し ま す 。 も ち ろ ん ハ イ ウ ォ ー タ ー マ ー ク は リ セ ッ ト さ れ ま す 。

構文 TRUNCATE TABLE tablename DROP STORAGE

TRUNCATE TABLE tablename REUSE STORAGE TRUNCATE CLUSTER clusername DROP STORAGE TRUNCATE CLUSTER clustername REUSE STORAGE

D R O P S T O R A G E 行 デ ー タ を す べ て 削 除 し 、 テ ー ブ ル 作 成 後 に 追 加 さ れ た エ ク ス テ ン ト を す べ て 解 放 し ま す 。 し た が っ て テ ー ブ ル の サ イ ズ が 、 テ ー ブ ル 作 成 時 に 指 定 し た INITIAL,MINEXTENTS のサイズより小さくなることはありません。 R E U S E S T O R A G E 行 デ ー タ を す べ て 削 除 し ま す 。 た だ し 現 在 割 り 当 て ら れ て い る エ ク ス テ ン ト は 解 放 さ れ ま せ ん 。 今 後 挿 入 す る デ ー タ の た め に 再 利 用 さ れ ま す 。

解 説      DROP STORAGE 、 REUSE STORAGE の 両 方 を 省 略 し た と き に は 、 DROP STORAGE がデフォルトになります。DROP STORAGE と REUSE STORAGE オプ シ ョ ン は 、 削 除 さ れ る 索 引 の 領 域 に も 適 用 さ れ ま す 。

REUSE STORAGE DROP STORAGE(デフォルト) 元のテーブル 図 2   D R O P S T O R A G E と R E U S E S T O R A G E の 違 い TRUNCATE は、行データを削除するという点では DELETE と同じです。しかしロ ー ル バ ッ ク 情 報 を 生 成 し な い の で 、DELETE よりも圧倒的に高速です。また DELETE で は ハ イ ウ ォ ー タ ー マ ー ク は 変 わ ら ず 、 領 域 も 開 放 さ れ る こ と は あ り ま せ ん 。 た だ し DELETE にはない以下の制限事項があります。 l WHERE 句を記述することができない。 l DELETE トリガーが起動しない。 l ロールバック情報が生成されないのでROLLBACK が効かない。またそれ以前の ト ラ ン ザ ク シ ョ ン は 自 動 的 に コ ミ ッ ト さ れ る 。 l 参 照 整 合 性 制 約 の 親 キ ー を 持 つ テ ー ブ ル に は 使 え な い 。 参 照 整 合 性 制 約 を 無 効 に す れ ば 可 能 。 l TRUNCATE は DDL に 分 類 さ れ て い る の で 、 ス ト ア ド プ ロ シ ー ジ ャ な ど PL/SQL 内で使用するには DBMS_SQL パッケージを使う必要がある。 こ れ ら の 制 限 事 項 の 中 で 問 題 に な る の はWHERE 句が記述できないことです。これに つ い て は ３ 章 で 対 策 を 検 討 す る こ と に し ま す 。 次 に ハ イ ウ ォ ー タ ー マ ー ク で 気 を 付 け な け れ ば な ら な い こ と に つ い て 解 説 し ま す 。

1.5. ハ イ ウ ォ ー タ ー マ ー ク で 注 意 す る こ と

ハ イ ウ ォ ー タ ー マ ー ク に 関 す る Oracle の特性を知らないと、パフォーマンスが低下 し た り 、 必 要 以 上 に デ ィ ス ク 領 域 を 使 う こ と に な り ま す 。 問 題 と な る の は 次 の ３ 点 で す 。 特 に 最 初 の ２ つ は 重 要 で す 。 そ れ ぞ れ の 問 題 に つ い て 解 説 し ま す 。 l フルテーブルスキャン時の内部動作 l SQL*Loader のダイレクトパスロードにおける領域の使用方法 l 割り当てた領域の解放(DEALLOCATE UNUSED)

1.5.1. フルテーブルスキャンの注意点

デ ー タ を 検 索 す る 方 法 に は 、 テ ー ブ ル を 直 接 読 み 込 む 「 フ ル テ ー ブ ル ス キ ャ ン 」 と イ ン デ ッ ク ス を 使 う 「 イ ン デ ッ ク ス ス キ ャ ン 」 が あ り ま す 。 問 題 に な る 可 能 性 が あ る の は フ ル テ ー ブ ル ス キ ャ ン 時 のOracle の動作です。 フ ル テ ー ブ ル ス キ ャ ン の と き 、Oracle はテーブルに割り当てられているすべてのブロ ッ ク を 読 む わ け で は あ り ま せ ん 。1 ブロック目からハイウォーターマークのあるブロッ ク ま で 読 み 取 り を 行 い ま す 。 た と え ば デ ー タ を イ ン サ ー ト し た 結 果 、 ハ イ ウ ォ ー タ ー マ ー ク が 100 ブ ロ ッ ク 目 に な っ た と し ま す 。 こ こ で フ ル テ ー ブ ル ス キ ャ ン を 行 え ば 当 然 100 ブロック目まで読みとります。次にすべてのデータを削除してからフルテーブルス キ ャ ン を 行 い ま す 。 す る と デ ー タ が 1 件も入ってないにもかかわらず、ハイウォーター マ ー ク が 100 番目のブロックを指しているので、100 ブロック目まで読むことになりま す 。 実際のデータはここまで HWM データのあるなしに関係なくフルテーブルスキャン では HWM までのブロックを読みとる。 ブロック読みとり 図 3   フ ル テ ー ブ ル ス キ ャ ン の 動 作 こ の よ う な 理 由 か ら 、 デ ー タ 量 の 増 減 が 激 し い テ ー ブ ル に 対 す る フ ル テ ー ブ ル ス キ ャ ン で は 、 必 要 以 上 の ブ ロ ッ ク 読 み と り が 発 生 し 、 パ フ ォ ー マ ン ス が 低 下 す る 可 能 性 が あ り ま す 。 た と え ば テ ン ポ ラ リ 表 な ど を 使 用 し て い る 場 合 、 挿 入 ・ 削 除 が 頻 繁 に 行 わ れ る の で 、 現 在 の デ ー タ 量 と ハ イ ウ ォ ー タ ー マ ー ク の 位 置 に 大 き な 差 が あ る 可 能 性 が あ り ま す 。

1.5.2. SQL*Loader のダイレクトパスロードの注意点

SQL*Loader は、Oracle にテキストデータをロードするユーティリティです。ロー ド す る 方 法 に は 「 コ ン ベ ン シ ョ ナ ル パ ス(デ フ ォ ル ト) 」 と 「 ダ イ レ ク ト パ ス 」 と い う ２ つ の 動 作 モ ー ド が あ り ま す 。 コ ン ベ ン シ ョ ナ ル パ ス ロ ー ド で は 、 テ キ ス ト デ ー タ を 読 み と り な が ら 内 部 的 に INSERT を発行しているのに対し、ダイレクトパスロードでは、データベースバッファ キ ャ ッ シ ュ を 経 由 せ ず に ハ イ ウ ォ ー タ ー マ ー ク 以 降 の ブ ロ ッ ク に 直 接 書 き 込 ん で い ま す 。 そ の た め ダ イ レ ク ト パ ス は 、 コ ン ベ ン シ ョ ナ ル パ ス の 数 倍 の 速 度 で デ ー タ を ロ ー ド で き ま す 。 コ ン ベ ン シ ョ ナ ル パ ス は 内 部 的 にINSERT を発行しているだけなので、ハイウォータ ー マ ー ク 以 前 の 領 域 に 空 き が あ れ ば 再 利 用 し ま す 。 し か し ダ イ レ ク ト パ ス ロ ー ド で は ハ イ ウ ォ ー タ ー マ ー ク 以 降 の ブ ロ ッ ク か ら 書 き 込 み を 開 始 す る た め 、 ハ イ ウ ォ ー タ ー マ ー ク の 前 に 空 き 領 域 が あ っ て も 使 用 さ れ る こ と は あ り ま せ ん 。 ハ イ ウ ォ ー タ ー マ ー ク の 前 に 大 き な 空 き 領 域 が あ っ た 場 合 、 本 来 よ り も 多 く の デ ィ ス ク 領 域 が 必 要 に な り ま す 。

ここから書き込み開始 HWM ダイレクトパスロードでは、HWM 以前に空きがあっても使用しない。 図 4   ダ イ レ ク ト パ ス ロ ー ド の 動 作

1.5.3. DEALLOCATE UNUSED の注意点

DEALLOCATE UNUSED はテーブルやインデックスに割り当てられた未使用の領域 を 解 放 す る コ マ ン ド で す 。Oracle7 R7.3 から導入されました。このコマンドによって、 必 要 以 上 に 大 き な エ ク ス テ ン ト が 割 り 当 て ら れ て も 、 未 使 用 の ブ ロ ッ ク だ け を 解 放 す る こ と が で き ま す 。 し か し 、 あ ら ゆ る 状 況 で 使 え る わ け で は あ り ま せ ん 。 オ ブ ジ ェ ク ト 作 成 時 に 指 定 し た INITIAL と MINEXTENTS のサイズより小さくはなりません。また解放されるのはハ イ ウ ォ ー タ ー マ ー ク 以 降 の ブ ロ ッ ク に 限 ら れ ま す 。 し た が っ て 一 度 ハ イ ウ ォ ー タ ー マ ー ク が 上 昇 す る と 、 デ ー タ を 削 除 し て ブ ロ ッ ク が 空 の 状 態 に な っ て い て も 、 そ れ ら の 領 域 は 解 放 さ れ ま せ ん 。 DEALLOCATE UNUSED について簡単に解説します。

構文 ALTER TABLE tablename DEALLOCATE UNUSED [KEEP integer]

ALTER CLUSTER clustername DEALLOCATE UNUSED [KEEP integer] ALTER INDEX indexname DEALLOCATE UNUSED [KEEP integer]

K E E P i n t e g e r ハ イ ウ ォ ー タ ー マ ー ク 以 降 か ら integer に指定したサイズだけを解放せずに確保しま す 。 解 説       DEALLOCATE UNUSED を使うと、ハイウォーターマーク以降の未使用のブロック を 解 放 す る こ と が で き ま す 。 た だ し テ ー ブ ル 作 成 時 に 指 定 し た INITIAL と MINEXTENTS の サ イ ズ よ り 小 さ く な る こ と は あ り ま せ ん 。 つ ま り オ ブ ジ ェ ク ト 作 成 時 に 確 保 し た エ ク ス テ ン ト に ハ イ ウ ォ ー タ ー マ ー ク が あ る 場 合 に は 、DEALLOCATE UNUSED は何の効果もありません。

DEALLOCATE UNUSED 実行 実データはここまで HWM DEALLOCATE UNUSED で は 、 HWM までしかブロックを解放しない。 HWM 図 5   D E A L L O C A T E U N U S E D の 動 作

2. 問 題 を 検 証 す る

2.1. 概 要

１ 章 で は 、 ハ イ ウ ォ ー タ ー マ ー ク に 関 す る 注 意 事 項 を 解 説 し ま し た 。 こ の 章 で は そ れ ぞ れ の 問 題 に つ い て 、UNUSED_SPACE プロシージャや SQL トレースなどを使用して、 実 際 に 確 認 し ま す 。

2.2. 検 証 の た め の 準 備 を す る

検 証 を 行 う た め に テ ー ブ ル を 作 成 し 、 デ ー タ を ロ ー ド し ま す 。 実 際 に 作 業 を し な い か た は 、 次 の セ ク シ ョ ン へ 進 ん で く だ さ い 。 フ ル テ ー ブ ル ス キ ャ ン の 検 証 にFULLSCAN テーブルを使います。また SQL*Loader

とDEALLOCATE UNUSED の検証には、CONVLDR テーブルと DRCTLDR テーブ

ル を 使 い ま す 。FULLSCAN テ ー ブ ル に は 100,000 件 、 CONVLDR テ ー ブ ル と

DRCTLDR テーブルには 10,000 件のデータをロードします。

ま た プ ロ シ ー ジ ャ の 戻 り 値 に 使 用 す る バ イ ン ド 変 数 は 定 義 し て あ る も の と し て 話 を 進 め ま す 。

１ ． テ ー ブ ル を 作 成 し ま す 。

SQL> CREATE TABLE fullscan(COL1 CHAR(6)) 2 TABLESPACE user_data

3 STORAGE(INITIAL 100K NEXT 100K PCTINCREASE 0);

SQL> CREATE TABLE convldr(COL1 CHAR(6)) 2 TABLESPACE user_data

3 STORAGE(INITIAL 40K NEXT 40K PCTINCREASE 0);

SQL> CREATE TABLE drctldr(COL1 CHAR(6)) 2 TABLESPACE user_data

3 STORAGE(INITIAL 40K NEXT 40K PCTINCREASE 0);

２ ． デ ー タ を ロ ー ド す る た め に SQL*Plus から次のように入力します(数分間かかりま

す) 。 100 件 ご と に コ ミ ッ ト し て い ま す が 、 こ れ は ロ ー ル バ ッ ク セ グ メ ン ト が あ ふ れ な い た め の 予 防 策 で す 。

SQL> BEGIN

2 FOR i IN 1 ..100000 LOOP

3 INSERT INTO fullscan VALUES(TO_CHAR(i)); 4 IF MOD(i,100) = 0 THEN

5 COMMIT; 6 END IF; 7 END LOOP; 8 END;

３ ． 同 様 にCONVLOAD と DRCTLOAD にもデータをロードします。 SQL> BEGIN

2 FOR i IN 1 ..10000 LOOP

3 INSERT INTO convload VALUES(TO_CHAR(i)); 4 IF MOD(i,100) = 0 THEN 5 COMMIT; 6 END IF; 7 END LOOP; 8 END; 9 / SQL> BEGIN 2 FOR i IN 1 ..10000 LOOP

3 INSERT INTO drctload VALUES(TO_CHAR(i)); 4 IF MOD(i,100) = 0 THEN 5 COMMIT; 6 END IF; 7 END LOOP; 8 END; 9 / ４ ．UNUSED_SPACE プロシージャを使って、それぞれのテーブルのハイウォーター マ ー ク を 確 認 す る と 次 の よ う に な っ て い ま す 。 F U L L S C A N テ ー ブ ル デ ー タ ロ ー ド 前 1 0 0 , 0 0 0 件 ロ ー ド 後 T O T A L _ B L O C K S 50 700 T O T A L _ B Y T E S 102,400 1,433,600 U N U S E D _ B L O C K S 49 10 U N U S E D _ B Y T E S 100,352 20,480 L A S T _ U N U S E D _ B L O C K 1 40 C O N V L O A D 、 D R C T L O A D テ ー ブ ル デ ー タ ロ ー ド 前 1 0 , 0 0 0 件 ロ ー ド 後 T O T A L _ B L O C K S 20 80 T O T A L _ B Y T E S 40,960 163,840 U N U S E D _ B L O C K S 19 10 U N U S E D _ B Y T E S 38,912 20,480 L A S T _ U N U S E D _ B L O C K 1 10

2.3. フ ル テ ー ブ ル ス キ ャ ン の 検 証

各 ス テ ッ プ ご と に 「SELECT COUNT(*) FROM FULLSCAN」を実行します。そし

て SQL トレースと UNUSED_SPACE プロシージャを使って、フルテーブルスキャン

時 の 動 作 とHWM の関係を検証します。

１ ．100,000 件のデータをインサートした状態におけるフルテーブルスキャンの動作を

確 認 し ま す 。SQL トレースの結果は次のようになっています。689 ブロックの読みと り が 発 生 し て い る こ と が 分 か り ま す 。

SELECT COUNT(*) FROM FULLSCAN

call count cpu elapsed disk query current rows --- --- --- ---Parse 1 0.00 0.03 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 1.07 689 689 3 1 --- --- --- ---total 3 0.00 1.10 689 689 3 1

Rows Execution Plan

--- 0 SELECT STATEMENT GOAL: CHOOSE

0 SORT (AGGREGATE)

100000 TABLE ACCESS (FULL) OF 'FULLSCAN'

２ ．DELETE を使ってすべてのデータを削除します。

SQL> DELETE FROM FULLSCAN; SQL> COMMIT; ３ ．UNUSED_SPACE プロシージャを実行して HWM を確認します。DELETE で削 除 し て もHWM の位置は変わっていません。 SQL> EXECUTE dbms_space.unused_space('SCOTT','FULLSCAN','TABLE',:total_blocks, 2 :total_bytes,:unused_blocks,:unused_bytes,:last_used_extent_file_id, 3 :last_used_extent_block_id,:last_used_block) デ ー タ ロ ー ド 後 D E L E T E 後 T O T A L _ B L O C K S 700 700 T O T A L _ B Y T E S 1,433,600 1,433,600 U N U S E D _ B L O C K S 10 10 U N U S E D _ B Y T E S 20,480 20,480 L A S T _ U N U S E D _ B L O C K 40 40

４ ． 次 に 、 こ の 状 態 に お い て も う 一 度 「SELECT COUNT(*) FROM FULLSCAN」を 実 行 し 、 フ ル テ ー ブ ル ス キ ャ ン を 行 い ま す 。SQL ト レ ー ス の 結 果 を 見 る と 、 一 件 も

デ ー タ が な い に も か か わ ら ず 、 削 除 前 と 同 じ 689 ブロック(うち 128 ブロックがディ

ス ク へ の リ ー ド)の読みとりが発生しています。

SELECT COUNT(*) FROM FULLSCAN

call count cpu elapsed disk query current rows --- --- --- ---Parse 1 0.00 0.04 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.16 128 689 3 1 --- --- --- ---total 3 0.00 0.20 128 689 3 1

Rows Execution Plan

--- 0 SELECT STATEMENT GOAL: CHOOSE

0 SORT (AGGREGATE)

0 TABLE ACCESS (FULL) OF 'FULLSCAN'

５ ．TRUNCATE で HWM をリセットします。

SQL> TRUNCATE TABLE FULLSCAN;

６ ．HWM を確認すると、トータルブロック数も HWM の位置も作成直後状態に戻って い ま す 。 デ ー タ ロ ー ド 前 デ ー タ ロ ー ド 後 D E L E T E 後 T R U N C A T E 後 T O T A L _ B L O C K S 50 700 700 50 T O T A L _ B Y T E S 102,400 1,433,600 1,433,600 102,400 U N U S E D _ B L O C K S 49 10 10 49 U N U S E D _ B Y T E S 100,352 20,480 20,480 100,352 L A S T _ U N U S E D _ B L O C K 1 40 40 1 ７ ． こ の 状 態 で の フ ル テ ー ブ ル ス キ ャ ン の ト レ ー ス 結 果 を 見 る と 、 読 み と り ブ ロ ッ ク 数 は ２ ブ ロ ッ ク へ 大 幅 に 減 少 し て い ま す 。

SELECT COUNT(*) FROM FULLSCAN

call count cpu elapsed disk query current rows --- --- --- ---Parse 1 0.00 0.03 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 0 2 1 --- --- --- ---total 3 0.00 0.03 0 0 2 1 Rows Execution Plan

--- 0 SELECT STATEMENT GOAL: CHOOSE

0 SORT (AGGREGATE)

以 上 の 結 果 か ら 、DELETE だけではハイウォーターマークが下がらないことが分かり ま す 。 ま た 実 際 の デ ー タ と HWM に大きな差があったときにはパフォーマンスが低下す る こ と も 確 認 で き ま し た 。 今 回 は デ ー タ が 小 さ い(700 × 2K=1400K)の で 問 題 に は な り ま せ ん が 、 デ ー タ サ イ ズ が 大 き い 場 合 に は 十 分 問 題 に な る 可 能 性 が あ り ま す 。

2.4. SQL*Loader の 検 証

ロ ー ド さ れ て い る デ ー タ を す べ て 削 除 し 、HWM が上昇している状態を作り出します。 そ し て こ の テ ー ブ ル に 対 し 、 ダ イ レ ク ト パ ス と コ ン ベ ン シ ョ ナ ル パ ス の ２ つ の モ ー ド で デ ー タ を ロ ー ド し 、 そ れ ぞ れ の 違 い を 確 認 し ま す 。 １ ．DELETE を使って CONVLOAD,DRCTLOAD のすべてのデータを削除します。

SQL> DELETE FROM convload; SQL> COMMIT;

SQL> DELETE FROM drctload; SQL> COMMIT; ２ ．UNUSED_SPACE プロシージャを実行して HWM を確認します。どちらのテーブ ル もHWM の位置は変わりません。 デ ー タ ロ ー ド 後 D E L E T E 後 T O T A L _ B L O C K S 80 80 T O T A L _ B Y T E S 163,840 163,840 U N U S E D _ B L O C K S 10 10 U N U S E D _ B Y T E S 20,480 20,480 L A S T _ U N U S E D _ B L O C K 10 10 ３ ． コ ン ベ ン シ ョ ナ ル パ ス で 、1 から 10000 までの数値が入っている d10000.txt ファ イ ル を CONVLOAD テーブルにロードします。コマンドプロンプトより次のように 入 力 し ま す 。

sqlldr control=convload.ctl data=d10000.txt userid=scott/tiger log=convload.log

注 意      W i n d o w s 9 5 / N T では、O r a c l e のバージョンによって SQL*Loader の実行ファイル名

が 異 な り ま す 。 R 7 . 3→sqlldr73.exe、R 8 . 0→sqlldr80.exe のようになっています。

S Q L * L o a d e r の 制 御 フ ァ イ ル( c o n v l o a d . c t l )

LOAD DATA

INFILE 'd10000.txt' INTO TABLE convload

(COL1 POSITION(01:06) CHAR)

４ ．CONVLOAD テーブルの HWM を確認すると、TOTAL_BLOCKS に変わりはなく、

既 存 の エ ク ス テ ン ト が 再 利 用 さ れ て い る こ と が 分 か り ま す 。

デ ー タ ロ ー ド 後 D E L E T E 後 コ ン ベ ン シ ョ ナ ル

５ ． 今 度 は ダ イ レ ク ト パ ス で 、DRCTLOAD テーブルにロードします。コマンドプロン プ ト よ り 次 の よ う に 入 力 し ま す 。

sqlldr control=drctload.ctl data=d10000.txt userid=scott/tiger direct=true log=drctload.log

S Q L * L o a d e r の 制 御 フ ァ イ ル( d r c t l o a d . c t l )

UNRECOVERABLE LOAD DATA

INFILE 'd10000.txt' INTO TABLE drctload

(COL1 POSITION(01:06) CHAR)

６ ．DRCTLOAD テーブルの HWM を確認すると、70(=80−10)から 139(=140−1)へ と 倍 増 し て お り 、HWM 以前のブロックが再利用されていないことが分かります。 デ ー タ ロ ー ド 後 D E L E T E 後 ダ イ レ ク ト ロ ー ド 後 T O T A L _ B L O C K S 80 80 140 T O T A L _ B Y T E S 163,840 163,840 286,720 U N U S E D _ B L O C K S 10 10 1 U N U S E D _ B Y T E S 20,480 20,480 2048 L A S T _ U N U S E D _ B L O C K 10 10 19

2.5. DEALLOCATE UNUSED の 検 証

SQL*Loader の 検 証 で 使 用 し た DRCTLOAD テ ー ブ ル を そ の ま ま 使 い 、 DEALLOCATE UNUSED の動作を検証します。

１ ．DRCTLOAD テーブルのデータをすべて削除し、DEALLOCATE UNUSED を実

行 し ま す 。

SQL> DELETE FROM drctload; SQL> COMMIT;

SQL> ALTER TABLE drctload DEALLOCATE UNUSED;

２ ．DRCTLOAD テーブルの HWM を確認すると、HWM 以降の１ブロックだけが解放 さ れ て い る こ と が 分 か り ま す 。 テ ー ブ ル 作 成 直 後 ダ イ レ ク ト ロ ー ド 後 D E A L L O C A T E U N U S E D 後 T O T A L _ B L O C K S 20 140 139 T O T A L _ B Y T E S 40,960 286,720 284,672 U N U S E D _ B L O C K S 19 1 0 U N U S E D _ B Y T E S 38,912 2,048 0 L A S T _ U N U S E D _ B L O C K 1 19 19 DEALLOCATE UNUSED では、たとえ一件もデータがないときでも、HWM 以前の ブ ロ ッ ク が 解 放 さ れ る こ と は あ り ま せ ん 。DEALLOCATE UNUSED は 、 実 デ ー タ 以 上 にHWM が上昇しているオブジェクトに対して効果的ではありません。

3. ハ イ ウ ォ ー タ ー マ ー ク を 下 げ る

3.1. 概 要

す べ て の デ ー タ を 削 除 す る の で あ れ ば 、TRUNCATE を使って HWM を下げることが で き ま す 。 し か し 行 デ ー タ が 残 っ て い る テ ー ブ ル の HWM を下げるには、どのようにす れ ば よ い の で し ょ う か 。 １ つ の コ マ ン ド で は 不 可 能 で す が 、 い く つ か の コ マ ン ド を 組 み 合 わ せ れ ば デ ー タ が 残 っ て い る テ ー ブ ル で も HWM を適正な位置まで下げることができ ま す 。 主 な 方 法 に 次 の も の が あ り ま す 。 １ ．Import/Export を使う。

２ ．CREATE TABLE AS SELECT でテーブルを新たに作成し直す。

３ ． 一 時 的 に デ ー タ を 待 避 し 、TRUNCATE してからデータを戻す。 １ と ２ は 元 の テ ー ブ ル を 削 除 す る 方 法 で 、 ３ は 削 除 し な い 方 法 で す 。 一 概 に ど の 方 法 が 優 れ て い る と は 言 え ま せ ん が 、 テ ー ブ ル を 削 除 す る と き に は 注 意 す る 点 が あ り ま す 。 l そのテーブルに依存するオブジェクトが無効になります。 l そのテーブルのオブジェクト権限を再度付与しなければなりません。 l そ の テ ー ブ ル の イ ン デ ッ ク ス 、 整 合 性 制 約 、 ト リ ガ ー を 再 作 成 し た 上 で STORAGE パラメータを再指定しなければなりません。 こ の 章 で は そ れ ぞ れ の 方 法 に つ い て 実 際 に 検 証 し な が ら 解 説 を し ま す 。

3.2. 検 証 の た め の 準 備 を す る

10,000 件データをインサートして HWM を上げておき、それから半分の 5,000 件のデ ー タ を 削 除 し ま す 。 こ れ で デ ー タ の ス タ ー ト 位 置 か ら HWM の間に空きの領域ができま す 。 そ れ ぞ れ の 検 証 作 業 の 中 で は こ の 手 順 を 記 述 し ま せ ん 。 実 際 に 試 さ れ る 方 は 毎 回 こ の 手 順 を 実 行 し て く だ さ い 。 １ ． テ ー ブ ル を 作 成 し ま す 。SQL*Plus から次のように入力します。

SQL> CREATE TABLE shrktest(col1 CHAR(6)) 2 TABLESPACE user_data

3 STORAGE(INITIAL 40K NEXT 40K PCTINCREASE 0);

２ ．SHRKTEST テーブルに１万件のデータをインサートします。

SQL> BEGIN

2 FOR i IN 1 ..10000 LOOP

3 INSERT INTO shrktest VALUES(TO_CHAR(i)); 4 IF MOD(i,100) = 0 THEN

5 COMMIT; 6 END IF; 7 END LOOP; 8 END;

こ れ で 準 備 は 完 了 で す 。 そ れ ぞ れ の 段 階 に お け る HWM の情報は次のようになってい ま す 。 作 成 直 後 I N S E R T 後 D E L E T E 後 T O T A L _ B L O C K S 20 80 80 T O T A L _ B Y T E S 40,960 163,840 163,840 U N U S E D _ B L O C K S 19 10 10 U N U S E D _ B Y T E S 38,912 20,480 20,480 L A S T _ U N U S E D _ B L O C K 1 10 10

3.3. Import/Export を 使 う

Import/Export は、もっとも基本的な方法です。次の手順で行います。 １ ．Export ユーティリティで特定の表をエクスポートする。 ２ ． 表 を 削 除 す る 。 ３ ． 表 を イ ン ポ ー ト す る 。 １ ．Export ユーティリティを使って SHRKTEST テーブルをエクスポートします。コ マ ン ド プ ロ ン プ ト か ら 次 の よ う に 入 力 し て く だ さ い 。

exp scott/tiger tables=(shrktest) direct=y compress=y file=expdat.dmp

注 意      W i n d o w s 95/NT では、O r a c l e のバージョンによって Export ユーティリティの実行フ ァ イ ル 名 が 異 な り ま す 。 R 7 . 3→exp73.exe、R 8 . 0→exp80.exe のようになっています。 ヒ ン ト      Oracle7 R 7 . 3 から Export ユーティリティにもダイレクトパスモードが付きました。従 来 の 方 法 よ り も 高 速 で す 。 R 7 . 3 以前をご使用のときは direct=y を外してください。使用 方 法 は コ マ ン ド ラ イ ン か ら 「 exp help=y」と入力すると表示されます。 ２ ．SHRKTEST テーブルを削除します。

SQL> DROP TABLE shrktest;

３ ． 今 度 は Import ユーティリティを使って先ほどのダンプファイルをインポートしま

す 。 コ マ ン ド プ ロ ン プ ト か ら 次 の よ う に 入 力 し て く だ さ い 。

imp scott/tiger file=expdat.dmp

注 意      W i n d o w s 95/NT では、O r a c l e のバージョンによって I m p o r t ユーティリティの実行フ ァ イ ル 名 が 異 な り ま す 。 R 7 . 3→imp73.exe、R 8 . 0→imp80.exe のようになっています。 ４ ．HWM を確認します。エクスポート前には 70(=80−10)ブロック目だった HWM が 36(=80−44)ブロック目になっていることが分かります。 作 成 直 後 D E L E T E 後 I m p o r t 後 T O T A L _ B L O C K S 20 80 80 T O T A L _ B Y T E S 40,960 163,840 163,840 U N U S E D _ B L O C K S 19 10 44 U N U S E D _ B Y T E S 38,912 20,480 90,112 L A S T _ U N U S E D _ B L O C K 1 10 36

COMPRESS=Y に つ い て

先 ほ ど の 結 果 を 注 意 深 く 見 た か た は 、 イ ン ポ ー ト 後 に エ ク ス テ ン ト の 状 態 が 変 わ っ て い る こ と に 気 付 い た か も し れ ま せ ん 。 エ ク ス ポ ー ト 時 に COMPRESS=Y （ デ フ ォ ル ト ） を 指 定 す る と 、 イ ン ポ ー ト 時 に 作 成 さ れ る テ ー ブ ル の INITIAL の値には、そのテ ー ブ ル に 割 り 当 て ら れ て い た セ グ メ ン ト サ イ ズ が 使 用 さ れ ま す 。 そ の た め 複 数 の エ ク ス テ ン ト か ら 構 成 さ れ て い る テ ー ブ ル で も 、 イ ン ポ ー ト し 直 す と １ つ の エ ク ス テ ン ト に ま と め る こ と が で き ま す 。 そ の た め エ ク ス テ ン ト の 数 が 増 加 し す ぎ た オ ブ ジ ェ ク ト を １ つ の エ ク ス テ ン ト に ま と め る た め に 、Export/Import を使うことがよくあります。しかしすべての場合において COMPRESS=Y が 優 れ て い る わ け で は あ り ま せ ん 。 オ ブ ジ ェ ク ト に 割 り 当 て ら れ て い る 領 域 に 対 し て 、 格 納 さ れ て い る デ ー タ 量 が 少 な い と き に は 、 必 要 以 上 の デ ィ ス ク を 占 有 す る こ と に な り ま す 。 COMPRESS=Y COMPRESS=N 元のテーブル COMPRESS=Y では、エクステントは一つにな るが、占有するサイズは変わらない。 図 6 C O M P R E S S = Y と N の 違 い 今 回 の 結 果 を 見 る と 、 ま さ に こ の こ と が 表 れ て い ま す 。5000 件のデータを削除したに も か か わ ら ず 、 イ ン ポ ー ト 後 の 総 ブ ロ ッ ク 数 は 80 のままです。実際にデータディクシ ョ ナ リ を 確 認 し て み ま す 。 テ ー ブ ル 作 成 時 に は 40K バイトだった INITIAL エクステン ト の 大 き さ が 、 イ ン ポ ー ト 後 に は160K バイトになっています。 SQL> SELECT initial_extent,next_extent,extents,bytes 2 FROM user_segments 3 WHERE segment_name='SHRKTEST';

INITIAL_EXTENT NEXT_EXTENT EXTENTS BYTES --- -- 163840 40960 1 163840

今 回 は COMPRESS=Y で エ ク ス ポ ー ト し ま し た が 、 次 の 問 い 合 わ せ は

COMPRESS=N でエクスポートしたときの結果です。INITIAL エクステントのサイズ

SQL> SELECT initial_extent,next_extent,extents,bytes 2 FROM user_segments

3 WHERE segment_name='SHRKTEST';

INITIAL_EXTENT NEXT_EXTENT EXTENTS BYTES --- -- 40960 40960 2 81920

3.4. CREATE TABLE AS SELECT で テ ー ブ ル を 再 作 成 す る

CREATE TABLE AS SELECT を使ってテーブルを再作成する方法です。テーブルを

再 作 成 す る こ と に よ っ て 、HWM を下げることができます。次の手順で行います。

１ ．CREATE TABLE AS SELECT でテーブルをコピーする。

２ ． 元 の テ ー ブ ル を 削 除 す る 。

３ ． コ ピ ー し た テ ー ブ ル を 元 の テ ー ブ ル の 名 前 に 改 名 す る 。

１ ．CREATE TABLE AS SELECT で元のテーブルのデータをコピーします。

SQL> CREATE TABLE temp UNRECOVERABLE 2 TABLESPACE user_data

3 STORAGE(INITIAL 40K NEXT 40K PCTINCREASE 0) 4 AS SELECT * FROM shrktest;

注 意      O r a c l e 7 R 7 . 2 よりも前のバージョンをご使用のときは UNRECOVERABLE は使えま

せ ん 。

2. 元のテーブルを削除します。 SQL> DROP TABLE shrktest;

3.SQL*Plus の RENAME コマンドを使って、テーブルの名前を元に戻します。これで 終 了 で す 。

SQL> RENAME temp TO SHRKTEST;

4.HWM を確認します。40 ブロック(2 エクステント)が解放され、70(80−10)ブロック 目 だ っ たHWM は 36(=40−4)ブロック目になっていることが分かります。 作 成 直 後 D E L E T E 後 コ ピ ー 後 T O T A L _ B L O C K S 20 80 40 T O T A L _ B Y T E S 40,960 163,840 81,920 U N U S E D _ B L O C K S 19 10 4 U N U S E D _ B Y T E S 38,912 20,480 8,192 L A S T _ U N U S E D _ B L O C K 1 10 16

CREATE TABLE AS SELECT に つ い て

CREATE TABLE AS SELECT( 以下 CTAS)は、テーブルのデータと定義情報をコピ

ー す る SQL で す 。UNRECOVERABLE オプションを使用すると、データ挿入に伴う REDO エントリを生成しません(COMMIT 情報やエクステント確保などの最小限のエン ト リ は 生 成 さ れ ま す) 。 し た が っ て 通 常 よ り も 高 速 に コ ピ ー が 可 能 で す 。 UNRECOVERABLE オプションは Oracle7 R7.2 から導入されました。回復処理が不 要 で あ れ ば 、UNRECOVERABLE オプションを使用することをお勧めします。以下に、 CTAS の注意点を紹介します。

l Oracle8 から、UNRECOVERABLE キーワードは NOLOGGING キーワードに

変 更 に な り ま し た 。UNRECOVERABLE キーワードを使用することも可能です が 、 で き る だ けNOLOGGING キーワードを使用してください。 l Oracle8 から LOGGING/NOLOGGING の仕様が変わりました。 O r a c l e 7 ： ロ グ の モ ー ド に か か わ ら ず デ フ ォ ル ト はRECOVERABLE です。 O r a c l e 8 ： ノ ー ア ー カ イ ブ ロ グ モ ー ド の デ フ ォ ル ト はNOLOGGING です。         アーカイブログモードのデフォルトは LOGGING です。 l CTAS に TABLESPACE 句をつけないと、作成したテーブルはユーザーのデフォ ル ト 表 領 域 に 作 成 さ れ ま す 。 も ち ろ んSTORAGE パラメーターには、そのデフォ

ル ト 表 領 域 のDEFAULT STORAGE の値が適用されます。CTAS を使用すると

き に は 、 必 ずSTORAGE 句と TABLESPACE 句を付けてください。

l NOLOGGING(UNRECOVERABLE) キ ー ワ ー ド の 位 置 に 注 意 し て く だ さ い 。

AS 句の前に書かないと無視されます。

l CTAS でコピーされるのは「行データ」と「NOT NULL 制約」だけであること

に 気 を 付 け て く だ さ い 。 イ ン デ ッ ク ス や そ の 他 の 制 約 は コ ピ ー さ れ ま せ ん 。 次 の 表 はSHRKTEST テーブルに対して 100,000 行の CTAS を実行したときに生成さ れ た REDO サ イ ズ の 比 較 で す 。 NOLOGGING を 指 定 し た ほ う が 、 圧 倒 的 に 少 な い REDO サイズで済みます。この差はおよそ今回のデータ量に相当するので、サイズが大 き い テ ー ブ ル ほ ど パ フ ォ ー マ ン ス の 差 は 顕 著 に 表 れ ま す 。 O r a c l e 7 O r a c l e 8 ノ ー ア ー カ イ ブ ロ グ モ ー ド N O L O G G I N G ( U N R E C O V E R A B L E ) な し 1440K 42K ノ ー ア ー カ イ ブ ロ グ モ ー ド N O L O G G I N G ( U N R E C O V E R A B L E ) あ り 33K 42K ア ー カ イ ブ ロ グ モ ー ド N O L O G G I N G ( U N R E C O V E R A B L E ) な し 1440K 1450K ア ー カ イ ブ ロ グ モ ー ド N O L O G G I N G ( U N R E C O V E A R B L E ) あ り 33K 42K 表 1 L O G G I N G モ ー ド に よ る 生 成 さ れ る R E D O サ イ ズ の 違 い

3.5. TRUNCATE し て か ら デ ー タ を 戻 す

一 時 的 に デ ー タ を ほ か の 表 に コ ピ ー し 、 そ れ か ら 戻 す 方 法 で す 。 こ の 方 法 で は 元 の テ ー ブ ル を 削 除 し な い の で 、 こ の テ ー ブ ル に 関 す る オ ブ ジ ェ ク ト が 無 効 に な っ た り 、 制 約 や イ ン デ ッ ク ス を 再 作 成 す る 必 要 は あ り ま せ ん 。 次 の 手 順 で 行 い ま す 。

１ ．CREATE TABLE AS SELECT でテーブルをコピーする。

２ ．TRUNCATE で、元のテーブルのデータをすべて削除する。

３ ．INSERT SELECT でコピーしたデータを元に戻す。

４ ． 一 時 的 に 作 成 し た テ ー ブ ル を 削 除 す る 。

1.CREATE TABLE AS SELECT で元のテーブルのデータをコピーします。 SQL> CREATE TABLE temp UNRECOVERABLE

2 TABLESPACE user_data 3 PCTFREE 0

4 STORAGE(INITIAL 40K NEXT 40K PCTINCREASE 0) 5 AS SELECT * FROM shrktest;

ヒ ン ト        t e m p テーブルは一時的に使用するだけなので、格納効率を高くするために PCTFREE=0

が 最 適 で す 。

2.TRUNCATE で元のテーブルのデータをすべて削除します。 SQL> TRUNCATE TABLE shrktest;

3. 一時的に待避しておいたデータを、INSERT SELECT で元のテーブルに戻します。 APPEND ヒントについては後述の「ダイレクトロード INSERT について」で解説し ま す 。

O r a c l e 8 専 用( 高 速)

SQL> ALTER TABLE shrktest NOLOGGING;

SQL> INSERT /*+ APPEND */ INTO shrktest SELECT * FROM temp; SQL> COMMIT;

O r a c l e 7 , O r a c l e 8 共 通

SQL> INSERT INTO shrktest SELECT * FROM temp; SQL> COMMIT;

4. 一時テーブルを削除します。 SQL> DROP TABLE temp;

5.HWM を確認します。40 ブロック(2 エクステント)が解放され、70(80−10)ブロック 目 だ っ たHWM は 36(=40−4)ブロック目になっていることが分かります。 作 成 直 後 D E L E T E 後 コ ピ ー 後 T O T A L _ B L O C K S 20 80 40 T O T A L _ B Y T E S 40,960 163,840 81,920 U N U S E D _ B L O C K S 19 10 4 U N U S E D _ B Y T E S 38,912 20,480 8,192 L A S T _ U N U S E D _ B L O C K 1 10 16

ダ イ レ ク ト ロ ー ド INSERT について

Oracle8 からダイレクトロード INSERT(DLI:Direct Load Insert)が可能になりま

し た 。DLI は 、SQL*Loader のダイレクトパスモードと同様にデータベースバッファ キ ャ ッ シ ュ を 経 由 せ ず に 直 接 書 き 込 み を 行 い ま す 。 本 来 パ ラ レ ルINSERT のために使わ れ る も の で す が 、APPEND ヒ ン ト を 記 述 す る こ と に よ っ て 、 パ ラ レ ル で は な い INSERT でも使用することができます。ただしいずれにしても単純な INSERT ではな く INSERT ... SELECT で あ る 必 要 が あ り ま す 。 ま た イ ン サ ー ト 対 象 の テ ー ブ ル に NOLOGGING 属性が設定してあるときには、REDO エントリの生成を最小限に抑える こ と が で き ま す 。 パ ラ レ ルINSERT についての詳細は参考文献３、４をご覧ください。 次 の 表 はSHRKTEST テーブルに対して 100,000 行インサートしたときに生成された REDO サイズの比較です。ノーアカイブログモードでは NOLOGGING オプションの有 無 に か か わ ら ず 、APPEND ヒント指定時には REDO がほとんど生成されていないこと が 分 か り ま す 。 ま た ア ー カ イ ブ ロ グ モ ー ド で は APPEND ヒントと NOLOGGING オプ シ ョ ン の 組 み 合 わ せ が 最 小 のREDO サイズになっています。 ノ ー ア カ イ ブ ロ グ モ ー ド ア ー カ イ ブ ロ グ モ ー ド テ ー ブ ル の 属 性 ： L O G G I N G A P P E N D ヒ ン ト な し 1900K 1900K テ ー ブ ル の 属 性 ： L O G G I N G A P P E N D ヒ ン ト あ り 28K 1440K テ ー ブ ル の 属 性 ： N O L O G G I N G A P P E N D ヒ ン ト あ り 28K 28K 表 2 A P P E N D ヒ ン ト に よ る 生 成 さ れ る R E D O サ イ ズ の 違 い

4. お わ り に

本 書 で は 、 ハ イ ウ ォ ー タ ー マ ー ク に 関 す る 注 意 事 項 を 実 際 の 検 証 を 交 え な が ら 解 説 し て き ま し た 。 ハ イ ウ ォ ー タ ー マ ー ク に 関 す る 知 識 が 不 足 し て い る と 、 原 因 不 明 の パ フ ォ ー マ ン ス 低 下 や デ ィ ス ク 容 量 不 足 に 陥 る 可 能 性 が あ る こ と が お 分 か り い た だ け た と 思 い ま す 。

そ れ 以 外 に も 特 に 注 目 す る べ き な の はCREATE TABLE AS SELECT とダイレクト

ロ ー ドINSERT です。これらの機能を使用することによって、最少の時間でデータをコ

ピ ー す る こ と が で き ま す 。 本 書 で は 時 間 を 表 示 し て い ま せ ん が 、 実 際 に み な さ ま の 環 境 で 試 し て 、 そ の 効 果 を 比 べ て み る こ と を お 勧 め し ま す 。 こ れ ら の テ ク ニ ッ ク は 、 今 回 の よ う な ハ イ ウ ォ ー タ ー マ ー ク を 下 げ る 目 的 だ け で は な く 、 デ ー タ ウ ェ ア ハ ウ ス ア プ リ ケ ー シ ョ ン な ど で サ マ リ ー テ ー ブ ル や 一 時 テ ー ブ ル を 作 成 す る と き に も 役 立 ち ま す 。

5. 参 考 文 献

1)「Oracle8 Server 管理者ガイド R8.0」,日本オラクル,1997 2)「Oracle8 ユーティリティ R8.0」,日本オラクル,1997 3)「Oracle8 チューニング R8.0」,日本オラクル,1997 4)日本オラクル株式会社監修「Oracle8 新機能活用ガイド」ソフトバンク,1998 5) 日 本 オ ラ ク ル 株 式 会 社 監 修 「 Oracle7 リ リ ー ス 7.3 新 機 能 活 用 ガ イ ド 」 ソ フ ト バ ン ク,1997 こ の 文 書 は あ く ま で も 参 考 資 料 で あ り 、 掲 載 さ れ て い る 情 報 は 予 告 な し に 変 更 さ れ る こ と が あ り ま す 。 こ の 文 書 に 関 連 し て 不 都 合 が 生 じ た 場 合 も 、 米 国 オ ラ ク ル 社 及 び 日 本 オ ラ ク ル 株 式 会 社 は 一 切 保 証 せ ず 、 特 に 責 任 は 負 い か ね ま す の で ご 容 赦 く だ さ い 。 ま た 許 可 な く 、 改 編 、 引 用 す る こ と を 禁 じ ま す 。 1998 年 8 月 10 日 初版

日 本 オ ラ ク ル 株 式 会 社   シ ス テ ム 本 部  Design & Migration Services Copyright© ORACLE CORPORATION JAPAN 1998