【SilverSQL】第11章

シーケンス、シノニムおよび索引

シーケンス（sequence）

自動で連番を発行するオブジェクト。
主キーやユニークなIDを作るときによく使います。

シノニム

シノニムはオブジェクトの別名を表すスキーマ・オブジェクト(データベースに格納されるオブジェクトで、一意に識別できるオブジェクトIDを持つ)。表やビューなどのオブジェクトに別名を付けることで、オブジェクトの指定を簡単に行えるようになったり、スキーマ名を隠蔽してセキュリティを高めたりすることができる。

• シノニムにはプライベートシノニムとパブリックシノニムの2種類のシノニムがある。
  プライベートシノニムはシノニムを作成したユーザーだけが使用できるシノニムであるのに対し、パブリックシノニムは全てのユーザーが使用できるシノニム。

• ただし、シノニムを使用するには、基となるオブジェクトに対する適切な権限が必要。例えば、表のシノニムを使用して問合せを行う場合は、基になる表に対する参照権限が必要。

• プライベートシノニムを作成するにはCREATE SYNONYM権限、パブリックシノニムを作成するにはCREATE PUBLIC SYNONYM権限が必要になる。

CREATE [PUBLIC] SYNONYM シノニム名 FOR オブジェクト名;

• プライベートシノニムはスキーマと紐付けられますが、パブリックシノニムは特定のスキーマと紐付けられていないため、プライベートシノニムとパブリックシノニムとで同名のシノニムを作成することができる。

• プライベートシノニムとパブリックシノニムで同名のシノニムが存在する場合は、プライベートシノニムが優先される。

シノニム削除

• プライベートシノニムの削除はDROP ANY SYNONYM権限を持つユーザーかシノニムの所有者によって行われる。

• パブリックシノニムの削除はDROP PUBLIC SYNONYM権限を持つユーザーによって行われます。

DROP SYNONYM emp;

順序

順序は、指定した規則に従って一意の番号を自動的に生成するスキーマ・オブジェクトです。順序は主に重複したデータを受け付けない、PRIMARY KEY制約が定義された列の値を生成する際に利用されます。

順序の作成

順序の作成はCREATE SEQUENCE権限を持つユーザーによって行われる。

CREATE SEQUENCE 順序名
　[INCREMENT BY n]
　[START WITH n]
　[MAXVALUE n | NOMAXVALUE]
　[MINVALUE n | NOMINVALUE]
　[CYCLE | NOCYCLE]
　[CACHE n | NOCACHE];

順序から新しい順序値を取得したり、現在の順序値を確認するためには、NEXTVAL疑似列、CURRVAL疑似列を参照する。
それぞれの参照方法は次の通り。

順序名.NEXTVAL
順序名.CURRVAL

[NEXTVAL疑似列]
NEXTVAL疑似列を参照すると、新しい順序値が生成されて返される。
例：seq_id.NEXTVAL → 次の番号を取得

[CURRVAL疑似列]
CURRVAL は Oracle Database などで使われる、シーケンスの現在の値（最後に取得した値） を返す擬似カラム。
CURRVAL疑似列を参照すると、最後に生成された順序値が返される。
そのセッションで最後に取得したNEXTVALの値を返す。
つまり「今あなたのセッションで使っているシーケンスの最新値」が取れます

NEXTVAL疑似列、CURRVAL疑似列は以下の箇所で参照することができる。

• 主問合せのSELECT句
• INSERT文の副問合せのSELECT句
• INSERT文のVALUES句
• UPDATE文のSET句
• CREATE TABLE文またはALTER TABLE文の列のDEFAULT値

なお、現在のセッションで一度も順序値が生成されていない(一度もNEXTVAL疑似列を参照していない)ときにCURRVAL疑似列を参照すると、エラーとなる

次のSQL文で順序を生成。
CREATE SEQUENCE s_dept
    INCREMENT BY 10
    MAXVALUE 90
    NOCYCLE;

その後、次のSQL文を10回実行した結果として正しいものはどれか。
SELECT s_dept.NEXTVAL FROM dual;

設問のSQL文では、増分値が10、最大値が90、順序値が最大値に達した場合、順序値の生成を終了するというオプションが指定。

設問のSELECT文を10回繰り返すと、初期値が1(明示的に指定がない場合の初期値は1)で増分値が10、順序値は91となり、オプションで指定した最大値より大きな値になる。順序値が最大値に達した場合、順序値の生成を終了するというオプションを指定しているので、順序値が最大値を超えた場合はエラーが発生する。

以上より、エラーとなる

順序の変更

• 順序の定義を変更しても、既に生成された順序値に影響はない
• キャッシュ上に生成するキャッシュ値の個数を変更できる
• 順序の定義の変更時、妥当性のチェックが行われる

次のSQL文で順序の変更したところエラーとなりました。エラーの原因は何か。
SQL> SELECT seq_id.currval FROM dual;

CURRVAL
-------
250

SQL> ALTER SEQUENCE seq_id
2 INCREMENT BY 10
3 MAXVALUE 200
4 NOCYCLE
5 NOCACHE;

最大値に既に生成されている順序値よりも小さい値が指定されたため

順序の変更では、START WITHオプション以外のオプションを変更することができる。
ただし、現在までに生成された順序値よりも小さな最大値を設定するなど、現在までに生成された値と矛盾するような定義の変更はできない。

索引

索引はデータの検索を高速化するスキーマ・オブジェクト。
表の列に索引が設定されると、索引が設定された列の値と、その物理的な格納場所（ROWID）を登録。

索引の作成はCREATE ANY INDEX権限を持つユーザーによって行われる。
構文は以下のとおり。

CREATE [UNIQUE] INDEX 索引名
　ON 表名(列名 [,列名...] [ASC| DESC])
　[VISIBLE | INVISIBLE];

索引には PRIMARY KEY制約やUNIQUE制約によってOracleが自動的に作成する一意索引と、ユーザが任意に作成する索引がある。

一意索引

索引に指定した列または列の組合せに重複した値を持つ行がない場合に作成できる索引です。PRIMARY KEY制約やUNIQUE制約によって自動的に作成されるほか、CREATE UNIQUE INDEX文により作成することができる(ただし、索引に指定する列に重複した値が既に登録されていると、一意索引は作成できません)。

任意に索引を作成する場合、複数の列の組合せで索引を作成することもできる(「複合の索引」といいます)。また、PRIMARY KEY制約やUNIQUE制約が複数の列の組合せで指定された場合も、「複合の索引」となる。

• CREATE UNIQUE INDEX文で一意索引を作成
• CREATE INDEX文で複合の索引を作成
• PRIMARY KEY制約で一意索引を作成

INDEXオブジェクト権限

対象のテーブルに対して索引（INDEX）を作成できる権限。
他人のスキーマには適用できません。

GRANT INDEX ON employees TO user_a;

→ user_aは、employeesテーブルに対して索引を作成できる。

特徴
「このテーブルに限り」INDEXを作れる。
テーブル所有者またはDBAが個別に付与。

CREATE ANY INDEXシステム権限

任意のスキーマに対してINDEXを作成できる強力なシステム権限。
全データベース内のどのテーブルに対してもINDEXを作成可能。

GRANT CREATE ANY INDEX TO user_b;

→ user_bは他人のスキーマにあるテーブルにも索引を作れる。

特徴
「ANY（どのスキーマでも）」INDEXを作れる。
管理者権限に近いので通常はDBAなど限られたユーザーのみに付与。

表に索引を作成する権限であるINDEXオブジェクト権限かCREATE ANY INDEXシステム権限があれば、別のユーザーが所有する表に対しても索引を作成できる。「スキーマ名.表名」のように表名の前にスキーマ名をつけて、別ユーザーの表を参照する。
※スキーマとは、オブジェクトの所有者を表す論理的な概念。

以上より、

• 別のユーザーが所有する表に対しても索引を作成できる

シノニムを削除した場合、シノニムが参照していた表自体に影響はない。
また、シノニムが作成されている表を削除しても、シノニムは削除されない。

• 初期値は1
• 増分は1
• 最大値は50
• 順序が最大または最小値に達した場合は、順序値の生成を終了
• 順序値をキャッシュ

CREATE SEQUENCE s_dept
    MAXVALUE 50;

オプションを明示的に指定しない場合の初期値は、

• 初期値は1
• 増分値は1
• 順序が最大または最小値に達した場合は、順序値の生成を終了する
• 順序値をキャッシュする

次の条件に該当する列に索引を作成すると、検索時のパフォーマンス向上につながる。

• WHERE句の条件や結合条件としてよく使用される列
• 列にNULL値が多く含まれており、NULL値以外の値を指定して検索する列
• 表の規模が大きく、多くの問合せで15%未満の行を検索する列

CREATE PUBLIC SYNONYM emp
FOR pingt.employees;

• CREATE PUBLIC SYNONYM
  → 「パブリック・シノニム」を作成する。

• emp
  → パブリックなので、全ユーザーが使える別名になる。
  → 作成するシノニム名（別名）。ここでは "emp" が別名になる。

• FOR pingt.employees
  → どのオブジェクトに対して別名を作るかを指定。

ここではpingt スキーマのemployees テーブル。

何ができるようになるか
この命令を実行すると、元々は

SELECT * FROM pingt.employees;

と書かないと参照できなかったテーブルを

SELECT * FROM emp;

のように 短い名前で呼び出せるようになる。

問題

データベース管理者が次のSQL文でシノニムを作成した。

CREATE PUBLIC SYNONYM emp FOR pingt.employees;

ユーザーAがこのシノニムを使用して次の問合せを実行したところエラーとなりました。エラーの原因は何か。

SELECT * FROM emp;

ユーザーAにEMPLOYEES表を参照する権限がないため

1. テーブル作成

CREATE TABLE departments6
AS
SELECT * FROM departments
WHERE 1 = 0;

SELECT の結果を使って新しいテーブルを作る（これを CTAS: Create Table As Select と呼ぶ）。
SELECT * FROM departments
→ departments 表の全列を選択。
WHERE 1 = 0
→ 常に偽になる条件なので、データは1行も返らない。
結果：
列構造はコピーされるが、行は0件 の新しいテーブルができる。

2. シーケンス作成

CREATE SEQUENCE seq_dept;

seq_deptという名前のシーケンス（連続番号生成器）を作成。
デフォルト設定なら、1からスタートし1ずつ増加します。

3. データ挿入

INSERT INTO departments6 VALUES (seq_dept.nextval, 'Sales', 1001);
COMMIT;

seq_dept.nextval でシーケンスの次の番号を取得（たとえば1）。
departments6 に (1, 'Sales', 1001) の行を挿入。
COMMIT で確定（コミット）し、データが確実に保存される。

4. データ挿入（ロールバック）

INSERT INTO departments6 VALUES (seq_dept.nextval, 'Dev', 1002);
ROLLBACK;

またシーケンスの次の番号を取得（たとえば2）。
departments6 に (2, 'Dev', 1002) の行を挿入するが、
ROLLBACK で直前の操作を取り消し、この挿入はキャンセルされる。

5. データ挿入

INSERT INTO departments6 VALUES (seq_dept.nextval, 'Dev', 1002);
COMMIT;

もう一度シーケンスの次の番号を取得（ここがポイント）。
シーケンスは1度呼び出されると値が進むので、前のロールバックがあっても番号は進んでいます。
たとえば3になる。
(3, 'Dev', 1002) を挿入し、COMMITで確定。

CREATE SYNONYM emp FOR pingt.employees;

次のSQL文を実行するユーザーはPINGTユーザーのEMPLOYEES表を参照する権限が与えられているものとします。

「PUBLIC」を書かずに作ると、プライベートシノニムになる
プライベートシノニムは作ったユーザーのスキーマの中だけで使える
作ったユーザー以外は使えない
スキーマを指定した場合

CREATE SYNONYM schema_name.emp FOR pingt.employees;

もし全ユーザーが使えるようにしたければ、

CREATE PUBLIC SYNONYM emp FOR pingt.employees;

権限がなかった場合、参照権限がないので、SQLはエラーになります。

権限を付与

GRANT SELECT ON pingt.employees TO <対象ユーザー>;

主キー制約（PRIMARY KEY）やユニーク制約（UNIQUE）と索引（インデックス）
PRIMARY KEY制約やUNIQUE制約は、テーブルの中で「重複を許さない」ルール。
これらの制約を実現するために、データベースは 自動的に一意のインデックス（一意索引） を作成します。

一意索引の特徴
この自動作成されたインデックスは制約とセットで管理されます。
インデックス単体で勝手に削除したり変更したりできません。

• DROP INDEX文では削除できない
  DROP INDEX インデックス名;
  とやっても、
  PRIMARY KEYやUNIQUE制約により自動作成されたインデックスは削除できません。

• 削除する方法
  制約（PRIMARY KEYやUNIQUE）を削除したときに、関連する自動インデックスも自動的に削除されます。

ALTER TABLE テーブル名 DROP PRIMARY KEY;
ALTER TABLE テーブル名 DROP CONSTRAINT ユニーク制約名;

を実行すると、
それに紐づく自動作成された一意インデックスも一緒に消える。
PRIMARY KEYやUNIQUE制約を削除する
関連した一意のインデックスが自動で削除される

プライベートシノニムを削除するにはDROP SYNONYM文を使用する。
DROP PUBLIC SYNONYM文はパブリックシノニムを削除する。

1. シーケンスの作成

CREATE SEQUENCE seq_id MAXVALUE 100;

seq_id というシーケンスを作成しました。
1からスタートし、最大値100まで番号を生成します。

2. テーブルの作成

CREATE TABLE list
(
  id NUMBER(3) DEFAULT seq_id.NEXTVAL PRIMARY KEY,
  name VARCHAR2(10)
);

listテーブルを作成。
id 列は NUMBER(3) 型で、デフォルト値に seq_id.NEXTVAL を設定。
つまり、idに値を指定しなければ、seq_id シーケンスから自動的に番号が入る設計。
idは主キー。

3. シーケンスの削除

DROP SEQUENCE seq_id;

シーケンス seq_id を削除。

このあと list テーブルにデータを挿入するとどうなる？
ポイントは「id列のデフォルト値に使っていた seq_id.NEXTVAL はもう存在しない」こと。

設問のSQL文では順序SEQ_IDを作成し、LIST表のID列のデフォルト値にNEXTVAL疑似列を指定し、その後、順序SEQ_IDを削除している。列のデフォルト値はデータの追加時にその列への値の入力が省略された場合に格納されるものなので、順序SEQ_IDが無いとエラーになる。

不可視索引

デフォルトでは使用されない索引である

CREATE INDEX ind_price
ON new_products(list_price) INVISIBLE;

1. CREATE INDEX ind_price
ind_price という名前のインデックス（索引）を作成します。
インデックスは、検索や並び替えを速くするための仕組みです。

2. ON new_products(list_price)
new_products というテーブルの list_price 列に対してインデックスを作ります。
この列を条件に検索するとき（例：WHERE list_price > 1000）に処理が速くなります。

3. INVISIBLE
インデックスを「不可視（Invisible）」として作成。
不可視インデックスとは：
通常のSQL文実行時、オプティマイザはこのインデックスを使いません。
ただし、ヒント句で明示的に指定すれば使うことはできます。

主な用途：
インデックスを削除する前に「本当に不要か」をテストする
新しいインデックスを作ったけど、パフォーマンスへの影響を安全に検証したいとき

オプティマイザ

データベース（特にOracleなど）で SQL文をどのような手順で実行するかを決める“頭脳” のこと。

SELECT /*+ ヒント内容 */ 列名 FROM テーブル名 WHERE 条件;

/*+ ... */ がヒント句です。
普通のコメントに見えますが、+ があることでオプティマイザへの命令として扱われる。

SELECT /*+ INDEX(products ind_price) */ * FROM products WHERE list_price > 1000;

ind_price というインデックスを使うよう指示。

CREATE INDEX ind_price ON new_products(list_price DESC);

降順索引
ファンクション索引として扱われる

降順索引はOracle Databaseにファンクション索引として扱われます。ファンクション索引とは、関数や式を使用した列に作成される索引。

プライベートシノニムを作成するSQL文として、正しいものはどれですか。

CREATE SYNONYM dept FOR departments;

パブリックシノニムを作成するSQL文として、正しいものはどれですか。

CREATE PUBLIC SYNONYM dept FOR departments;

索引に関する説明として、正しいもの

索引はデータの検索を高速化するスキーマ・オブジェクト。
表の列に索引が設定されると、索引が設定された列の値と、その物理的な格納場所（ROWID）を登録する。

索引が設定されていない表でデータを検索する場合、問合せの条件に従って、表の先頭のデータから1行ずつ検索していくので、データが大量にある場合には相当の時間がかかる。しかし索引を設定している表では、ROWIDを使用してデータを検索するため、大量のデータの中からでも高速に目的のデータを探すことができる。

また、表に対してDML文を実行する度に、表に設定された索引はメンテナンス（必要であれば更新）される。

順序に関する説明として、正しいもの

順序は複数のユーザーで共有可能なため、複数のユーザーが同じ順序で順序値を生成した場合、常に全体を通して一意な値(※)を生成。

※ただし、順序の作成時にCYCLEオプション(順序値が最大値または最小値に達した場合、初期値に戻り繰り返し順序を生成する)を指定した場合は、重複した順序値が生成されることもあります。

また、順序は表と関連付けられるものではないので、複数の表で1つの順序を使用することもできます。

なお、順序は連番が保証されているわけではなく、ロールバックが発生した場合などに欠番が生じることもある。

以上より、

• 欠番が発生する場合もある
• 複数のユーザーで共有可能である
• 1つの順序を複数の表で使用できる

順序に関する説明として、正しいもの

順序はCREATE SEQUENCE文で作成し、ALTER SEQUENCE文で変更、DROP SEQUENCE文で削除。

作成した順序から新しい順序値を取得したり、現在の順序値を確認するためには、NEXTVAL疑似列、CURRVAL疑似列を参照します。ただし、CURRVAL疑似列は、順序生成後に一度NEXTVAL疑似列を参照してから参照しなければなりません。

以上より、

• 順序を削除する場合は、DROP SEQUENCE文で削除
• セッション開始後、CURRVAL疑似列を参照する前にNEXTVAL疑似列を参照しなければならない

CREATE SEQUENCE seq_test START WITH 1;

❌ まだNEXTVALしていないのでCURRVALは使えない

SELECT seq_test.CURRVAL FROM dual;

→ ORA-08002: sequence CURRVAL is not yet defined in this session

✅ NEXTVALで番号を生成

SELECT seq_test.NEXTVAL FROM dual;

結果

1

✅ NEXTVALを呼んだ後ならCURRVALが使える

SELECT seq_test.CURRVAL FROM dual;

結果

1

1. シーケンス作成

CREATE SEQUENCE seq_id MAXVALUE 100;

seq_id という名前のシーケンスを作成
シーケンスは連番を自動生成するオブジェクト
MAXVALUE 100 → 生成できる最大値は 100
デフォルトでは START WITH 1, INCREMENT BY 1 なので、1から1ずつ増える

2. テーブル作成

CREATE TABLE emp
(
  id NUMBER(3) DEFAULT seq_id.NEXTVAL PRIMARY KEY,
  name VARCHAR2(10),
  hiredate DATE DEFAULT SYSDATE
);

各列の意味

• id NUMBER(3)
  最大3桁の数値（NUMBER(3) は「桁数3」）
  DEFAULT seq_id.NEXTVAL → デフォルトで seq_id シーケンスの次の番号が自動採番される
  → INSERT時にidを省略すると、自動的に連番が入る
  PRIMARY KEY → 主キー制約（重複不可、NULL不可）

• name VARCHAR2(10)
  最大10文字の文字列
  名前など短いテキスト用

• hiredate DATE DEFAULT SYSDATE
  日付型（DATE）
  デフォルト値は SYSDATE（SQL実行時のシステム日付）
  INSERTでhiredateを指定しなければ、実行した日付が自動で入る

CREATE SEQUENCE seq_id MAXVALUE 100;

CREATE TABLE list
(
　id NUMBER(3) DEFAULT seq_id.NEXTVAL PRIMARY KEY,
　name VARCHAR2(10)
);

GRANT SELECT, INSERT ON list TO userA;

この後、userAユーザーとしてLIST表にデータを挿入する際の説明
ID列の値を省略する場合は、順序SEQ_IDを参照する権限が必要

設問のSQL文では順序SEQ_IDを作成し、LIST表のID列のデフォルト値にNEXTVAL疑似列を指定しています。データの追加時にID列への値の入力が省略された場合に、順序SEQ_IDの一意な順序値が列に格納される。

GRANT文では、userAユーザーにLIST表へのデータの参照と挿入に必要なオブジェクト権限を付与しています。これでuserAユーザーはPINGTユーザーのLIST表にデータを挿入することは可能です。ただし、ID列の値を省略する場合は順序SEQ_IDが使用されるので、順序SEQ_IDを参照するためのSELECTオブジェクト権限かSELECT ANY SEQUENCE権限が必要になる。

権限付与

GRANT SELECT ON seq_id TO userA;
-- または
GRANT EXECUTE ON seq_id TO userA;

1. SELECTオブジェクト権限（SELECT権限）
特定のオブジェクト（例：テーブルやシーケンス）に対して付与される権限。
たとえば、シーケンスseq_idに対して

GRANT SELECT ON seq_id TO userA;

を実行すると、userAはそのシーケンスseq_idだけに対してSELECT（番号取得）ができます。

2. SELECT ANY SEQUENCE権限
データベース内のすべてのシーケンスに対して番号を取得できる権限です。
システム全体の管理者や特権ユーザーに付与されることが多いです。

GRANT SELECT ANY SEQUENCE TO userA;

を付与すると、userAはどのスキーマのどのシーケンスでもNEXTVALやCURRVALを使える。

NEXTVAL擬似列、CURRVAL擬似列は以下の箇所で参照することができる。

• 主問合せのSELECT句
• INSERT文の副問合せのSELECT句
• INSERT文のVALUES句
• UPDATE文のSET句
• CREATE TABLE文またはALTER TABLE文の列のDEFAULT値

問題

DEPARTMENT_ID列とDEPARTMENT_NAME列の組合せに対して一意索引を作成するには、どのSQL文を実行するか。

CREATE UNIQUE INDEX ind ON departments (department_id, department_name);

2つ以上の列の組合せに対して索引を作成する場合は、CREATE INDEX文のON句に索引を作成する全ての列名を指定する。
また、一意索引を作成するには、CREATE INDEX文にUNIQUEオプションを指定する。

CREATE SEQUENCE seq_id CACHE 25 NOCYCLE;

順序の生成時、キャッシュを利用していない場合は、順序値の生成のためにデータディクショナリ（オブジェクトの情報を格納する表）に毎回アクセスし順序値を生成します。これに対し、キャッシュを利用している場合は、メモリ上にいくつかの順序値を生成するときだけデータディクショナリにアクセスします。そのため、キャッシュを利用しない場合と比べて、キャッシュを利用している場合のほうが順序値を取得する処理が高速になる。

問題

索引に関する説明として、正しいもの

• 索引の変更はできないので、内容を変更したい場合は索引をいったん削除してから再作成する必要がある
• 表を削除すると、その表に設定された索引も削除される