おつおつー＾＾
今日の投稿記事はGWの計画ということで今更ながら記事としてあげたのですが、その計画通り、今日は8章の勉強をしようと思います。
今回はスキーマなんですが、前からスキーマという言葉が出てきてなんだろうと気になっていたところなので、結構楽しく勉強できそうです！
では早速勉強していきたいと思います！
スキーマオブジェクトの管理
スキーマはユーザー作成した際に同時に自動的に作成されるオブジェクトを格納する箱のようなものです。
スキーマ名はユーザー名と同じ名前となります。
表など作成した場合、スキーマはユーザーの名前が付いたスキーマに格納されます。
他スキーマへのアクセスは権限が必要で、また他スキーマと自身のスキーマの中にある表の名前が同じでも衝突は起こらなくなります。
衝突を防ぐ役割を名前空間といいます。
ちなみに、ほかスキーマへSQLを使う場合、オブジェクト名の前に「スキーマ名.」を前置きします。
データ型と制約
列には必ずデータ型を指定する必要がありますが、主に7つ取り上げます。
データ型はよく使うため、少ししっかり目に勉強します。
NUMBER型
整数および小数を格納するためのデータ型です。
NUMBER(n)のnの桁数は1~38桁、NUMBER(n,m)のnも1~38桁mは小数点以下の最大桁数です。NUMBER(n,m)のnは1~38で設定できますnは数値全体の桁数であるため、もし整数の桁数を確認としたら小数点以下の桁数を引く必要があります。 NUMBERを指定すると最大の精度となります。
CHAR型
固定長文字列を格納するためのデータ型です。CHAR(n)と記載し、nに数値を指定することでその数値が最大バイト数となります。
もし数値よりも低いバイトで文字列を格納した場合、満たしていないところは空白文字で埋められます。
nは1~2000です。
VARCHAR2型
可変長文字列を格納するためのデータ型です。VARCHAR2(n)という形式で指定し、nには最大バイト数を指定します。
nバイトに満たない場合は空白で埋め合わせず、短いままで格納されます。
nは1~4000です。
BLOB型・CLOB型
BLOB型はバイナリデータを格納するためのデータ型です。
CLOB型はCHAR型やVARCHAR2型では対応できない大きなサイズの文字列（テキストデータ）を格納するための型です。
サイズ指定はありません。
DATE型・TIMESTAMP型
DATE型は年月日時分秒でTIMESTAMPには年月日時分秒に加えて小数秒のデータを格納できます。
制約とは表に無効なデータが格納されることを禁止するルールです。
これもよく使うものなのでしっかり目に勉強していきます。
NOT NULL制約
列にNULLを設定することを禁止する制約です。
UNIQUE制約
列の値重複を禁止する制約です。一意キー制約ともいい、その名のとおり一意性を保証します。
UNIQUE制約を設定すると自動的にその列に索引が作成されます。
また、NULLは禁止となっていないため別途禁止したい場合は、not null制約も併せて設定します。
PRIMARY KEY制約と主キー
主キーとは1件のデータ取得を特定するために使える列です。主キーはテーブルごとに1つは必要です。
主キーが機能するには、重複がなくNOT NULLでないといけないためそれらを合わせもつPRIMARY KEY制約は設定した列が主キーとなります。
FREIGN KEY 制約と外部キー
外部キーとは、関連する外部のテーブルのデータに対するキーです。
リレーショナルデータベースの肝となるリレーションシップが関係しています。
リレーションシップとは異なるテーブルの行データが関連することでそれぞれの列に同じ値が入っていることで示されます。
そして外部テーブルの参照元となるキーを外部キー、外部キーが参照する列（外部テーブル）を参照キーといいます。
また、外部キーが設定されている表を子表、参照キーが設定されているテーブルを親表といい、親子関係となります。
親がいなければ子は生まれません、ということで子表の外部キーとなる列にはFOREIGN KEY制約を設定します。
FOREIGN KEY制約は親表の参照キーにある値でないと外部キーとなる列へのデータ追加はできないという制約です。
親は子をたくさん持てますが、子は親をたくさん持てません。
子表のデータは親表1件のデータにつき複数件対応できますが、逆はできません。
そのため、参照キーにはUNIQUE制約かPRIMARY KEY制約を設定する必要があります。
CHECK制約
指定した条件を満たす値のみを設定可能にする制約です。
表作成と表削除
CREATE TABLEコマンドで表作成、またDROP TABLEコマンドで表削除となります。
CREATE TABLE テーブル名
(列名 型 CONSTRAINTS 制約名 制約の種類
,列名 型 CONSTRAINTS 制約名 制約の種類
)
それぞれ決まりがあります。
表明の長さは30倍と以内(oracle12cR1以前)または128バイト以内(oracle12cR2以降)。
スキーマ内で名前は一意に保つ。などあります。
テーブル削除については割愛しましたが、oracleにはゴミ箱という概念があるため説明していきます。
表を削除した際、ごみ箱に入るそうなのですが、ゴミ箱から復元する機能であるフラッシュバックドロップが可能となっています。
フラッシュバックドロップはデフォルトでONですが、RECYCLEBIN初期化パラメータをOFFにすると無効にできるそうです。
以下、フラッシュバックドロップ実行コマンドです。
FLASHBACK TABLE 表名 TO BEFORE DROP;
索引とビュー
検索条件とする例に索引を作成すると検索条件にマッチする行を高速に得ることができます。
ただ、検索にマッチするデータの件数が少ない場合のみです。
索引とした列を索引列と呼びますが、一つの列だけでなく複数の列の組み合わせに対しても作成できます。
作成についてはCREATE INDEXコマンドで作成できます。
索引を使ったデータ取得について以下にまとめます。
- 索引は、一番上のルートブロックからブランチブロック、リーフブロックが広がっていく木構造になっており上から順に検索を行います。
- 索引のリーフには、列値とROWIDの組みが格納されています、ROWIDとはその列値を含む行データのアドレスです。
- ROWIDによりデータファイルのデータブロックから行データを直接取り出すことができます。
以下に留意点をまとめました。
- 1つの表に多数の索引を作成すると表の行データ更新時の負荷が増加します。理由は索引のメンテナンス処理が実行されるためです。
- 索引を定義した表が削除されるとともにその索引も削除されます。
- PRIMARY KEY制約またはUNIQUE制約を設定すると索引が作成されます。
話は変わってビューについてですが、
ビューとは、1つあるいは複数の表に対するSELECT文により定義されるスキーマオブジェクトです。
CREATE VIEWコマンドでビュー作成できます。
ビューの対象である表を実表、それに対してビューは仮想表と呼ばれます。
ユーザーにオブジェクト権限にて実表の参照権限を与えずにビューへの参照権限のみ付与することでデータの保護も可能です。
以下ビューの留意点です。
- ビューにはデータは格納されません。
- ビューに対してDMLを実行すると実表のデータが更新されます。
- ビューを使用してもSQL処理は高速化されません。
データ管理
データ管理に関して、SQLLoaderとData Pumpを取り上げます。
SQLLoaderはoracleソフトウェアに含まれているツールで、データをロードするツールです。
CSVファイルのようなOS上のファイルに記載されたデータをoracleデータベースの表にロードするツールとなります。
データをロードするために必要な情報を記載した制御ファイルが必要です。
Data Pumpはoracleソフトウェアにふくまれている、oracleデータベース間でデータを移動させるツールです。
データをエクスポートするexpdpコマンドとデータをインポートするimpdpコマンドがあります。
データはダンプファイルを介して行います。
このツールにより、異なるOS、異なるリリース（バージョン）で動作するデータベース間でのデータ移動が可能になります。
表単位ではなく、スキーマ単位や表領域単位、さらに全体エクスポートも可能です。
