忍者ブログ
IT関係の小作人労働の日々の日記です。 最近データベースが好きです。 インフラ構築、DB構築、アプリケーション開発・・・何でも屋です。 何でもできそうで、何にもできない。

【データベース】実行計画の要！「オプティマイザー」の役割と2つのアプローチ

リレーショナルデータベース（RDB）に対してSQL文を発行する際、私たちは「どのようなデータを満たすか（WHAT）」だけを指定し、「どのようにデータを取得するか（HOW）」の手順は記述しない。
この「どのようにデータを取得するか」という具体的なアクセス経路（実行計画）を裏で自動的に導き出しているのが、DBMSの心臓部である「オプティマイザー（最適化エンジン）」である。

1. オプティマイザーとは

データベースアクセスを最適化する仕組み、およびそのシステムをオプティマイザーと呼ぶ。

オプティマイザーは、ユーザーから送られてきたSQL文を構文解析（パース）し、テーブルの構造やインデックスの有無を分析する。その後、内部で複数の実行計画（プラン）を組み立て、その中から最も効率が良いと判断したアクセス経路を検出して実行に移す役割を担っている。

2. オプティマイザーの2つの種類

SQL文の実行計画を選択・決定する手法には、歴史的な経緯やアプローチの違いにより、大きく分けて次の2種類が存在する。

（1）ルールベース・オプティマイザー（RBO）

ルールベースのオプティマイザーは、データの量や状態に関わらず、あらかじめシステムに組み込まれた固定の優先順位（ルール）に則って、機械的に実行計画を決定する。

一般的には、以下のようなアクセス・パス（経路）の優先順位が定義されており、より上位の条件にマッチするインデックス等があれば、それを優先的に選択する。

  • （ア）ユニーク・キーまたは主キーによる単一行アクセス（最優先）
  • （イ）インデックス付きのクラスター・キー
  • （ウ）複合インデックス（複数カラムを組み合わせたインデックス）
  • （エ）単一カラム・インデックス
  • （オ）ソート／マージ結合
  • （カ）インデックス付きカラムの MAX または MIN 関数
  • （キ）インデックス付きカラムの ORDER BY
  • （ク）テーブル全体のスキャン（フルスキャン）（最も優先順位が低い）

（2）コストベース・オプティマイザー（CBO）

現在の主要なDBMS（Oracle、PostgreSQL、MySQL、SQL Serverなど）で主流となっているのが、このコストベースである。
あらかじめデータベース内に収集・蓄積されている、テーブルのレコード数、カラムの平均長、インデックスの深さ、データの分布度といった「統計情報」を基にする。

オプティマイザーは内部で複数の実行計画をシミュレーションし、それぞれのパターンで発生する「コスト」を計算して、最もコストが低いプランを採用する。

※ここでいう「コスト」とは、SQL文を実行するために必要と予測される、CPU処理時間やディスクI/O（読み書き）の経過時間に比例する見積値のことである。データ量が少なければフルスキャンを選び、データ量が多ければインデックスを選ぶ、といった柔軟な判断が可能となる。

3. まとめ

ルールベースは「構造」だけで判断するためデータ量の変化に弱く、現代の複雑なシステムでは不都合が生じやすい。そのため、現在のRDB運用においては、定期的に ANALYZE などのコマンドを実行して最新の「統計情報」を維持し、コストベース・オプティマイザーに正しい判断をさせることが、パフォーマンス管理における最大の鉄則となっている。

【オプティマイザー運用のチェック項目】
□ 意図しないフルスキャン（テーブル全体スキャン）が発生していないか？
□ 大規模なデータ更新（バッチ処理等）のあとに統計情報を更新しているか？
□ 期待通りのインデックスアクセスが選択されるよう、実行計画（EXPLAIN）を確認したか？
PR

【データベースの知識】データベース運用で知っておくべき「更新可能なView」の8つの必須条件

SQLにおいて、複雑なクエリをカプセル化して擬似的なテーブルのように扱える「ビュー（View）」。
ビューは通常、データの参照（SELECT）に用いられるが、特定の条件をクリアしていれば、ビューに対して直接 INSERT、UPDATE、DELETE などの更新処理（DML）を行うことができる。

今回は、データベース設計や情報処理試験でも極めて重要となる、標準SQLにおける「更新可能なビュー」が満たすべき8つの厳格な条件を整理した。

1. 更新可能なビュー（Updatable View）の8大条件

DBMSがビューの向こう側にある実テーブル（基底テーブル）に対して、どのレコードのどの列を書き換えるべきかを一意に特定（マッピング）できる必要があるため、以下の条件が定められている。

  • ① 1つだけのテーブルから作られていること
    複数のテーブルを結合（JOIN）して作成されたビューは、原則として標準SQLでは直接更新できない。対象となる基底テーブルが単一である必要がある。
  • ② GROUP BY 句を使っていないこと
    データをグループ化してしまうと、ビュー上の1行が基底テーブルの複数行をまとめたものになってしまうため、どの行を更新すべきか特定できなくなる。
  • ③ HAVING 句を使っていないこと
    GROUP BY と同様に、グループ化された集計結果に対する絞り込みを行う句が含まれているビューは更新対象外となる。
  • ④ 集約関数（SUM, AVG, COUNT, MAX, MIN など）を使っていないこと
    計算・集計された結果の値を書き換えようとしても、基底テーブルの元のデータをどう変更すればよいか計算が成り立たないためである。
  • ⑤ 計算列を使っていないこと
    SELECT col1 + col2 AS total のような演算式によって算出された列（計算列）は、直接値を書き換えることができない。
  • ⑥ UNION、INTERSECT、EXCEPT を使っていないこと
    集合演算子を用いて複数のクエリ結果を統合・比較しているビューは、データの出所を一意にマッピングできないため更新できない。
  • ⑦ SELECT DISTINCT 句を使っていないこと
    重複行を排除（一意化）して表示されたビューは、実テーブルのどの行に対応しているかの追跡性が失われるため、更新が許可されない。
  • ⑧ ビューに含まれない基底テーブルのすべての列が、NULLを許可するか、デフォルト値が指定されていること
    ビュー経由で INSERT（行追加）を行う際、ビューに定義されていない列にはデータが渡らない。そのため、それらの列が NOT NULL（かつデフォルト値なし）の制約を持っていると、実テーブル側で制約エラーが発生して挿入できなくなる。

2. まとめ：実務でのアプローチ

上記のように、標準SQLでビューを更新可能にするためのハードルはかなり高い。実テーブルとビューの列が「1対1」できれいに対応しているシンプルなビューだけが、そのまま更新できる仕様になっている。

もし、これらの条件を満たさない複雑なビュー（複数テーブルの結合ビューなど）に対してどうしても更新処理を行いたい場合は、PostgreSQL等の主要なDBMSでサポートされているINSTEAD OF トリガーをビューに実装し、内部の更新ロジックを開発者が手動で記述するアプローチをとるのが一般的である。

【ビュー設計時のチェック項目】
□ 参照専用（読み取り専用）として利用するビューか？
□ アプリケーションからビューを介した DML 操作を想定しているか？
□ 条件を満たさない場合、トリガーによる代替ロジックの実装が必要か？

【PostgreSQL】Ubuntu + PostgreSQL スクリプト実行ガイド 〜 .sql ファイルの作成から psql での実行・検証まで 〜

本ガイドでは、Ubuntu環境において PostgreSQL のスクリプトファイル（.sql）を作成し、ターミナルから psql コマンドを使って実行する手順を解説する。

対話型シェルにログインして実行する基本方法から、シェルスクリプトなどの自動化（バッチ処理）に組み込む際の本質的な外部実行プロセスまで、ステップバイステップで検証を進めていく。

1. 開発環境の準備（ターミナルの操作）

まずはUbuntu環境に PostgreSQL が導入されているか確認する。未インストールの場合は、パッケージ管理システムの apt を用いて以下の通り導入を行う。

■ インストールの確認と実行

# パッケージインデックスの更新とインストール
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# データベースサービスの起動ステータス確認
sudo systemctl status postgresql

2. スクリプトファイルの作成

検証用の作業ディレクトリを作成し、テストとして「Hello World」の文字列を出力するだけのシンプルなSQLファイルを用意する。

■ 作業フォルダの作成と移動

mkdir ~/psql-study && cd ~/psql-study

■ テスト用SQLファイルの作成（コマンドラインより生成）

echo "SELECT 'Hello World' AS message;" > hello.sql

3. PostgreSQLへのログイン

UbuntuにおけるPostgreSQLの初期設定では、データベースの最高管理権限は postgres という名前のOSユーザーに紐づけられている。
そのため、以下のようにOSの管理者権限を介して psql シェルへログインする。

■ 管理者権限（スーパーユーザー）でのログイン

sudo -u postgres psql

※ sudo -u postgres を付与することで、ログインパスワードの入力を求めることなく、安全にPostgreSQLの内部プロンプトへ入ることができる。

4. ファイルの実行（対話モードでの実践）

psql の対話型プロンプト内で、先ほど作成した外部の .sql ファイルを読み込んで実行させる。

■ スクリプトの実行メタコマンド

プロンプト postgres=# が表示されている状態で、ファイルを指定して以下のインクルードコマンドを入力する。

postgres=# \i hello.sql

■ 期待される実行出力

   message    
-------------
 Hello World
(1 row)

無事に結果が得られたら、以下のメタコマンドで対話型シェルを終了（離脱）する。

postgres=# \q

5. 本質的な「検証」：OSコマンドラインからの非対話実行

実際のシステム運用や、シェルスクリプト（Cronジョブなど）による自動バッチ処理にSQLを組み込む際、わざわざ対話モードに手動ログインするのは現実的ではない。
そこで、プロンプトに入らず、Ubuntuのコマンドラインから直接SQLファイルを流し込んで結果を得る方法を検証する。

ターミナル上で、ディレクトリが ~/psql-study にあることを確認し、以下のコマンドを実行する。

sudo -u postgres psql -f hello.sql

■ 技術解説：-f オプションの役割

-f（file）オプションを使用することで、指定したファイルの中身を直接PostgreSQLのエンジンにパイプラインで流し込み、実行結果をそのまま標準出力（stdout）としてターミナルへ戻すことができる。

この非対話コマンドが正常に通れば、「SQLファイルの外部実行プロセス」がUbuntu環境において正しくセットアップできている証明となる。これをベースに、シェルスクリプトでのデータ抽出や定期バックアップタスク等への応用が可能だ。


【データベースの知識】データベース運用の基本「3つのバックアップ方式」とその選び方

データベース（DB）の運用において、万が一のシステム障害やデータ破損、誤操作からデータを守るバックアップ設計は生命線である。
データベースのバックアップは、データの採取範囲や運用のコスト（容量や処理時間）に応じて主に3つの方式に分類される。今回はそれぞれの仕組みとメリット・デメリット、そして選定基準を分かりやすく解説する。

1. フルバックアップ（完全バックアップ）

対象となるデータベース内のすべてのデータを丸ごと丸ごとバックアップする方式。

  • 仕組み： 基準となる時点のデータを1つのまとまったファイルとして完全に複製する。
  • メリット： 復旧（リストア）作業が極めてシンプルである。万が一の際は、このフルバックアップデータを1回書き戻すだけで、その時点の状態を完全に再現できる。
  • デメリット： データ量に比例して、バックアップの実行時間が最も長くかかり、保管に必要なストレージ容量も最大となる。

2. 差分バックアップ（ディファレンシャル）

「最後のフルバックアップ」から「現在」までの間に変更・追加されたデータのみを抽出してバックアップする方式。

  • 仕組み： 起点は常に「直近のフルバックアップ」となる。そのため、フルバックアップからの日数が経つにつれて、日毎に差分データのサイズは徐々に肥大化していく。
  • メリット： 復旧（リストア）の際は、「直近のフルバックアップ」と「最新の差分バックアップ」の計2点があれば良いため、比較的スピーディーに復旧が完了する。
  • デメリット： フルバックアップから次のフルバックアップまでの期間が長いと、後半の差分バックアップのサイズが大きくなり、日々のバックアップ時間が長くなってしまう。

3. 増分バックアップ（インクリメンタル）

フルか差分かを問わず、「前回のバックアップ（直近の何らかのバックアップ）」から「現在」までの間に変更・追加されたデータのみを抽出する方式。

  • 仕組み： 常に「一回前のバックアップ時点」からの変化量だけを追いかける。日々のデータ量は最小限に抑えられる。
  • メリット： 日常的なバックアップ実行時間が最も短く、消費するストレージ容量も最小限に抑えることができる。高頻度（1時間毎など）でバックアップを取りたい場合に最適。
  • デメリット： 復旧（リストア）のハードルが上がる。復旧には「直近のフルバックアップ」に加え、それ以降に取得したすべての増分バックアップファイルを順番通りにすべて適用する必要がある。もし途中の1世代でもファイルが破損していると、それ以降のデータに復旧できなくなるリスクがある。

4. まとめ：各方式の比較表と選定戦略

運用の現場では、どれか一つの方式だけを採用するのではなく、一般的に「週末にフルバックアップを取得し、平日に差分（または増分）を取得する」といった組み合わせによる戦略（世代管理）がとられる。

【3大バックアップ方式のクイック比較】
方式日々の実行時間消費容量リストアの容易さ
フル 長い 大 最高（1ファイルで完了）
差分 中 中 良好（フル ＋ 最新の差分）
増分 最短 極小 複雑（フル ＋ 全増分が必要）

システムの許容できる停止時間（RTO）や、データ復旧の目標地点（RPO）、利用可能なディスク予算のバランスを考慮し、最適なバックアップ計画を組み立ててほしい。


【Oracle Cloud Free & DB】第7回：初期状態の「ユーザー」を暴く！PaaSの中に並ぶアカウントの正体


前回まで、Autonomous Database（26ai）の論理的な表領域の特性や、裏側に隠されたマルチテナント（PDB）固有の物理ファイルパスを確認してきました。ストレージ構造の次は、データベースの『セキュリティ』に切り込んでみましょう。
クラウドでインスタンスを作成した際、最初からどのようなユーザー（スキーマ）が定義されており、どんな状態で管理されているのか、データ・ディクショナリから実態を調査します。

1. 初期ユーザーの定義状態とアカウントステータス

まずは、データベース作成直後の初期状態で、どのようなユーザーが存在しているのかを一覧で確認します。Oracle全体のユーザー情報を一元管理している DBA_USERS ビューから、主要なユーザーとそのアカウント状態（STATUS）を抽出してみましょう。なお、このクエリはすべて切り出された私たちの個室（PDB）側の環境を覗いています。

★ 初期ユーザーの状態を確認するSQL：
SELECT username, account_status, default_tablespace, profile
FROM dba_users
ORDER BY username;

【実行結果（主要なユーザーの抜粋）】
"USERNAME","ACCOUNT_STATUS","DEFAULT_TABLESPACE","PROFILE"
"ADMIN","OPEN","DATA","BM_PROFILE"
"SYS","OPEN","SYSTEM","DEFAULT"
"SYSTEM","OPEN","SYSTEM","DEFAULT"
"AUDSYS","LOCKED","SYSAUX","DEFAULT"
"APPQOSSYS","LOCKED","SYSAUX","DEFAULT"
"DBSNMP","LOCKED","SYSAUX","MONITORING_PROFILE"
"OJVMSYS","LOCKED","SYSTEM","DEFAULT"
"XDB","LOCKED","SYSAUX","DEFAULT"

【結果解説：ACCOUNT_STATUS（アカウント状態）の意味】
ズラリと並んだ実行結果のステータスには、Oracleのセキュリティ設計の基本がそのまま現れています。

  • OPEN（オープン）：現在、正常にログインして操作ができる「生きている」ユーザーです。私たちが使う ADMIN や、裏で常に稼働している主要なコアシステム（SYS や SYSTEM）など、ごく僅かなアカウントだけがこの状態を許されています。
  • LOCKED（ロック）：アカウントが凍結されている状態です。これらはOracle内部の特定の機能（監査や監視など）を動かすためだけに用意された専用ユーザーであり、悪意ある第三者が乗っ取って外部から不正ログインできないよう、安全のために最初からガチガチに鍵がかけされています。
  • EXPIRED & LOCKED（期限切れ＆ロック）：パスワードの有効期限が切れ、さらにアカウントもロックされている二重ロック状態です（上記抜粋外の多くのシステムユーザーが該当します）。現在は使われていない、完全に眠っている内部機能用のスキーマです。

【マルチテナントの仕様：なぜPDBにSYSやSYSTEMがいるの？】
「全体の親玉（CDB）ではなく個室（PDB）を見ているのに、なぜ最高権限のSYSやSYSTEMが一覧に並んでいるの？」と疑問に思うかもしれません。
これはマルチテナントの仕様によるものです。新しく個室（PDB）が作成された瞬間、その個室単体を管理・独立稼働させるためのシステムユーザー一式が、各PDBの内部にも自動的に複製されて配置される仕組みになっています。つまり、ここに並んでいる `SYS` や `SYSTEM` は、全体の親玉ではなく「この個室専用に配属された管理ユーザー」なのです。PaaSの自律運用の邪魔をしないよう、安全に制御されています。

2. まとめ

実機のデータ・ディクショナリからユーザー構造を紐解くことで、PaaS環境であってもOracle Databaseとしての堅牢なシステム構成がそのまま引き継がれていることが確認できました。

★ 本日のチェックリスト：
1. 私たちが操作する「ADMIN」は個室（PDB）内のローカルユーザーとして、正常にOPENされている。
2. 内部のシステムユーザーは、セキュリティの定石通り基本「LOCKED」状態で保護されている。
3. PDB内に見えているSYSやSYSTEMは、個室が作成された際に自動複製された「個室専用の管理アカウント」。


ストレージ（論理・物理）に続き、ユーザーの初期状態まで確認できました。完全管理型（PaaS）データベースの内部検証シリーズはここで一区切りとなります。
次回第8回は、この「枠からはみ出せない」PaaSの安心安全な世界から飛び出し、「もう一つの無料枠（Compute VM）」を立ち上げて、Linuxのroot権限、そしてデータベース全体の親玉であるCDB$ROOTまですべてを自分の完全な支配下に置く『IaaS型・23ai Express Edition 自作構築編』へ突入します。コントロールを100%自分で握るインフラ構築の世界へ。どうぞお楽しみに！