Oracle9i 物理設計

第3部 テーブルの設計

日本オラクル クロスインダストリー本部 OracleDirect SCグループ
中家 裕之

私の所属するOracleDirectでは電話やインターネットを利用して、各種のお問合せを頂いたお客様に対しまして、お客様の担当されておられるシステムの成約・成功のための各種ご支援をさせていただいております。単にお問合せに対する回答を行うだけでなく、ご要望があれば製品紹介や機能解説のプレゼンテーション、デモ等も電話とインターネット経由を併用して実施いたします。お問合せは無償ですのでOracle製品について確認したいことがございましたらお気軽にご連絡ください

◆ 第1章 テーブル容量の見積

はじめに

テーブルの容量見積については、OTN Japanに以下の資料・ツールが用意されています。
ここで解説している見積方法は、資料「領域サイズの見積もり方法」の考え方をやや簡略化して、計算しやすくした方法です。また、指針を出すということを優先し、プラットフォームやバージョンによる違いを始めとして細かい要素を省いているので、ここで解説している見積方法は完全には正確ではありません。といっても、誤差は実際の必要容量がより多い場合でも見積後に見込む余裕値で吸収できるレベルです。また、本見積方法では上記の資料・ツールでは対応していない、
  • ブロックサイズより長いレコード長のテーブルの見積
  • LOBに関する見積
についても解説しています。また、11月よりOTNオンラインサービスの一環として容量見積サービスが始まりました。こちらもご利用ください。

テーブルの見積方法概要

テーブルの見積手順は、大まかには以下の手順になります。

(手順1) 1レコードの平均長を求める
(手順2) 1ブロックに収まるレコード数を求める
(手順3) CEIL(想定レコード数÷手順2の値)×ブロックサイズがテーブル容量となる


CEILは一番近い整数に切り上げる関数です。手順1、手順2の詳細は後述します。


1レコードの平均長の求め方

レコードは大まかには図1のようなイメージで格納されます。レコードはレコードヘッダが一つと、各列のヘッダ、及び列データで構成されます。

図1:レコードの構造
レコード
ヘッダ
列ヘッダ 列データ 列ヘッダ 列データ 列ヘッダ 列データ

(1)レコードヘッダのサイズ
レコードヘッダのサイズは3バイトです。

(2)列ヘッダのサイズ
対応する列のデータ長が250バイト以下(NULL含む)の場合は1バイト、251バイト以上の場合は3バイトです。

(3)列データのサイズ
データ型によって異なります。主要データ型については表1を参照してください。データがNULLの場合はどのデータ型も0バイトです。

表1:データ型による実消費サイズ
データ型 固定/可変 データが格納された時の長さ
CHAR 固定/可変
  • バイト数指定時はテーブル定義の長さの固定長
  • 文字数指定時は文字数に対して固定ですが、実消費バイト数はキャラクタ・セット及び実際のデータにより異なります。例えばSJISのDBで「char(3 char)」のカラムに「AAA」というデータを入れると3バイト消費しますが、「あああ」というデータを入れると6バイト消費します
VARCHAR2 可変 実際に格納されているデータの長さ(バイト数指定時・文字数指定時)
NCHAR 固定 テーブル定義文字数の2倍(AL16UTF16指定時)
NVARCHAR2 可変 格納文字数の2倍(AL16UTF16指定時)
NUMBER 可変 長さ = 1 + CEIL (n / 2)
  • CEILは一番近い整数に切り上げる関数
  • nは格納された数値の整数部・小数部を合わせた総桁数。n>38の場合は38
  • 有効桁数38桁未満の負数の場合は更に1バイト加算
DATE 固定 7バイト
TIMESTAMP 可変 秒の小数部にデータがある場合:11バイト固定
秒の小数部にデータがない場合:7バイト固定
TIMESTAMP WITH TIME ZONE 固定 13バイト
TIMESTAMP WITH LOCAL TIME ZONE 固定 11バイト
INTERVAL YEAR TO MONTH 固定 5バイト
INTERVAL DAY TO SECOND 固定 11バイト
RAW 可変 実際に格納されているデータの長さ
LONG 可変 実際に格納されているデータの長さ
LONG RAW 可変 実際に格納されているデータの長さ
BLOB/CLOB/NCLOB 指定による
  • DISABLE IN ROW指定時:20バイト
  • ENABLE IN ROW指定で行内に格納時:実データ長+36バイト
  • ENABLE IN ROW指定で行外(LOBテーブル)に格納時:実データ長により36〜86バイト。見積時は余裕を見て86バイト固定で計算してください
  • LOBテーブルの見積については後述
  • EMPTYの場合はデータ長の部分を0として計算
BFILE 固定 530バイト
ROWID 固定 6バイト(〜Oracle7) 10バイト(Oracle8〜)


1ブロックに収まるレコード数の求め方

DBブロックの構造は大まかには図2の通りです。ブロックはブロックヘッダとデータ格納部、そしてテーブルパラメータPCTFREEの指定で確保した予備領域で構成されます。

(1)ブロックヘッダのサイズ
ブロックヘッダのサイズは以下の計算式より求められます。INITRANSはテーブルパラメータです。
ヘッダのサイズ = 90 + (INITRANS − 1)

(2)予備領域のサイズ
予備領域のサイズは以下の計算式より求められます。CEILは一番近い整数に切り上げる関数、PCTFREEはテーブルパラメータです。PCTFREEは単位が%なので、計算の際は小数に直してください。
予備領域のサイズ = CEIL((テーブル格納先の表領域のブロックサイズ − ヘッダのサイズ) × PCTFREE)

(3)データ格納部のサイズ
データ格納部のサイズは以下の計算式より求められます。
データ格納部のサイズ = テーブル格納先の表領域のブロックサイズ−ヘッダのサイズ−予備領域のサイズ

以上の値より、
TRUNC(データ格納部のサイズ ÷ 平均レコード長)
が1ブロックに収まるレコード数になります。TRUNCは小数を切り捨てる関数です。

図2:ブロックの構造
ブロックヘッダ

データ格納部

予備領域


平均レコード長がデータ格納部より長い場合の見積方法

平均レコード長がデータ格納部より長い場合、当然ながらレコードは複数のブロックにまたがって格納されます。この状態を行連鎖と呼びます。行連鎖が発生する平均レコード長の場合のテーブル容量の見積は、
CEIL(平均レコード長 ÷ データ格納部の長さ) × 想定レコード数 × ブロックサイズ
となります。CEILは一番近い整数に切り上げる関数です。データ格納部の計算の際は、PCTFREEは実際の指定に関わらず0として計算してください。また、この見積方法はレコードのサイズのばらつきが少ない場合は有効ですが、ばらつきが大きい場合は実際のレコードの格納順に実格納サイズが大幅に影響されるため、かなり大きめの見積になってしまう可能性があります。


LOBテーブルの見積方法

LOBテーブルの見積は以下の手順になります。

(手順1)CHUNKの値をブロックサイズの倍数に切り上げる
LOBテーブルのパラメータCHUNKはLOBのI/Oサイズになります。ただし、CHUNKのサイズがブロックサイズの倍数でない場合は、CHUNKのサイズを上回る、一番小さなブロックサイズの倍数の値が実際のI/Oサイズとなります。例えばCHUNKが5KB、ブロックサイズが4KBの場合は、実I/Oサイズは8KBとなります。この場合ディクショナリ上も5KBではなく8KBになります。

(手順2)平均LOB長を手順1の倍数に切り上げる
LOBのデータは手順1で求めたCHUNKサイズの倍数に切り上げられて格納されます。例えば平均LOB長が28KB、CHUNKのサイズが8KBの場合は32KBとなります。

(手順3)LOB格納領域のサイズを求める
LOB格納領域のサイズは以下の計算式より求められます。
LOB格納領域のサイズ = 手順2の値 × 想定レコード数

(手順4)RETENTION(PCTVERSION)領域を求める
LOBテーブルに対して更新を行った場合、更新前データはUNDOセグメントではなく、LOBテーブル自身に格納されます。従って、見積の際にこの分を見込む必要があります。自動UNDO管理を利用している場合はパラメータRETENTION、手動UNDO管理を利用している場合はパラメータPCTVERSIONで指定した領域がLOBのUNDOのために確保されます。システム中でトランザクションで最大で発生しうる変更前LOBデータが格納できるだけの大きさを見込んでください。よくわからない場合は手順3で求めた容量の20%程度見込んでおいてください。

(手順5)手順3と手順4の合計がLOBテーブルの容量となる


◆ 第2章 テーブルの見積例

レコードが1ブロックに収まる長さの場合の見積

SCOTTスキーマにあるEMP表を例に見積を実際に行ってみます。想定件数は10,000件とします。EMP表の定義は以下の通りです。

SQL> desc emp
 名前                                      NULL?    型
 ----------------------------------------- -------- ------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

(1)平均レコード長を求める
可変長のデータ型の平均データ長は、NUMBER型については精度通りのデータ長、VARCHAR2型については定義データ長の7割と仮定します。計算根拠については表1を参照してください。

表3:各列の長さの計算例
列名 データ型 列データの長さ 列ヘッダの長さ 合計列長
EMPNO NUMBER(4) 1 + CEIL (4 / 2) = 3バイト 1バイト 4バイト
ENAME VARCHAR2(10) CEIL (10 * 0.7) = 7バイト 1バイト 8バイト
JOB VARCHAR2(9) CEIL (9 * 0.7) = 7バイト 1バイト 8バイト
MGR NUMBER(4) 1 + CEIL (4 / 2) = 3バイト 1バイト 4バイト
HIREDATE DATE 7バイト 1バイト 8バイト
SAL NUMBER(7,2) 1 + CEIL (7 / 2) = 5バイト 1バイト 6バイト
COMM NUMBER(7,2) 1 + CEIL (7 / 2) = 5バイト 1バイト 6バイト
DEPTNO NUMBER(2) 1 + CEIL (2 / 2) = 3バイト 1バイト 3バイト
中 計 レ コ ー ド 長 47バイト
レコード長(列ヘッダ3バイトを加算) 50バイト

(2)1ブロックに収まるレコード数を求める
必要なテーブルパラメータはすべてデフォルト(INITRANS:1, PCTFREE:10)とします。ブロックサイズは8KB(8,192バイト)とします。

表4:ブロックの空き領域を求める
求める対象 計算内容
ブロックヘッダのサイズ 86 + (INITRANS − 1) = 90 + (1 − 1) = 90バイト
予備領域のサイズ CEIL((テーブル格納先の表領域のブロックサイズ − ヘッダのサイズ) × PCTFREE) = CEIL((8,192 - 90) * 0.1) = 811バイト
データ格納部のサイズ テーブル格納先の表領域のブロックサイズ−ヘッダのサイズ−予備領域のサイズ = 8,192 - 90 - 811 = 7,291バイト
1ブロック中で実際にデータを格納できる領域が7,291バイトが求まりましたので、これを(1)で求めた平均レコード長50バイトで割る(余り切り捨て)と、1ブロックに146件収まることがわかります。

(3)テーブル容量を求める
既に触れましたが、(2)よりテーブル容量は
想定レコード数÷(2)の値×ブロックサイズ
となります。実際に計算すると、
CEIL(10,000 / 146) * 8,192 = 565,248バイト = 552キロバイト
となります。


レコードが1ブロックに収まらない長さの場合の見積

ブロックサイズ8KBに対してVARACHAR2(3000)の3列のレコードが10,000件挿入されるテーブルの容量を見積もります。データはどの列も常に3,000バイト入るものとします。

(1)平均レコード長を求める
レコード長は(3,000 + 3(列ヘッダ)) * 3(列) + 3(レコードヘッダ) = 9,012バイトになります。

(2)データ格納部のサイズを求める
レコードがブロックに収まるケースと同じ前提だと、7,291バイトになります。

(3)テーブル容量を求める
この様なケースのテーブル容量の計算方法は
CEIL(平均レコード長 ÷ データ格納部の長さ) × 想定レコード数 × ブロックサイズ
ですので、
CEIL(9,012 / 7,291) * 10,000 * 8,192 = 163,840,000バイト = 約157メガバイト
となります。


LOBテーブルの見積

ブロックサイズ8KBに対して平均1MBのLOBを1000件格納するLOBテーブルの見積例です。RETENTIONで必要な領域はLOB格納領域の20%、DISABLE STORAGE IN ROW指定で1000件すべてがLOBテーブルに格納されるものとします。CHUNKは3KBとします。

(1)CHUNKの値をブロックサイズの倍数に切り上げる
CHUNKが3KBでブロックサイズが8KBですので、8KBとなります

(2)平均LOB長を(1)の倍数に切り上げる
1MBは8KBの倍数なので、1MBのままとなります。

(3)LOB格納領域のサイズを求める
LOB格納領域のサイズは
LOB格納領域 = (2)の値 × 想定レコード数
ですので、
1,048,576 * 1,000 = 1,048,576,000バイト = 1,000メガバイト
となります。

(4)RETENTION領域を求めて加えてテーブル容量とする
RETENTION領域はLOB格納領域の20%なので、(3)の値を1.2倍した1,200メガバイトが見積結果となります。


◆ 第3章 列の並び順の指針

列の並び順の基本指針

列の並び順は基本的に管理がやりやすいことを念頭において設計しましょう。例えば以下のような点に留意します。
  • 主キーを最初に定義する
  • 関連性のある列(例:姓と名、部コードと課コード)は連続する順に定義する
  • よく利用される列を前のほうに配置する


レコード長を短くするテクニック

レコード長を短く出来れば出来るほど、ブロックあたりに格納できるレコード数が多くなります。そうすると、キャッシュのヒット率が高まり、容量の節約も可能になります。

(1)可変長データ型の利用
例えば郵便番号のように桁数が決まっているようなデータを格納する列を除き、VARCHAR2のような可変長を利用するようにしましょう。定義長に対し実データが短ければ短いほど容量の節約に繋がります。

(2)SJISの利用
日本語のデータの格納効率(容量節約)の点では、キャラクタ・セットにSJISを利用するのが一番です。ただ国際化対応や利用プラットフォームとの親和性いう観点もあるので、日本語のデータの格納効率の点だけでキャラクタ・セットを決めないようにして下さい。

(3)NULLが格納されやすい列を後方に定義する
NULLになることが多い列はまとめて後ろに配置すると、列ヘッダが省略されるためレコード長を短くできます。


◆ 第4章 CREATE TABLEのパラメータの設定

はじめに

本章はローカル管理表領域を利用していることを前提に話を進めます。


ローカル管理とディクショナリ管理で意味の異なるパラメータ

以下のパラメータはローカル管理とディクショナリ管理で意味が異なります。
  • INITIAL
  • NEXT
  • PCTINCREASE
  • MAXEXTENTS
表5にて違いを解説します。ローカル管理表領域においてパラメータの意味が大きく異なるのは、大きさを指定するパラメータ(INITIAL/NEXT)が、エクステントのサイズを表すのではなく、初期に確保する総テーブル容量になる、ということです。

表5:ローカル管理とディクショナリ管理で意味の異なるパラメータ
パラメータ ディクショナリ管理 ローカル管理
INITIAL 初期で確保するエクステントのサイズ。例えば100MBのINITIALを指定すると、1個の100MBのエクステントを確保します。 初期で確保するエクステントの総サイズ。例えば1MBのUNIFORM指定の表領域で100MBのINITIALを指定すると、100個の1MBのエクステントを確保します。
NEXT 二つ目のエクステントのサイズ。例えば70MBのNEXTを指定すると、二つ目のエクステント70MBの大きさで作成されます。 MINEXTENTSが2以上の時にINITIALとNEXTの合計のサイズになるよう一つ以上のエクステントが確保されます。例えば1MBのUNIFORM指定の表領域でMINEXTENTS 2、INITIAL 100MB、NEXT 70MBと指定すると、170個の1MBのエクステントを確保します。
PCTINCREASE 三つ目以降のエクステントを確保する際の、一つ前のエクステントサイズからの増分。デフォルトは50(%)。例えばNEXT 100MB、PCTINCREASE 40という指定の場合、三つ目のエクステントはNEXT値の40%増しで140MB、四つ目のエクステントは三つ目のエクステントの40%増しで196MBとなります。 MINEXTENTSが3以上の時に、ディクショナリ管理とと同じような計算方法で一つ以上のエクステントが確保されます。デフォルトは0(%)。例えば1MBのUNIFORM指定の表領域でMINEXTENTS 4、INITIAL 100MB、NEXT 100MB、PCTINCREASE 40と指定すると、100 + 100 + 140 + 196 = 536個の1MBのエクステントを確保します。
MAXEXTENTS 指定した数より多くのエクステントが作成できなくなります。 意味はディクショナリ管理と同じですが、指定しても無視され、常にUNLIMITEDになります。


テーブル作成時に指定を考慮すべきパラメータ

ローカル管理表領域においては、常に指定すべきパラメータは基本的にINITIALのみと思って頂いて構いません。もちろん意識して指定した方がいいパラメータは他にも存在しますが、よほど処理量の多いケースとかでなければそんなにシビアに考える必要はありません。

INITIALは可能な限り想定レコード数が収められるだけの大きさを指定しましょう。ローカル管理表領域ではエクステントの数が多いことはパフォーマンスに特に影響を与えませんが、エクステントの拡張はディクショナリ管理表領域程ではないものの、それなりの負荷がかかります。初期のディスク容量の制限等で想定レコード数分の確保ができないような場合、初期レコード数が想定レコード数に比べ大幅に少ないような場合は小さ目に作成し、必要に応じて拡張する形を取ります。


場合により指定を考慮するパラメータ

(1)NEXT/MINEXTENTS/PCTINCREASE
大規模なテーブルの場合、エクステントを複数のデータファイルに分散させてI/O性能の向上を図るケースがあります。この様な場合に利用を検討します。その際PCTINCREASEはデフォルトの0のままの方が領域計算がやりやすいです。

(2)FREELIST GROUPS
デフォルトの1のままで構いません。RAC環境ではノード数に合わせた値を基本としてください。

(3)FREELISTS
デフォルトの1のままで構いません。大量挿入のトランザクションが同時発生するような場合は値を増やすことを検討してください。

(4)PCTFREE
初期レコードサイズから最終レコードサイズが長くなりうる率を設定するのが一番効率的な領域管理ができます。例えばINSERT時のレコードの平均サイズが100バイトで、更新を重ねて最終的に平均130バイトになるのであれば30(%)を指定するといいです。レコードサイズの増加率が読めない場合は、デフォルトの10で運用し、再編成時に値を調整します。読取専用の表であれば0でも構いません。

(5)PCTUSED
基本的にデフォルトの40で構いません。PCTUSEDの値を高くするとブロックの再利用が為されやすくなるので領域を効率的に利用できますが、空きリストにブロックがたくさん登録されやすいので更新系のパフォーマンスが落ちます。低くするとパフォーマンスは上がりますが領域の利用効率は落ちます。パフォーマンスを優先させる場合はより低めに、領域の利用効率を優先させる場合はより高めに設定します。また、全件検索のパフォーマンスを優先させたい場合はデータを詰めて格納した方がいいので高めの値に設定します。なお、PCTFREEとPCTUSEDの値の合計は仕様上100を超えることができませんので、この範囲で調整するようにして下さい。

(6)INITRANS
デフォルトの1のままで構いません。複数のトランザクションで同時に同じブロックへの更新が頻発するような場合は、値を増やすことを検討してください。

(7)LOGGING/NOLOGGING
テーブルに対する処理でNOLOGGINGの指定が効くものは以下のリストにある処理です。これらの処理の速度を上げたい場合はNOLOGGINGの指定を検討してください。ただし、REDOログに処理内容が記録されないので、これらの処理を行った後はバックアップを取ることを強く推奨します。
  • ダイレクト・ロード(SQL*Loader)
  • ダイレクト・ロード・インサート(APPENDヒントつきのINSERT SELECT)
  • CREATE TABLE ... AS SELECT
  • ALTER TABLE ... MOVE PARTITION
  • ALTER TABLE ... SPLIT PARTITION
  • LOBテーブルに格納されるNOCACHE NOLOGGING モードのLOBのINSERT、UPDATEおよびDELETE

 第2部 表領域の設計 第4部 インデックスの設計