助け合いフォーラム
Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26579
問題を開く
順序値の欠番が発生する原因として、誤っているものはどれですか。
正解
コミット
解説
順序は指定された規則に従って一意な順序値を生成しますが、以下のような場合には欠番が生じることもあり、連番が保証されているわけではありません。
・ロールバックが発生した時
・システムがクラッシュした時
また、複数の表で同じ順序を使用している場合も、それら複数の表全体で一意な順序値が生成されるので、1つの表だけを見ると連番とならない場合があります。
以上より、
・コミット
が正解となります。
・ロールバックが発生した時
・システムがクラッシュした時
また、複数の表で同じ順序を使用している場合も、それら複数の表全体で一意な順序値が生成されるので、1つの表だけを見ると連番とならない場合があります。
以上より、
・コミット
が正解となります。
参考
【順序】
順序は、指定した規則に従って一意の番号を自動的に生成するスキーマ・オブジェクトです。順序は主に重複したデータを受け付けない、PRIMARY KEY制約が定義された列の値を生成する際に利用されます。
順序は複数のユーザーで共有可能なため、複数のユーザーが同じ順序で順序値を生成した場合、常に全体を通して一意な順序値(※)が生成されます。また、順序は表と関連付けられるものではないので、複数の表で1つの順序を使用することもできます。
※ただし、順序の作成時にCYCLEオプション(順序値が最大値または最小値に達した場合、初期値に戻り繰り返し順序を生成する)を指定した場合は、重複した順序値が生成されることもあります。
順序は指定された規則に従って一意な順序値を生成しますが、ロールバックが発生した時などには欠番が生じることもあり、連番が保証されているわけではありません。
以下で順序値が欠番になるケースを解説します。
[ロールバックが発生した時]
順序で値を生成したINSERT文がロールバックされると、表の状態はロールバックされますが、生成した順序値はロールバックされないので、欠番が生じる場合があります。
[システムがクラッシュした時]
順序をキャッシュする設定で作成すると、メモリ上に順序値がキャッシュされ、データディクショナリ（オブジェクトの情報を格納する表）に次に生成する順序値が書き込まれます(順序値を20個生成する設定の場合、メモリ上に1から20の順序値が生成され、データディクショナリに21が書き込まれる)。
順序値がキャッシュされている状態の時にシステムがクラッシュすると、メモリ上にキャッシュされている順序値が失われてしまいます。
Oracleサーバーには、データディクショナリに書き込まれた次に生成する順序値（この場合は21）しか残されていませんので、システムを再起動した時には、次に生成する順序値から順序値が生成され、クラッシュ前にメモリ上にキャッシュされていた順序値は欠番となります。
[複数の表で同じ順序を使用]
複数の表で同じ順序を使用している場合も、それら複数の表全体で一意な順序値が生成されるので、1つの表だけを見ると連番とならない場合があります。
【順序の作成】
順序の作成はCREATE SEQUENCE権限を持つユーザーによって行われます。
CREATE SEQUENCE 順序名
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
CREATE SEQUENCE文のオプションは次の通りです。
順序から新しい順序値を取得したり、現在の順序値を確認するためには、NEXTVAL疑似列、CURRVAL疑似列を参照します。
それぞれの参照方法は次の通りです。
順序名.NEXTVAL
順序名.CURRVAL
[NEXTVAL疑似列]
NEXTVAL疑似列を参照すると、新しい順序値が生成されて返されます。
[CURRVAL疑似列]
CURRVAL疑似列を参照すると、最後に生成された順序値が返されます。
NEXTVAL疑似列、CURRVAL疑似列は以下の箇所で参照することができます。
・主問合せのSELECT句
・INSERT文の副問合せのSELECT句
・INSERT文のVALUES句
・UPDATE文のSET句
・CREATE TABLE文またはALTER TABLE文の列のDEFAULT値
※Oracle 12cより、CREATE TABLE文やALTER TABLE文のデフォルト値にNEXTVAL疑似列とCURRVAL疑似列を指定できるようになりました。
また、NEXTVAL疑似列、CURRVAL疑似列は以下では使用できません。
・SELECT文、UPDATE文、DELETE文内の副問合せ
・ビューのSELECT句
・DISTINCTキーワードが指定されたSELECT文
・GROUP BY句、ORDER BY句、HAVING句を持つSELECT文
・集合演算子UNION、INTERSECT、MINUSによって別のSELECT文と結合されているSELECT文
・SELECT文のWHERE句
・CHECK制約の条件
なお、現在のセッションで一度も順序値が生成されていない(一度もNEXTVAL疑似列を参照していない)ときにCURRVAL疑似列を参照すると、エラーとなります
順序は、指定した規則に従って一意の番号を自動的に生成するスキーマ・オブジェクトです。順序は主に重複したデータを受け付けない、PRIMARY KEY制約が定義された列の値を生成する際に利用されます。
順序は複数のユーザーで共有可能なため、複数のユーザーが同じ順序で順序値を生成した場合、常に全体を通して一意な順序値(※)が生成されます。また、順序は表と関連付けられるものではないので、複数の表で1つの順序を使用することもできます。
※ただし、順序の作成時にCYCLEオプション(順序値が最大値または最小値に達した場合、初期値に戻り繰り返し順序を生成する)を指定した場合は、重複した順序値が生成されることもあります。
順序は指定された規則に従って一意な順序値を生成しますが、ロールバックが発生した時などには欠番が生じることもあり、連番が保証されているわけではありません。
以下で順序値が欠番になるケースを解説します。
[ロールバックが発生した時]
順序で値を生成したINSERT文がロールバックされると、表の状態はロールバックされますが、生成した順序値はロールバックされないので、欠番が生じる場合があります。
SQLを表示
CREATE TABLE departments6
AS
SELECT * FROM departments
WHERE 1 = 0;
CREATE sequence seq_dept;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Sales', 1001);
COMMIT;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
ROLLBACK;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
AS
SELECT * FROM departments
WHERE 1 = 0;
CREATE sequence seq_dept;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Sales', 1001);
COMMIT;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
ROLLBACK;
INSERT INTO departments6
VALUES (seq_dept.NEXTVAL, 'Dev', 1002);
SELECT * FROM departments6;
[システムがクラッシュした時]
順序をキャッシュする設定で作成すると、メモリ上に順序値がキャッシュされ、データディクショナリ（オブジェクトの情報を格納する表）に次に生成する順序値が書き込まれます(順序値を20個生成する設定の場合、メモリ上に1から20の順序値が生成され、データディクショナリに21が書き込まれる)。
順序値がキャッシュされている状態の時にシステムがクラッシュすると、メモリ上にキャッシュされている順序値が失われてしまいます。
Oracleサーバーには、データディクショナリに書き込まれた次に生成する順序値（この場合は21）しか残されていませんので、システムを再起動した時には、次に生成する順序値から順序値が生成され、クラッシュ前にメモリ上にキャッシュされていた順序値は欠番となります。
[複数の表で同じ順序を使用]
複数の表で同じ順序を使用している場合も、それら複数の表全体で一意な順序値が生成されるので、1つの表だけを見ると連番とならない場合があります。
【順序の作成】
順序の作成はCREATE SEQUENCE権限を持つユーザーによって行われます。
CREATE SEQUENCE 順序名
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];
SQLを表示
CREATE SEQUENCE s_id
INCREMENT BY 1
START WITH 1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20;
INCREMENT BY 1
START WITH 1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20;
CREATE SEQUENCE文のオプションは次の通りです。
順序から新しい順序値を取得したり、現在の順序値を確認するためには、NEXTVAL疑似列、CURRVAL疑似列を参照します。
それぞれの参照方法は次の通りです。
順序名.NEXTVAL
順序名.CURRVAL
[NEXTVAL疑似列]
NEXTVAL疑似列を参照すると、新しい順序値が生成されて返されます。
SQLを表示
CREATE SEQUENCE seq_id;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
[CURRVAL疑似列]
CURRVAL疑似列を参照すると、最後に生成された順序値が返されます。
SQLを表示
CREATE SEQUENCE seq_id;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.CURRVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.NEXTVAL FROM dual;
SELECT seq_id.CURRVAL FROM dual;
NEXTVAL疑似列、CURRVAL疑似列は以下の箇所で参照することができます。
・主問合せのSELECT句
・INSERT文の副問合せのSELECT句
・INSERT文のVALUES句
・UPDATE文のSET句
・CREATE TABLE文またはALTER TABLE文の列のDEFAULT値
※Oracle 12cより、CREATE TABLE文やALTER TABLE文のデフォルト値にNEXTVAL疑似列とCURRVAL疑似列を指定できるようになりました。
また、NEXTVAL疑似列、CURRVAL疑似列は以下では使用できません。
・SELECT文、UPDATE文、DELETE文内の副問合せ
・ビューのSELECT句
・DISTINCTキーワードが指定されたSELECT文
・GROUP BY句、ORDER BY句、HAVING句を持つSELECT文
・集合演算子UNION、INTERSECT、MINUSによって別のSELECT文と結合されているSELECT文
・SELECT文のWHERE句
・CHECK制約の条件
なお、現在のセッションで一度も順序値が生成されていない(一度もNEXTVAL疑似列を参照していない)ときにCURRVAL疑似列を参照すると、エラーとなります
SQLを表示
CREATE SEQUENCE seq_id;
SELECT seq_id.CURRVAL FROM dual;
SELECT seq_id.CURRVAL FROM dual;
シーケンスの欠番について
投稿日 2024/01/29
コミットで正常終了した場合でも、キャッシュされたシーケンスの値は欠番になりませんか？
r
rink_rew
2024/01/30 17:39
コミットだけでは飛ばされないと思います。共有プールがクリアされたりすれば、欠番が発生することになると思いますが。
SQL> create sequence seq01 cache 20;
順序が作成されました。
SQL> select seq01.nextval from dual;
NEXTVAL
----------
1
SQL> select sequence_name, last_number from dba_sequences where sequence_name = 'SEQ01';
SEQUENCE_NAME LAST_NUMBER
-------------------- -----------
SEQ01 21
SQL> select seq01.nextval from dual;
NEXTVAL
----------
2
SQL> commit;
コミットが完了しました。
SQL> select seq01.nextval from dual;
NEXTVAL
----------
3
SQL>
コメント
この投稿に対して返信しませんか？
k kenka_saikyo
2024/01/30 16:32
補足：キャッシュされたが未使用のシーケンス値は、コミットしたとき、次にシーケンスを参照するときにとばされますか？