Oracle9i での問合せ最適化
オラクル・ホワイト・ペーパー 2002 年 2 月
Oracle9i での問合せ最適化
概要 ... 4 はじめに ... 4 問合せオプティマイザについて ... 4 Oracle の問合せ最適化機能 ... 4 SQL 変換... 6 経験則的問合せ変換... 6 単純なビュー・マージング ... 6 複雑なビュー・マージング ... 7 副問合せのフラット化 ... 7 推移述語の生成 ... 9 共通副次式の排除... 9 述語のプッシュダウンとプルアップ ... 9 CUBE 問合せ用のグループ・プルーニング ... 10 外部結合から内部結合への変換 ... 11 コストベースの問合せ変換 ... 11 マテリアライズド・ビューのリライト... 11 OR 拡張 ... 12 スター型変換... 12 外部結合ビューに関する述語プッシュダウン ... 14 アクセス・パス選択 ... 14 結合順序 ... 15 適応検索方針... 15 複数の初期順序経験則 ... 16 ビットマップ索引 ... 16 ビットマップ結合索引 ... 18 ドメイン索引と拡張性 ... 18 高速全索引スキャン... 18 索引結合 ... 19 索引スキップ・スキャン... 19 パーティション最適化 ... 19 パーティション・ワイズ・ジョイン、GROUP-BY、ソート... 20 ソートの排除 ... 20 OLAP 最適化... 20 パラレル実行 ... 21 ヒント ... 21 コスト・モデルと統計... 22 オプティマイザ統計... 22 オブジェクト・レベル統計 ... 22 システム統計... 23 ユーザー定義統計... 23 統計管理 ... 23 パラレル・サンプリング ... 24 監視 ... 24ヒストグラムの自動的決定 ... 24 動的サンプリング ... 24 最適化コスト・モード ... 25 動的実行時最適化 ... 26 動的並列度 ... 26 動的メモリー割当て... 26 データベース・リソース・マネージャ ... 28 結論 ... 29
Oracle9i での問合せ最適化
概要
本書では、Oracle の顧客に優れたパフォーマンスを提供するための主要なデータ ベース・コンポーネントである、Oracle9i の問合せオプティマイザについて説明し ます。Oracle の問合せオプティマイザ・テクノロジは、その機能範囲において最 も優れたコンポーネントです。本書では、問合せ最適化の主要事項を詳細に説明 します。はじめに
問合せオプティマイザについて
問合せ最適化は、リレーショナル・データベースのパフォーマンス、特に複雑な SQL 文の実行にとって非常に重要です。問合せオプティマイザは、それぞれの問 合せの実行に最適な方針を判断します。問合せオプティマイザは、たとえば、所 定の問合せに索引を使用するか、または複数の表の結合時にどの結合方法を使用 するかを決定します。このような判断は、SQL のパフォーマンスに多大な影響を 及ぼすため、業務系システムからデータ・ウェアハウスまで、分析システムから コンテンツ管理システムまでのあらゆるアプリケーションにとって、問合せ最適 化は重要なテクノロジです。 問合せオプティマイザは、アプリケーションおよびエンド・ユーザーにとってト ランスペアレントです。アプリケーションは、非常に複雑な SQL を生成すること があるため、優れたパフォーマンスを保証するには、問合せオプティマイザが高 度に洗練されていて堅牢である必要があります。たとえば、問合せオプティマイ ザは、複雑な SQL 文をより優れたパフォーマンスを持つ同等の SQL 文に変換し ます。 問合せオプティマイザは、一般的にコストベースです。コストベースの最適化方 針では、所定の問合せについて複数の実行プランが生成され、それぞれのプラン について見積りコストが計算されます。問合せオプティマイザは、見積りコスト が最低のプランを選択します。Oracle の問合せ最適化機能
Oracle のオプティマイザは、おそらく、業界で最も実績のあるオプティマイザで す。1992 年に Oracle7 に採用されたコストベース・オプティマイザは、約 10 年間 の実環境での顧客経験をとおして、機能強化が継続して行われ、改善されてきま した。優れた問合せオプティマイザは、純粋な理論的推測や仮定に基づいて研究 所ベースで開発されるものではなく、顧客の実際の要件に応じて開発され完成します。Oracle の問合せオプティマイザは、他のどの問合せオプティマイザよりも 多数のデータベース・アプリケーションによって使用され、継続的に実環境から のインプットによって強化されてきました。 Oracle のオプティマイザは、4 つの主要コンポーネントで構成されています。それ ぞれのコンポーネントについて、本書で詳細に説明します。 SQL 変換: Oracle は、問合せ最適化中に、洗練された各種の方法を使用して SQL 文を変換します。問合せ最適化のこのフェーズの目的は、オリジナルの SQL 文を、セマンティクスが同等な処理効率のよい SQL 文に変換するこ とです。 実行プラン選択:それぞれの SQL 文について、オプティマイザは、実行プラン （Oracle の EXPLAIN PLAN ファシリティ、または Oracle の“v$sql_plan”
ビューを使用して表示できる）を選択します。実行プランには、表のアク セス順序、表の結合方法、および表が索引経由でアクセスされるかどうか など、SQL 処理時のすべての手順が記載されます。オプティマイザは、そ れぞれの SQL 文について使用可能な多数の実行プランを検討し、最善の プランを選択します。 コスト・モデルと統計: Oracle のオプティマイザは、SQL 文の実行を構成する 個々の操作に関するコスト見積りに依拠します。最適実行プランを選択す るには、オプティマイザは、最良のコスト見積りが必要です。コスト見積 りは、それぞれの問合せ操作が必要とする I/O、CPU およびメモリー資源 についての深い知識、データベース・オブジェクトに関する統計情報（表、 索引、およびマテリアライズド・ビュー）、そしてハードウェア・サーバー・ プラットフォームに関するパフォーマンス情報に基づきます。このような 統計およびパフォーマンス情報を収集する処理は、非常に効率よく、高度 に自動化される必要があります。 実行時の動的最適化: SQL 実行のすべての面についての最適化計画が事前にで きるとは限りません。そのため Oracle は、現在のデータベース処理負荷に 基づいて問合せ処理方針を動的に調整します。動的最適化の目的は、それ ぞれの問合せが理想的な量の CPU やメモリー資源を獲得できない場合で も最適なパフォーマンスを達成できるようにすることです。 Oracle は、レガシー・オプティマイザであるルールベース・オプティマイザも持っ ています。このオプティマイザは、下位互換性を維持していますが、Oracle の次 回の主要リリースからはサポートされなくなります。現在、Oracle の顧客のほと んどは、コストベースのオプティマイザを使用しています。主要アプリケーショ ン・ベンダーのすべて（たとえば、Oracle Applications、SAP、Peoplesoft）および 最近構築されたカスタム・アプリケーションの大部分は、パフォーマンス向上の ためにコストベース・オプティマイザを利用しています。本書では、コストベー ス・オプティマイザについてのみ説明します。
SQL 変換
SQL を使用して複雑な問合せを表現する方法は多数あります。データベースにサ ブミットされる SQL のスタイルは、一般的に、エンド・ユーザーによる記述また はアプリケーションによる生成のいずれかのよりシンプルなほうになります。 ただし、これらの手書きまたは機械生成の問合せ式は、問合せ実行用の最も効率 的な SQL とは限りません。たとえば、アプリケーションが生成した問合せには無 関係な条件が存在し、削除できることがよくあります。または、問合せから推測 可能なその他の条件があり、SQL 文に追加することが必要な場合があります。 SQL 変換の目的は、所定の SQL 文をより優れたパフォーマンスを提供できるよう にセマンティックが同等の SQL 文（同じ結果を返す SQL 文）に変換することで す。 このような変換は、すべてアプリケーションにもエンド・ユーザーにもトランス ペアレントです。SQL の変換は、問合せ最適化時に自動的に行われます。 Oracle は、広範な SQL 変換を実装しています。これらは、次の 2 つのカテゴリに 大きく分類されます。 経験則的問合せ変換:これらの変換は、可能なかぎりいつでも着信 SQL 文に適 用されます。これらの変換により同等またはより優れた問合せパフォーマ ンスが必ず提供されるため、この変換の適用によりパフォーマンスが低下 することはありません。 コストベースの問合せ変換: Oracle は、問合せ変換のいくつかのクラスにコス トベース・アプローチをとります。このアプローチをとることで、変換済 問合せが元の問合せと比較され、Oracle のオプティマイザは最適実行方針 を選択します。 以降の項では、Oracle の変換テクノロジの数例について説明します。これは確定 的なリストではなく、主要な変換テクノロジとその利点を読者に理解させること を意図しています。経験則的問合せ変換
単純なビュー・マージング 問合せ変換の最も単純な形はおそらく、ビュー・マージングでしょう。ビューを 含んだ問合せの場合、問合せを使用してビュー定義を「マージ」することにより、 ビューへの参照を問合せから完全に取り除くことができます。たとえば、非常に シンプルなビューと問合せを検討してみます。CREATE VIEW TEST_VIEW AS
SELECT ENAME, DNAME, SAL FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;
SELECT ENAME, DNAME FROM TEST_VIEW WHERE SAL > 10000;
問合せ変換を行わない場合、この問合せを処理する唯一の方法は、EMP のすべて の行を DEPT 表のすべての行に結合して、適切な SAL 値で行をフィルタする方法 です。
ビュー・マージングを使用する場合、前述の問合せは次のように変換できます。
SELECT ENAME, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO
AND E.SAL > 10000;
変換済問合せを処理する場合、EMP 表と DEPT 表の結合の前に、述語‘SAL>10000’ を適用できます。この変換は、結合対象のデータ量を削減し、問合せのパフォー マンスを大幅に向上させます。このようなシンプルな例からも、問合せ変換のメ リットと重要性は明らかです。 複雑なビュー・マージング 多数のビュー・マージング操作は、前述の例のとおり非常にわかりやすいもので す。ただし、GROUP BY または DISTINCT 操作を含むより複雑なビューは、簡単 にはマージできません。Oracle は、複雑なビューのマージ用にもいくつかの高度 な方法を提供しています。 GROUP BY 句を持つビューを検討します。この例では、ビューは各部門の平均給 与を計算します。
CREATE VIEW AVG_SAL_VIEW AS
SELECT DEPTNO, AVG(SAL) AVG_SAL_DEPT FROM EMP GROUP BY DEPTNO
Oakland（オークランド）市の各部門の平均給与を計算する問合せは、次のとおり です。
SELECT DEPT.NAME, AVG_SAL_DEPT FROM DEPT, AVG_SAL_VIEW
WHERE DEPT.DEPTNO = AVG_SAL_VIEW.DEPTNO AND DEPT.LOC = 'OAKLAND'
次のとおり変換できます。
SELECT DEPT.NAME, AVG(SAL) FROM DEPT, EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO AND DEPT.LOC = 'OAKLAND' GROUP BY DEPT.ROWID, DEPT.NAME
この変換によるパフォーマンス上のメリットは即時明確になります。結合を行う 前に EMP 表の全データをグループ化するかわりに、この変換を行うと、グループ 化の前に EMP データを結合およびフィルタできます。 副問合せのフラット化 Oracle は、各種の副問合せを結合、セミ結合、アンチ結合に変換する各種の変換 を備えています。たとえば、年収が 10000 以上の従業員がいる部門を選ぶ、次の 問合せを検討します。
SELECT D.DNAME FROM DEPT D WHERE D.DEPTNO IN (SELECT E.DEPTNO FROM EMP E WHERE E.SAL > 10000)
この問合せに最適な、使用可能な実行プランは各種あります。Oracle は、各種の 可能な変換を検討し、コストに基づいてベストなプランを選択します。
変換を行わない場合、この問合せの実行プランは次のようになります。
OPERATION OBJECT NAME OPTIONS
SELECT STATEMENT FILTER
TABLE ACCESS DEPT FULL
TABLE ACCESS EMP FULL
この実行プランでは、DEPT 表のそれぞれの行について、副問合せの条件を満た すすべての EMP レコードがスキャンされます。一般的に、これは効率のよい実行 方針とはいえません。ただし、問合せ変換によってさらに効率的なプランが可能 になります。 この問合せで可能なプランの 1 つは、問合せをセミ結合として実行することです。 セミ結合は、結合の内部表から重複値を除去する特別な種類の結合です（この副 問合せにとっては正しいセマンティクスです）。この例では、オプティマイザは ハッシュ・セミ結合を選択しましたが、Oracle はソート/マージおよびネステッド・ ループ・セミ結合にも対応しています。
OPERATION OBJECT NAME OPTIONS
SELECT STATEMENT
HASH JOIN SEMI
TABLE ACCESS DEPT FULL
TABLE ACCESS EMP FULL
SQL には、セミ結合用の直接構文が存在しないため、この変換済問合せは標準 SQL を使用して表現できません。ただし、変換済の擬似 SQL は次のとおりです。
SELECT DNAME FROM EMP E, DEPT D WHERE D.DEPTNO <SEMIJOIN> E.DEPTNO AND E.SAL > 10000;
もう 1 つの実行可能なプランでは、DEPT 表が結合の内側の表である必要がある とオプティマイザが判断します。この場合、オプティマイザは、問合せを通常の 結合として実行しますが、重複する部門番号を除去するため、EMP 表の固有なソー トを実行します。
OPERATION OBJECT NAME OPTIONS
SELECT STATEMENT HASH JOIN
SORT UNIQUE
TABLE ACCESS EMP FULL
TABLE ACCESS DEPT FULL
この場合の変換済 SQL は、次のとおりです。
SELECT D.DNAME FROM (SELECT DISTINCT DEPTNO FROM EMP) E, DEPT D WHERE E.DEPTNO = D.DEPTNO
AND E.SAL > 10000;
副問合せフラット化は、ビュー・マージングと同様、優れた問合せパフォーマン スのための基本的な最適化です。
推移述語の生成
表の結合関係によっては、1 つの表の述語を他の表の述語に変換できる問合せも あります。Oracle は、この方法で新しい述語を推論します。このような述語を推 移述語と呼びます。たとえば、発注日と同じ日に出荷されたすべての勘定科目を 見つけ出す問合せを検討します。
SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY
AND O_ORDERDATE = L_SHIPDATE
AND O_ORDERDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002'
推移性を使用することにより、ORDER 表の述語を LINEITEM 表にも適用できま す。
SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY
AND O_ORDERDATE = L_SHIPDATE
AND O_ORDERDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002' AND L_SHIPDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002'
新しい述語の存在は、結合対象のデータ量を減らす、または追加索引の使用を可 能にすることがあります。
共通副次式の排除
1 つの問合せ内で同じ副次式または計算が何度も使用される場合、Oracle は、それ ぞれの行につき一度のみ式を評価します。
役職が Vice President または年収が 100000 以上の Dallas（ダラス）市の従業員を見 つけ出す問合せを検討します。
SELECT * FROM EMP, DEPT WHERE
(EMP.DEPTNO = DEPT.DEPTNO AND LOC = 'DALLAS' AND SAL > 100000) OR
(EMP.DEPTNO = DEPT.DEPTNO AND LOC = 'DALLAS' AND JOB_TITLE = 'VICE PRESIDENT')
オプティマイザは、問合せを次のように変換した方が効率よく評価できることが わかっています。
SELECT * FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND LOC = ‘DALLAS’ AND
(SAL > 100000 OR JOB_TITLE = 'VICE PRESIDENT');
この変換済問合せの場合、DEPT のそれぞれの行につき結合述語と LOC 上の述語 を 2 度評価するかわりに、それぞれの行につき 1 度評価するだけです。 述語のプッシュダウンとプルアップ 複雑な問合せは、ビューや副問合せに適用される多数の述語を持つ複数のビュー や副問合せを含むことがあります。Oracle は、よりパフォーマンスに優れた新し い問合せを生成するため、述語をビューに入れたり、またはビューから出すこと ができます。
単一表のビューを使用して、述語のプッシュ/ダウンを示すことができます。
CREATE VIEW EMP_AGG AS SELECT DEPTNO, AVG(SAL) AVG_SAL, FROM EMP GROUP BY DEPTNO; ここで、次の問合せが実行されたと想定します。
SELECT DEPTNO, AVG_SAL FROM EMP_AGG WHERE DEPTNO = 10;
Oracle は述語 DEPTNO=10 をビューにプッシュし、問合せを次の SQL に変換しま す。
SELECT DEPTNO, AVG(SAL) FROM EMP WHERE DEPTNO = 10 GROUP BY DEPTNO; この変換済問合せにより、GROUP-BY 操作の前に DEPTNO=10 述語が適用され、 集計対象のデータ量が大幅に削減されます。 Oracle は、WHERE 句条件を外から問合せブロックにプッシュする、問合せブロッ クから条件をプルする、および結合されている問合せブロックで結合する別の表 の条件に変換するための多数の高度な技術を備えています。WHERE 句条件が伝 播可能な場合は、行をフィルタ・アウトする機会を可能にし、初期段階で処理対 象にセットされるデータのサイズを削減することがあります。したがって、結合 や GROUP-BY などの以降の操作は非常に小さいデータ・セットに適用されるため、 さらに効率的に実行できます。さらに、述語プッシュダウン/プルアップは、新し い述語の追加なしには不可能だった新しいアクセス・パスを可能にすることによ り、パフォーマンスを向上させる場合もあります。 CUBE 問合せ用のグループ・プルーニング SQL CUBE 式は､SQL の group-by 演算子の拡張であり、1 つの SQL 文で複数の集 計操作を検索できます。CUBE 式を持つビューを含む問合せの場合、問合せの評 価に必要なデータ量を削減することが可能になります。たとえば、次の問合せを 検討します。
SELECT MONTH, REGION, DEPARTMENT FROM (SELECT MONTH, REGION, DEPARTMENT,
SUM(SALES_AMOUNT) AS REVENUE FROM SALES GROUP BY CUBE (MONTH, REGION, DEPT)) WHERE MONTH = ‘JAN-2001’;
この問合せは、次の SQL に変換できます。
SELECT MONTH, REGION, DEPARTMENT FROM (SELECT MONTH, REGION, DEPARTMENT,
SUM(SALES_AMOUNT) AS REVENUE FROM SALES WHERE MONTH = ‘JAN-2001’
GROUP BY MONTH, CUBE(REGION, DEPT)) WHERE MONTH = ‘JAN-2001’;
集計対象のデータ量が大幅に削減され、集計数もさらに削減されるため（2001 年 1 月のデータのみの集計が必要）、この変換済 SQL に伴う集計は非常にわずかで す。これらのツールは、CUBE 演算子を含んだビューで事前定義されている論理 「立方体」を問い合せることがあるため、これは分析アプリケーション向けの SQL
外部結合から内部結合への変換 状況によっては、問合せ内の外部結合が内部結合と同じ結果を返す可能性があり ます。このような場合、オプティマイザは、外部結合を内部結合に変換します。 この変換により、問合せが外部結合の場合は不可能な、追加のビュー・マージン グまたは新しい結合順序を選択できます。
コストベースの問合せ変換
マテリアライズド・ビューのリライト 頻繁に使用されるデータの事前計算およびマテリアライズド・ビュー形式での保 存は、問合せ処理を大幅にスピードアップします。問合せ内の 1 つまたは複数の 表参照を 1 つのマテリアライズド・ビュー参照で置換できるよう、SQL 問合せを 変換できます。マテリアライズド・ビューがオリジナルの表よりも小さい場合、 またはより利用しやすいアクセス・パスを持っている場合、変換済 SQL 文は、オ リジナルの文と比較し、より高速に実行できます。 たとえば、次のマテリアライズド・ビューを検討します。CREATE MATERIALIZED VIEW SALES_SUMMARY
AS SELECT SALES.CUST_ID, TIME.MONTH, SUM(SALES_AMOUNT) AMT FROM SALES, TIME
WHERE SALES.TIME_ID = TIME.TIME_ID GROUP BY SALES.CUST_ID, TIME.MONTH;
このマテリアライズド・ビューは、次の問合せの最適化に使用できます。
SELECT CUSTOMER.CUST_NAME, TIME.MONTH, SUM(SALES.SALES_AMOUNT) FROM SALES, CUSTOMER, TIME
WHERE SALES.CUST_ID = CUST.CUST_ID AND SALES.TIME_ID = TIME.TIME_ID
GROUP BY CUSTOMER.CUST_NAME, TIME.MONTH;
リライトされた問合せは、次のとおりです。
SELECT CUSTOMER.CUST_NAME, SALES_SUMMARY.MONTH, SALES_SUMMARY.AMT
FROM CUSTOMER, SALES_SUMMARY
WHERE CUSTOMER.CUST_ID = SALES_SUMMARY.CUST_ID;
この例では、変換済問合せは、いくつかの理由により、非常に高速になります。 sales_summary 表は、sales 表に比べ非常に小さく、変換済問合せに必要な結合が 1 つ少なく、集計は不要です。 Oracle は、できるかぎり広範な問合せセットに対してそれぞれのマテリアライズ ド・ビューを使用できるよう、マテリアライズド・ビュー向けの非常に堅牢なリ ライト方法セットを備えています。 Oracle のマテリアライズド・ビューの注目に値するもう 1 つの特性は、Oracle デー タベース内の宣言型ディメンションとの統合です。Oracle は、それぞれのディメ ンション内の階層を記述するディメンション・メタデータ・オブジェクトの作成 を許可しています。この階層化メタデータは、さらに洗練されたマテリアライズ ド・ビューのクエリー・リライトをサポートするために使用されます。たとえば、 月と四半期の階層関係を記述している時間のディメンションがある場合は、四半 期売上データを要求する問合せのサポートには、月間売上高データを含むマテリ アライズド・ビューを使用できます。
なお、マテリアライズド・ビューを使用する変換済問合せが必ずオリジナルの問 合せよりも効率的であるとはかぎりません。マテリアライズド・ビューが基本と する表よりも小さい場合でも、基本表の方がより広範囲に渡って索引付けされて いるため、高速アクセスを可能にします。最適な実行プランを選択する唯一の方 法は、マテリアライズド・ビューを使用した場合、および使用しない場合を計算 してそれぞれのコストを比較することです。Oracle は、まさにそれを行っている ため、マテリアライズド・ビューのリライトは、コストベース問合せ変換の一例 です。（マテリアライズド・ビューの詳細は、ホワイト・ペーパー 『Oracle9i のマテリアライズド・ビュー』を参照してください）。 OR 拡張 この方法は、WHERE 句内に OR を持つ問合せを OR なしの複数問合せの UNION ALL に変換します。OR が異なる表の制限を参照する場合、これは非常に便利で す。Oakland 市に入るまたは Oakland 市から出るすべての積み荷を検出する次の問 合せを検討します。
SELECT * FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID
AND (P1.PORT_NAME = 'OAKLAND' OR P2.PORT_NAME = 'OAKLAND')
この問合せは、次のとおり変換できます。
SELECT * FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID AND P1.PORT_NAME = 'OAKLAND'
UNION ALL
SELECT * FROM SHIPMENT, PORT P1, PORT P2 WHERE SHIPMENT.SOURCE_PORT_ID = P1.PORT_ID AND SHIPMENT.DESTINATION_PORT_ID = P2.PORT_ID
AND P2.PORT_NAME = 'OAKLAND' AND P1.PORT_NAME <> 'OAKLAND'
なお、それぞれの UNION ALL ブランチは、別々の最適化結合順序を持つことが できます。最初のブランチでは、Oracle は、P1 に課された制限を利用し、P1 を駆 動表として結合が行われ、2 番目のブランチでは P2 を駆動表として結合が行われ ます。結果のプランは、これらの表の索引とデータに依存しますが、オリジナル の問合せに比べ非常に高速になります。この変換がすべての問合せのパフォーマ ンスを改善することはないため、この問合せ変換は必然的にコストベースになり ます。 スター型変換 スター・スキーマは、データ・マートやデータ・ウェアハウスに一般的に使用さ れるモデリング方針です。スター・スキーマは一般的に、ファクト表と呼ばれる 1 つまたは複数の非常に大きな表を含みます。ファクト表にはトランザクション・ データが保存されます。また、ディメンション表と呼ばれる多数の小さいルック アップ表(検索表)に、説明データが保存されます。 Oracle は、スター型変換と呼ばれるスター・スキーマに照らし合わせて問合せを 評価する方法をサポートしています。この方法は、新しい副問合せをオリジナル SQL に追加する変換を適用することにより、スター・クエリーのパフォーマンス を向上させます。これらの新しい副問合せを使用することにより、ビットマップ・ 索引経由でファクト表にさらに効率よくアクセスできるようになります。
スター型変換を理解するために、例を検討します。2001 年の第 3 四半期における 州ごとの飲料の売上を返す次の問合せを検討してください。なお時間次元は DAY 表と QUARTER 表という 2 つの表で構成されているため、スノーフレーク・ディ メンションです。
SELECT STORE.STATE, SUM(SALES.AMOUNT) FROM SALES, DAY, QUARTER, PRODUCT, STORE
WHERE SALES.DAY_ID = DAY.DAY_ID AND DAY.QUARTER_ID = QUARTER.QUARTER_ID
AND SALES.PRODUCT_ID = PRODUCT.PRODUCT_ID AND SALES.STORE_ID = STORE.STORE_ID
AND PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES' AND QUARTER.QUARTER_NAME = '2001Q3'
GROUP BY STORE.STATE
変換済問合せは、次のとおりです。
SELECT STORE.STATE, SUM(SALES.AMOUNT) FROM SALES, STORE WHERE SALES.STORE_ID = STORE.STORE_ID
AND SALES.DAY_ID IN
(SELECT DAY.DAY_ID FROM DAY, QUARTER WHERE DAY.QUARTER_ID = QUARTER.QUARTER_ID AND QUARTER.QUARTER_NAME = '2001Q3') AND SALES.PRODUCT_ID IN
(SELECT PRODUCT.PRODUCT_ID FROM PRODUCT WHERE PRODUCT.PRODUCT_CATEGORY = 'BEVERAGES') GROUP BY STORE.STATE
変換済 SQL の場合、この問合せは、2 つの主要なフェーズで効率よく処理されま す。最初のフェーズでは、ビットマップ索引を使用してファクト表から必要なす べての行が検索されます。この場合、副問合せ述語に現れるのが day_id と product_id の 2 列であるため、ファクト表は、day_id および product_id 上のビット マップ索引を使用してアクセスされます。
問合せの第 2 フェーズ（再結合フェーズ）では、最初のフェーズからディメンショ ン表がデータ・セットに再結合されます。この問合せでは、select-list に現れる唯 一のディメンション表列が store.state であるため、結合が必要な表は store 表のみ です。問合せの最初のフェーズで、PRODUCT 、DAY および QUARTER を含んだ 副問合せが存在することは、第 2 にフェーズにおけるこれらの表の結合の必要性 を未然に防ぎ、問合せオプティマイザは、これらの結合をインテリジェントに除 去します。 スター型変換は、コストベースの問合せ変換であり、特定次元の副問合せの使用 が費用効率的かどうかの判断、およびリライトされた問合せがオリジナルより優 れているかどうかの判断は、いずれもオプティマイザのコスト見積りに基づきま す。 このスター・クエリー実行方法は、Oracle が特許を所有する固有のテクノロジで す。その他のベンダーもスター・クエリー用に同様の問合せ変換機能を提供して いますが、これに静的ビットマップ索引およびインテリジェントな再結合除去を 組み合せたベンダーはありません。
外部結合ビューに関する述語プッシュダウン その他の表に結合されるビューが問合せに含まれている場合、通常、問合せを最 適化するためビューをマージできます。ただし、外部結合を使用してビューが結 合される場合、ビューはマージできません。この場合、Oracle は、結合述語がビュー に挿入されるよう、固有な述語プッシュダウン操作を行います。この変換により、 ビュー内の表の 1 つにおいて索引を使用して外部結合を実行できるようになりま す。索引アクセスが最も効果的とは限らないため、この変換はコストベースです。
アクセス・パス選択
アクセス・パス選択の目的は、問合せ内の表を結合する順序、使用する結合方式、 およびそれぞれの表のデータのアクセス方法を決定することです。Oracle の EXPLAIN PLAN ファシリティまたは Oracle の v$sql_plan ビューを使用し、特定の 問合せに関してこの情報をすべて表示できます。 Oracle は、データベース構造および問合せ評価方法に関して、特別に豊富なセッ トを提供しているため、Oracle のアクセス・パス選択アルゴリズムは非常に洗練 されています。Oracle のアクセス・パス選択およびコスト・モデルは、この機能 を完全に具体化しているため、最適な方法で活用可能です。 Oracle のデータベース構造には、次のようなものがあります。 表構造 表（デフォルト） 索引構成表 ネストした表 クラスタ ハッシュ・クラスタ 索引構造 B ツリー索引 ビットマップ索引 ビットマップ結合索引 逆キーB ツリー索引 関数ベースの B ツリー索引 関数ベースのビットマップ索引 ドメイン索引 パーティション化方法 レンジ・パーティション化 ハッシュ・パーティション化 レンジ-ハッシュ・コンポジット・パーティション化 リスト・パーティション化 レンジ-リスト・コンポジット・パーティション化Oracle のアクセス方法は、次のとおりです。 索引アクセス方法 索引一意キー・ルックアップ 索引最大/最小ルックアップ 索引レンジ・スキャン 索引降順レンジ・スキャン 索引全体スキャン 索引高速全スキャン 索引スキップ・スキャン 索引 and-equal 処理 索引結合 索引 B ツリー/ビットマップ変換 ビットマップ索引 AND/OR 処理 ビットマップ索引・レンジ処理 ビットマップ索引 MINUS (NOT) 処理 ビットマップ索引 COUNT 処理 結合方式 ネステッド・ループ内部結合、外部結合、セミ結合、およびアンチ結合 ソート/マージ内部結合、外部結合、セミ結合、およびアンチ結合 ハッシュ内部結合、外部結合、セミ結合、およびアンチ結合 パーティション・ワイズ・ジョイン この文書は、すべての処理方法を説明するものではありませんが、Oracle のアク セス・パス選択の主要な特性をいくつか次に説明します。
結合順序
多数の表を結合する場合、使用可能なすべての実行プランの領域は非常に大きく なり、オプティマイザがこの領域を徹底的に調査するために、非常に時間がかか ります。たとえば、5 つの表を使用する問合せには、120（5! = 120）の可能な結合 順序があり、索引、アクセス方式、および結合方法の各種組合わせに基づけば、 それぞれの結合順序には数十もの可能な実行プランがあります。5 つの表を使用 する問合せでは、実行プランの総数は千単位になりますが、最も可能性の高い実 行プランの検討は可能です。ただし、10 個の表を結合する場合は、3,000,000 以上 の結合順序が存在し、100,000,000 以上の使用可能な実行プランが生じます。その ためオプティマイザは、使用可能な実行プランの調査ではインテリジェンスを使 用する必要があります。 適応検索方針 Oracle のオプティマイザは、多数の方法を使用して検索領域をインテリジェント に詰めていきます。注目に値する 1 つのテクニックは、Oracle が使用する適応検 索方針です。問合せが 1 秒で実行できる場合、問合せの最適化に 10 秒も使うこと は余分だと考えられます。一方、問合せの実行に数分または数時間かかる場合は、 より優れたプランを見つけるための最適化フェーズに数秒または数分使うことは有益です。Oracle は、複雑な問合せ用に十分な最適化時間をあてる一方、適応最 適化アルゴリズムを利用して、問合せの最適化時間が、予定の問合せ実行時間内 のわずかな割合になることを保証します。 データベース・システムによっては、DBA が最適化レベルを指定できるものもあ ります。最適化レベルは、問合せ最適化にあてる合計時間を制御します。ただし、 この適応検索方針は、最適化を制御するシステム・レベルのパラメータよりも効 率的な方法です。DBA は、システム・レベルのパラメータにより、すべての問合 せの最適化を均一に判断しますが、Oracle の適応検索方針は、個々の問合せにつ いて最適化のベスト・レベルを判断します。 複数の初期順序経験則 Oracle オプティマイザの検索アルゴリズムのもう 1 つの重要な点は、革新的な複 数の初期順序経験則です。オプティマイザが検索処理の初期に最適なプランを検 索すると、オプティマイザは早く終了します。そのため、この経験則は、ほとん ど最適化された実行プランまたは優れた実行プランである検索領域内の特定プラ ンの即時検出に関して、洗練された方式を使用します。オプティマイザは、ラン ダムに生成されたプランではなく、これらのプランで検索を開始します。問合せ 最適化に要する時間を大幅に削減するため、この経験則は、効率的な問合せ最適 化にとって極めて重大です。
ビットマップ索引
Oracle が特許を所有する革新的なビットマップ索引は、特にデータ・ウェアハウ ス・アプリケーションをはじめ、広く使用されています。その他のデータベース・ ベンダーは、動的ビットマップ索引を提供していますが、Oracle は、実ビットマッ プ索引（動的ビットマップ索引の他に）をサポートしています。実ビットマップ 索引は、索引の圧縮されたビットマップ表現がデータベースに保存される索引構 造です。一方、動的ビットマップ索引はデータベース内の B ツリー索引構造を問 合せ処理中にビットマップ構造に変換します。実ビットマップ索引は、通常の B ツリー索引に比べると領域を大幅に節約できるため、非常に重要なメリットをも たらします。このような領域の節約は、さらにディスク I/O 数の削減という形で パフォーマンス上のメリットも提供します。実ビットマップ索引は、1/10 の索引 記憶領域で 10 倍の速度で多数の問合せを処理できます。ビットマップ索引のメ リットに関する定量化の詳細は、ホワイト・ペーパー『Key Data Warehousing Features in Oracle9i: A Comparative Analysis』を参照してください。ビットマップ索引は、AND/OR 演算と組み合わされる複数の述語の評価に非常に 効率的です。その他、ビットマップ索引は、Oracle 標準整合性モデルを使用して いるため、ビットマップ索引付きの表の問合せと同時に DML 操作（挿入、更新、 削除）を実行する際、完全なデータ整合性が維持されます。 Oracle のビットマップ索引の豊富な機能により、問合せオプティマイザは、多数 の新しい実行方針を選択できます。Oracle の問合せオプティマイザは、実ビット マップ索引と動的ビットマップ索引上の両方の WHERE 句に含まれる AND/OR/NOT 条件に対応する索引を組み合わせたビットマップ操作の複雑なツ リーを含む実行プランを生成できます。ビットマップ上のこれらのブール演算は 非常に高速であり、ビットマップ演算を集中して利用できる問合せのパフォーマ
ンスは非常に優れています。 さらに、Oracle は、ビットマップ索引・クエリー用に、索引専用アクセスをサポー トしています。索引専用アクセスは、AND を行うビットマップ用の精密なアルゴ リズムを使用します。この精密さは、動的ビットマップ索引を使用し、ハッシン グに依存して ROWID リストを二分するデータベース・システムと対照的です。 （例は、http://as400bks.rochester.ibm.com/cgi-bin/bookmgr/BOOKS/EZ30XB00/2.4.1 を 参照してください） このようなシステムは、正しい結果を保証するため、動的ビットマップ索引を使 用している場合でも表アクセスを回避できません。対照的に、Oracle は、表をア クセスせずに広範な各種の問合せを評価できます。たとえば、ある銀行がカリフォ ルニア州内の既婚の顧客数を調査します。
SELECT COUNT(*) FROM CUSTOMER
WHERE STATE = 'CA' AND MARITAL_STATUS = 'MARRIED'
州および配偶者の有無（既婚/未婚）にビットマップ索引が付けられているとすれ ば、Oracle は、「CA（カリフォルニア）」および「既婚」用のビットマップのビッ ト単位の AND を実行して、結果のビットマップの 1 の数をカウントするだけです。 高度に圧縮された 2 つの索引構造にアクセスするだけで問合せ全体を実行できる ため、これは非常に高速で効率的な演算です。このような問合せ結果を索引から のみでは計算できないデータベース・システムは、表の何百万もの行のアクセス を強制され、非常に時間がかかるためパフォーマンスが低下する結果になります。 Oracle の実ビットマップ索引の精密さ（動的ビットマップ・索引との比較）は、 Oracle のスター型変換およびビットマップ結合索引の再結合除去機能にとっても 非常に重要です。精密なビットマップ演算を持たないデータベース・システムは、 再結合フェーズで必ずすべてのディメンション表を結合する必要があります。一 方、Oracle は、ディメンション表の最小セットを結合するだけです。 Oracle のビットマップ索引には、圧縮索引構造をとるその他の種類の索引に比べ、 重要な利点があります。たとえば、Oracle のビットマップ索引を使用して、値の 範囲にアクセスする際、問合せオプティマイザは、索引構造の一部のみがアクセ スされるようにスタート・キーとストップ・キーを生成します。これは、問合せ の実行のためにそれぞれの索引が常にスキャンされる（非常に浪費的な方法）エ ンコード済ベクター索引などの索引とは明らかに正反対のものです。 Oracle の問合せオプティマイザは索引の複数の種類を組み合わせて 1 つの表にア クセスできます。たとえば、ビットマップ索引をドメイン索引および B ツリー索 引と組み合わせて、特定の表にアクセスできます。ビットマップ索引と組み合せ るために、ドメイン索引と B ツリー索引が動的ビットマップ索引化の方法を使用 してアクセスされます。
ビットマップ結合索引
結合索引は、複数の表にまたがる索引構造であり、この表結合のパフォーマンス を向上させます。 結合索引は、索引付けされた列および索引内の ROWID/ビットマップ表現が他の 表を指している索引です。ビットマップ結合索引の定義の一部には、したがって、 表の行の一致方法を指定する結合条件も含まれます。索引付けされた列がディメ ンション表に属する場合は、通常、ファクト表に結合索引を作成します。たとえ ば、「売上」という名前のファクト表および「製品」という名前のディメンショ ン表が与えられているかぎり、売上についてのビットマップ結合インデックスを 作成できますが、索引付けされた列は製品表内の製品カテゴリであり、product_id 上の結合条件に左右されます。このような索引が存在する場合は、結合を行わず に、索引から直接、特定の製品カテゴリ内の製品についてすべての売上行を検索 できます。ビットマップ結合インデックスには、次のとおり 2 つの主要な利点が あります。 1. カーディナリティの削減: 区別可能な製品カテゴリの数は、製品 ID 数よ りもはるかに少ない場合があります。そのため、ビットマップ結合索引は、 ファクト表の結合列上のどの索引よりも低いカーディナリティを持つこ とになり、ビットマップ索引用に小さいサイズに変換されます。 2. 再結合の排除: 多くの場合、ビットマップ結合索引が使用されるかぎり、 問合せから結合を排除できます。 複数の索引を組み合せるビットマップ・テクノロジを使用し、同じアクセス・パ ス内で、ビットマップ結合インデックスを非結合索引と同時に使用できます。一 般的に、これらの他の索引は、ビットマップ結合索引との組合せで機能するスター 型変換のために使用されます。ドメイン索引と拡張性
Oracle は、空間データ、イメージ、ビデオ・クリップなどのカスタマイズされた 複雑なデータ型への効率的なアクセスを提供するため、アプリケーションのドメ イン索引をサポートします。このような索引は、Oracle 組込み索引ではありませ んが、プロパティは Oracle に登録できます。Oracle のオプティマイザは拡張可能 なため、ドメイン索引およびユーザー定義ファンクションには統計やコスト関数 を関連付けることができます。Oracle の組込み索引/関数と同じコスト・モデルお よび検索領域内で考慮され、Oracle のビットマップ・テクノロジを使用して、同 じアクセス・パス内の通常の Oracle 索引と組み合せることもできます。高速全索引スキャン
高速全索引スキャンは、索引のようなツリー・ベースの順序ではなく、表として 索引全体をスキャンする機能です。表アクセスを不要にする目的で必要なすべて の表列が索引に含まれている場合、これを使用できます。マルチブロック・ディ スク I/O を最大限活用できるため、またレンジ・スキャンよりもパラレル化が可 能なため、大量データを検索する場合、高速全スキャンが有効です。索引は表に 比べ非常に小さいため、表自体よりも索引をスキャンする方がコストがかかりま せん。索引結合
索引結合を使用することにより、必要なすべての列を含んでいる単一の索引がな い場合に、表からの列のサブセットを複数の索引から再構築できます。必要な表 列をすべて含んでいる索引のセットがある場合、オプティマイザは、潜在的に高 価な表アクセスを避け、ROWID または全体スキャンによってこれらの索引を使用 することができます。最初に、WHERE 句条件がある場合はそれが索引に適用さ れ、索引項目のセットが戻されます。異なる索引からの結果項目が ROWID 値に よって結合されます。このアクセス方式は、基礎となる表には多数の列が存在す るにもかかわらず、問合せには少数の列のみ必要な場合に有効です。索引スキップ・スキャン
索引スキップ・スキャンは、先頭列にスタート/ストップ・キーが存在しない場合 でも、オプティマイザが複数列索引を利用できるようにした機能です。索引の先 頭列ではなく末尾列に選択述語が含まれる場合、先頭列の値の数が比較的制限さ れているという条件があるかぎり、オプティマイザは索引を利用できる可能性が あります。先頭列のそれぞれの値に関して、適切なリーフ・ブロックに効率的に 到達するため選択末尾列を使用して索引がプローブされます。そのため、限定さ れた数のスキップに基づいて索引を横断できます。それぞれのスキップ・プロー ブは、非常に効率的です。問合せに完全に一致する索引がなく、唯一の代替案が 全表スキャンまたは全索引スキャンの実行である場合、この方法は状況を改善す るために非常に有効です。パーティション最適化
パーティション化は、管理可能性に関する重要な機能です。Oracle は、表と索引 の両方でパーティション化をサポートしています。Oracle は各種のパーティショ ン化方式をサポートしていますが、特にデータのローリング・ウィンドウが一般 的なデータ・ウェアハウスでは、レンジ・パーティション化（または複合パーティ ション化レンジ/ハッシュまたはレンジ/リスト）がおそらく最も有効なパーティ ション方式です。データ・レンジ上のレンジ・パーティション化は、データ・ウェ アハウス内のロード/ドロップ・サイクルに対して、効率と管理しやすさの両方で 非常に大きなメリットをもたらします。 ただし、パーティション化は、問合せ処理にも有効で、Oracle のオプティマイザ は、パーティションに完全対応しています。また、意思決定支援問合せは特定の 時間間隔を制限する条件を含んでいることが多いため、日付レンジ・パーティショ ン化は概して断然一番重要な方法です。2 年分の売上データが月ごとにパーティ ション化されて保存されているケースを検討します。最近の 3 か月の売上合計を 計算します。Oracle のオプティマイザは、過去 3 か月のパーティションをスキャ ンする方法がデータにアクセスする最も効率的な方法であることを認識します。 この方法であれば、適切なデータが正確にスキャンされます。レンジ・パーティ ション化を持たないシステムは、表全体をスキャンするか索引を使用する必要が ありますが、アクセスするデータ量が多い場合は非常に効率の悪いアクセス方式 です。不要部分のスキャンを回避するオプティマイザの機能は、パーティション・ プルーニングと呼ばれます。パーティション・ワイズ・ジョイン、GROUP-BY、ソート
表のパーティション化キーを伴う特定の操作の場合は、パーティション・ワイズ・ ベースで実施できます。たとえば、売上表が日付によってレンジ・パーティショ ン化されていたとします。問合せが日付順の売上レコードを要求した場合、Oracle のオプティマイザは、（パーティション・ワイズ・ベースで）それぞれのパーティ ションが個々にソートでき、したがって単に連結するだけで結果が出ることを認 識します。それぞれのパーティションを別々にソートすることは、一度に表全体 をソートする方法に比べ、非常に効率的です。結合および GROUP-BY 操作に対し て、同様の最適化が行われます。ソートの排除
大量データのソートは、問合せ処理でも最も資源を消費する操作の 1 つです。 Oracle は、不要なソート操作を排除します。ソート（DISTINCT、GROUP BY、 ORDER BY に関する）が排除可能である理由はいくつかあります。たとえば、索 引の使用により、行がすでに正しい順序であること、または一意であることを保 証できます。一意な制約は 1 行のみが返されることを保証できます。ORDER BY 列にすべて同じ値が返るため、ソートが不要であることがわかる場合もあります。 ソート・マージ結合によって既に正しい順序にソート済みであることもあります。 Oracle のオプティマイザは、問合せ内である表にその他すべての表を結合した後、 その表にどの索引を使用するかのローカルな意志決定の方が ORDER BY ソート を排除できるかどうかの判断よりも大きな影響力があることを認識しています。 この判断は、適用される結合順序および結合方式（表から索引が選択された時点 では不明の）などの要素の影響も受けます。したがって、ローカルに最適化され た選択項目、最適な索引、および最も安価な結合方式などに基づいて実行プラン を作成する他、オプティマイザは、正しい索引、結合方式および結合順序を選ぶ ことにより、グローバル・ソートの排除を目的とした実行プランの生成を試みま す。全体のコストが算出されると、ソートの排除を行うプランは、ローカルで最 も安価な操作に基づくプランよりも有効な場合があります。OLAP 最適化
Oracle は、OLAP で通常使用される各種の SQL 構文をサポートしています。 SQL 解析関数の他、CUBE、ROLLUP、またはグルーピング・セット構成メンバー を持つ問合せを処理する場合、グルーピングの数が大きい可能性があるため、大 量のソートが必要になる場合があります。ただし、1 つのグルーピングによって データがすでにソートされている場合は、グルーピング列の釣り合い次第で他の グルーピングのためのソートの必要性を排除（またはそのコストを削減）できる 場合があります。そのため、グルーピング・ソートの実行方法を工夫することで、 パフォーマンスを大幅に向上できます。Oracle のオプティマイザは、1 つのソート の結果が次のソートをスピードアップするか排除できるよう、高度なアルゴリズ ムを使用して、ソートの順序付けおよびグルーピング列の並べ替えを行います。パラレル実行
パラレル実行は、複数のプロセス（通常は複数 CPU、場合によっては複数ノード を使用）を 1 つの SQL 文の実行に適用する機能です。並列性は、大きなデータセッ トの問合せおよび管理に関する基本的な機能です。 Oracle のパラレル実行アーキテクチャを使用すると、実質的にどの SQL 文も任意 の並列度で実行できます。なお、並列度は、基本データベース・オブジェクトの パーティション化スキームには基づいていません。問合せ対象の表のサイズや問 合せを発行しているユーザーの優先順位などの要因に基づいて、Oracle は、並列 度を調整できるため、柔軟なパラレル・アーキテクチャにより大きなメリットが 提供されます。 オプティマイザは、パラレル実行を完全に取り込み、パラレル実行の影響を考慮 して、最善の実行プランを選択します。ヒント
ヒントは、SQL 問合せに追加する述語で、実行プランに影響を及ぼします。 ヒントは、オプティマイザが高効率でないプランを選択するまれなケースに対処 するために最も一般的に使用されますが、マーケティングや販売プレゼンテー ションで誤解または誤用されることもあります。ヒントの存在は、オプティマイ ザの弱点であると断言する人もいますが、ほとんどの主要データベース製品 （Oracle、Microsoft SQL Server、IBM Informix、Sybase）は、オプティマイザ・ディレクティブのなんらかの形をサポートしているのが事実です。いかなるオプティ マイザも完璧ではなく（たとえば、1999 年の International DB2 Users Group Meeting での IBM Almaden Research Center によるプレゼンテーション『Why does DB2's optimizer generate wrong plans?』を参照してください）、Oracle のヒントのような ディレクティブは、オプティマイザが高効率でないプランを選択した場合に最も シンプルな回避策を提供します。 高効率でないプランを修正するだけではなく、アクセス・パスを試してみたいユー ザー、または単に問合せの実行を完全に制御したいユーザーにとっても、ヒント は有効なツールです。たとえば、ユーザーは、問合せ内の高効率でない索引のパ フォーマンスと最適索引のパフォーマンスとを比較できます。最適索引が特定の 問合せでのみ使用され、高効率でない索引が多数の異なる問合せによって使用さ れている場合、パフォーマンスの違いが小さく問題にならないかぎり、ユーザー は、最適索引の削除を検討できます。ヒントによって、このようなパフォーマン スに関する実験を簡単かつ迅速に実施できます。（ヒントがない場合、この実験 を行うユーザーは、最適索引を削除してから再構築する必要があります） ヒントは、パフォーマンスに関する問題を解決するため、例外的な場合にのみ使 用するよう設計されています。これらのヒントにより、状況の変化（表の拡大化、 索引の追加など）に応じてオプティマイザが実行プランを調整することが防止さ れる可能性、および古いオプティマイザ統計などの問題が不可視になる可能性が あるため、例外的に使用する目的で提供されています。実際ヒントの過剰使用は パフォーマンスに悪影響を及ぼします。ヒントの適切な使用方法の一例は、Oracle 独自のアプリケーションです。Oracle の E-Business Suite 11i の高度に調整された
ERP モジュールは、モジュール内の 270,000 SQL 文の 0.3%でヒントを利用してい ます。
コスト・モデルと統計
コストベース・オプティマイザは、各種の代替実行プランのコストを見積もり、 最適（最低）コスト見積りのプランを選択します。コスト・モデルの精度は、オ プティマイザが最適実行プランを認識および選択する機能に直接影響するため、 コスト・モデルは、オプティマイザに含まれる不可欠な要素です。 実行プランのコストは、実行プランのそれぞれのコンポーネントの慎重なモデリ ングに基づいています。コスト・モデルには、Oracle のアクセス方式およびデー タベース構造の詳細情報が組み込まれるため、操作のそれぞれのタイプに関する 精確なコストを保証できます。さらに、コスト・モデルは、データベース内のオ ブジェクトおよびハードウェア・プラットフォームの性能特性を描写するオプ ティマイザ統計に依存します。この統計については、次に詳しく説明します。 コスト・モデルが効率よく機能するには、精確な統計が必要です。Oracle は、統 計収集の簡素化および自動化に役立つ多数の機能を備えています。 コスト・モデルは、問合せオプティマイザの非常に洗練されたコンポーネントで す。コスト・モデルは、データベースが提供しているそれぞれのアクセス方式を 理解するだけではなく、キャッシングがパフォーマンスに及ぼす影響、I/O の最適 化、パラレル化、およびその他のパフォーマンス特性も考慮する必要があります。 その上、コストの定義は複数存在します。最初の行または最初の N 行セットを返 すための所要時間を最短にすることが問合せ最適化の目的であるアプリケーショ ンもあれば、最小時間内に結果セット全体を返すことが目的のアプリケーション もあります。Oracle のコスト・モデルは、DBA の作業環境に応じて異なる種類の コストを算出することにより、両方の目的をサポートします。オプティマイザ統計
問合せを最適化する際、オプティマイザは、コスト・モデルに基づいて実行プラ ンに伴う操作（結合、索引スキャン、表スキャンなど）のコストを見積もります。 このコスト・モデルは、基盤となるハードウェア・プラットフォーム、および SQL 問合せに必要とされるデータベース・オブジェクトのプロパティに関する情報に 依存します。Oracle の場合、この情報、すなわちオプティマイザ統計には、オブ ジェクト・レベル統計とシステム統計の 2 種類があります。 オブジェクト・レベル統計 オブジェクト・レベル統計は、データベース内のオブジェクトを描写します。 この統計は、それぞれの表のブロック数、行の数、およびそれぞれの B ツリー索 引内のレベル数などの値を追跡します。それぞれの表の列を描写する統計もあり ます。列の統計は、それぞれの問合せの WHERE 句内の条件と突き合わせる行数 の見積りに使用されるため、特に重要です。どの列についても、Oracle の列統計 は、最小値、最大値および個々値の数を示します。 その他、Oracle は、傾斜分布を持つ列に対する問合せをより最適化するため、ヒ ストグラムをサポートしています。Oracle は、高さ調整済ヒストグラムと頻度ヒストグラムの両方に対応し、列の正確な特性に応じて適切な種類のヒストグラム を自動的に選択します。 システム統計 システム統計は、ハードウェア・プラットフォームの性能特性を描写します。 オプティマイザのコスト・モデルは、CPU コストと I/O コストを区別します。 ただし、CPU の速度は、システムの種類によって大きく異なる他、CPU パフォー マンスと I/O パフォーマンスの比率にも非常に大きな違いがあります。そのため、 CPU コストと I/O コストを組み合せる固定式に頼らず、Oracle は、個々のシステ ムの特性に関する情報を通常の作業負荷時に収集し、それぞれのシステムについ てこれらのコストを組み合せる最適な方法を判断する機能を備えています。修正 される情報には、CPU 速度と各種 I/O のパフォーマンス（オプティマイザは I/O 統計を収集する際、単一ブロック、複数ブロックおよび直接ディスク I/O を区別 します）を含みます。ハードウェア環境ごとにシステム統計をカスタマイズする ことにより、いかなる組合せのハードウェア・ベンダーのどのような機器構成に 対しても、Oracle のコスト・モデルは、非常に精確です。 ユーザー定義統計 Oracle は、ユーザー定義関数およびドメイン索引用にユーザー定義のコスト関数 もサポートしています。Oracle の機能を顧客独自の関数と索引で拡張する顧客は、 独自のアクセス方式を Oracle のコスト・モデルに完全に統合できます。Oracle の コスト・モデルは、モジュール形式をとっているため、ユーザー定義の統計は、 Oracle 独自の組込み索引/関数と同様のコスト・モデル内および同様の検索領域内 にあると考えられます。
統計管理
トランザクション処理または新しいデータのデータ・ウェアハウスへのロードの ため、時間の経過とともにデータが変化するため、データベースのプロパティも 変化する傾向があります。オブジェクト・レベルのオプティマイザ統計を精確に 維持するには、基盤となるデータが変更された場合にこのような統計を更新する 必要があります。統計収集の問題は、DBA にいくつかの課題を突きつけます。 統計収集は、大規模データベースの場合、資源を非常に多く使用する可能性が あります。 どの表が更新済統計を必要としているかは、簡単には判断できません。多くの 表に対する変更が少ない場合は、そのような表の統計を再算出することは 資源の浪費になります。ただし、数千の表を持つデータベースの場合、DBA がそれぞれの表の変更レベルおよびどの表が新しい統計を必要としてい るかを手作業で追跡することは簡単ではありません。 どの列がヒストグラムを必要としているかは簡単には判断できません。ヒスト グラムを必要とする列もあれば、必要としない列もあります。必要として いない列にヒストグラムを作成することは、時間と領域の浪費です。ただ し、必要としている列にヒストグラムを作成しない場合は、オプティマイ ザの判断ミスにつながります。Oracle の統計収集ルーチンは、このような課題に対処します。 パラレル・サンプリング 効率的な統計収集を可能にする基本的な機能がサンプリングです。表全体をス キャン（およびソート）して統計を収集するかわりに、行の小さいサンプルを調 べることにより、有用な統計が収集できます。サンプリングは、表スキャンに要 する時間の他、データ処理に要する時間も大幅に削減するため（ソートおよび分 析するデータが少ないため）、大幅なサンプリングによるスピードアップが実現 します。パラレル化とサンプリングを組み合せて使用することにより、大規模デー タベースの統計収集をさらにスピードアップできます。Oracle の統計収集ルーチ ンは、基盤となる表のデータ特性に基づいて、適切なサンプリング・パーセント と適切な並列（パラレル）度を自動的に判断します。 監視 統計管理を簡素化するためのもう 1 つの主要な機能は監視です。Oracle は、最終 の統計収集以降に表に加えられた変更（挿入、更新および削除）の数を追跡しま す。新しいオプティマイザ統計に値する十分な変更が加えられた表は、監視プロ セスにより自動的にマークされます。DBA が統計を収集する場合、Oracle は、十 分な修正が行われた表のみの統計を収集します。 ヒストグラムの自動的決定 Oracle の統計収集ルーチンも、どの列がヒストグラムを必要とするかを暗黙的に 判断します。Oracle は、2 つの特性、それぞれの列のデータ分布、および SQL 文 の WHERE 句に列が現れる頻度を検討してこれを判断します。非常に傾斜した分 布を持ち、WHERE 句によく現れる列については、Oracle はヒストグラムを作成 します。 これらの機能の組合せは、オプティマイザ統計収集処理を事実上自動化します。 DBA は、1 つのコマンドでスキーマ全体の統計を収集できます。Oracle は、どの 表が新しい統計を必要とするか、どの列がヒストグラムを必要とするか、および それぞれの表に適したサンプリング・レベルと並列度を暗黙的に判断します。
動的サンプリング
最適な問合せ実行には、精確な統計だけでは不十分な場合があります。オプティ マイザ統計は、実際のデータベース・オブジェクトの大まかな描写として定義付 けられます。場合によっては、これらの静的統計は不完全です。Oracle は、問合 せの最適化中に動的に収集される追加統計で静的オブジェクト・レベル統計を補 足することにより、これらのケースに対処します。 静的オプティマイザ統計が不適切と考えられるのは、主に次の 2 つのケースです。 相関関係: 問合せは、1 つの表に 2 つ以上の条件がある複雑な WHERE 句を持 つことがよくあります。非常にシンプルな例を次に示します。SELECT * FROM EMP
WHERE JOB_TITLE = 'VICE PRESIDENT' AND SAL < 40000
この簡単な最適化アプローチでは、この 2 つの列が独立していると仮定し ています。すなわち、従業員の 5%が「Vice President」であり、40%が 40,000 未満の年収を受け取っている場合、このシンプルなアプローチでは、従業 員の 0.05 × 0.40 = 0.02 がこの問合せの両方の基準に一致すると見なします。 このシンプルなアプローチでは、この場合は不正確です。「 Vice President」 という Job_title を持つ従業員は高収入である可能性が高いため、Job_title と給与は相関関係があります。シンプルなアプローチでは、この問合せが 行の 2%を返すことになりますが、この問合せは実際には行を返さない可 能性があります。 それぞれの列についての情報を個々に保存している静的オプティマイザ 統計は、どの列が相関関係にあるかをオプティマイザに指し示すことはあ りません。その上、可能な列の組合せの数は幾何学級的に膨大になるため、 統計を保存して相関関係を理解することは困難です。 一時データ: アプリケーションによっては、一時的に表に保存される中間結果 セットを生成するものもあります。結果セットは、その先の操作の基礎と して使用された後、削除されるか単にロールバックされます。データは、 短時間のみ存在し、コミットされない可能性もあるため、中間結果が保存 される一時表についての精確な統計を収集することは非常に困難です。 DBA がこれらの一部オブジェクトの静的統計を収集する機会はありませ ん。 Oracle の動的サンプリング機能は、これらの問題を解決します。問合せの最適化 中、オプティマイザは、列セットに相関関係があること、または表の統計がない ことに気付く場合があります。そのような場合、オプティマイザは、適切な表か ら行の小さいセットをサンプリングし、適切な統計をその場で収集します。相関 関係の場合、WHERE 句内のすべての関連条件がこれらの行に同時に適用され、 相関関係の影響度が直接測定されます。問合せと同じトランザクション内でサン プリングが発生するため、データがコミットされていなくても、オプティマイザ はユーザーの一時データを見ることができるため、この動的サンプリング方法は 一時データにも非常に効果的です。
