オプティマイザのアクセス・パス

概要

    目的

    このチュートリアルでは、オプティマイザが使用するさまざまなアクセス・パスを紹介します。

    所要時間

    約30分

    はじめに

    このチュートリアルでは、次のケース（シナリオ）に対してオプティマイザのアクセス・パスを使用します。

    • ケース1： 索引の使用の有無
    • ケース2： 単一列索引を使用したアクセス・パスの比較
    • ケース3： 連結索引
    • ケース4： ビットマップ索引によるアクセス
    • ケース5： 索引のみのアクセス
    • ケース6： ビットマップ索引のみのアクセス
    • ケース7： B*ツリー索引のみのアクセス
    • ケース8： ファンクション索引

    ハードウェアとソフトウェアの要件

    ハードウェアとソフトウェアの要件リストは、以下のとおりです。

    • Oracle Database 11g Enterprise Edition（Oracle Tuning Management PackおよびOracle Diagnostic Management Packを使用できること、サンプル・スキーマがインストールされていること）
    • Oracle SQL Developer 3.2

    前提条件

    • Oracle SQL Developer 3.2をこちらからインストールしてください。

    注： このチュートリアルを最適な状態で表示するには、ブラウザとしてFirefoxまたはChromeを使用してください。

データベース接続の作成

    オプティマイザのアクセス・パスを使用するための最初のステップは、データベース接続の作成です。
    次の手順に従って、データベース接続を作成します。

    デスクトップ上のSQL Developer 3.2アイコンをクリックして、SQL Developerを起動します。


    Oracle SQL Developer 3.2が起動します。



    接続ナビゲータで、「Connections」を右クリックし、「New Connection」を選択します。
    New / Select Database Connectionダイアログ・ボックスが開きます。 以下の接続情報を入力し、「Test」をクリックします。

    Connection Name： hr_conn
    Username： hr
    Password： ＜使用するパスワード＞（「Save Password」を選択）
    Hostname： localhost
    SID： ＜使用するSID＞ 


    左下（Helpボタンの上）で接続のステータスを確認します。 Successと表示されているはずです。 「Save」をクリックしてから、「Connect」をクリックします。



ケース1： 索引の使用の有無

    索引の使用の有無によるパフォーマンスの違いを確認するには、次の手順を実行します。

    「hr_conn」を右クリックして、「Open SQL Worksheet」を選択します。

    主キー索引と一意キー索引（*_PK、*_UK）を除くすべての索引をemployees表から削除します。

    drop index EMP_JOB_IX;
    drop index EMP_NAME_IX;
    drop index EMP_MANAGER_IX;
    drop index EMP_DEPARTMENT_IX;

    問合せの自動トレースを実行します。

    select * from employees where department_id = 10;

    出力内容を確認します。 employees表の索引は表示されていません。

    行を取得するためにオプティマイザが使用できるのは、全体スキャンのみです。 全表スキャンには長い時間がかかることが分かります。


    ステップ1の問合せパフォーマンスを向上するため、索引を作成します。

    create index emp_idx_dept_no on hr.employees(department_id) nologging compute statistics;


    ステップ1の問合せに対してもう一度自動トレースを実行し、出力を確認します。


    パフォーマンスが大幅に向上していることが分かります。 時間、コスト、物理読取りの違いを確認します。

ケース2： 単一列索引を使用したアクセスの比較

    単一列索引によるアクセスを比較するには、次の手順を実行します。

    hrスキーマに接続します。 主キー索引と一意キー索引（*_PK、*_UK）を除くすべての索引をemployees表から削除します。

    drop index EMP_IDX_DEPT_NO;

    問合せの自動トレースを実行します。

    SELECT /*+ FULL(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000;

    employees表のdepartment_id列とsalary列に対して2つのB*ツリー索引を作成します。

    CREATE INDEX emp_dept_id_idx ON employees(department_id) NOLOGGING COMPUTE STATISTICS;
    CREATE INDEX emp_sal_idx ON employees(salary) NOLOGGING COMPUTE STATISTICS;

    employees表の索引の使用状況を監視するため、次の文を実行します。

    ALTER INDEX emp_dept_id_idx MONITORING USAGE;
    ALTER INDEX emp_sal_idx MONITORING USAGE;

    ステップ2の問合せに対して自動トレースを実行します。

    SELECT /*+ FULL(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000;

    employees表の索引は表示されていません。

    行を取得するためにオプティマイザが使用できるのは、全体スキャンのみです。 今回も全表スキャンが行われていることが分かります。

    次の問合せの自動トレースを実行します。

    SELECT /*+ INDEX_COMBINE(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000;

    今回は、オプティマイザが複数の索引を使用し、これらを組み合わせて表にアクセスしていることが分かります。 かかったコストは全表スキャンよりも低くなっています。


ケース3： 連結索引

    連結索引を使用した問合せのパフォーマンスを確認するには、次の文を実行します。

    hrスキーマに接続します。 主キー索引を除くすべての索引をemployees表から削除します。

    drop index EMP_DEPT_ID_IDX;
    drop index EMP_SAL_IDX;

    employees表のdepartment_id列、salary列、hire_date列に対して連結索引を作成します。

    CREATE INDEX emp_dept_id_sal_hiredt_idx
    ON employees(department_id,salary,hire_date)
    NOLOGGING COMPUTE STATISTICS;

    問合せの自動トレースを実行します。

    SELECT /*+INDEX(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000
    AND hire_date between '13-JAN-07' AND '13-JAN-08';

    オプティマイザが連結索引を使用しており、結果としてコストが非常に良好になります。

    問合せの自動トレースを実行します。

    SELECT /*+INDEX(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000;

    この問合せは前のステップと非常に良く似ていますが、HIRE_DATEに対する条件が削除されています。

    この場合も、オプティマイザは連結索引を使用できます。

    問合せの自動トレースを実行します。

    SELECT /*+INDEX(e)*/e.*
    FROM employees e
    WHERE salary > 1000
    AND hire_date between '13-JAN-07' AND '13-JAN-08';

    この問合せには連結索引の最初の部分は含まれていませんが、 オプティマイザは全索引スキャンを使用することで、引き続きこの索引を使用します。

ケース4： ビットマップ索引

    ビットマップ索引を使用した問合せのパフォーマンスを確認するには、次の文を実行します。

    hrスキーマに接続します。 主キー索引を除くすべての索引をemployees表から削除します。

    drop index EMP_DEPT_ID_SAL_HIREDT_IDX;

    次のビットマップ索引を作成します。

    CREATE BITMAP INDEX emp_dept_id_bidx
    ON employees(department_id)
    NOLOGGING COMPUTE STATISTICS;

    CREATE BITMAP INDEX emp_sal_bidx
    ON EMPLOYEES(salary)
    NOLOGGING COMPUTE STATISTICS;

    CREATE BITMAP INDEX emp_hire_date_bidx
    ON employees(hire_date)
    NOLOGGING COMPUTE STATISTICS;

    問合せの自動トレースを実行します。

    SELECT /*+INDEX_COMBINE(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000
    AND hire_date between '13-JAN-07' AND '13-JAN-08';

    この問合せを解決するために、すべてのビットマップ索引が使用されています。

    コストは良好であり、 全表スキャンよりも若干低くなっています。

ケース5： 索引アクセスのみ

    索引アクセスのみを使用した問合せのパフォーマンスを確認するには、次の文を実行します。

    hrスキーマに接続します。 主キー索引を除くすべての索引をemployees表から削除します。

    drop index EMP_DEPT_ID_BIDX;
    drop index EMP_SAL_BIDX;
    drop index EMP_HIRE_DATE_BIDX;

    employees表のfirst_name列とsalary列に対して1つの索引を作成します。

    CREATE INDEX emp_last_first_name_idx
    ON employees(last_name,first_name)
    NOLOGGING COMPUTE STATISTICS;

    問合せの自動トレースを実行します。

    SELECT e.last_name, e.first_name
    FROM employees e;

    オプティマイザは表自体にはアクセスすることなく、索引を使用してSELECT構文の全リストを取得できます。 コストは良好です。

ケース6： ビットマップ索引アクセスのみ

    ビットマップ索引アクセスのみを使用した問合せのパフォーマンスを確認するには、次の文を実行します。

    接続ナビゲータで、「Connections」を右クリックし、「New Connection」を選択します。
    New / Select Database Connectionダイアログ・ボックスが開きます。 以下の接続情報を入力し、「Test」をクリックします。

    Connection Name： sh
    Username： sh
    Password： ＜使用するパスワード＞（「Save Password」を選択）
    Hostname： localhost
    SID： ＜使用するSID＞ 


    左下（Helpボタンの上）に表示される接続ステータスを確認します。 Successと表示されているはずです。 「Save」をクリックしてから、「Connect」をクリックします。


    主キー索引と一意キー索引（*_PK、*_UK）を除くすべての索引をcustomers表から削除します。


    DROP INDEX customers_gender_bix ;
    DROP INDEX cust_cust_credit_limit_idx;
    DROP INDEX cust_cust_postal_code_bidx;
    DROP INDEX emp_first_name_sal_idx;

    customers表のcust_credit_limit列に対してビットマップ索引を作成します。

    CREATE BITMAP INDEX cust_cust_credit_limit_bidx ON CUSTOMERS(cust_credit_limit)
    NOLOGGING COMPUTE STATISTICS;

    問合せの自動トレースを実行します。

    SELECT count(*) credit_limit
    FROM CUSTOMERS
    WHERE cust_credit_limit=2000;

    salary列は選択の対象になっていませんが、ビットマップ索引に対するCOUNT処理は非常に効率的であることが分かります。

ケース7： B*ツリー索引アクセスのみ

    ビットマップ索引アクセスのみを使用した問合せのパフォーマンスを確認するには、次の文を実行します。

    shスキーマに接続します。 主キー索引を除くすべての索引をcustomers表から削除します。

    DROP INDEX cust_cust_credit_limit_bidx;

    customers表のcust_credit_limit列に対してB*ツリー索引を作成します。

    CREATE  INDEX cust_cust_credit_limit_idx ON CUSTOMERS(cust_credit_limit)
    NOLOGGING COMPUTE STATISTICS;

    問合せの自動トレースを実行します。

    SELECT count(*) credit_limit
    FROM CUSTOMERS
    WHERE cust_credit_limit=2000;

    オプティマイザはB*ツリー索引を使用しているものの、前のケースのビットマップ索引と比べると効率は低下していることが分かります。

ケース8： ファンクション索引

    ファンクション索引のみを使用した問合せのパフォーマンスを確認するには、次の文を実行します。

    hrスキーマに接続します。 主キー索引を除くすべての索引をemployees表から削除します。

    drop index EMP_SAL_BIDX;

    employees表のfirst_name列に対してB*ツリー索引を作成します。

    CREATE  INDEX emp_fname_idx ON employees(first_name)
    NOLOGGING COMPUTE STATISTICS;

    問合せの自動トレースを実行します。

    SELECT employee_id, department_id
    FROM EMPLOYEES
    WHERE LOWER(first_name) like 's%';

    関数によって列が変更されているため、存在する索引を使用できないことが分かります。

    この問合せのパフォーマンスを向上するため、ファンクション索引を作成します。

    CREATE  INDEX emp_lower_fname_idx ON employees(LOWER(first_name));

    ステップ3の問合せに対して、もう一度自動トレースを実行します。 今回は問合せのパフォーマンスが大幅に向上したことが分かります。

まとめ

    このチュートリアルでは、次のケース（シナリオ）に対するオプティマイザ・アクセス・パスの使用法について学習しました。

    • ケース1： 索引の使用の有無
    • ケース2： 単一列索引を使用したアクセス・パスの比較
    • ケース3： 連結索引
    • ケース4： ビットマップ索引によるアクセス
    • ケース5： 索引のみのアクセス
    • ケース6： ビットマップ索引のみのアクセス
    • ケース7： B*ツリー索引のみのアクセス
    • ケース8： ファンクション索引

    著者

    • カリキュラムのおもな開発者： Dimpi Sarmah、Sharon Sophia Stephen
    • 共著者： Ashley Chen、Swarnapriya Shridhar、Nancy Greenberg

このOracle by Exampleをナビゲートする際、以下の機能を使用できます。

ヘッダー・ボタンの非表示：
ヘッダー内のボタンを非表示にするには、タイトルをクリックします。 ボタンを再表示するには、もう一度タイトルをクリックします。
トピック一覧ボタン：
すべてのトピックの一覧です。 いずれかのトピックをクリックすると、その項に移動します。
すべてのトピックを開く/閉じる：
すべての項に対する詳細を表示または非表示にします。 デフォルトでは、すべてのトピックが閉じられています。
すべてのイメージを表示/非表示：
すべてのスクリーンショットを表示または非表示にします。 デフォルトでは、すべてのイメージが表示されています。
印刷：
コンテンツを印刷します。 現在表示または非表示にされているコンテンツが印刷されます。

このチュートリアルの特定の項に移動するには、一覧からトピックを選択してください。