SPOOLで実行結果を書き出す

SQLを流したあとによく見かける “SPOOL” これは一体何を表しているのだろうか。

SPOOLを使うことで、SQL*Plusで出力した画面の結果をテキストファイルに保存できます。

例）spool.sql
SPOOL file.txt
SELECT * FROM 社員;
SPOOL OFF

上記のsqlをSQL*Plusで接続して、実行すると、SELECT * FROM 社員; の実行結果が書き出されたfile.txtが出力されます。


■CSVファイルとして出力
アプリケーション間のデータのやり取りとして利用されるCSV。
SPOOLを使って、CSV形式としてファイルを出力することもできます。

SELECTで呼び出した実行結果をカンマで区切ってCSVとして出力します

例）spool_csv.sql
SET COLSEP ‘,’
SPOOL file.csv
SELECT * FROM 社員;
SPOOL OFF

ビューを利用する

CREATE TABLEで作成された表は実表とよばれます。ビューは実表ではありません。間違いが許されないデータベース管理にとって、実表を守るという意味でビューは重要な役割を果たします。

■ビューの作成
必要な表の必要な列だけを、必要な条件で抜き出したものです。

SQL> CREATE VIEW ビューの名前
                AS SELECT 列名 条件;

例）
SQL> select * from 社員;
社員番号 　　　社員名                職務         上司　　  入社日         給与 　　歩合給 部門番号
---------- -------------------- ------------------ ---------- -------------- ---------------- ---------- ----------
7900 　　　　　田中                 業務         7698 01-12-03        195000 　　　　　30
7902 　　　　　桜井                 主任         7566 01-12-03         300000 　　　　　20
7934 　　　　　田村                 業務         7782 02-01-03 　　　230000 　　　　　10

3行が選択されました。

SQL> create view 給与は秘密
2         as select 社員番号,社員名,職務,上司,入社日,部門番号
3         from 社員;

ビューが作成されました。

SQL> select * from 給与は秘密;

社員番号 社員名                職務                         上司 　入社日 部門番号
---------- -------------------- ------------------ --------------- ------------- --------------
7900 田中                 業務                         7698 　01-12-03         30
7902 桜井                 主任                         7566　 01-12-03         20
7934 田村                 業務                         7782 　02-01-03         10

3行が選択されました。


■ビューと元になる表の関係
元になる表の値が更新されると、ビューの値も更新されます。

例）
SQL> INSERT INTO 社員 VALUES('9999','おらくる','主任','','','1000000','1000','20');

1行が作成されました。

SQL> select * from 給与は秘密;

社員番号 社員名                職務                         上司 入社日         部門番号
-------------- -------------------- ----------------- ---------- -------------- --------------
9999 おらくる                主任                                         　　　　　20
7876 長谷川                SE                         7788 　07-07-13         20
7900 田中                 業務                         7698 　01-12-03         30
7902 桜井                 主任                         7566　 01-12-03         20
7934 田村                 業務                         7782 　02-01-03         10

5行が選択されました。

■ビューの確認
ビューの存在は、USER_VIEWSで確認することができます。
ビューの名前はVIEW_NAME、ビューの内容はTEXTに保存されています。

例）
SQL> select VIEW_NAME,TEXT FROM USER_VIEWS
2                 WHERE VIEW_NAME='給与は秘密';

VIEW_NAME                                                TEXT
----------------------------------------------------　--------------------------------------------------------------------------------
給与は秘密                                                select 社員番号,社員名,職務,上司,入社日,部門番号 from 社員


■ビューの削除
DROPコマンドを使います。
SQL> DROP VIEW ビューの名前;

例）
SQL> DROP VIEW 給与は秘密;

ビューが削除されました。

参考）
『基礎からのOracle』 西沢夢路著

ユーザと権限

実運用のデータベースでは、強力な権限をもつSYSTEMユーザやSYSユーザで操作は好ましくなりません。
どのようなユーザが、どこまでデータを利用できるのか、を明確にすることが大事です。

■管理者ユーザと一般ユーザ
Oracleの管理者ユーザはDBAと呼ばれ、SYSユーザ・SYSTEMユーザがそれにあたります。

一般ユーザは、管理者によって作成されるユーザのことです。
権限を与えない限りは、データベースに対して何の操作もできません。
ユーザを作成するのは、下記のコマンドです(SYSTEMユーザまたはSYSユーザでSQL*Plusに接続します)

SQL> CREATE USER ユーザ名 IDENTIFIED BY パスワード;


■権限
Oracleには、システム権限とオブジェクト権限という2種類があります。

システム権限
データベースの操作に対する許可を与えます。
SELECTやDROPといった操作ができるようになります。

システム権限を付与するには、下記のコマンドを使います。
SQL> GRANT システム権限名 TO ユーザ名

システム権限名としては、ざっと以下のものがあります（本当はもっと複雑で多いけども省略）
・CREATE SESSION（データベースに接続できる権限）
・CREATE TABLE（表を作成できる権限）
・CREATE ANY TABLE（別のスキーマも含めて表を作成できる権限）
・SYSDBA（データベースの起動・停止、オブジェクトの作成など何でもできる権限）
・ALL PRIVILEGES（何でもできる権限）


オブジェクト権限
特定のユーザのオブジェクトを利用する許可を与えます。
つまり、別のユーザのスキーマを利用することができます。

オブジェクト権限を付与するには、下記のコマンドを使います。
SQL> GRANT オブジェクト権限名 ON オブジェクト名 TO ユーザー名

オブジェクト権限名としは、ざっと以下のものがあります
・SELECT（検索）
・INSERT（挿入）
・ALTER（変更）
・INDEX（索引作成）


権限の確認
与えられている権限は下記のようにして確認できます。
権限を見たいユーザでSQL*Plusにつなぐ。
SQL> SELECT * FROM USER_SYS_PRIVS;


権限の削除
設定されている権限を削除します。

SQL> REVOKE 権限 [ON オブジェクト名] FROM 対象ユーザ名;

オブジェクト権限を削除するときに、[ON オブジェクト名] を指定します。
権限に、ALL PRIVIREGES を指定すると、全ての権限を削除することができます。

参考) 基礎からのOracle

インデックスの設定

インデックス(index)とは、表を効率よく検索するための”索引”です。
日常生活で多く使われています。
例えば、本を使ってある語句を調べる際に、本の末尾にある索引から目的の語句が記載されているページの情報を得る、といったものです。
索引を使うことで素早く情報を得られることは容易に理解できると思います。

Oracle内にある大量のデータが保管されている表を検索するとき、どうすれば早く検索できるでしょうか。
そう、検索に表そのものではなく、インデックスを利用すれば良いのです。
基本的にデータベースの行は、規則性のない並び方をしているのだから、規則的に並べられたデータであるインデックスを利用することで、検索の時間が短縮される、という考え方です。


■インデックスの設定
SQL> CREATE INDEX インデックス名 ON 表名(列名);

例）
SQL> desc 社員;
名前                                                                 NULL? 型
----------------------------------------------------------------- -------- --------------------------------------------
社員番号                                                         NOT NULL     NUMBER(4)
社員名                                                                                  VARCHAR2(10)
職務                                                                                     VARCHAR2(9)
上司                                                                                     NUMBER(4)
入社日                                                                                  DATE
給与                                                                                     NUMBER(9)
歩合給                                                                                  NUMBER(9)
部門番号                                                                               NUMBER(2)

SQL> CREATE INDEX PK_社員 ON 社員(社員番号);


■インデックスの削除
SQL> DROP INDEX インデックス名;

例)
SQL> DROP INDEX PK_社員;


■インデックスの再構築
索引は、表からは独立していますが、Oracleによって自動的に維持されます。
つまり、表にデータが挿入されれば、索引にも自動的に値が入ります。
そのさいにOracleは検索のしやすい、バランスのよい構造を作ろうと頑張ってくれます。
しかし、何度も更新されることで、次第にアンバランスで検索に時間がかかる索引になります。

そこで、元のバランスの良い索引に戻すための仕組みが ”インデックスの再構築” です。

SQL> ALTER INDEX インデックス名 REBUILD ONLINE;

例）
SQL> ALTER INDEX PK_社員 REBUILD ONLINE;
索引が変更されました。

気になるのは、どのタイミングでインデックスを再構築するか、ではないでしょうか。
http://www.itmedia.co.jp/enterprise/articles/0606/02/news106.html
なるほど。


■使用していないインデックスの検出方法
http://blogs.oracle.com/oracle4engineer/entry/index
インデックスの増加はパフォーマンスの低下にもつながります。
監視してみて、あるインデックスが使用されないと分かれば、削除していくことも考える必要があります。