08/04/08 更新

SI様から、ORACLEではテーブル作成時に初期サイズを指定すると、長期使用していてもパフォーマンスが維持できる、と教えていただきましたので、その方法を記載します。

テーブル作成時に、テーブルに入力されるデータ量を見積もり初期サイズを指定してCreate Tableを実行する方法です。

1、TPICSで全テーブル作成

2、データ量を見積もる

�@平均レコード長を求める

平均レコード長=3(レコードヘッダのサイズ)+列ヘッダサイズ(列データサイズ250バイト以下1、以上3)+列データサイズ+列ヘッダサイズ+列データサイズ・・・・・・

型	長さ
VARCHAR2	文字数 TPiCSでは、コード25、名称52などです。
NUMBER	長さ = 1 + CEIL (n / 2) CEILは小数部切り上げ nは整数少数すべてのあわせた桁数 TPiCSでは数量関連は28、区分関連は4や8などです。

例 XHEAD(アイテム名称テーブル)

フィールド	型	byte	総桁数	データサイズ計算	列データサイズ	列ヘッダサイズ	列計
CODE	VARCHAR2	25			25	1	26
NAME	VARCHAR2	52			52	1	53
MAINBUMO	VARCHAR2	12			12	1	13
DOFUKUSUU	NUMBER		8	1+Ceil(8/2)	5	1	6
DOSEIBAN	NUMBER		8	1+Ceil(8/2)	5	1	6
SEIBHKU	NUMBER		8	1+Ceil(8/2)	5	1	6
MANUHKU	NUMBER		8	1+Ceil(8/2)	5	1	6
DORIREKIOYA	NUMBER		8	1+Ceil(8/2)	5	1	6
DORIREKIKO	NUMBER		8	1+Ceil(8/2)	5	1	6
DOLOT	NUMBER		8	1+Ceil(8/2)	5	1	6
FMRPGROUP	VARCHAR2	12			12	1	13
DOFMRP	NUMBER		8	1+Ceil(8/2)	5	1	6
PROJONLY	NUMBER		8	1+Ceil(8/2)	5	1	6
TANI1	VARCHAR2	4			4	1	5
OYAK	NUMBER		8	1+Ceil(8/2)	5	1	6
BIKOU	VARCHAR2	52			52	1	53
INPUTDATE	NUMBER		12	1+Ceil(12/2)	7	1	8
INPUTUSER	VARCHAR2	10			10	1	11
						合計	242

平均レコード長=3+242=245

�A1ブロックに収まるレコード数を求める

ブロックヘッダサイズ = 90 ＋ (INITRANS − 1)

予備領域サイズ = CEIL(表領域ブロックサイズ − ブロックヘッダサイズ) × PCTFREE)

データ格納部サイズ = 表領域のブロックサイズ−ヘッダサイズ−予備領域サイズ

1ブロックに収まるレコード数=TRUNC(データ格納部サイズ ÷ 平均レコード長)

TRUNCは小数部切り下げ

例 XHEADでデフォルトのパラメータの場合

INITRANS:1（デフォルト値）
PCTFREE:10 (デフォルト値)
ブロックサイズ:8,192(デフォルト値)と仮定します。

ブロックヘッダサイズ = 90 ＋ (1 − 1)=90

予備領域サイズ = CEIL((8192 − 90) × 10/100)=811

データ格納部サイズ = 8192−90−811=7291

1ブロックに収まるレコード数=TRUNC(7291/245)=29

�Bテーブル容量を求める

平均レコード長よりデータ格納部サイズが大きい場合

テーブル容量=CEIL(想定レコード数÷1ブロックに収まるレコード数)×表領域ブロックサイズ

平均レコード長よりデータ格納部サイズが小さい場合

テーブル容量=CEIL(平均レコード長 ÷ データ格納部の長さ) × 想定レコード数 × ブロックサイズ

例 XHEAD

想定レコード数を10000とします。

テーブル容量=CEIL(10000÷29)×8192=2826240byte=2760kbyte=2.7Mbyte

3、Create Table文を実行する

�@Create Table文取得

OracleのツールからCreate文を取得します。

Oracle11g では、Oracle Enterprise Manager の [スキーマ]タブ-データベース・オブジェクトの[表]で該当スキーマの該当テーブルを表示後、アクションで"DDLの生成"を選択し実行すると取得できます。

Oracle10g では、Oracle Enterprise Manager の [管理]タブ-スキーマ データベース・オブジェクトの[表]で該当スキーマの該当テーブルを表示後、アクションで"DDLの生成"を選択し実行で取得できます。

Oracle9iでは、Oracle Enterprise Managerコンソールで[スキーマ]-[該当のスキーマ]-[表]-[該当のテーブル]を選択し、右クリック"オブジェクトのDDL表示"で取得できます。

・TPiCSのテーブル作成時の[定義表示]ボタンでもCreate Table文が表示できますが、プライマリ以外の制約(CONSTRAINT)は含まれないので、"�CCreate Table文の実行"の後に、TPiCSのテーブル作成の[チェック制約全再作成]をする必要があります。

�Aテーブルを削除

テーブル削除と一緒にデータもインデックスも削除されます。データがある場合には、バックアップがあるか確認してから削除してください。

プライマリインデックス以外は、Create Table文では作成できないので、以下のどちらかの方法をとる必要があります。

・削除前に、"DDL表示"でインデクス作成文を保存し、"�CCreate Table文の実行"の後にインデクス作成文を実行する。

・"�CCreate Table文の実行"の後に、TPiCSのテーブル作成の[インデックス全再作成]ボタンですべてのテーブルのインデクスを再作成。簡単な方法ですが、他のテーブルにデータがあるときは、そちらのインデックスも再作成されることに注意が必要です。

�BCreate文編集

STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT)の　INITIALを求めたテーブル容量にします。

INITIALの最大値は14M、少数点を含む数値は指定できませんでした。

変更例

CREATE TABLE "TEST"."XHEAD" 
( "CODE" VARCHAR2(25) DEFAULT '' 
, "NAME" VARCHAR2(52) DEFAULT '' 
, "MAINBUMO" VARCHAR2(12) DEFAULT 'DefBUMO' NOT NULL 
, "DOFUKUSUU" NUMBER(8) DEFAULT 0 NOT NULL 
, "DOSEIBAN" NUMBER(8) DEFAULT 0 NOT NULL 
, "SEIBHKU" NUMBER(8) DEFAULT 0 NOT NULL 
, "MANUHKU" NUMBER(8) DEFAULT 1 NOT NULL 
, "DORIREKIOYA" NUMBER(8) DEFAULT 0 NOT NULL 
, "DORIREKIKO" NUMBER(8) DEFAULT 0 NOT NULL 
, "DOLOT" NUMBER(8) DEFAULT 0 NOT NULL 
, "FMRPGROUP" VARCHAR2(12) DEFAULT '' 
, "DOFMRP" NUMBER(8) DEFAULT 0 NOT NULL 
, "PROJONLY" NUMBER(8) DEFAULT 0 NOT NULL 
, "TANI1" VARCHAR2(4) DEFAULT '' 
, "OYAK" NUMBER(8) DEFAULT 2 NOT NULL 
, "BIKOU" VARCHAR2(52) DEFAULT '' 
, "INPUTDATE" NUMBER(12) DEFAULT 0 
, "INPUTUSER" VARCHAR2(10) DEFAULT '' 
, CONSTRAINT "XHEAD0" PRIMARY KEY ("CODE") VALIDATE 
, CONSTRAINT "XHEAD_DOFUKUSUUCHECK" CHECK (DOFUKUSUU in (0,1,2,3,4,5)) VALIDATE 
, CONSTRAINT "XHEAD_DOLOTCHECK" CHECK (DOLOT in (0,1)) VALIDATE 
, CONSTRAINT "XHEAD_DORIREKIKOCHECK" CHECK (DORIREKIKO in (0,1)) VALIDATE 
, CONSTRAINT "XHEAD_DORIREKIOYACHECK" CHECK (DORIREKIOYA in (0,1,2)) VALIDATE 
, CONSTRAINT "XHEAD_DOSEIBANCHECK" CHECK (DOSEIBAN in (0,1,2,3,4)) VALIDATE 
, CONSTRAINT "XHEAD_MANUHKUCHECK" CHECK (MANUHKU in (0,1,2)) VALIDATE 
, CONSTRAINT "XHEAD_SEIBHKUCHECK" CHECK (SEIBHKU in (0,1,9)) VALIDATE 
) 
PCTFREE 10 INITRANS 1 MAXTRANS 255 
STORAGE ( INITIAL 2760K BUFFER_POOL DEFAULT) 
LOGGING 

�CCreate Table文の実行

Oracleのツールから実行します。

�Dインデクスを作成

・"�Aテーブルを削除"まえに保存しておいたインデクス作成文を実行して作成。

または

・TPiCSのテーブル作成の[インデックス全再作成]ボタンですべてのインデクスを再作成。

�E確認

テーブル、インデックス、制約がTPiCSから作成したテーブルと同じかチェックしてください。

参考 http://otndnld.oracle.co.jp/skillup/oracle9i/3_1/index.html