intro to Oracle DB 6

§14. 索引(インデックス)

テーブルに含まれる「列」を指定することで検索を高速化する.すなわちWHERE句で指定するような列を対象とする.内部的には二分木探索(をより進化させたB*Tree)を用いる.

ただし検索方法によっては索引を活用できない.例えば辞書式に索引を作成していて,後方一致検索をしても,最初の文字が分からなければ索引を引くことができず索引の意味がなくなる.

  • CREATE INDEX
    CREATE INDEX <索引名> ON <テーブル名> ( <列名> [,<列名>] ) [ TABLESPACE <表領域> ];

    indデータディクショナリビューで作成した索引を確認することができる.

  • DROP INDEX
    索引を削除する

    DROP INDEX <索引名>;
  • ALTER INDEX
    索引名を変更する

    ALTER INDEX <旧索引名> RENAME TO <新索引名>;

§15. ビュー

ビューとは,SELECT文に別名をつけて登録しておくオブジェクト.実際のデータは持たないため,「仮想表」とも言われる.

ビューを作るメリットは,

  1. SELECT文が簡略になること
    複雑なSELECT文を毎回打たなくても良い→利便性の向上
  2. セキュリティの向上
    簡略になることでテーブル名を伏せることができる

などがある.

  • CREATE VIEW
    ビューを作成する.

    CREATE VIEW [ OR REPLACE ] VIEW <ビュー名> AS <SELECT文>;

    CREATE VIEWを行うにはCREATE VIEWシステム権限が必要
    OR REPLACEオプションはすでに作成されているビューを置き換える(変更する)際に指定する.
    作成したビューはDESCRIBEコマンドで確認することができる.

    DESCRIBE <ビュー名>;
  • DROP VIEW
    ビューを削除する.

    DROP VIEW <ビュー名>;

§16. シノニム

ビューが「SELECT文に別名を付けて登録しておくオブジェクト」だとすれば
シノニムは「オブジェクトに別名をつけて登録しておくオブジェクト」
例えば,ユーザwsedrftgyhujikoのテーブルtbl_lkoijuhygtfrdsはwsedrftgyhujiko.tbl_lkoijuhygtfrds で参照するが,このwsedrftgyhujiko.tbl_lkoijuhygtfrdsテーブルの使用頻度が非常に高い場合,もっと短くてわかりやすい名前をつけたくなる.シノニムが使われるのはこのような場合である.

シノニムには二つの種類があり,

  • プライベートシノニム
    特定のユーザのみで使用するシノニムで,他のユーザーからは使うことができない.
  • パブリックシノニム
    パブリックシノニムは全てのユーザーが使用できるシノニム.このシノニムはPUBLICの所有として実現される.

プライベートシノニムとパブリックシノニムでは作成や削除の方法が異なる.通常,シノニムとはプライベートシノニムを指す

  • [プライベートシノニム] CREATE SYNONYM
    CREATE [OR REPLACE] [PUBLIC] SYNONYM <シノニム名> FOR <オブジェクト名>;

    CREATE SYNONYMを行うにはCREATE SYNONYMシステム権限が必要.
    OR REPLACEオプションはすでに作成されているシノニムを置き換える(変更する)際に指定する.
    追加されたシノニムはuser_synonymsデータディクショナリビューでシノニムを確認することができる.
    DROP SYNONYMで削除できる.

  • [パブリックシノニム] CREATE PUBLIC SYNONYM
    CREATE [OR REPLACE] PUBLIC SYNONYM <シノニム名> FOR <オブジェクト名>;

    CREATE PUBLIC SYNONYMを行うにはCREATE PUBLIC SYNONYMシステム権限が必要.
    OR REPLACEオプションはすでに作成されているシノニムを置き換える(変更する)際に指定する.
    追加されたシノニムはall_synonymsデータディクショナリビューでシノニムを確認することができる.
    DROP PUBLIC SYNONYMで削除できる.

intro to Oracle DB 5

§12. ユーザー

ユーザーを作成するには次の五つの情報が必要

  1. ユーザ名
    30文字以内(半角英数字)
    Oracleの予約語は使用できない
  2. パスワード
    30文字以内(半角英数字)
    Oracleの予約語は使用できない
  3. デフォルト表領域
    作成するユーザーがデフォルトで使用する領域.これを指定しないと,SYSTEM表領域に書き込まれることになる.ここにはOracleの構成情報が入っているので避ける.
  4. クォータ
    ユーザーが表領域をどれだけ使用できるかを定義する.設定しないと際限なく使えることになる.
  5. デフォルト一時表領域
    特に指定しなくて良い.指定しなければ,Oracle内で共通の一時表領域を使用できる.
  • CREATE TABLESPACE
    表領域の作成はCREATE TABLESPACE文で行う.

    CREATE TABLESPACE <表領域> DATAFILE '<データファイル名>' SIZE M;

    データファイル名は絶対パスで指定する.DBFファイルが出来ていれば作成成功.

  • CREATE USER
    ユーザーの作成はCREATE USER文で行う.

    CREATE USER <ユーザー名> IDENTIFIED BY <パスワード>
    [ DEFAULT TABLESPACE <表領域名>]
    [ TEMPORARY TABLESPACE <一時表領域名>]
    [ QUOTA <>M ON <対象表領域名>];

    ユーザー情報の確認はdba_usersデータディクショナリビューを使って確認することができる

§13. 権限とロール

権限には大きく次の2種類がある.

  • システム権限
    Oracleを構築したり,構成情報の変更,テーブルの作成など運用管理する際に必要な権限

    • CREATE SESSION:  Oracleへ接続するための権限
    • ALTER DATABASE: 構成情報の変更
    • CREATE TABLE: テーブルの作成

    特にCREATE SESSION権限がないとOracleに接続できないため,真っ先にこれを与える必要がある.

  • オブジェクト権限
    他のユーザーが所有するテーブルや索引などのオブジェクトに対して「特定の操作を実行する」ための権限.システム権限とは異なり,オブジェクト権限はオブジェクトの所有者が他のユーザーに付与する.

実際に権限を与える操作を見ていく.

  • GRANT: 権限の付与
    • システム権限
      GRANT <システム権限名> [, <システム権限名>...]
      TO <ユーザー名> [, <ユーザー名>...]
      [WITH ADMIN OPTION];

      システム権限はほぼテーブルに作用する操作名に対応する.例えば
      テーブルから行を検索する権限: SELECT
      テーブルに行を追加する権限:INSERT
      テーブルの構成を変更する権限:ALTER TABLE
      などである.
      WITH ADMIN OPTIONは権限が付与されたユーザーは,その権限をさらに他のユーザーに付与できる(admin権限)
      システム権限の確認はuser_sys_privsデータディクショナリビューを使って確認することができる.データベース全体の情報を確認したい場合はdba_sys_privsデータディクショナリビューを使うと良い.

    • オブジェクト権限
      GRANT <オブジェクト権限> [ ( <列名> ) ]
            [,<オブジェクト権限> [ ( <列名> ) ]...]
      ON [<ユーザー名>.]<オブジェクト名>
      TO <ユーザー名> [, <ユーザー名>...]
      [WITH GRANT OPTION];

      WITH GRANT OPTIONは権限が付与されたユーザーは,その権限をさらに他のユーザーに付与できる(admin権限)
      ここで大事なことは,ONに続くオブジェクト名の指定ではほとんどの場合他のユーザーが所有するオブジェクトを指定することになるので<ユーザー名>を指定することである.
      システム権限の確認はuser_tab_privsデータディクショナリビューを使って確認することができる.
      なお,権限を取り消すにはREVOKE文を使う.

ところで,実際のユースケースでは複数の権限をまとめて管理したい場合がある.ユーザーが数百人いて,その一人一人に一つ一つ細かい権限を与えることはとても辛い.

そこでロールの必要性が出てくる.ロールとは,複数の権限を一つにまとめたもので,システム権限やオブジェクト権限だけでなく,作成してある別のロールを含めることもできる.
標準で用意されているロールは例えば

  • CONNECT: CREATE SESSIONシステム権限
  • RESOURCE: CREATE TABLEシステム権限, CREATE SEQUENCEシステム権限等8つのシステム権限
  • DBA: 全てのシステム権限(管理者用)

などがある.自分で作成する場合は

  • CREATE ROLE
    CREATE ROLE <> [ IDENTIFIED BY <> | NOT IDENTIFIED ];

    ロールを作成する(中身は空).dba_rolesデータディクショナリビューでロールの確認ができる.
    ロールに権限を付与したり削除するのは,システム権限・オブジェクト権限同様にGRANTやREVOKEを用いる.

intro to Oracle DB 4

§8 DMLによるデータの更新

  • INSERT
    • 一行ずつ値を指定して挿入する
      INSERT INTO <テーブル名> [ (<列名> [, <列名>...])] VALUES (<値> [,<値>...])

      VALUESには挿入するデータを指定する.数値ならそのままで良いが,文字型・日付型ならシングルクォーテーションで囲む

    • SELECT文の問い合わせ結果を挿入する
      既存のテーブルから別のテーブルにデータを追加する際に用いる.

      INSERT INTO <テーブル名> SELECT <列名> [, <列名>...] FROM <テーブル名> WHERE <検索条件>
  • UPDATE
    既存の行の値を更新するのに使う

    UPDATE <テーブル名> SET <列名> = <値> [ ,<列名> = <値>... ] [WHERE <検索条件>];

    SETには挿入するデータを指定する.数値ならそのままで良いが,文字型・日付型ならシングルクォーテーションで囲む

  • DELETE
    既存の行を削除するのに使う

    DELETE FROM <テーブル名> [WHERE <検索条件>]

§9 DCLと読み取り一貫性

  • トランザクションの原子性
    トランザクションとはDMLの複数の処理を1つにまとめたもの.トランザクションに含まれる一連の処理は「全ての処理が正しく実行されるか,全く実行されるかのいずれか」となる.
    例えば,

    1. 振込元から残高を減額する
    2. 振込先から残高を加算する
      この二つの処理は不可分である.どちらかの処理しか行われないと振込処理としては不正である.このようにトランザクションの不可分な性質をトランザクションの原子性という.
  • ROLLBACK
    DMLによって実行された処理を取り消す
  • COMMIT
    DMLによって実行された処理を確定する
  • 読み取り一貫性
    あるトランザクションによって変更されたデータは,COMMITもしくはROLLBACKが実行されるまでは別のセッションではそれ以前のデータが返される.この性質を読み取り一貫性という.
    もし読み取り一貫性がなければ,データベースの変更中に別のセッションからデータベースを書き換えることを許してしまい,このことで検索結果が変わってしまう.

§10 DDLによるテーブルの作成・削除

  • CREATE TABLE
    テーブルを作成する
  • ALTER TABLE
    テーブルの構造を変更する(列の追加や削除).
    テーブルに列を追加する.

    ALTER TABLE <テーブル名> ADD ( <列名> <データ型> [ , <列名> <データ型> ...] )

    テーブルから列を削除する.

    ALTER TABLE <テーブル名> DROP ( <列名> [ , <列名> ...] )
  • DROP TABLE
    テーブルを削除する