日本語PDF
  1. データベース概要
  2. Oracleデータ・アクセス
  3. SQL

7 SQL

この章では、Structured Query Language (SQL)の概要およびOracle DatabaseによるSQL文の処理方法について説明します。

この章の内容は次のとおりです。

SQLの概要

SQL(「シーキューエル」と読みます)は、すべてのプログラムおよびユーザーがOracleデータベースのデータへアクセスする際に使用する、セットベースの高度な宣言型コンピュータ言語です。

いくつかのOracleツールおよびアプリケーションでは、SQLの使用はマスクされていますが、すべてのデータベース・タスクはSQLを使用して実行されます。その他のデータ・アクセス方法を使用すると、Oracle Databaseに組み込まれているセキュリティが活用されず、データのセキュリティと整合性が損われる可能性があります。

SQLはOracle Databaseなどのリレーショナル・データベースに対するインタフェースを提供します。SQLでは、次のようなタスクを1つの一貫した言語に統合します。

  • オブジェクトの作成、置換、変更および削除

  • 表の行の挿入、更新および削除

  • データの問合せ

  • データベースおよびそのオブジェクトへのアクセスの制御

  • データベースの整合性と一貫性の保証

SQLは対話的に使用できます(つまり、文を手動でプログラムに入力できます)。SQL文はCまたはJavaなどの異なる言語で記述されたプログラム内に埋め込むこともできます。

関連項目:

SQLデータ・アクセス

コンピュータ言語には大きく分けて2つの種類があり、1つは実行目的を表す非手続き型の宣言型言語、もう1つは処理の実行経緯を表すC++およびJavaなどの手続き型言語です。

SQLは、ユーザーが結果の導出方法ではなく目的とする結果を指定するという意味において、宣言型です。たとえば、次の文を使用すると、姓がKで始まる従業員のレコードを問い合せることができます。

データベースでは、データをナビゲートし、リクエストされた結果を取得するプロシージャを生成する作業を実行します。SQLの宣言型の性質により、論理レベルでデータを操作できます。実装の詳細は、データを操作する場合にのみ、考慮する必要があります。 

SELECT   last_name, first_name
FROM     hr.employees
WHERE    last_name LIKE 'K%'
ORDER BY last_name, first_name;

データベースはWHERE条件(述語とも呼ばれる)を満たすすべての行を1つのステップで取得します。データベースは、ユーザー、別のSQL文またはアプリケーションに、これらの行をまとめて渡すことができます。アプリケーションで行を1つずつ処理する必要はなく、また、開発者が行の物理的な格納方法または取得方法を認識する必要もありません。

SQL文を実行すると、データベースのオプティマイザが働き、指定したデータに最も速くアクセスする方法が決定されます。Oracle Databaseでは、オプティマイザにジョブを適切に実行させるための技術もサポートされています。

関連項目:

SQL文およびSQLのその他の部分(演算子、関数、書式モデルなど)の詳細は、Oracle Database SQL言語リファレンスを参照してください

SQL規格

オラクル社は業界で承認されている標準への準拠に努め、SQLの規格協会に積極的に参加しています。

業界が認可している協会として、米国規格協会(ANSI)および国際標準化機構(ISO)があります。ANSIおよびISO/IECはいずれも、リレーショナル・データベースの標準言語としてSQLを認可しています。

SQL標準は10のパートで構成されます。1つのパート(SQL/RPR:2012)は2012年の新しいものです。5つのパートは2011年に改訂されました。他の4つのパートは2008年バージョンで、元のままです。

Oracle SQLには、ANSI/ISO標準SQL言語に対応する多くの拡張機能が組み込まれており、Oracle Databaseのツール製品とアプリケーションを使用することで、文を追加できます。SQL*Plus、SQL DeveloperおよびOracle Enterprise Managerなどのツールを使用すると、Oracle Databaseに対してANSI/ISO標準のSQL文を実行でき、さらにこれらのツール製品で使用可能な追加の文や機能を実行できます。

関連項目:

SQL文の概要

Oracle Database内の情報に対するすべての操作は、SQL文を使用して実行します。SQL文は識別子、パラメータ、変数、名前、データ型およびSQL予約語で構成されるコンピュータ・プログラムまたは命令です。

ノート:

SQL予約語は、SQLで特別な意味を持ち、他の目的には使用できません。たとえば、SELECTとUPDATEは予約語のため、表名には使用できません。

SQL文は、完全なSQL文と等価であることが必要です。次に例を示します。

SELECT last_name, department_id FROM employees

Oracle Databaseでは、完全なSQL文のみが実行されます。次のような不完全文を実行しようとすると、テキストの不足を示すエラーが発生します。 

SELECT last_name;

Oracle SQL文は、次のカテゴリに分類されます。

データ定義言語(DDL)文

データ定義言語(DDL)文は、スキーマ・オブジェクトに対し、定義、構造の変更および削除を実行します。

DDLを使用すると、オブジェクトにアクセスするアプリケーションを変更することなく、オブジェクトの属性を変更できます。たとえば、アプリケーションをリライトせずに、人事管理アプリケーションからアクセスする表に列を追加できます。また、DDLを使用して、データベース・ユーザーがデータベース内で作業を実行している間に、オブジェクトの構造を変更することもできます。

具体的には、DDL文によって次のことが可能になります。

  • スキーマ・オブジェクトと他のデータベース構造(データベースとデータベース・ユーザーを含む)を作成、変更および削除します。ほとんどのDDL文はCREATE、ALTERまたはDROPのキーワードで始まります。

  • スキーマ・オブジェクトの構造を削除することなく、それらのオブジェクトの中のすべてのデータを削除します(TRUNCATE)。

    ノート:

    DELETEとは異なり、TRUNCATEはUNDOデータを生成しないため、DELETEよりも処理が速くなります。また、TRUNCATEは削除のトリガーを起動しません。

  • 権限とロールを付与および取り消します(GRANT、REVOKE)。

  • 監査オプションをオンまたはオフに切り換えます(AUDIT、NOAUDIT)。

  • データ・ディクショナリにコメントを追加します(COMMENT)。

例7-1 DDL文

次の例では、DDL文を使用してplants表を作成した後、DMLを使用して表に2つの行を挿入します。次に、DDLを使用して表構造を変更し、この表に対するユーザーの読取り権限を付与および取り消してから、表を削除します。 

CREATE TABLE plants
    ( plant_id    NUMBER PRIMARY KEY, 
      common_name VARCHAR2(15) );

INSERT INTO plants VALUES (1, 'African Violet'); # DML statement

INSERT INTO plants VALUES (2, 'Amaryllis'); # DML statement

ALTER TABLE plants ADD 
    ( latin_name VARCHAR2(40) );

GRANT READ ON plants TO scott;

REVOKE READ ON plants FROM scott;

DROP TABLE plants;

データベースがDDL文を実行する直前に暗黙的なCOMMITが行われ、直後にCOMMITまたはROLLBACKが行われます。次の例では、2つのINSERT文の後にALTER TABLE文が続くため、データベースによって2つのINSERT文がコミットされます。ALTER TABLE文が成功した場合は、この文はデータベースによってコミットされ、失敗した場合は、この文はデータベースによってロールバックされます。いずれの場合も、2つのINSERT文はすでにコミットされています。

関連項目:

データ操作言語(DML)文

データ操作言語(DML)文は、既存のスキーマ・オブジェクト内のデータの問合せや操作を実行します。

DDL文ではデータベースの構造を変更するのに対して、DML文では内容の問合せまたは変更を行います。たとえば、ALTER TABLEは表の構造を変更するのに対して、INSERTは1つ以上の行を表に追加します。

DML文は、最も頻繁に使用するSQL文であり、次の操作を実行できます。

  • 1つ以上の表またはビューからデータの取出しまたはフェッチを行います(SELECT)。

  • 列値のリストを指定するか、既存のデータを選択および操作する副問合せを使用して、表またはビューに新しいデータ行を追加します(INSERT)。

  • 表またはビューの既存の行の列値を変更します(UPDATE)。

  • 行の更新または、条件付きで表またはビューに行の挿入を行います(MERGE)。

  • 表またはビューから行を削除します(DELETE)。

  • SQL文の実行計画を表示します(EXPLAIN PLAN)。

  • 表またはビューをロックして、一時的に他のユーザーのアクセスを制限します(LOCK TABLE)。

次の例では、DMLを使用してemployees表に問い合せます。例では、DMLを使用してemployeesに行を挿入し、この行を更新した後、行を削除します。 

SELECT * FROM employees;

INSERT INTO employees (employee_id, last_name, email, job_id, hire_date, salary)
  VALUES (1234, 'Mascis', 'JMASCIS', 'IT_PROG', '14-FEB-2008', 9000);

UPDATE employees SET salary=9100 WHERE employee_id=1234;

DELETE FROM employees WHERE employee_id=1234;

論理作業単位を形成するDML文のコレクションは、トランザクションと呼ばれます。たとえば、預金を振り替えるトランザクションは、普通預金口座の残高の減額、当座預金口座の残高の増額および預金口座履歴表への振替えの記録という3つの別々の操作を伴います。DDL文とは異なり、DML文は現行のトランザクションを暗黙的にコミットしません。

関連項目:

SELECT文

問合せは、表またはビューからデータを取得する操作です。

SELECTはデータの問合せに使用できる唯一のSQL文です。SELECT文の実行から取得した一連のデータは結果セットと呼ばれます。

次の表に、SELECT文で一般的に使用される2つの必須キーワードおよび2つのキーワードを示します。また、表では、キーワードとSELECT文の機能を関連付けています。

表7-1 SQL文のキーワード

キーワード 必須 説明 機能

SELECT

はい

結果に表示される列を指定します。投影により、表内の列のサブセットが作成されます。

式は1つ以上の値、演算子、および値を評価するSQL機能の組合せです。SELECTキーワードの後かつFROM句の前に置かれる式のリストはSELECT構文のリストと呼ばれます。

投影

FROM

はい

データの取得元の表またはビューを指定します。

結合

WHERE

いいえ

条件を指定して行をフィルタ処理し、表内の行のサブセットを作成します。条件は、1つ以上の式および論理(ブール)演算子の組合せを指定し、TRUE、FALSEまたはUNKNOWNの値を戻します。

選択

ORDER BY

いいえ

行の表示順序を指定します。

  

関連項目:

SELECTの構文およびセマンティクスについては、『Oracle Database SQL言語リファレンス』を参照してください。

結合

結合は2つ以上の表、ビューまたはマテリアライズド・ビューからの行を組み合せる問合せです。

次の例は、employees表およびdepartments表を結合して(FROM句)、指定した条件に一致する行のみを選択し(WHERE句)、投影を使用して2つの列からデータを取得します(SELECT)。SQL文の後にサンプルの出力を示しています。 

SELECT email, department_name
FROM   employees 
JOIN   departments
ON     employees.department_id = departments.department_id
WHERE  employee_id IN (100,103)
ORDER BY email;

EMAIL                     DEPARTMENT_NAME
------------------------- ------------------------------
AHUNOLD                   IT
SKING                     Executive

次の図に、前述の問合せの結合で示した投影および選択の操作を表します。

図7-1 投影と選択

図7-1の説明が続きます
「図7-1 投影と選択」の説明

ほとんどの結合には、FROM句またはWHERE句のいずれかに1つ以上の結合条件があり、それぞれ異なる表の2つの列をこの条件によって比較します。データベースでは1対の行を組み合せ、それぞれの対には、結合条件がTRUEになる各表の行を1行ずつ含みます。オプティマイザは結合条件、索引、および表に使用できる任意の統計に基づいて、データベースが表を結合する順序を決定します。

結合タイプは次のとおりです。

  • 内部結合

    内部結合は、結合条件を満たす行のみを戻す2つ以上の表の結合です。たとえば、結合条件がemployees.department_id=departments.department_idの場合、この条件を満たさない行は戻されません。

  • 外部結合

    外部結合は結合条件を満たすすべての行を戻すとともに、一方の表からは、他方の表の条件を満たさない行に対応する行も戻します。

    結合条件が右側の表Bのレコードに一致していない場合でも、表AとBの左側外部結合の結果には常に、左側の表Aのすべてのレコードが含まれます。Bに一致していない行が存在する場合、Bの列では、Bで一致していない行にはNULLが含まれます。たとえば、一部の従業員が部署に属していない場合、employees (左側の表)とdepartments (右側の表)の左側外部結合では、departmentsの行が結合条件を満たしていない(employees.department_idがNULLである)ときも、employeesのすべての行が取得されます。

    結合条件が左側の表Aの行に一致していない場合でも、表AとBの右側外部結合の結果には右側の表Bのすべてのレコードが含まれます。Aに一致していない行が存在する場合、Aの列では、Aで一致していない行にはNULLが含まれます。たとえば、一部の部署に従業員がいない場合、employees (左側の表)とdepartments (右側の表)の右側外部結合では、employeesの行が結合条件を満たしていないときも、departmentsのすべての行が取得されます。

    完全外部結合は、左側外部結合と右側外部結合の組合せです。

  • デカルト積

    結合問合せ内の2つの表に結合条件がない場合、データベースはデカルト結合を実行します。一方の表の各行を他方の表の各行と組み合せます。たとえば、employeesに107行ありdepartmentsには27行ある場合、デカルト積には107*27行が含まれます。デカルト積が便利な場合はほとんどありません。

関連項目:

副問合せ

副問合せは別のSQL文内にネストされたSELECT文です。副問合せは、1つの問題を解決するために複数の問合せを実行する必要がある場合に便利です。

各文の問合せ部分は問合せブロックと呼ばれます。次の問合せでは、カッコ内の副問合せが内部問合せブロックです。 

SELECT first_name, last_name 
FROM   employees
WHERE  department_id 
IN     ( SELECT department_id 
         FROM departments 
         WHERE location_id = 1800 );

内部SELECT文により、場所IDが1800の部門のIDが取得されます。これらの部門IDは、副問合せによってIDが戻された部門の従業員名を取得する外部問合せブロックに必要です。

このSQL文の構造では、内部問合せを最初に実行することをOracle Databaseに強制しているわけではありません。たとえば、Oracle Databaseにより問合せ全体がemployeesとdepartmentsの結合としてリライトされることもあり、その場合は副問合せ自体が実行されません。また、別の例として、Virtual Private Database (VPD)機能では、WHERE句を使用して従業員の問合せが絞り込まれることがあり、この場合は、Oracle Databaseでは、最初に従業員を問い合せてから部門IDが取得されます。オプティマイザにより、要求された行を取得するためのステップの最適な順序が決定されます。

関連項目:

「Virtual Private Database (VPD)」

トランザクション制御文

トランザクション制御文は、DML文による変更の内容を管理し、一連のDML文をトランザクションとしてグループ化します。

これらの文によって、次のことを実行できます。

  • トランザクションの変更を確定します(COMMIT)。

  • トランザクションの開始以降(ROLLBACK)またはセーブポイント以降(ROLLBACK TO SAVEPOINT)に実行されたトランザクション内での変更を取り消します。セーブポイントは、トランザクションのコンテキスト内のユーザー宣言による中間マーカーです。

    ノート:

    ROLLBACK文によりトランザクションは終了しますが、ROLLBACK TO SAVEPOINTでは終了しません。

  • ロールバックできるポイントを設定します(SAVEPOINT)。

  • トランザクションのプロパティを設定します(SET TRANSACTION)。

  • 後に続く各DML文の遅延可能整合性制約をチェックするかどうか、またはトランザクションをコミットするタイミングを指定します(SET CONSTRAINT)。

次の例はUpdate salariesという名前のトランザクションを開始します。例ではセーブポイントを作成し、従業員の給与を更新した後、セーブポイントまでトランザクションをロールバックします。さらに、給与を別の値に更新してコミットします。 

SET TRANSACTION NAME 'Update salaries';

SAVEPOINT before_salary_update;

UPDATE employees SET salary=9100 WHERE employee_id=1234 # DML

ROLLBACK TO SAVEPOINT before_salary_update;

UPDATE employees SET salary=9200 WHERE employee_id=1234 # DML

COMMIT COMMENT 'Updated salaries';

関連項目:

セッション制御文

セッション制御文は、ユーザー・セッションのプロパティを動的に管理します。

セッションは、データベースに対する現在のユーザー・ログインの状況を示す、データベース・インスタンス・メモリー内の論理エンティティです。セッションは、ユーザーがデータベースに認証された時点から、ユーザーが接続を切断するか、データベース・アプリケーションを終了する時点まで続きます。

セッション制御文によって、次のことを実行できます。

  • デフォルトの日付形式の設定など、特化された機能を実行して現行のセッションを変更します(ALTER SESSION)。

  • 現行のセッションのロール(権限のグループ)を使用可能または使用禁止にします(SET ROLE)。

次の文は、セッションのデフォルトの日付形式を動的に'YYYY MM DD-HH24:MI:SS'に変更します。 

ALTER SESSION 
   SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

セッション制御文は現行のトランザクションを暗黙的にコミットしません。

関連項目:

システム制御文

システム制御文では、データベース・インスタンスのプロパティを変更します。

システム制御文は、ALTER SYSTEMのみです。この文は、設定値(共有サーバーの最小数など)の変更、セッションの終了およびその他のシステム・レベルでのタスクの実行に使用します。

システム制御文の例を次に示します。

ALTER SYSTEM SWITCH LOGFILE; 

ALTER SYSTEM KILL SESSION '39, 23';

ALTER SYSTEM文は現行のトランザクションを暗黙的にコミットしません。

関連項目:

ALTER SYSTEMの構文およびセマンティクスは、『Oracle Database SQL言語リファレンス』を参照してください。

埋込みSQL文

埋込みSQL文は、手続き型言語プログラム内にDDL、DMLおよびトランザクション制御文を取り込みます。

埋込み文は、Oracleプリコンパイラで使用されます。埋込みSQLは、手続き型言語アプリケーションにSQLを取り込むための1つの方法です。他に、Open Database Connectivity(ODBC)またはJava Database Connectivity(JDBC)などの手続き型APIを使用する方法もあります。

埋込みSQL文によって、次のことを実行できます。

  • カーソルの定義、割当ておよび解放を行います(DECLARE CURSOR、OPEN、CLOSE)。

  • データベースを指定して、接続します(DECLARE DATABASE、CONNECT)。

  • 変数名を割り当てます(DECLARE STATEMENT)。

  • 記述子を初期化します(DESCRIBE)。

  • エラー条件と警告の処理方法を指定します(WHENEVER)。

  • SQL文を解析および実行します(PREPARE、EXECUTE、EXECUTE IMMEDIATE)。

  • データベースからデータを取り出します(FETCH)。

関連項目:

「サーバー側プログラミングの概要」および「クライアント側のAPI」

オプティマイザの概要

Oracle DatabaseがSQL文を処理する仕組みを理解するには、データベースのオプティマイザと呼ばれる部分(問合せオプティマイザまたはコストベース・オプティマイザとも呼ばれる)を理解する必要があります。すべてのSQL文でオプティマイザを使用して、指定されたデータへの最も効率的なアクセス手段を決定します。

オプティマイザの使用

オプティマイザは、使用可能な実行方法を表す実行計画を生成します。

オプティマイザにより、いくつかの情報ソースが考慮されて、最も効率的な実行計画が決定されます。たとえば、オプティマイザでは、問合せ条件、使用可能なアクセス・パス、収集された対象システムの統計、ヒントなどが考慮されます。

DML文を実行するために、Oracle Databaseで多くの処理を必要とする場合があります。各処理では、データベースからデータ行を物理的に検索するか、文を発行したユーザーのためにデータ行を準備します。データベースが文を実行するときに使用するステップは、文の実行速度に大きく影響します。多くの場合、DML文の処理には多くの様々な方法を使用できます。たとえば、表または索引のアクセス順序を変更できます。

SQL文の最適な実行計画を決定する際に、オプティマイザは次の操作を実行します。

  • 式および条件の評価

  • データの詳細を把握し、このメタデータに基づいて最適化するための整合性制約の検査

  • 文の変換

  • オプティマイザの目的の選択

  • アクセス・パスの選択

  • 結合順序の選択

オプティマイザは、問合せの処理に使用できる方法の大部分を生成し、生成した実行計画の各ステップにコストを割り当てます。コストが最も低い計画が、実行する問合せ計画として選択されます。

ノート:

SQL文の実行計画は、その計画を実行しなくても取得できます。データベースが問合せの実行に実際に使用する実行計画は、適切に規定された問合せ計画のみです。

オプティマイザが選択する内容は、オプティマイザの目的を設定して、オプティマイザ用の代表的な統計を収集することにより決まります。たとえば、オプティマイザの目的を次のいずれかに設定できます。

  • 合計スループット

    結果の最後の行を可能なかぎり迅速にクライアント・アプリケーションに取得するよう、ALL_ROWSヒントがオプティマイザに指示します。

  • 初期レスポンス時間

    最初の行を可能なかぎり迅速にクライアントに取得するよう、FIRST_ROWSヒントがオプティマイザに指示します。

典型的なエンド・ユーザーの対話型アプリケーションの場合、初期レスポンス時間による最適化が有用であるのに対して、バッチモードの非対話型アプリケーションの場合、合計スループットによる最適化が有用です。

関連項目:

オプティマイザ・コンポーネント

オプティマイザには、3つの主要コンポーネント(トランスフォーマ、エスティメータおよびプラン・ジェネレータ)が含まれています。

次の図は、そのコンポーネントを示しています。

図7-2 オプティマイザのコンポーネント

図7-2の説明が続きます
「図7-2 オプティマイザのコンポーネント」の説明

オプティマイザには、解析済の問合せが入力されます。オプティマイザは、次の操作を実行します。

  1. オプティマイザは解析済の問合せを受け取り、使用可能なアクセス・パスおよびヒントに基づいてSQL文に対する一連の計画候補を生成します。

  2. オプティマイザは、データ・ディクショナリ内の統計に基づき、各計画のコストを見積ります。コストとは、特定の計画を使用して文を実行するために必要な、予想されるリソース使用に比例した見積り値です。

  3. オプティマイザは各計画のコストを比較し、コストが最も低い計画(問合せ計画と呼ばれる)を選択して、行ソース・ジェネレータに渡します。

関連項目:

問合せトランスフォーマ

問合せトランスフォーマは、オプティマイザがより優れた実行計画を生成できるように、問合せの形式を変更することが有用かどうかを判断します。問合せトランスフォーマへの入力は解析済の問合せで、オプティマイザでは問合せブロックのセットとして表されます。

関連項目:

「クエリー・リライト」

エスティメータ

エスティメータは指定された実行計画の全体コストを判断します。

この目的を達成するために、エスティメータは3つの異なる計測タイプを生成します。

  • 選択性

    この計測タイプは、行セット内の行の一部分を表します。選択性は、問合せの述語(last_name='Smith'など)や述語の組合せに関連します。

  • カーディナリティ

    この計測タイプは、行セット内の行数を表します。

  • コスト

    このメジャーは、作業単位または使用されるリソースを表します。問合せオプティマイザはディスクI/O、CPU使用量、メモリー使用量を作業単位として使用します。

統計が使用できる場合、エスティメータは統計を使用して計測値を計算します。統計によって、メジャーの正確さの度合いは改良されます。

プラン・ジェネレータ

プラン・ジェネレータは、発行された問合せに対して異なる計画を試行します。オプティマイザは、コストが最も低い計画を選択します。

ネストされた副問合せおよびマージされていないビューごとに、オプティマイザはサブプランを生成します。オプティマイザは、各サブプランを個別の問合せブロックとして表します。プラン・ジェネレータは、別のアクセス・パス、結合方法および結合順序を試行することによって、問合せブロックに対する様々な計画を探索します。

適応問合せ最適化機能によって、文の実行中に収集された統計に基づいて計画が変更されます。すべての適応メカニズムにより、デフォルト・プランと異なる文の最終プランを実行できます。適応最適化では、文の実行中にサブプランの中から選択する動的プラン、または現在の実行後の実行でプランを変更する再最適化のいずれかを使用します。

関連項目:

アクセス・パス

アクセス・パスは、問合せで行の取得に使用される手法です。

たとえば、索引を使用する問合せは、索引を使用しない問合せとは異なるアクセス・パスになります。通常、表内の行の小規模なサブセットを取得する文には、索引アクセス・パスが最適です。表内の大きな一部分にアクセスするには、全体スキャンがより効率的です。

データベースは、表からデータを取得するために、複数の異なるアクセス・パスを使用できます。代表的なものは次のとおりです。

  • 全表スキャン

    このタイプのスキャンは、表からすべての行を読み取り、選択基準に合致しない行をフィルタ処理して除外します。データベースは、使用済領域と未使用領域を分離する最高水位標(HWM)より下のものも含め、セグメント内のすべてのデータ・ブロックを順次スキャンします(「セグメント領域と最高水位標」を参照)。

  • ROWIDスキャン

    行のROWIDは、そのブロック内の行および行の位置を含むデータファイルおよびデータ・ブロックを指定します。データベースは、最初に文中のWHERE句または索引スキャンのいずれかから選択した行のROWIDを取得し、次にそのROWIDに基づいて選択した各行の位置を特定します。

  • 索引スキャン

    このスキャンは、SQL文によってアクセスする索引付けされた列値に対応した索引を検索します(「索引スキャン」を参照)。文によって索引の列のみにアクセスする場合、Oracle Databaseは索引付けされた列値を索引から直接読み取ります。

  • クラスタ・スキャン

    クラスタ・スキャンは、索引付きの表クラスタに格納された表からデータを取得するもので、表クラスタでは、同じクラスタ・キー値を持つすべての行が同じデータ・ブロックに格納されています(「索引付きクラスタの概要」を参照)。データベースは最初にクラスタ索引をスキャンして、選択した1つの行のROWIDを取得します。Oracle DatabaseはこのROWIDに基づいて行の位置を特定します。

  • ハッシュ・スキャン

    ハッシュ・スキャンはハッシュ・クラスタ内の行の位置を特定するもので、ハッシュ・クラスタ内では、同じハッシュ値を持つすべての行が同じデータ・ブロックに格納されています(「ハッシュ・クラスタの概要」を参照)。データベースは最初に、文によって指定されたクラスタ・キー値にハッシュ関数を適用することにより、ハッシュ値を取得します。次に、Oracle Databaseはこのハッシュ値の行を含むデータ・ブロックをスキャンします。

オプティマイザは、文に対して使用可能なアクセス・パスと、各アクセス・パスまたはパスの組合せの使用時の見積りコストに基づいて、1つのアクセス・パスを選択します。

関連項目:

アクセス・パスについて学習するには、Oracle Database 2日でパフォーマンス・チューニング・ガイドおよびOracle Database SQLチューニング・ガイドを参照してください

オプティマイザ統計

オプティマイザ統計は、データベースの詳細を記述したデータとデータベース内のオブジェクトのコレクションです。統計は、アクセス・パスを評価する場合にオプティマイザが使用できるデータ記憶域とデータ配分に関する、統計的に正しいピクチャを提示します。

オプティマイザ統計のタイプは次のとおりです。

  • 表統計

    行数、ブロック数および行の平均長が含まれます。

  • 列統計

    列内の個々の値とNULLの数およびデータ配分が含まれます。

  • 索引統計

    リーフ・ブロックおよび索引レベルの数が含まれます。

  • システム統計

    CPUとI/Oのパフォーマンスおよび使用率が含まれます。

Oracle Databaseは、すべてのデータベース・オブジェクトのオプティマイザ統計を自動的に収集し、これらの統計を自動メンテナンス・タスクとして保持します。また、DBMS_STATSパッケージを使用して、手動で統計を収集することもできます。このPL/SQLパッケージでは、統計を変更、表示、エクスポート、インポートおよび削除できます。

ノート:

オプティマイザ統計は問合せの最適化を目的として作成され、データ・ディクショナリに格納されます。これらの統計と動的パフォーマンス・ビューを介して表示できるパフォーマンス統計を混同しないでください。

オプティマイザ統計アドバイザは、現在の統計収集の状況、既存の統計収集ジョブの有効性、および収集された統計の品質を分析するビルトイン診断ソフトウェアです。オプティマイザ統計アドバイザは、現在の機能セットに基づくOracleベスト・プラクティスを体現する、ルールを維持します。このように、アドバイザは常に統計収集のための最新の推奨事項を提供します。

関連項目:

オプティマイザ・ヒント

ヒントは、オプティマイザへの指示として機能するSQL文内のコメントです。

特定のアプリケーションのデータについて、オプティマイザよりもさらに詳細な知識を持つアプリケーション設計者の方が、SQL文をより効率的に実行する方法を選択できることもあります。アプリケーション設計者は、SQL文内にヒントを使用して文の実行方法を指定できます。次の例では、ヒントの使用方法を示します。

例7-2 FIRST_ROWSヒントを指定したSELECTの実行計画

対話型アプリケーションによって、50行を戻す問合せが実行されるとします。このアプリケーションでは、問合せによって戻される最初の25行のみを初めにフェッチしてエンド・ユーザーに表示します。オプティマイザを使用して、最初の25レコードを可能なかぎり迅速に取得する計画を生成し、ユーザーが待たずに済むようにします。この指示をオプティマイザに渡すには、次の例のSELECT文およびAUTOTRACE出力に示すように、ヒントを使用します。 

SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id
FROM   hr.employees
WHERE  department_id > 50;

------------------------------------------------------------------------
| Id | Operation                    | Name              | Rows | Bytes
------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |                   | 26   | 182
|  1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES         | 26   | 182
|* 2 |   INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      |
------------------------------------------------------------------------

この例では、実行計画は、オプティマイザがemployees.department_id列の索引を選択して部門IDが50を超えるemployeesの最初の25行を検索することを示します。オプティマイザは索引から取得したROWIDを使用してemployees表からレコードを取得し、クライアントに戻します。通常、最初のレコードの取得はほとんど即時です。

例7-3 ヒントを指定しないSELECTの実行計画

オプティマイザ・ヒントを指定しない同じ文を実行するとします。

SELECT employee_id, department_id
FROM   hr.employees
WHERE  department_id > 50;
 
------------------------------------------------------------------------
| Id | Operation              | Name              | Rows | Bytes | Cos
------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |                   | 50   | 350   |
|* 1 |  VIEW                  | index$_join$_001  | 50   | 350   |
|* 2 |   HASH JOIN            |                   |      |       |
|* 3 |    INDEX RANGE SCAN    | EMP_DEPARTMENT_IX | 50   | 350   |
|  4 |    INDEX FAST FULL SCAN| EMP_EMP_ID_PK     | 50   | 350   |

この場合、実行計画では2つの索引を結合し、可能なかぎり迅速に要求されたレコードを戻します。オプティマイザは、例7-2のように索引から表へと繰り返し移動するのではなく、EMP_DEPARTMENT_IXのレンジ・スキャンを選択して、部門IDが50を超えるすべての行を検索し、ハッシュ表にこれらの行を配置します。オプティマイザは次に、EMP_EMP_ID_PK索引を読み取ることを選択します。この索引の各行について、ハッシュ表を調査して部門IDを検索します。

この場合、EMP_DEPARTMENT_IXの索引レンジ・スキャンが完了するまで、データベースはクライアントに最初の行を戻すことができません。このため、生成されたこの計画では最初のレコードを戻すために、より長い時間がかかります。索引のROWIDによって表にアクセスする例7-2の計画とは異なり、計画はマルチブロックI/Oを使用し、読取りの範囲が大きくなります。読取りによって、全結果セットの最後の行をより迅速に戻すことができます。

関連項目:

オプティマイザ・ヒントの使用方法を学習するには、Oracle Database SQLチューニング・ガイドを参照してください。

SQL処理の概要

この項では、Oracle DatabaseによるSQL文の処理の仕組みについて説明します。特に、データベースがDDL文を処理してオブジェクトを作成する仕組み、DMLを使用してデータを変更する仕組み、問合せを使用してデータを取得する仕組みについて説明します。

SQL処理の段階

SQL処理の一般的な段階は、解析、最適化、行ソース生成、および実行です。文によっては、データベースがこれらのステップの一部を省略する場合があります。

次の図は、全体的な段階を示しています。

図7-3 SQL処理の段階

図7-3の説明が続きます
「図7-3 SQL処理の段階」の説明
SQLの解析

SQL処理の最初の段階は、SQLの解析です。この段階では、SQL文の各部分を他のルーチンで処理できるデータ構造に分解する処理が行われます。

アプリケーションがSQL文を発行すると、アプリケーションはOracle Databaseに解析コールを出して、文の実行を準備します。解析コールによって、解析済のSQL文と処理に使用するその他の情報が保持されるセッション固有のプライベートSQL領域のハンドルであるカーソルがオープンまたは作成されます。カーソルおよびプライベートSQL領域はPGA内にあります。

解析コール時には、データベースでは次のチェックを実行します。

  • 構文チェック

  • セマンティクス・チェック

  • 共有プール・チェック

これらのチェックによって、文の実行前に検出できるエラーを特定します。解析では捕捉できないエラーもあります。たとえば、データベースで、文の実行時にのみ、データ変換に関するデッドロックまたはエラーが発生する場合があります。

関連項目:

「ロックとデッドロック」

SQLの最適化

問合せの最適化は、SQL文を実行する最も効率的な方法を選択するプロセスです。

データベースは、アクセスする実際のデータに関して収集した統計に基づいて、問合せを最適化します。オプティマイザは行数、データ・セットのサイズおよびその他の要因を使用して、使用可能な実行計画を生成し、各計画に数値コストを割り当てます。データベースは最もコストが低い計画を使用します。

データベースは一意のDML文ごとに必ず1回以上のハード解析を実行して、この解析中に最適化を実行します。最適化を要求する副問合せなどのDMLコンポーネントを含まないかぎり、DDLは最適化されません。

関連項目:

SQLの行ソース生成

行ソース・ジェネレータは、オプティマイザから最適な実行計画を受け取り、データベースのその他の部分で使用可能な問合せ計画と呼ばれる反復計画を作成するソフトウェアです。

問合せ計画では、ステップの組合せ形式を使用します。各ステップでは行セットが戻されます。このセット内の行は次のステップまたは最後のステップのいずれかで使用され、SQL文を発行するアプリケーションに戻されます。

行ソースは、行を反復的に処理できる制御構造に沿って、実行計画のステップから戻された行セットです。表、ビュー、または結合処理あるいはグループ化処理の結果が行ソースになる可能性があります。

SQLの実行

実行時には、SQLエンジンが行ソース・ジェネレータによって作成されたツリー内の各行ソースを実行します。これのみがDML処理の必須ステップです。

実行中、データがメモリー内にない場合は、データベースがディスクからメモリーへとデータを読み取ります。また、データベースはデータ整合性を保証するために必要なすべてのロックおよびラッチを取り除き、SQL実行時に行われたすべての変更を記録します。SQL文の処理の最後の段階は、カーソルのクローズです。

データベースがインメモリー列ストア(IM列ストア)を使用するように構成されている場合、データベースは問合せを、可能な場合はIM列ストアに、さもなければディスクおよびデータベース・バッファ・キャッシュに透過的にルーティングします。1つの問合せでも、IM列ストア、ディスク、バッファ・キャッシュを使用できます。たとえば、ある問合せで2つの表を結合し、そのうち1つのみがIM列ストアにキャッシュされていることがあります。

関連項目:

DML処理とDDL処理の違い

Oracle DatabaseはDMLとは異なる方法でDDLを処理します。

たとえば、表を作成するとき、データベースはCREATE TABLE文を最適化しません。かわりに、Oracle DatabaseはDDL文を解析してコマンドを実行します。

DDLとは対照的に、ほとんどのDML文には問合せコンポーネントがあります。問合せでカーソルを実行すると、問合せで生成された行が結果セットに入力されます。

データベースでは、結果セットの行を一度に1行ずつまたはグループとしてまとめてフェッチできます。フェッチでは、データベースで行が選択され、問合せで要求された場合には行がソートされます。最後の行がフェッチされるまで、毎回のフェッチで結果の行が連続して取得されます。

関連項目:

DDLの処理、トランザクション制御およびその他のタイプの文について学習するには、『Oracle Database開発ガイド』を参照してください。