SAK 図書館
SAK 図書館
SQL 基礎実地編 (その一) テーブル作成、項目タイプ、キー作成、順序作成
リレーショナルデータベースの実際的な使い方を説明する。
Oracle 固有の部分もあるので注意して下さい。
■テーブル作成(テーブル創成)
・5 千件を超える新規テーブル作成では、STORAGE パラメタを指定した方が
良い。特に増分値の指定でレスポンスが大幅に変わる。
データベースのパフォーマンスチューニングも重要である。
Oracle の場合、初期値では、とても実用にならないと思う。
・テーブル容量の見積であるが、データベースには実レコード長以外に管理に
消費される部分が多い。単純にはレコード長 x 2 ぐらいで見積もれば良い。
number は、22 バイト必ず食われる。varchar2 は、文字数に文字長管理の
領域が食われる。(2 〜 4 bytes ぐらいかな。)
create table sak.テストm (
キー char (008) primary key,
データ1 number (009, 0),
データ2 number (009, 0),
データ3 number (009, 0)
);
create table sak.テストm (
キー char (008) primary key,
データ1 number (009, 0),
データ2 number (009, 0),
データ3 number (009, 0)
) storage(initial 10m next 2m maxextents 99);
| | |_ 拡張回数
| |_ 拡張容量
|_ 初期容量 (m は MB、k は KB、単位なしは bytes)
・テーブル作成時に NOT NULL 制約や DEFAULT 初期値も指定できる。
default sysdate のような指定も可能。
create table sak.テストm (
キー char (008) primary key,
データ1 number (009, 0) not null,
|_ NULL 禁止
データ2 number (009, 0) default 0 not null,
|_ 初期値
データ3 number (009, 0)
);
・constraint でプライマリキーを付けるには、次のようにする。
unlimited は上限なし。
create table sak.テストm (
キー char (008) not null,
データ1 number (009, 0) not null,
データ2 number (009, 0) default 0 not null,
データ3 number (009, 0),
constraint KEY_NAME primary key (キー) using index
pctfree 10 initrans 2 maxtrans 255
storage(initial 65536 next 32768 maxextents 99)
)
pctfree 10 pctused 50 maxtrans 255
storage(initial 10m next 2m maxextents 99);
create table sak.テストm (
キー char (008) not null,
データ1 number (009, 0) not null,
データ2 number (009, 0) default 0 not null,
データ3 number (009, 0),
constraint KEY_NAME primary key (キー) using index
pctfree 10 initrans 2 maxtrans 255 tablespace TAB_SPACE_IDX
storage(initial 64k next 32k maxextents unlimited)
)
pctfree 10 pctused 50 maxtrans 255 tablespace TAB_SPACE_DAT
storage(initial 10m next 2m maxextents unlimited)
nocache logging;
■項目タイプ(フィールドタイプ)
・コード項目を数字で 0001 のように構成することが多いと思うが、問い合わ
せで like を使えると便利である。また char 型の方が容量も少ない。
従って、数字だけで構成するコード項目は char 型を使うべきである。
・日付や時間も特別な型にするより、char 型か varchar2 型を使う方が良い。
後でフォーマットするより、更新時に 2001.06.18 のような形式でセットし
ておく方が便利である。
変更日などのように通常 NULL のレコード多い場合は varchar2 型を、
受注日のように必ずセットされるものは char 型を使う。
但し、日付型(DATE 型) は、日付や時間計算に便利ではある。
・通常使用する項目タイプは、次のとおり。
varchar2 は NULL 値の扱いに注意が必要である。
アクセススピードだけを考えれば char の方が速い。
桁数が揃う文字データには char を使うべきである。
char (008)
| |_ 8 文字 (全角 4 文字)
|_ 固定長文字列
char (008 char)
| | |_ char は、文字単位 (Oracle9i 以降)
| | byte は、省略値
| |_ 8 文字
|_ 固定長文字列
varchar2(008)
| |_ 最長 8 文字 (最長全角 4 文字)
|_ 可変長文字列 (2000 文字まで)
varchar2(008 char)
| | |_ char は、文字単位 (Oracle9i 以降)
| | byte は、省略値
| |_ 最長 8 文字
|_ 可変長文字列 (2000 文字まで)
long
|
|_ 可変長文字列 (2GB まで)
number (009, 0)
| | |_ 小数桁 (この例では小数なし)
| |_ 小数部を含む全桁 (この例では整数 9 桁)
|_ 数値
number (011, 2)
| | |_ 小数桁 (この例では小数 2 桁)
| |_ 小数部を含む全桁 (この例では整数 9 桁 + 小数 2 桁)
|_ 数値
date
|_ 日付時間(秒単位まで扱える)
nvarchar2(008)
| |_ 最長 8 文字 (最長全角 4 文字)
|_ 可変長文字列 (2000 文字まで)
** nvarchar2 は、Oracle9i から UNICODE 専用型となった。
UNICODE は、半角でも 2 バイト使用する。
nvarchar2 へのセットは、to_nchar() を使用するようである。
9i では、to_nchar() がなくても一応は動作した。
■項目名
・VB でのアクセスを考慮するなら、英数字は半角、カタカナは全角にする。
rs!データ1 形式の参照の時、英数字が全角や半角カナでは、うまく動作し
ない。どうしても使いたければ rs("データ1") 形式で参照する。
× データＡ８ number (009, 0),
○ データA8 number (009, 0),
■コメント作成
・テーブルや項目に対して、コメントを付加できる。
comment on table テーブル名 is 'テーブルコメント';
comment on column テーブル名.項目名 is '項目コメント';
■キー作成(キー創成)
・単項目のキー設定は、テーブル作成時の create 文で行うと良い。
項目定義に primary key を指定するだけである。
キー char (008) primary key,
・複合キーは、テーブル作成後に別途 alter 文で追加する必要がある。
alter table sak.テストm add primary key (
キー1,
キー2,
キー3
);
・constraint で、主キーを追加するには、次のようにする。
alter table sak.テストm add
constraint KEY_NAME primary key (
キー1,
キー2,
キー3
)
using index
pctfree 10 initrans 2 maxtrans 255
storage(initial 64k next 32k maxextents unlimited);
alter table sak.テストm add
constraint KEY_NAME primary key (
キー1,
キー2,
キー3
)
using index
pctfree 10 initrans 2 maxtrans 255 tablespace TAB_SPACE_IDX
storage(initial 64k next 32k maxextents unlimited);
・主キー以外にいくつでも副キー索引を作成できる。
(主キー作成、主インデックス作成、主 index 作成)
(副キー作成、副インデックス作成、副 index 作成)
create index で作成するが、ユニークでない重複キーも作れる。
頻繁に使われるクエリのキーは作成しておくと、レスポンスが著しく違う。
但し、あまりキー(索引) を作りすぎてもいけない。
更新時に索引の更新負荷が増大することになる。
従って、問い合わせクエリを意識した最小限で、かつ、高速アクセスとなる
索引を設計する。
また、where 句や order by 句にキーを含めて指定するようにする。
データベースの更新や問い合わせに異様に時間がかかるのは、キーの指定が
されていないからである。
・create tabble、alter table 時の primary key は、システムが自動的に
索引を作成するので、手作業で別途作成する必要はない。
それ以外の副キーは、次のように作成する。
unique 指定がなければ重複可となる。
create unique index KEY_NAME on sak.受注v1 (
機種CD1,
機種CD2
);
create index KEY_NAME on sak.受注v1 (
得意先名
);
・索引の削除は、次のようにする。
(主キー削除、副キー削除、インデックス削除、index 削除)
drop index KEY_NAME;
■順序作成
・順序は、シーケンス番号のような連番タイプが欲しいときに利用する。
単純な順序は、次のように作成する。
1 〜 10^27 まで順に加算されていきます。
(オートナンバー、自動採番、通番、ナンバリング、シリアル値)
(シリアルナンバー、シリアル番号、シーケンスナンバー、連続番号)
create sequence sak.seq;
・次のようにすると、10000 〜 9999900、増分 100 でくるくる回ります。
cache の省略値は 20 です。
create sequence sak.seq
start with 10000
increment by 100
maxvalue 9999900
minvalue 10000
cycle
cache 200
;
・順序の削除は、次のようにする。
drop sequence sak.seq;
・実際に順序を利用するには、次のようにする。
nextval がシーケンス番号を生み出す。
尚、順序は rollback されないので、飛ぶ可能性も考慮すること。
sak.seq.currval は、順序の現在値ではなく、セッションユーザが最後に使
用した値を参照する。この次の値を既に他のユーザが使用している可能性が
ある。
create table sak.受注m (
受注番号 number (12),
品番 varchar2(16),
受注数 number (12)
);
insert into sak.受注m values(sak.seq.nextval, 'a001', 100);
■SQL 基礎実地編資料
■SQL 基礎編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
Copyright (C) 1999-2024 Y.SAK