ORACLE MASTER Silver DBA 取得に必要な知識のまとめ～シーケンス、シノニム、索引編～

はじめに

本記事はORACLE MASTER Silver DBA取得に必要な知識の個人的まとめになります。

ORACLE MASTERとは

Oracle Databaseの管理スキルを証明するOracle社の資格です。

Bronze, Silver, Gold, Platinumの4ランクあります。

索引

索引とは、表からのデータ読み取りを高速化するためのオブジェクトです。

検索条件に含まれる表の列に索引（インデックス）を作成すると、検索条件にマッチする行を高速に得ることができます。

表に索引がない場合、欲しいデータを取得するためにすべての行を読み出す場合があります。

索引を作成するには CREATE INDEX を実行します。

• 索引列は一般に WHERE で検索条件に指定される列とします。
• UNIQUE を指定すると一意索引が作成されます
• 他のユーザーが所有する表に索引を作成するには、その表のINDEXオブジェクト権限が必要です

索引には一意索引と非一意索引があります。

一意索引は、索引列の値が表のすべての行について重複がないことを保証するものです。

非一意索引は、索引列の値の一意性を特に保証しません。

索引を使用すると、読み出すデータ量を削減し、処理の高速化を実現できます。しかし、処理を高速化できるのは、検索にマッチするデータの件数が少ない場合のみです。

件数が少ない場合は索引が有効ですが、検索にマッチする件数が多い場合は、索引を使用せず、表のデータを全件呼び出すほうが効率的です。

索引を使用する・しないはオプティマイザが行います。

オプティマイザはSQLを実行するにあたり、どのようなオブジェクトがどのような手順でアクセスすれば効率的かを判断するデータベース内部のコンポーネントです。

索引の留意点は以下の通りです。

• 索引は1つの列に対してだけでなく、列の組み合わせに対しても作成できます
  • 複数の列に作成した索引をコンポジット索引と呼びます
• 索引は検索条件にマッチするデータが少ない場合に有効です
• 1つの表に多数の索引を作成すると、表の行データ変更時の負荷が増加します
  • 行の索引列を変更するたびに、索引のメンテナンス処理が実行されるためです
• 索引を定義した表が削除されると、その索引も一緒に削除されます
• 主キー制約または一意制約を設定すると、制約を設定した列に対して、一意索引が自動的に作成されます
• 索引を設定した表と索引を別の表領域に格納することができます
• 索引を設定した表と索引を別のスキーマに格納することもできます

索引を削除するには DROP INDEX を実行します。

また、一時的に使用しない索引を UNUSABLE にすると、索引セグメントを削除し、領域を解放できます。

索引を削除する前に、その索引が本当に使用されていないか、仮に使用されなくなったとしても、SQLの処理パフォーマンスに影響を与えないかを確認する場合に ALTER INDEX 索引名 INVISIBLE; を使用します。

• OPTIMIZER_USE_INVISIBLE_INDEXES初期化パラメータが true であるセッションは索引が使用できます、OPTIMIZER_USE_INVISIBLE_INDEXES初期化パラメータが false であるセッションは索引が使用できません
  • 段階的に索引削除の影響度調査を行えます
• OPTIMIZER_USE_INVISIBLE_INDEXES=false のセッションから見えなくなるだけで、索引の実体は残ります
  • DML実行時に索引のメンテナンスも実行されます

シーケンスの管理

主キーや一意キーとなる列は、表に格納された行データを識別するための列であるため、それらの列値にはそれぞれ一意の値を設定しなければなりません。

一意の値として、ある列に対して連番を設定することがよくありますが、シーケンスを使うと連番を振り出す処理を簡単に行うことができます。

シーケンスによって振り出された連番はデータベース全体で一意であることが保証されます。

シーケンスを作成するには CREATE SEQUENCE を実行します。

オプションに関する留意点は以下の通りです。

• CYCLE を指定するとシーケンスの値が最大値に達した後に初期値に戻ります
• NOCYCLE を指定するとそれ以上値が生成されません
• CACHE にはメモリー上にキャッシュするシーケンスの値の数を指定します
  • シーケンスをキャッシュすると、データディクショナリへのアクセスを削減できます
• NOCACHE を指定するとシーケンスの値をメモリーにキャッシュしません
• デフォルトでは初期値及び増分値の値は1、最大値なし、 NOCYCLE 、 CACHE 20 です

シーケンスから新たに連番を振り出すには シーケンス名.NEXTVAL を参照します。

また、すでに振り出された連番を確認するには シーケンス名.CURRVAL を参照します。

シーケンスが付与する連番には空きが発生することがあります。例えば、一度シーケンス値を振り出した後にトランザクションをロールバックしても、同じシーケンス値は振り出されません。シーケンス値をキャッシュした場合も同様に、一度キャッシュされた値と同じシーケンス値は振り出されません。このような動作になる理由は、シーケンスは動作の高速性を重視しているためです。

空きのない連番を振り出す必要がある場合にはシーケンスは使用できません。

シーケンスを削除するには DROP SEQUENCE を実行します。

シノニムの管理

シノニムとはオブジェクトに付ける別名です。

表やビュー、シーケンス、プロシージャ、パッケージなどの様々なオブジェクトに対して、シノニムを定義して別名をつけることができます。

シノニムを作成するには CREATE SYNONYM を実行します。

オプションに関する留意点は以下の通りです。

• 指定されたオブジェクトに対してシノニムを作成します
• PUBLIC を指定するとパブリックシノニムになります
• PUBLIC を指定しないとプライベートシノニムになります
• プライベートシノニムを作成するにはCREATE SYNONYMシステム権限が必要です
• パブリックシノニムを作成するにはCREATE PUBLIC SYNONYMシステム権限が必要です

プライベートシノニムとパブリックシノニムの違いは以下の通りです。

分類	説明
プライベートシノニム	・所有ユーザーのスキーマに格納される ・所有ユーザーおよびシノニムがさすオブジェクトにアクセスできるユーザーがこのプライベートシノニムにアクセスできる
パブリックシノニム	・特に所有ユーザーは存在しない ・特定のスキーマに格納されず、どのユーザーからもシノニム名のみでアクセスできる ・どのユーザーからもアクセスできる ・データベースで一位の名前にする必要がある

シノニム自体にアクセスできることとシノニムが指すオブジェクトへのオブジェクト権限は別のものです。（オブジェクト権限は別途に必要）

シノニムはオブジェクトに短い別名をつけたり、同じ名前で違うオブジェクトにアクセスできるようにするために使います。

シノニムを削除するには DROP SYNONYM を実行します。

PUBLIC を指定するとパブリックシノニム、指定しないとプライベートシノニムを削除します。

おわり

シーケンス、シノニム、索引編はここまで。

次は制約とキーについて説明していきます。