SQLで便利なオブジェクトを使いこなす：ORACLE MASTER Bronze SQL基礎I 講座（12）
Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう！
前回に引き続き、オブジェクトの作成と管理について解説します。前回「SQLで表のデータを制限する」で、表と制約の作成を紹介しました。今回は索引、ビューなどの作成について学びましょう。
ORACLE MASTER Bronze SQL基礎I 講座 各回のインデックス
- 第1回 Oracleで使うSQLの基礎を学ぶ
- 第2回 SQL問い合わせによる行の制限とソート
- 第3回 SQLで使える関数の基礎知識
- 第4回 SQLの関数を使いこなす
- 第5回 SQLの関数でデータを集計する
- 第6回 SQLで複数の表からデータを取り出す
- 第7回 SQLの外部結合でデータを取り出す
- 第8回 SELECT文中のSELECT文、副問い合わせ
- 第9回 副問い合わせの構文を覚える
- 第10回 SQLでデータを自在に操作する
- 第11回 SQLで表のデータを制限する
- 最終回 SQLで便利なオブジェクトを使いこなす
理解しておきたいこと
1．確認しておきたい内容
- 索引、ビュー、シノニムなどのオブジェクトの使用方法
2．索引の作成
ページ数の多い本などで何かを調べる場合、索引を使用してその項目が掲載されているページを確認してから、実際のページを開くと思います。データベースもそれと同じで、列値を使用して表の行に対するポインタを別領域に保存します。
行にアクセスするときは、まず索引からポインタを取得してから、実際の行にアクセスを行います。大規模な表から少ない行を頻繁に取得する場合は索引が役立ちます。しかし、行が更新されると索引も自動的に更新されるため、負荷がかかります。そのため、更新が頻繁に行われる表の場合、索引の作成は慎重に行う必要があります。
- UNIQUE：索引内の値が一意であることを保証する
- 列リスト：複数列を宣言した場合は、指定列の組み合わせによる索引レコードを作成する
例： SQL> CREATE INDEX emp_ename_idx ON emp(ename); |
3．ビューの作成
ビューは、SELECT文に名前を付けて保存したオブジェクトです。ビューを使用することで、表示する列を制限したり、複雑なSELECT文を隠したりすることができます。
ビューの作成SELECT文で列別名を使用しない場合、元の列名がビューにおける列名になります。そのため、列の計算や関数を使用した列が含まれる場合は、列別名を使用する必要があります。
- FORCE：ビューの基礎表が存在しなくてもビュー定義を作成
- WITH CHECK OPTION：ビューに対する更新があるときもWHERE句によるチェックを実行し、ビュー定義に違反する更新を禁止
- WITH READ ONLY：ビューに対する更新を禁止（読み取り専用のビューになる）
例：
SQL> CREATE VIEW emp_view
2 AS SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno;
SQL> DESC emp_view
名前 NULL? 型
----------------------------- -------- --------------------
DEPTNO NUMBER(2)
CNT
SQL> SELECT * FROM emp_view;
DEPTNO CNT
---------- ----------
10 3
20 5
30 6 |
問題1
部門番号10と20のみのデータを表示だけできるようにしたビューを選択しなさい。
a．
CREATE VIEW emp_view AS SELECT * FROM emp
WHERE deptno IN (10,20);
b．
CREATE VIEW emp_view AS SELECT * FROM emp
WHERE deptno IN (10,20)
WITH CHECK OPTION;
c．
CREATE VIEW emp_view AS SELECT * FROM emp
WHERE deptno IN (10,20)
WITH READ ONLY;
d．
CREATE FORCE VIEW emp_view AS SELECT * FROM emp
WHERE deptno IN (10,20);
e．
CREATE FORCE VIEW emp_view AS SELECT * FROM emp
WHERE deptno IN (10,20)
NO UPDATE;
正解：c
解説
ビューの作成時に、表示だけできるように指定するということですから、読み取り専用のビューを作成します。それにはWITH READ ONLY句を使用してビューを作成します（正解c）。
選択肢aのように標準のビューの場合は、グループ関数の使用など制限に違反しなければ更新可能です。この更新を防ぐのがWITH READ ONLY句ということになりますね。
選択肢bのWITH CHECK OPTION句は、更新は可能ですが、更新結果がビュー定義に違反するときは更新できないというものです。この問題であれば、WHERE句で使用している部門番号10と20のレコード以外の更新が拒否されます。
選択肢dと選択肢eで使用しているFORCE句は、ビューの基礎表（この問題ではemp表）が存在していなくてもビュー定義を作成できるというキーワードです。ビューの基礎表が存在していない場合、FORCE句のない選択肢aではエラーとなりますが、選択肢dなら作成できます。
選択肢eのNO UPDATEという句は存在しません。
問題2
ビューの操作に必要なものを選択しなさい。
a．ビューの基礎表に行が格納されていること
b．ビューへのSELECT権限
c．ビューの基礎表の所有者であること
d．ビューの基礎表へのSELECT権限
正解：b
解説
ビューを使用する利点の1つに、アクセス制御があります。ビューはSELECT文に名前を付けたオブジェクトですので、列と行にアクセス制限をすることが可能です。
もう1つの利点は、ビューに対する権限さえあれば、ビューの基礎表へのアクセス権限は必要ないということです（正解b）。
ビューの使用に当たり、選択肢aのような行の格納は必須ではありません。基礎表が空であってもビューを定義したりビューを使用したりすることはできます。
選択肢cと選択肢dのような基礎表の所有や表へのSELECT権限は、ビューの使用には必要ではありません。
問題3
ビューを使用して行えることを2つ選択しなさい。
a．読み取り専用にすることができる
b．データベースが停止した状態でもアクセスできる
c．データに対するアクセス制限を行える
d．問い合わせのパフォーマンスを向上させることができる
正解：a、c
解説
問題2でも解説したとおり、ビューの利点の1つはアクセス制限が行えることです（正解c）。ビューを作成するときにWITH READ ONLY句を指定すれば、読み取り専用のビューを作成することもできます（正解a）。グループ関数やDISTINCTなどを使用したビューの場合は更新はできませんが、単純なビューの場合はビューを通した基礎表への更新もできます。その更新を禁止するのがWITH READ ONLY句です。
データベースが停止した状態では、ビューを使用することはできません。ビューもデータベースオブジェクトですから、データベースが起動している必要があります（選択肢b）。また、ビューはSELECT文に名前を付けて保存しているだけですから、問い合わせのパフォーマンスを向上させるという目的で使用するものではありません（選択肢d）。問い合わせパフォーマンスを向上させたいのであれば、索引の利用などを検討しましょう。
4．シノニムの作成
シノニムを使用すると、オブジェクトに別名を作成できます。複雑な表名や別のユーザーが所有するオブジェクト（「ユーザー名.オブジェクト名」でアクセス）に単純な名前を付けることで、アクセスが容易になります。
- PUBLIC：全データベースユーザーが使用できるシノニム。CREATE PUBLIC SYNONYM権限が必要
- 元のオブジェクト名：別のユーザーが所有するオブジェクトの場合は「ユーザー名.オブジェクト名」
例： SQL> CREATE SYNONYM emp2 FOR hr.employees; |
シノニムを経由してアクセスするオブジェクトに対する権限は別途必要です。権限がない場合、シノニムを使用しようとするとエラーとなります。
例： SQL> DESC emp2 ERROR: ORA-04043: オブジェクト"HR"."EMPLOYEES"は存在しません |
問題1
シノニムを作成することが有効なものを2つ選択しなさい。
a．検索パフォーマンスを向上させたい場合
b．表名が複雑な場合
c．権限を与えたくない場合
d．ほかのスキーマのオブジェクトにアクセスする場合
e．アクセス制限をしたい場合
正解：b、d
解説
前回の宿題にした問題です。シノニムはオブジェクトに付ける別名です。同じものを指し示すなら、長く複雑な名前よりも短く簡単な名前の方がアクセスが容易ですね。最初からそのように考えてオブジェクト名を宣言すればよいのですが、別のユーザーが所有するオブジェクトの場合は「所有者名.オブジェクト名」が正式名称となるため、やはり長い名前になってしまいます。
このようなとき、シノニムを宣言しておくことでアクセスが楽になります。アプリケーションなどを開発していてバージョンアップしたとき、名前を変えなくてはならないが、下位互換性のため元の名前でもアクセスできるようにしておきたいなどの場合もシノニムは有効です。
ただし、シノニムを使用したからといって権限管理が変化するわけではありません。シノニムの所有者が、シノニム経由でアクセスするオブジェクトに対する権限を持っていない場合、アクセス時にエラーとなります（選択肢c）。
シノニムを使用することで、元のオブジェクト名を隠すことにはなりますが、列や行を制限するわけではないため、アクセス制限目的で使用することはないでしょう（選択肢e）。また、シノニムを使用したからといって検索パフォーマンスが向上するわけではありません（選択肢a）。検索パフォーマンスを向上させたいのであれば、索引の調整などを行った方がよいでしょう。
5．順序の作成
順序とは、一意的な数値を自動生成するためのオブジェクトです。昇順（値を増やす）順序オブジェクトと降順（値を減らす）順序オブジェクトのいずれかを作成できます。表から独立していますので、複数の表で共有するように採番することもできます。
一度発行された番号は、トランザクションをロールバックしても元に戻すことはできないため、連番とは限りません。順序の作成時にCYCLEを宣言した場合、最大値（降順の場合は最小値）に達したら最小値（降順の場合は最大値）に戻りますので、一意でない値を作成することもできます。
- START WITH：開始番号。デフォルトは1
- INCREMENT BY：増分値。デフォルトは1。負の値を指定すると降順になる
- MINVALUE：順序の最小値を指定できる。START WITH〜MAXVALUEの値
- NOMINVALUE：デフォルト。昇順の場合は1、降順の場合は-10^26
- MAXVALUE：順序の最大値を指定できる。28けた以内の整数
- NOMAXVALUE：デフォルト。昇順の場合は10^27、降順の場合は-1
- CACHE：メモリ上に事前にキャッシュしておく数。デフォルトは20
- NOCACHE：キャッシュしない
- CYCLE：昇順の場合は最大値に達すると最小値に戻る。降順の場合は最小値に達すると最大値に戻る
- NOCYCLE：デフォルト。順序が最大値（降順の場合は最小値）に達すると、それ以上の値を生成しない
順序を使用するときには、NEXTVAL疑似列、CURRVAL疑似列を使用します。
| NEXTVAL | 次の順序番号を取得する。複数のセッションが同時に取得しようとした場合、先着順に次の番号を発行する |
|---|---|
| CURRVAL | そのセッションで最後に取得した値を戻す。セッションで一度も採番していない場合はエラーとなる |
例：
SQL> CREATE SEQUENCE empseq START WITH 9000;
SQL> INSERT INTO emp(empno, ename)
VALUES(empseq.NEXTVAL, 'SCOTT');
SQL> SELECT empseq.CURRVAL FROM dual;
CURRVAL
----------
9000
SQL> SELECT empno,ename FROM emp WHERE empno=9000;
EMPNO ENAME
---------- ----------
9000 SCOTT |
問題1
順序に関する説明として正しいものを2つ選択しなさい。
a．次に割り当てられる番号を取得するには、CURRVALを使用する
b．順序は1つの表でのみ使用することができる
c．順序の最大値に達した後、最小値に戻るようにするには、REPEATを指定する
d．INSERT文でNEXTVALを使用すると、自動的に次の番号を割り当てることができる
e．そのセッションで最後に割り当てた番号を取得するには、CURRVALを使用する
f．同時に使用するアプリケーションで同じ順序にNEXTVALを使用すると、同じ番号が割り当てられる可能性がある
正解：d、e
解説
順序は一意な数値を生成するオブジェクトです。番号を取得するには、NEXTVAL疑似列とCURRVAL疑似列を使用します。正解dのように、INSERT文で主キー列に一意な値を自動採番するときなどにはNEXTVAL疑似列を使用します。正解eのように現セッションで最後に取得した番号（NEXTVAL）の値にアクセスするには、CURRVAL疑似列を使用します。
そのほかの選択肢の不正解の理由は次のとおりです。
●選択肢a：次に割り当てられる番号の取得にはNEXTVAL疑似列を使用します。
●選択肢b：順序は表とは独立したオブジェクトですから、複数の表で1つの順序から番号を取得することも可能です。その場合、より早くNEXTVAL疑似列によって番号を取得しようとした方から番号を割り当てます。
●選択肢c：昇順で最大値に達した後、最小値に戻すにはCYCLE句を指定します。
●選択肢f：同じ順序に対し、NEXTVAL疑似列をほぼ同時に使用した場合でも、同じ番号が割り当てられることはありません。Oracleサーバが識別した順番に番号が割り当てられます。
まとめ
前回と今回の2回にわたって、オブジェクトの作成と管理について解説しました。次の内容をチェックしておきましょう。
- 各種データ型（日付に関するデータ型はDATE、TIMESTAMP、INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTHなど）
- 列のデフォルト値（DEFAULT）は列に値が格納されないときに使用され、表の変更によって設定したときは次回以降の更新で使用される
- 制約は表レベルまたは列レベルで設定
- 主キー制約と一意キー制約では、索引が存在しなければ自動的に索引が作成される
- ビューを使用することでアクセス制限が可能。読み取り専用のビューも作成できる（WITH READ ONLY句を使用）
- シノニムを使用することで複雑なオブジェクトに簡単な名前を付けられる
- 順序を使用すると、NEXTVAL疑似列で次の番号、CURRVAL疑似列でセッションで最後に発行されたNEXTVAL値を取得できる
最後に
以上で、Bronze SQL 基礎Iの試験に必要な知識の紹介は終了です。SQL文は慣れればさほど難しいものではありません。アプリケーションを開発するときだけでなく、管理のうえでも必要なものですから、正しく理解しておきましょう。
Copyright © ITmedia, Inc. All Rights Reserved.
注目のテーマ
ITmediaはアイティメディア株式会社の登録商標です。