5
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@b150005

Oracle Database/SQLを完全に理解する①【基礎編】

5
Posted at

📖 参考

Databases for Developers: Foundations

テーブルの作成(create table)

テーブル構成

テーブル構成は、organization句で定義し、レコードの格納方法を指定することができる。

オプション レコードの格納方法
Heap(既定) 下から順次追加
Index 主キーでソートされた状態で追加
External 外部ファイル(.csv等)の読込

ヒープ構成表

ヒープ構成表の作成
create table toys_heap (
  toy_name varchar2(100)
) organization heap;

インデックス構成表

インデックス構成表を作成する場合は主キーが必要

インデックス構成表の作成
create table toys_iot (
  toy_id   integer primary key,
  toy_name varchar2(100)
) organization index;

外部表

外部表を作成する場合はファイルパスを定義するディレクトリの作成と、
ファイル名の記述が必要

外部表の作成
-- 管理者権限(SYSTEMユーザ)でディレクトリを作成
create or replace directory tmp as '/path/to/file';

-- 一般ユーザに読み書き権限を付与
grant read, write on directory tmp to ORCLADM;

-- 一般ユーザで外部表を作成
create table toys_ext (
  toy_name varchar2(100)
) organization external (
  default directory tmp
  location ('toys.csv')
);

一時表

SQLセッションの期間中のみ実体化される表を一時表と呼ぶ。
一時表にはグローバル・プライベートの2種類が存在し、それぞれ以下のように分類される。

種類 概要
グローバル 全ユーザがアクセス可能
プライベート 作成者のみアクセス可能

グローバル一時表

グローバル一時表の作成
create global temporary table toys_gtt (
  toy_name varchar2(100)
);

プライベート一時表

プライベート一時表を作成する場合は、テーブル名の接頭辞にora$ptt_を付与

プライベート一時表の作成
create private temporary table ora$ptt_toys (
  toy_name varchar2(100)
);

パーティション表

特定のカラムをパーティションキーとして同一テーブルをセグメントに分割することをパーティショニング(パーティション化)と呼ぶ。
パーティショニングはあくまで論理上の分割であり、パーティションキーが同じであれば同一領域に格納される。

パーティショニングの分割方式は、以下の3種類に分類される。

分割方式 条件
レンジ 値の範囲
リスト 値
ハッシュ 要素数(各パーティションの要素数が均等になるように分配)

パーティション条件は、方式・パーティションキー・パーティション名の3つが必要

パーティション表の作成
-- レンジ方式
create table toys_range (
  toy_name varchar2(100)
) partition by range ( toy_name ) (
  partition p0 values less than ('b'),
  partition p1 values less than ('c')
);

-- リスト方式
create table toys_list (
  toy_name varchar2(100)
) partition by list ( toy_name ) (
  partition p0 values ('Sir Stripypants'),
  partition p1 values ('Miss Snuggles')
);

-- ハッシュ方式
create table toys_hash (
  toy_name varchar2(100)
) partition by hash ( toy_name ) partitions 4;

-- ハッシュ方式 × インデックス構成表
create table toys_part_iot (
  toy_id   integer primary key,
  toy_name varchar2(100)
) organization index 
  partition by hash ( toy_id ) partitions 4;

クラスタ表

複数テーブルの特定カラムをクラスタキーとして、同一クラスタキーをもつ複数テーブルを一つに結合したテーブルをクラスタ表と呼ぶ。
同一クラスタキーをもつ複数テーブルのデータは、同一データブロックに格納される。

クラスタ表を作成する場合は、事前に(表)クラスタを作成する必要がある。

クラスタ表の作成
-- クラスタの作成
create cluster toy_cluster (
  toy_name varchar2(100)
);

-- クラスタキーを指定したクラスタ表の作成
create table toys_cluster_tab (
  toy_name varchar2(100)
) cluster toy_cluster ( toy_name );

create table toy_owners_cluster_tab (
  owner    varchar2(20),
  toy_name varchar2(100)
) cluster toy_cluster ( toy_name );

テーブルの削除(drop table)

テーブルの削除
drop table toys_heap;

💡 データ型の定義

区分 型 概要
文字列 varchar2 32,767[bytes]以下の可変長
^ char 2,000[bytes]以下の固定長
^ clob 4$ b $[GB]以下の可変長($ b $はブロックサイズ)
^ nvarchar2
nchar
nclob		 Unicode文字列のみ対応
数値 number($ p $, $ s $) 小数第$ s $位で丸め込まれた$ p $桁までの数値
^ float 小数(非推奨)
^ binary_float 32[bit]の単精度浮動小数点数
^ binary_double 64[bit]の倍精度浮動小数点数
^ integer = number(*, 0)
^ real = float(63)
日付 date yyyy-mm-dd hh24:mi:ssで表される日時
^ timestamp yyyy-mm-dd hh24:mi:ss.ffで表される日時
^ time interval 年→月または日→秒に変換して行う日付の加減算
バイナリ raw 32,767[bytes]以下のバイナリデータ
^ blob 4$ b $[GB]以下のバイナリデータ($ b $はブロックサイズ)
各データ型の定義
-- 文字列
create table character_data (
  varchar_10_col   varchar2(10),
  varchar_4000_col varchar2(4000),
  char_10_col      char(10),
  clob_col         clob
);

-- 数値
create table numeric_data (
  number_3_sf_2_dp  number(3, 2),
  number_3_sf_2     number(3, -2),
  number_5_sf_0_dp  number(5, 0),
  integer_col       integer,
  float_col         float(10),
  real_col          real,
  binary_float_col  binary_float,
  binary_double_col binary_double
);

-- 日付
create table datetime_data (
  date_col                      date,
  timestamp_with_3_frac_sec_col timestamp(3),
  timestamp_with_tz             timestamp with time zone,
  timestamp_with_local_tz       timestamp with local time zone,
  year_to_month_col             interval year to month,
  day_to_second_col             interval day to second
);

-- バイナリ
create table binary_data (
  raw_col  raw(1000),
  blob_col blob
);

テーブルの変更

カラムの追加(add)

1テーブル内のカラム数は最大で1000列

alter table this_table_has_three_columns add (
  this_is_a_timestamp_column    timestamp, 
  this_is_a_binary_large_object blob
);

カラムの削除(drop)

alter table this_table_has_three_columns drop (
  this_is_a_timestamp_column, 
  this_is_a_binary_large_object
);

テーブル設計

概念モデル

概念モデル(Conceptual Model)では、データベース上に登録する実体(ヒト・モノ) を定義する。

実体 概要
患者 治療を受けるヒト
医師 診断・処方を行うヒト
予約 予約日時・場所

論理モデル

論理モデル(Logical Model)では、概念モデルで定義した実体の属性(=データ型・制約)を定義する。

太字は正規化(Normalization)によって論理モデルで追加された項目

正規化(Normalization) ・・・ データ間の依存関係を見つけ、テーブルを分割すること。

実体 属性
患者 氏名
医師 氏名
予約 日時
^ 病院名・住所
^ 患者・担当医
病院 病院名
^ 住所

物理モデル

物理モデル(Physical Model)では、システム上の非機能要件(パフォーマンス・テーブル構成等)を考慮したテーブルを作成する。

create table consultants (
  consultant_id   integer,
  consultant_name varchar2(100)
);

create table patients (
  patient_id   integer,
  patient_name varchar2(100)
);

create table clinics (
  clinic_name varchar2(10),
  address     varchar2(1000)
);

create table appointments (
  appointment_id       integer,
  appointment_datetime date,
  clinic_name          varchar2(30),
  consultant_id        integer,
  patient_id           integer
);

スーパータイプ/サブタイプ

物理モデルで定義したconsultants・patientsテーブルには担当医と患者の氏名をもつカラムがあるものの、同一人物が医師でもあり患者でもある場合は、consultants⇄patientsテーブル間での整合性が取りづらくなる。
そのため、人物はpersonテーブルをスーパータイプとして一括管理し、医師や患者の詳細情報を別途consultants・patientsテーブルで管理するのが望ましい。

複数テーブルに同じ情報をもつカラムが存在する場合、スーパータイプとして汎化(generalization)させることを検討する

同一テーブルに特定条件においてのみ適用されるカラムが存在する場合、サブタイプとして特化(specialization)させることを検討する

drop table consultants;
drop table patients;

create table people (
  person_id integer,
  full_name varchar2(100)
);

create table consultants (
  consultant_id  integer,
  salary         number(10,2),
  speciality     varchar2(30)
);

create table patients (
  patient_id      integer,
  hopsital_number integer
);

レコードの取得

全件取得

*(アスタリスク, スター)は全ての可視カラムを取得することができる。
不可視カラムを取得する場合は、カラム名で指定する必要がある。

必要なカラムだけを取得することで、データ転送量を抑え、アプリケーションの高速化を図る

select * from toys;

select toy_name, price from toys;

フィルタリングを用いたレコード取得

and条件はor条件よりフィルタリング条件における優先順位が高いため、or条件を適用したい場合は括弧(parenthese) を用いるか、場合によってはinを用いることが可能

リスト指定
-- (toy_name = 'Mr Bunnykins') or
-- (toy_name = 'Baby Turtle' and colour = 'green')
select * from toys
where  toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle'
and    colour = 'green';

-- (toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle') and
-- (colour = 'green')
-- 括弧を用いる場合
select * from toys
where  ( toy_name = 'Mr Bunnykins' or toy_name = 'Baby Turtle' )
and    colour = 'green';
-- inを用いる場合
select * from toys
where  toy_name in ( 'Mr Bunnykins', 'Baby Turtle' )
and    colour = 'green';

フィルタリング条件が閉区間の場合はbetweenを用いることも可能

範囲指定
-- フィルタリング条件は閉区間[6, 20]
select * from toys
where  price >= 6
and    price <= 20;
-- betweenを用いる場合
select * from toys
where  price between 6 and 20;

like条件とワイルドカード(_・%)を用いてワイルドカードによる条件指定も可能
_ ・・・ 1文字のワイルドカード
% ・・・ 0文字以上のワイルドカード

エスケープ文字はescape句で宣言する
エスケープ文字は任意の文字だが、慣習的に\(バックスラッシュ)や#(ハッシュ)が用いられる

ワイルドカード指定
select * from toys
where  colour like '%_e_%';

-- エスケープ文字の指定
select * from toys
where  toy_name like '%#_%' escape '#';

select * from toys
where  toy_name like '%\_%' escape '\';

Nullの扱い

Nullはあらゆる値に対して等価であるとも等価でないともいえない値であり、nullとの比較結果はunknownとなるため、値がnullかどうかを調べる場合はis null条件を用いる。

is null条件
-- 取得結果が0件になってしまう
select * from toys
where price = null;

-- is null条件
select * from toys
where price is null;

否定条件

Aでない要素を満たすレコードを取得する場合は、notを条件式の前に付与するか、比較演算子!=/<>のどちらかを用いる

nullでないレコードを取得する場合のみis not null条件を用いる

not, !=, <>
-- notを用いる場合
select * from toys 
where not colour = 'green';

-- 比較演算子を用いる場合
select * from toys 
where colour != 'green';
select * from toys 
where colour <> 'green';

-- nullでないレコード
select * from toys
where colour is not null;

テーブルの結合

ここでは、以下のtoys・bricksテーブルを用いて説明する。

  • toysテーブル
TOY_ID TOY_NAME TOY_COLOUR
1 Miss Snuggles pink
2 Cuteasaurus blue
3 Baby Turtle green
  • bricksテーブル
BRICK_ID BRICK_COLOUR BRICK_SHAPE
2 blue cube
3 green cube
4 blue pyramid

なお、結合構文(join syntax) にはOracle方式とANSI方式の2種類が存在するが、基本的にANSI方式を用いるのが望ましい。

クロス結合(cross join)

クロス結合(cross join)は、2つのテーブルの直積(direct product, Cartesian product)集合を表す。
直積(デカルト積)の定義は以下の通り。

A \times B = \{ (a,b)\ |\ a \in A, b \in B \}

クロス結合を多用せざるをえない場合は、データ構造を見直す必要がある。

クロス結合
-- Oracle構文
select * 
from   toys, bricks;

-- ANSI構文
select * 
from   toys
cross join bricks;

上記のSELECT文による取得結果は、いずれも以下の通りとなる。

TOY_ID TOY_NAME TOY_COLOUR BRICK_ID BRICK_COLOUR BRICK_SHAPE
1 Miss Snuggles pink 2 blue cube
1 Miss Snuggles pink 3 green cube
1 Miss Snuggles pink 4 blue pyramid
2 Cuteasaurus blue 2 blue cube
2 Cuteasaurus blue 3 green cube
2 Cuteasaurus blue 4 blue pyramid
3 Baby Turtle green 2 blue cube
3 Baby Turtle green 3 green cube
3 Baby Turtle green 4 blue pyramid

内部結合(inner join)

内部結合(inner join)は、2つのテーブルの直積集合のうち、where・on句で指定された条件を満たすレコードの部分集合(subset) を表す。

ANSI構文におけるinnerキーワードは省略可能

内部結合
-- Oracle構文
select * 
from   toys, bricks
where  toy_id = brick_id;

-- ANSI構文
select * 
from   toys
inner  join bricks
on     toy_id = brick_id;

-- ANSI構文(innerキーワードの省略) ・・・ A
select * 
from   toys
join   bricks
on     toy_colour <> brick_colour;

AのSELECT文による取得結果は、以下の通りとなる。

TOY_ID TOY_NAME TOY_COLOUR BRICK_ID BRICK_COLOUR BRICK_SHAPE
1 Miss Snuggles pink 2 blue cube
1 Miss Snuggles pink 3 green cube
1 Miss Snuggles pink 4 blue pyramid
2 Cuteasaurus blue 3 green cube
3 Baby Turtle green 2 blue cube
3 Baby Turtle green 4 blue pyramid

外部結合(outer join)

外部結合(outer join)は、基準となるテーブルに対して可能な限り、on・where句で指定された条件を満たす外部テーブルのレコードを結合した集合を表す。
条件を満たさない場合、外部テーブルの各カラムの値がnullのレコードが結合される。

ANSI構文の場合、from句で指定したテーブルのカラムが左側に来る
Oracle構文の場合、from句で指定した順番通りに左側から並べられる

ANSI構文におけるouterキーワードは省略可能
ANSI構文の場合、結合の方向をleft・rightキーワードで表す

※ $ A \leftarrow B $ の方向に結合される場合、$ A $ が基準テーブルとなる

Oracle構文の場合、結合対象テーブルのカラムを(+)キーワードで表す

外部結合
-- ANSI構文(基準テーブル: toys)
select * 
from   toys
left   outer join bricks
on     toy_id = brick_id;

-- Oracle構文(基準テーブル: toys)
select * 
from   toys, bricks
where  toy_id = brick_id (+);

-- ANSI構文(outerキーワードの省略, 基準テーブル: bricks) ・・・ B
-- toysテーブルのカラムが左側
select * 
from   toys
right  join bricks
on     toy_id = brick_id;

-- ANSI構文(outerキーワードの省略, 基準テーブル: bricks)
-- bricksテーブルのカラムが左側
select * 
from   bricks
left   join toys
on     toy_id = brick_id;

BのSELECT文による取得結果は、以下の通りとなる。

TOY_ID TOY_NAME TOY_COLOUR BRICK_ID BRICK_COLOUR BRICK_SHAPE
2 Cuteasaurus blue 2 blue cube
3 Baby Turtle green 3 green cube
(null) (null) (null) 4 blue pyramid

複数の外部結合条件の定義

複数の外部結合条件を定義したい場合、
ANSI構文では、on句にand・or条件を用いる
Oracle構文では、where句のand・or条件に対しても(+)キーワードを用いる

外部結合におけるフィルタリング
-- ANSI構文(取得結果: 1件)
select * 
from   toys
join   bricks
on     toy_id = brick_id
where  brick_colour = 'green';

-- ANSI構文(取得結果: 3件)
select * 
from   toys
left   join bricks
on     toy_id = brick_id
and    brick_colour = 'green';

-- Oracle構文(取得結果: 1件)
select * 
from   toys, bricks
where  toy_id (+) = brick_id
and    brick_colour = 'green';

-- Oracle構文(取得結果: 3件) ・・・ C
select * 
from   toys, bricks
where  toy_id = brick_id (+)
and    brick_colour (+) = 'green';

CのSELECT文による取得結果は、以下の通りとなる。

TOY_ID TOY_NAME TOY_COLOUR BRICK_ID BRICK_COLOUR BRICK_SHAPE
3 Baby Turtle green 3 green cube
1 Miss Snuggles pink (null) (null) (null)
2 Cuteasaurus blue (null) (null) (null)

完全外部結合(full outer join)

完全外部結合(full outer join)は、2つのテーブルに対して相互に外部結合を行った集合を表す。

ANSI構文では、full join句を用いて完全外部結合を定義できる
Oracle構文では、完全外部結合のキーワードが用意されていないため、双方向の外部結合を定義し、union allを用いて2つの取得結果を結合する。

完全外部結合
-- ANSI構文
select * 
from   toys
full   join bricks
on     toy_id = brick_id;

-- Oracle構文 ・・・ D
select * 
from   toys, bricks
where  toy_id = brick_id (+)
-- 上部は toys ← bricks の外部結合
union all
-- 下部は toys → bricks の外部結合のうち、
-- toysテーブルの各カラムの値がnullであるレコード
select * 
from   toys, bricks
where  toy_id (+) = brick_id
and    toy_id is null;

DのSELECT文による取得結果は、以下の通りとなる。
この場合、上3行がtoys ← bricksの外部結合を表し、下1行がtoys → bricksの外部結合のうち、toysテーブルの各カラムの値がnullであるレコードを表す。

TOY_ID TOY_NAME TOY_COLOUR BRICK_ID BRICK_COLOUR BRICK_SHAPE
2 Cuteasaurus blue 2 blue cube
3 Baby Turtle green 3 green cube
1 Miss Snuggles pink (null) (null) (null)
(null) (null) (null) 4 blue pyramid

集計関数

集計関数(aggregate function)は、レコードを集計する関数を表す。

関数 概要
count 行数
sum 総和
min/max 最小値/最大値
avg 平均値
stddev 標準偏差(standard deviation)
median 中央値
variance 分散
stats_mode 最頻値(the most common value)

ここでは、以下のbricksテーブルを用いて説明する。

  • bricksテーブル
COLOUR SHAPE WEIGHT
red cube 1
red pyramid 2
red cuboid 1
blue cube 1
blue pyramid 2
green cube 3

射影条件の指定(distinct, unique, all)

テーブルを射影(project)する際にdistinct(unique)・allキーワードを付与することで、射影条件を指定することができる。
※射影(projection) ・・・ テーブルから一部のカラムを抽出すること

キーワード 射影対象
distinct(unique) 一意なレコード
all 全レコード

一つのDML(Data Manipulation Language)文中で指定できる射影条件(distinct・unique・all)は一つまで

キーワードによる射影条件の比較
-- allキーワード(E-1)
select all colour from bricks;

-- distinct, uniqueキーワード(E-2)
select distinct colour from bricks;
select unique colour from bricks;

E-1・E-2のSELECT文による取得結果は、それぞれ以下の通りとなる。

  • bricksテーブル(all, E-1)
COLOUR
red
red
red
blue
blue
green
  • bricksテーブル(distinct & unique, E-2)
COLOUR
red
blue
green

取得結果を別名で表示する場合、テーブル・カラム名の直後にas <別名>を付与
※asキーワードは省略可能

集計対象の限定
-- all vs. distinct ・・・ F
select sum ( weight ) total_weight, 
       sum ( distinct weight ) sum_of_unique_weights, 
       avg ( weight ) overall_mean, 
       avg ( distinct weight ) mean_of_unique_weights 
from   bricks;

FのSELECT文による取得結果は、以下の通りとなる。

TOTAL_WEIGHT SUM_OF_UNIQUE_WEIGHTS OVERALL_MEAN MEAN_OF_UNIQUE_WEIGHTS
10 6 1.66666666666666666666666666666666666667 2

グループ化(group by)

特定のカラムの値をもとに、一つのテーブルを複数のテーブルに分割することをグループ化(grouping) と呼ぶ。

group by句を用いることで、グループ化条件を指定する

グループ化を行う場合、集計関数の集計対象は各グループになる

グループ化
-- shape, weightカラムの値をもとにグループ化 ・・・ G
select shape, weight, count (*) 
from   bricks
group  by shape, weight;

ここで、Gのグループ化の過程に着目するため、IDカラムを以下のように一番左に追加したbricksテーブルを再掲する。

  • bricksテーブル(IDカラムを追加して再掲)
ID COLOUR SHAPE WEIGHT
A red cube 1
B red pyramid 2
C red cuboid 1
D blue cube 1
E blue pyramid 2
F green cube 3

SHAPE・WEIGHTカラムの値によって、以下のようにグループ化される。

  • SHAPE = cube & WEIGHT = 1 のグループ
ID COLOUR SHAPE WEIGHT
A red cube 1
D blue cube 1
  • SHAPE = pyramid & WEIGHT = 2 のグループ
ID COLOUR SHAPE WEIGHT
B red pyramid 2
E blue pyramid 2
  • SHAPE = cuboid & WEIGHT = 1 のグループ
ID COLOUR SHAPE WEIGHT
C red cuboid 1
  • SHAPE = cube & WEIGHT = 3 のグループ
ID COLOUR SHAPE WEIGHT
F green cube 3

以上から、GのSELECT文による取得結果は以下の通りとなる。

SHAPE WEIGHT COUNT(*)
cube 1 2
pyramid 2 2
cuboid 1 1
cube 3 1

グループ化後の値でフィルタリング(having)

having句を用いて、各グループに対する集計関数の結果でフィルタリングすることができる。

where句はグループ化前のフィルタリング条件を表し、
having句はグループ化後のフィルタリング条件を表す

グループ化後のフィルタリング
-- フィルタリング条件がグループ化"前"の値であればwhere句を用いる
select colour, count (*) 
from   bricks
where  weight > 1
group  by colour;

-- フィルタリング条件がグループ化"後"の値であればhaving句を用いる ・・・ H
select colour, sum( weight )
from   bricks
having sum ( weight ) > 1
group  by colour;

HのSELECT文による取得結果は、以下の通りとなる。

COLOUR SUM(WEIGHT)
red 4
blue 3
green 3

小計・総計の算出(rollup, cube)

任意のグループに対して、rollup・cubeを用いて小計(subtotal)や総計(total) を求めることができる。
なお、小計・総計を表すカラムの値にはnullが挿入される。

rollupは、引数の右のカラムから順に小計を求める
・・・ 左のカラムから順にグループ化条件の優先度が高いとも言える

cubeは、引数のクロス集計で小計を求める

小計・総計の算出
-- rollup  ・・・ I
select colour, shape, count (*)
from   bricks
group  by rollup ( colour, shape );

-- cube  ・・・ J
select colour, shape, count (*)
from   bricks
group  by cube ( colour, shape );

shape → colour の順に小計を行うIのSELECT文による取得結果は、以下の通りとなる。

COLOUR SHAPE COUNT(*) グループ化条件
red cube 1 COLOUR = red & SHAPE = cube
red pyramid 1 COLOUR = red & SHAPE = pyramid
red cuboid 1 COLOUR = red & SHAPE = cuboid
blue cube 1 COLOUR = blue & SHAPE = cube
blue pyramid 1 COLOUR = blue & SHAPE = pyramid
green cube 1 COLOUR = green & SHAPE = cube
red (null) 3 COLOUR = red(を満たすSHAPEの小計)
blue (null) 2 COLOUR = blue(を満たすSHAPEの小計)
green (null) 1 COLOUR = green(を満たすSHAPEの小計)
(null) (null) 6 -(総計; COLOUR(とSHAPE)の小計)

一方で、colour×shapeのクロス集計を行うJのSELECT文による取得結果は、以下の通りとなる。

COLOUR SHAPE COUNT(*) グループ化条件
(null) (null) 6 -(総計; COLOUR(とSHAPE)の小計)
(null) cube 3 SHAPE = cube(を満たすCOLOURの小計)
(null) cuboid 1 SHAPE = cuboid(を満たすCOLOURの小計)
(null) pyramid 2 SHAPE = pyramid(を満たすCOLOURの小計)
red (null) 3 COLOUR = red(を満たすSHAPEの小計)
red cube 1 COLOUR = red & SHAPE = cube
red cuboid 1 COLOUR = red & SHAPE = cuboid
red pyramid 1 COLOUR = red & SHAPE = pyramid
blue (null) 2 COLOUR = blue(を満たすSHAPEの小計)
blue cube 1 COLOUR = blue & SHAPE = cube
blue pyramid 1 COLOUR = blue & SHAPE = pyramid
green (null) 1 COLOUR = green(を満たすSHAPEの小計)
green cube 1 COLOUR = green & SHAPE = cube

レコードの追加(insert)

レコードを追加する場合は、insert文を用いる。

以下の説明では、次のようなテーブルを用いる。

  • toysテーブル
COLUMN_NAME DATA_TYPE NULLABLE DATA_DEFAULT
TOY_ID NUMBER(38,0) Yes (null)
TOY_NAME VARCHAR2(100 BYTE) Yes (null)
COLOUR VARCHAR2(10 BYTE) Yes (null)
  • bricksテーブル
COLUMN_NAME DATA_TYPE NULLABLE DATA_DEFAULT
BRICK_ID NUMBER(38,0) Yes (null)
COLOUR VARCHAR2(10 BYTE) Yes (null)
SHAPE VARCHAR2(10 BYTE) Yes (null)

カラム名は省略可能だが、データを追加するカラム名を記述することで保守性を高める
※カラム名を省略する場合は、テーブルで定義された全てのカラムに対してデータを指定する必要がある

データを指定しないカラムに対してはnullが挿入される

レコードの追加
-- カラム名を省略(非推奨)した単一行の挿入
insert into toys values ( 1, 'Miss Snuggles', 'pink' );

-- カラム名を指定(推奨)した単一行の挿入
insert into toys ( toy_id, toy_name ) values ( 2, 'Baby Turtle' );

-- 複数行の挿入 ・・・ K
insert into bricks ( brick_id )
  select toy_id
  from   toys;

KのINSERT文では、まずtoysテーブルに対してクエリを実行し、その取得結果をbricksテーブルに追加している。

KのINSERT文を実行した後のbricksテーブル情報は、以下の通り。

BRICK_ID COLOUR SHAPE
1 (null) (null)
2 (null) (null)

単一行/複数行の挿入パフォーマンス比較

同じ数のレコードをテーブルに追加する場合、DML文の実行回数が少ない複数行の挿入の方が高速

bricksテーブルに50,000行を追加する以下のPL/SQLを実行する。

DBMS_OUTPUTパッケージの出力を表示する場合、以下のPL/SQLを実行する。

-- `DBMS_OUTPUT`パッケージの出力を表示
SET SERVEROUTPUT ON;
単一行と複数行のパフォーマンス比較(PL/SQL)
declare
  start_time   pls_integer;
  insert_count pls_integer := 50000;
begin
  start_time := dbms_utility.get_time ();
  
  for i in 1 .. insert_count loop
    insert into bricks 
    values ( i, 'red', 'cube' );
  end loop;
  
  dbms_output.put_line ( 'Single-row duration = ' || ( dbms_utility.get_time () - start_time) );
  
  rollback;
  
  start_time := dbms_utility.get_time ();
  
  insert into bricks
    select level, 'red', 'cube' from dual
    connect by level <= insert_count;
  
  dbms_output.put_line ( 'Multi-row duration = ' || ( dbms_utility.get_time () - start_time) );
  
  rollback;
end;
/
出力結果
Single-row duration = 34
Multi-row duration = 2

DML文による変更の保存(commit)

データ操作言語(Data Manipulation Language; DML)によるデータの変更(=トランザクション)を確定する場合はcommit文を実行する。

commit文が送信されるまで、更新後のデータについて他ユーザは参照不可であり、自ユーザのセッション内でのみ参照可能
※多くの開発ツールでオートコミット機能が利用可能

DML文による変更の保存
-- DML文の送信
insert into toys ( toy_id, toy_name, colour ) 
  values ( 6, 'Green Rabbit', 'green' );

-- トランザクションの確定(コミット)
commit;

DML文による変更の取消(rollback)

データ操作言語によるトランザクションを取り消す場合はrollback文を実行する。

rollbackは最後にcommit文が送信された状態までしかロールバックできないため、複数のDML文を実行する場合は全ての変更が完了するまでコミットしないのが望ましい

DML文による変更の取消
-- DML文の送信
insert into toys ( toy_id, toy_name, colour ) 
  values ( 7, 'Pink Rabbit', 'pink' );

-- トランザクションの取消(ロールバック)
rollback;

セーブポイントの作成(savepoint)

トランザクションの途中でロールバック可能なセーブポイントを作成する場合はsavepoint文を実行する。

セーブポイント ≠ コミット であるため、全てのセーブポイントでの変更をrollback文で取り消すことができる

sp1 → sp2 という流れでセーブポイントを作成しsp1までロールバックした場合、sp2へのロールバックはできない

セーブポイントの作成
-- DML文の送信
insert into toys ( toy_id, toy_name, colour ) 
  values ( 8, 'Pink Rabbit', 'pink' );

-- セーブポイントの作成
savepoint after_six;

-- DML文の送信
insert into toys ( toy_id, toy_name, colour ) 
  values ( 9, 'Purple Ninja', 'purple' );

-- セーブポイントへのロールバック
rollback to savepoint after_six;

-- 最後にコミットされた状態へのロールバック
rollback;

マルチテーブルインサート(insert all)

insert all文を用いることで、複数レコードを複数テーブルに対して挿入することができる。

dualテーブルはOracle Database上で事前定義されたテーブルであり、マルチテーブルインサートで必要なSELECT文のスタブとして、実テーブル上に存在しないデータ(= 値, 関数)を追加する場合に慣習的に用いられる

マルチテーブルインサート
-- マルチデーブルインサート
insert all
  into toys ( toy_id ) values ( id )
  into bricks ( brick_id ) values ( id )
  into bricks ( brick_id ) values ( id )
select 0 id from dual;

条件付きマルチテーブルインサート(all, first)

マルチテーブルインサートにおいて、条件式に基づいてレコードを追加するテーブルを指定する場合はwhen句を用いて条件式を定義する。

else節は他言語における条件文とは異なり、直前の条件式だけでなく他の条件全てに該当しない場合にtrueとして適用される

また、all・firstキーワードを用いて各条件式の評価方法を指定することができる。

キーワード 評価方法
all 全ての条件式を評価
first 最初に条件式がtrueとなった時点で、他の条件式は評価しない

以下の説明では、次のようなテーブルを用いる。

  • toysテーブル
TOY_ID TOY_NAME COLOUR
11 Cuteasaurus blue
12 Sir Stripypants blue
13 White Rabbit white
  • bricksテーブル(中身は空の状態)
BRICK_ID COLOUR SHAPE
条件付きマルチテーブルインサート
-- allキーワード ・・・ L
insert all
  when colour = 'blue' then  -- 条件式①
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  when toy_name = 'Cuteasaurus' then  -- 条件式②
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  else  -- 条件式③
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  select * from toys;

-- firstキーワード ・・・ M
insert first
  when colour = 'blue' then
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  when toy_name = 'Cuteasaurus' then
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  else
    into bricks ( brick_id, colour ) 
    values ( toy_id, colour )
  select * from toys;

LのINSERT文を実行した後のbricksテーブル情報は、以下の通り。

BRICK_ID COLOUR SHAPE
11 blue (null)
12 blue (null)
11 blue (null)
13 white (null)

上記の結果から、allキーワードを用いたLのINSERT文では、toysテーブルの各レコードが次のように評価されたことが分かる。

TOY_ID TOY_NAME COLOUR trueと評価された条件式
11 Cuteasaurus blue ①, ②
12 Sir Stripypants blue ①
13 White Rabbit white ③

一方で、MのINSERT文を実行した後のbricksテーブル情報は、以下の通り。

BRICK_ID COLOUR SHAPE
11 blue (null)
12 blue (null)
13 white (null)

上記の結果から、firstキーワードを用いたMのINSERT文では、toysテーブルの各レコードが次のように評価されたことが分かる。

TOY_ID TOY_NAME COLOUR trueと評価された条件式
11 Cuteasaurus blue ①
12 Sir Stripypants blue ①
13 White Rabbit white ③

データの更新(update)

レコードのデータを更新する場合は、update文を用いる。

データが更新されたレコードは、更新したユーザがコミットまたはロールバックを行うまでデータベースによってロックされる

where句でレコードを指定しない場合は、全レコードのデータが更新される

データの更新
-- データの更新
update bricks
set    quantity = quantity + 10
where  colour = 'blue'
and    shape = 'cube';

デッドロック

同じレコードを操作する複数のトランザクションが互いにロック解除待ち状態となり、処理が進まなくなることをデッドロック(deadlock) と呼ぶ。

以下のPL/SQLでは、自律型トランザクション(Autonomous Transaction) を用いてデッドロックを引き起こしている。

デッドロック(PL/SQL)
update bricks
set    quantity = 60;

declare
  pragma autonomous_transaction;
begin
  update bricks
  set    unit_weight = 55;
  commit;
end;
/
出力結果
エラー・レポート -
ORA-00060: リソース待機の間にデッドロックが検出されました。
ORA-06512: 行4
00060. 00000 -  "deadlock detected while waiting for resource"
*Cause:    Transactions deadlocked one another while waiting for resources.
*Action:   Look at the trace file to see the transactions and resources
           involved. Retry if necessary.

悲観的ロック(select for update)

デッドロックを未然に防ぐには、トランザクションの冒頭で予めselect .. for update文を用いて、更新対象のレコードをロックしておく

悲観的ロックを利用するには、アプリケーションがステートフル(stateful) である必要があるため、ステートレス(stateless) なアプリケーションと連携する場合は後述する楽観的ロックを用いる。
※ステートフルではデータの取得と更新が同じトランザクションで行われ、ステートレスではそれぞれ異なるトランザクションで行われる

for update
-- for updateキーワードによる悲観的ロック
select * from bricks
where  colour = 'red'
for    update;

-- DML文の送信
update bricks
set    quantity = 1001
where  colour = 'red';

-- トランザクションの確定(コミット)
commit;

ロストアップデート

同一レコードに対する変更タイミングが重なり、最終更新によって他の更新が上書きされてしまう現象をロストアップデート(lost update) と呼ぶ。

ロストアップデート
-- unit_weightの更新
update bricks
set    quantity = 60,
       unit_weight = 8
where  colour = 'red'
and    shape  = 'cylinder';

-- quantityの更新
-- → unit_weightが更新前の値で上書きされる
update bricks
set    quantity = 1001,
       unit_weight = 13
where  colour = 'red'
and    shape  = 'cylinder';

楽観的ロック

ロストアップデートへの対処法として、悲観的ロック(pessimistic locking) と楽観的ロック(optimistic locking) が挙げられる。

悲観的ロックと楽観的ロックの説明は、それぞれ以下の通りである。

手法 概要
悲観的ロック トランザクションの冒頭でレコードを事前にロック
楽観的ロック トランザクションの途中(=更新時) で他カラムの値の整合性を確認した上で値を更新

楽観的ロックにおける整合性確認の手法としては、次のような例が挙げられる。

  • バージョン番号や最終更新日時を表すカラムを追加
  • 全カラムの更新前の値をwhere句で指定し抽出

カラム数が多いテーブルの場合に後者の手法を用いるとwhere句が肥大化するため、バージョン番号や最終更新日時をレコードに追加して整合性を確認するのが望ましい

楽観的ロック
-- 全カラムの値で確認する場合
update bricks
set    quantity = 60,
       unit_weight = 8
where  colour = 'red'
and    shape  = 'cylinder'
and    quantity = 60
and    unit_weight = 13;

-- バージョン番号を表すカラムを追加(既定値: 1)
alter table bricks add ( version_number integer default 1 );

-- バージョン番号で確認する場合
update bricks
set    quantity = 1001,
       unit_weight = 13,
       version_number = version_number + 1
where  colour = 'red'
and    shape  = 'cylinder'
and    version_number = 1;

レコードの削除(delete, trancate)

レコードを削除する場合は、deleteまたはtrancate文を用いる。

deleteとtrancateの違いは、以下の通り。

文 区分 削除対象 テーブルへの表領域の割り当て 膨大な量のレコード削除
delete DML 一部または全てのレコード 解除しない 低速
trancate DDL 全てのレコード 解除する(※) 高速

delete文におけるfromキーワードは省略可能
表領域の割り当てを解除しないため、同程度のレコード再挿入が高速

trancate文はデータ定義言語(DDL) であるため、コミットが不要
既定では表領域の割り当てを解除するが、reuse storageキーワードを付与することで解除させないことも可能

レコードの削除
-- delete文を用いた全レコードの削除
delete from toys;

-- 一部レコードの削除
delete toys where price < 1;

-- trancate文を用いた全レコードの削除(表領域の割り当てを解除する)
truncate table toys;

-- trancate文を用いた全レコードの削除(表領域の割り当てを解除しない)
truncate table toys reuse storage;

物理削除/論理削除

「削除」には物理削除(hard delete) と論理削除(soft delete) の2種類が存在し、それぞれ以下のような違いをもつ。

手法 概要 膨大な量のレコード削除 システム全体のレスポンス
物理削除 テーブルからレコードを削除 レコードを削除
→ 低速 		データ量 少
→ 高速
論理削除 フラグで削除済かどうかを識別 フラグを更新
→ 高速 		データ量 多
→ 低速
物理削除/論理削除
-- 物理削除(delete文)
delete toys;

-- 物理削除(trancate文)
truncate table toys;

-- 削除フラグの追加
alter table toys add is_deleted varchar2(1) default 'N';

-- 論理削除(削除フラグを'N'→'Y'に更新) ・・・ N
update toys
set    is_deleted = 'Y'
where  toy_name = 'Cuteasaurus';

NのUPDATE文を実行した後のtoysテーブル情報は次の通りであり、以降このテーブル情報をもとに説明する。

TOY_NAME PRICE IS_DELETED
Baby Turtle 0.01 N
Miss Snuggles 0.51 N
Cuteasaurus 10.01 Y
Sir Stripypants 14.03 N
Purple Ninja 14.22 N

論理削除は不要レコードを高速に扱える一方で、where句にフラグの条件式を挿れる必要があるという点で少し複雑になる。

そこで、Oracle Databaseでは現存レコードの抽出を簡易化するために、以下のような仕組みが用意されている。

  • ビュー(View) ・・・ 一部のレコードを抽出して作成した仮想テーブル
  • 仮想プライベートデータベース(Virtual Private Database; VPD) ・・・ 閲覧可能なレコードをユーザ単位で制御する機能
  • データベース内アーカイブ(In-Database Archiving) ・・・ ora_archive_stateという不可視カラムが0(=現存)か0以外(=削除済)かで削除済レコードを識別する機能

ビューを用いた現存レコードの抽出

ビューを用いた現存レコードの抽出
-- ビューの作成
create or replace view active_toys as
  select * from toys
  where is_deleted = 'N';

VPDを用いた現存レコードの抽出

VPDポリシーが適用されたテーブルはカラム・レコード単位で保護されるため、テーブルに変更を加える際はVPDポリシーを削除する必要がある

VPDを用いた現存レコードの抽出
-- 以下、DBAロールが付与されたユーザで実行 --
-- ユーザ(visible_only)の作成
create user visible_only identified by visib;

-- データベースへの接続を許可
grant connect to visible_only;

-- 他ユーザのテーブルへの接続を許可
grant select on ORCLADM.toys to visible_only;

-- VPDファンクションの作成
create or replace function vpdfunc
  (schema_var varchar2, table_var varchar2)
  return varchar2
is
  cond varchar2(400);
  username varchar2(20);
begin
  select sys_context('USERENV', 'SESSION_USER') into username from dual;
  if (username = 'VISIBLE_ONLY') then
    cond := 'is_deleted = ''N''';
  else
    cond := 'is_deleted = ''Y''';
  end if;
  return cond;
end vpdfunc;
/

-- VPDファンクションをVPDポリシーとして適用
begin
  dbms_rls.add_policy(
    object_schema => 'ORCLADM',  -- VPDによって保護するスキーマ
    object_name => 'toys',  -- VPDによって保護するスキーマ内オブジェクト
    policy_name => 'VPDPOL',  -- VPDポリシー名
    function_schema => 'ORCLADM',  -- VPDポリシーを保持するスキーマ
    policy_function => 'vpdfunc');  -- VPDポリシーとして規定するVPDファンクション
end;
/

-- VPDポリシーの削除
begin
  dbms_rls.drop_policy(
    object_schema => 'ORCLADM',  -- VPDファンクションの第一引数
    object_name => 'toys',  -- VPDファンクションの第二引数
    policy_name => 'VPDPOL');  -- VPDファンクションが適用されたVPDポリシー
end;
/

-- 以下、作成したユーザ(visible_only)で実行 --
-- ORCLADM.toysテーブルの参照 ・・・ O
select * from ORCLADM.toys;

OのSELECT文による取得結果は、以下の通りとなる。

TOY_NAME PRICE IS_DELETED
Baby Turtle 0.01 N
Miss Snuggles 0.51 N
Sir Stripypants 14.03 N
Purple Ninja 14.22 N

データベース内アーカイブを用いた現存レコードの抽出

row archivalキーワードを用いてデータベース内アーカイブを有効化すると、システムによってora_archive_stateという不可視カラムがテーブルに追加される

データベース内アーカイブを用いた現存レコードの抽出
-- データベース内アーカイブの有効化(= カラムora_archive_stateの追加)
alter table toys row archival;

-- toy_name = 'Baby Turtle'のレコードを論理削除 ・・・ P
update toys 
set    ora_archive_state = '1'
where  toy_name = 'Baby Turtle';

-- セッション内で全レコードを可視化する ・・・ Q
alter session set row archival visibility = all;

-- セッション内でアーカイブ済レコードのみを可視化する ・・・ P
alter session set row archival visibility = active;

データベース内アーカイブが有効化され、アーカイブ済レコードのみが可視化されたPの状態におけるtoysテーブル情報は以下の通り。

  • toysテーブル(状態P)
TOY_NAME PRICE IS_DELETED
Miss Snuggles 0.51 N
Cuteasaurus 10.01 Y
Sir Stripypants 14.03 N
Purple Ninja 14.22 N

一方で、全レコードが可視化されたQの状態におけるtoysテーブル情報は以下の通り。

  • toysテーブル(状態P)
TOY_NAME PRICE IS_DELETED
Baby Turtle 0.01 N
Miss Snuggles 0.51 N
Cuteasaurus 10.01 Y
Sir Stripypants 14.03 N
Purple Ninja 14.22 N
5
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?