| Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) B71277-09 |
|
 前 |
 次へ |
オプティマイザは、複数の問合せ変換技法を使用します。この章では、最も重要なことについて説明します。
この章の内容は次のとおりです。
関連項目:
OR拡張では、オプティマイザは、OR演算子を含むWHERE句がある問合せを、UNION ALL演算子を使用する問合せに変換します。
データベースでは、様々な理由でOR拡張が実行されます。たとえば、より効率的なアクセス・パスや、デカルト積を回避する代替の結合方法を有効にできます。通常と同じように、オプティマイザは、変換された文のコストが元の文のコストよりも低い場合にのみ、拡張を実行します。
次の例では、ユーザーshが、sales.prod_idおよびsales.promo_id列で連結索引を作成し、OR条件を使用してsales表を問い合せます。
CREATE INDEX sales_prod_promo_ind ON sales(prod_id, promo_id); SELECT * FROM sales WHERE promo_id=33 OR prod_id=136;
前述の問合せでは、promo_id=33およびprod_id=136条件はそれぞれ、索引アクセス・パスを活用できるため、オプティマイザは、文を例5-1の問合せに変換します。
例5-1 変換された問合せ: UNION ALL条件
この例の問合せ変換では、オプティマイザは、索引を使用してsales表にアクセスする実行計画を選択し、結果をアセンブルします。計画を、例5-2に示します。
SELECT * FROM sales WHERE prod_id=136 UNION ALL SELECT * FROM sales WHERE promo_id=33 AND LNNVL(prod_id=136);
例5-2 salesの問合せの実行計画
-------------------------------------------------------------------------------- | Id| Operation | Name | Rows| -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | CONCATENATION | | | | 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES | 710 | | 3 | INDEX RANGE SCAN | SALES_PROD_PROMO_IND | 710 | | 4 | PARTITION RANGE ALL | | 229K| | 5 | TABLE ACCESS FULL | SALES | 229K| --------------------------------------------------------------------------------
ビューのマージでは、オプティマイザは、ビューを表す問合せブロックを、それを包含する問合せブロックにマージします。ビューのマージにより、オプティマイザが追加の結合順序、アクセス方法およびその他の変換を考慮できるようになり、計画を向上させることができます。
たとえば、ビューがマージされ、複数の表が1つの問合せブロックに存在するようになると、ビューの内部の表で、オプティマイザが結合の絞込みを使用してビューの外部の表を削除できるようになります。マージにより常に計画が改善されるような特定の単純なビューの場合、オプティマイザは、コストを考慮せずに自動的にビューをマージします。それ以外の場合、オプティマイザは、コストを使用して判断を行います。オプティマイザは、コストや妥当性の制約など、多くの理由でビューをマージしないことを選択することもあります。
OPTIMIZER_SECURE_VIEW_MERGINGがtrue (デフォルト)の場合、Oracle Databaseでは、ビューのマージおよび述語のプッシュによってビュー作成者のセキュリティ意図が侵害されないことを確認するために、チェックが実行されます。特定のビューでこれらの追加のセキュリティ・チェックを無効にするには、このビューのユーザーにMERGE VIEW権限を付与します。特定のユーザーのすべてのビューで追加のセキュリティ・チェックを無効にするには、そのユーザーにMERGE ANY VIEW権限を付与します。
注意:
ヒントを使用して、妥当性ではなく、コストやヒューリスティックが原因で拒否されたビューのマージをオーバーライドすることができます。
この項の内容は次のとおりです。
関連項目:
MERGE ANY VIEW権限およびMERGE VIEW権限の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
OPTIMIZER_SECURE_VIEW_MERGING初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
オプティマイザは、個別の問合せブロックによるネストされた副問合せまたはマージされていないビューをそれぞれ表します。データベースは、問合せブロックを下位から上位へ順番に最適化します。このため、データベースは最も内側の問合せブロックを最初に最適化し、その計画の一部を生成して、問合せ全体を表す外側の問合せブロックの計画を生成します。
パーサーは、問合せで参照される各ビューを個別の問合せブロックに拡張します。ブロックは、基本的にはビュー定義を表しますが、このため必然的にビューの結果も表すことになります。オプティマイザの1つのオプションは、ビューの問合せブロックを個別に分析し、サブプランを生成して、実行計画全体の生成にビュー・サブプランを使用することで、残りの問合せを処理することです。ただし、この手法では、ビューが個別に最適化されるため、最適ではない実行計画となる可能性があります。
ビューのマージにより、パフォーマンスが向上する場合があります。例5-3に示されているように、ビューのマージでは、ビューから外側の問合せブロックに表がマージされ、内側の問合せブロックが削除されます。そのため、ビューの個別の最適化は必要ありません。
単純ビューのマージでは、オプティマイザは選択表示結合ビューをマージします。たとえば、employees表の問合せにはdepartments表とlocations表を結合する副問合せが含まれます。
マージ後は追加の結合順序およびアクセス・パスが使用可能になるため、単純ビューのマージにより、多くの場合、より最適な計画が作成されます。次の理由により、単純ビューのマージでは、ビューが有効にならない場合があります。
次のような、選択表示結合ビューに含まれていない構成メンバーがビューに含まれている。
GROUP BY
DISTINCT
外部結合
MODEL
CONNECT BY
集合演算子
集計
ビューのSELECTリスト内に副問合せが含まれている。
外側の問合せブロックにPL/SQLファンクションが含まれている。
ビューが外部結合に含まれており、ビューをマージできるかどうかを判断する複数の追加の妥当性要件の1つを満たさない。
例5-3 単純ビューのマージ
次の問合せでは、hr.employees表がdept_locs_vビューに結合されます。このビューは、各部門の所在地を戻します。dept_locs_vは、departments表とlocations表が結合されたものです。
SELECT e.first_name, e.last_name, dept_locs_v.street_address,
dept_locs_v.postal_code
FROM employees e,
( SELECT d.department_id, d.department_name,
l.street_address, l.postal_code
FROM departments d, locations l
WHERE d.location_id = l.location_id ) dept_locs_v
WHERE dept_locs_v.department_id = e.department_id
AND e.last_name = 'Smith';
データベースは、departmentsおよびlocationsを結合してビューの行を生成し、この結果をemployeesに結合することで、この問合せを実行できます。問合せにはdept_locs_vビューが含まれており、このビューには2つの表が含まれているため、オプティマイザは、次の結合順序のいずれかを使用する必要があります。
employees、dept_locs_v(departments、locations)
employees、dept_locs_v(locations、departments)
dept_locs_v(departments、locations)、employees
dept_locs_v(locations、departments)、employees
また、結合方法にも制約があります。このビューの列には索引が存在しないため、索引ベースのネステッド・ループ結合は、employeesで始まる結合順序では実行できません。オプティマイザは、ビューのマージを行わずに、次の実行計画を生成します。
-----------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 (15)|
|* 1 | HASH JOIN | | 7 (15)|
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 (0)|
|* 3 | INDEX RANGE SCAN | EMP_NAME_IX | 1 (0)|
| 4 | VIEW | | 5 (20)|
|* 5 | HASH JOIN | | 5 (20)|
| 6 | TABLE ACCESS FULL | LOCATIONS | 2 (0)|
| 7 | TABLE ACCESS FULL | DEPARTMENTS | 2 (0)|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - access("E"."LAST_NAME"='Smith')
5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
ビューのマージでは、ビューから外側の問合せブロックに表がマージされ、内側の問合せブロックが削除されます。ビューのマージ後、問合せは次のようになります。
SELECT e.first_name, e.last_name, l.street_address, l.postal_code FROM employees e, departments d, locations l WHERE d.location_id = l.location_id AND d.department_id = e.department_id AND e.last_name = 'Smith';
3つの表すべてが1つの問合せブロックにあるため、オプティマイザは、次の6つの結合順序から選択できます。
employees、departments、locations
employees、locations、departments
departments、employees、locations
departments、locations、employees
locations、employees、departments
locations、departments、employees
これで、employeesおよびdepartmentsへの結合は、索引ベースで実行できるようになりました。ビューのマージ後、オプティマイザは、ネステッド・ループを使用する、より効率的な次の計画を選択します。
-------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 (0)|
| 1 | NESTED LOOPS | | |
| 2 | NESTED LOOPS | | 4 (0)|
| 3 | NESTED LOOPS | | 3 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 (0)|
|* 5 | INDEX RANGE SCAN | EMP_NAME_IX | 1 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 (0)|
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 0 (0)|
|* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 0 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID | LOCATIONS | 1 (0)|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."LAST_NAME"='Smith')
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
関連項目:
単純ビューのマージの特殊なケースである外部結合ビューのマージの詳細は、Oracleオプティマイザのブログ(https://blogs.oracle.com/optimizer/)を参照してください。
複合ビューのマージでは、オプティマイザは、GROUP BYおよびDISTINCTビューを含むビューをマージします。単純ビューのマージと同様、複合マージでは、オプティマイザは、追加の結合順序とアクセス・パスを考慮することができます。
オプティマイザは、結合の評価が実行されるまで、GROUP BYまたはDISTINCT操作の評価を遅延することができます。これらの操作を遅延すると、データの特性によって、パフォーマンスが向上したり低下したりすることがあります。結合でフィルタが使用される場合、結合後まで操作を遅延することで、操作が実行されるデータセットを減らすことができます。早期に操作を評価することで、以降の結合で処理されるデータの量を減らすことができます。そうしないと、結合により、操作で処理されるデータの量が増える可能性があります。オプティマイザは、コストを使用してビューのマージを評価し、コストがより低い場合にのみビューをマージします。
コスト以外でも、オプティマイザは、次の理由で複合ビューのマージを実行できない場合があります。
外部問合せ表に、ROWID列または一意列がない。
ビューがCONNECT BY問合せブロックにある。
ビューにGROUPING SETS、ROLLUPまたはPIVOT句が含まれている。
ビューまたは外側の問合せブロックにMODEL句が含まれている。
例5-4 GROUP BYでの複合ビューの結合
次のビューでは、GROUP BY句が使用されます。
CREATE VIEW cust_prod_totals_v AS SELECT SUM(s.quantity_sold) total, s.cust_id, s.prod_id FROM sales s GROUP BY s.cust_id, s.prod_id;
次の問合せでは、毛皮をあしらったセーターを少なくとも100着購入した米国の顧客がすべて検索されます。
SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email FROM customers c, products p, cust_prod_totals_v WHERE c.country_id = 52790 AND c.cust_id = cust_prod_totals_v.cust_id AND cust_prod_totals_v.total > 100 AND cust_prod_totals_v.prod_id = p.prod_id AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater';
cust_prod_totals_vビューは、複合ビューのマージの対象です。マージ後、問合せは次のようになります。
SELECT c.cust_id, cust_first_name, cust_last_name, cust_email
FROM customers c, products p, sales s
WHERE c.country_id = 52790
AND c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater'
GROUP BY s.cust_id, s.prod_id, p.rowid, c.rowid, c.cust_email, c.cust_last_name,
c.cust_first_name, c.cust_id
HAVING SUM(s.quantity_sold) > 100;
変換された問合せは、変換されていない問合せよりもコストが低いため、オプティマイザはビューをマージすることを選択します。変換されていない問合せでは、GROUP BY演算子は、ビュー内のsales表全体に適用されます。変換された問合せでは、productsおよびcustomersへの結合により、sales表の行の大部分がフィルタ処理で除外されるため、GROUP BY操作のコストは低くなります。sales表は小さくならないため、結合はよりコストの高い操作となりますが、GROUP BY操作でも、元の問合せの行セットのサイズはあまり小さくならないので、コストはそれほど変わりません。前述の特性のいずれかが変わると、ビューのマージは、コストが低いとはかぎらなくなります。最終計画(これはビューを含みません)は、次のようになります。
--------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 2101 (18)|
|* 1 | FILTER | | |
| 2 | HASH GROUP BY | | 2101 (18)|
|* 3 | HASH JOIN | | 2099 (18)|
|* 4 | HASH JOIN | | 1801 (19)|
|* 5 | TABLE ACCESS FULL| PRODUCTS | 96 (5)|
| 6 | TABLE ACCESS FULL| SALES | 1620 (15)|
|* 7 | TABLE ACCESS FULL | CUSTOMERS | 296 (11)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("QUANTITY_SOLD")>100)
3 - access("C"."CUST_ID"="CUST_ID")
4 - access("PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
7 - filter("C"."COUNTRY_ID"='US')
例5-5 DISTINCTでの複合ビューの結合
cust_prod_vビューの次の問合せでは、DISTINCT演算子が使用されます。
SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
FROM customers c, products p,
( SELECT DISTINCT s.cust_id, s.prod_id
FROM sales s) cust_prod_v
WHERE c.country_id = 52790
AND c.cust_id = cust_prod_v.cust_id
AND cust_prod_v.prod_id = p.prod_id
AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater';
ビューのマージによってよりコストの低い計画が作成されると判断すると、オプティマイザは、問合せを次のような同等の問合せにリライトします。
SELECT nwvw.cust_id, nwvw.cust_first_name, nwvw.cust_last_name, nwvw.cust_email
FROM ( SELECT DISTINCT(c.rowid), p.rowid, s.prod_id, s.cust_id,
c.cust_first_name, c.cust_last_name, c.cust_email
FROM customers c, products p, sales s
WHERE c.country_id = 52790
AND c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater' ) nwvw;
前述の問合せの計画は次のようになります。
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | VIEW | VM_NWVW_1 |
| 2 | HASH UNIQUE | |
|* 3 | HASH JOIN | |
|* 4 | HASH JOIN | |
|* 5 | TABLE ACCESS FULL| PRODUCTS |
| 6 | TABLE ACCESS FULL| SALES |
|* 7 | TABLE ACCESS FULL | CUSTOMERS |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."CUST_ID"="S"."CUST_ID")
4 - access("S"."PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
7 - filter("C"."COUNTRY_ID"='US')
この計画には、ビューのマージが行われた後でも、予測ビューとして知られるvm_nwvw_1という名前のビューが含まれています。予測ビューは、DISTINCTビューがマージされた問合せに表示されます。または、GROUP BYビューが、GROUP BY、HAVINGまたは集計も含まれている外側の問合せブロックにマージされる問合せに表示されます。後者の場合、予測ビューには、元の外側の問合せブロックのGROUP BY、HAVINGおよび集計が含まれます。
予測ビューの前述の例では、オプティマイザがビューをマージするときに、DISTINCT演算子を外側の問合せブロックに移動し、元の問合せとの意味的な等価性を維持するためにいくつかの列を追加します。その後、問合せでは、外側の問合せブロックのSELECTリスト内の、目的の列のみを選択できます。最適化では、ビューのマージによって得られるすべてのメリットが保持されます。それらのメリットには、すべての表が1つの問合せブロック内にある、オプティマイザは、最終的な結合順序で必要に応じて表の順序を変更できる、すべての結合が完了するまでDISTINCT操作が遅延されるなどがあります。
述語のプッシュでは、オプティマイザは関連する述語を、それを含む問合せブロックからビューの問合せブロックにプッシュします。マージされていないビューでは、プッシュされた述語は、索引へのアクセスまたはフィルタとして使用できるので、この方法を使用するとマージされていないビューのサブプランが改善されます。
たとえば、次のように表hr.contract_workersを作成するとします。
DROP TABLE contract_workers; CREATE TABLE contract_workers AS (SELECT * FROM employees where 1=2); INSERT INTO contract_workers VALUES (306, 'Bill', 'Jones', 'BJONES', '555.555.2000', '07-JUN-02', 'AC_ACCOUNT', 8300, 0,205, 110); INSERT INTO contract_workers VALUES (406, 'Jill', 'Ashworth', 'JASHWORTH', '555.999.8181', '09-JUN-05', 'AC_ACCOUNT', 8300, 0,205, 50); INSERT INTO contract_workers VALUES (506, 'Marcie', 'Lunsford', 'MLUNSFORD', '555.888.2233', '22-JUL-01', 'AC_ACCOUNT', 8300, 0,205, 110); COMMIT; CREATE INDEX contract_workers_index ON contract_workers(department_id);
employeesおよびcontract_workersを参照するビューを作成します。ビューは、次のようにUNION集合演算子を使用した問合せによって定義されます。
CREATE VIEW all_employees_vw AS
( SELECT employee_id, last_name, job_id, commission_pct, department_id
FROM employees )
UNION
( SELECT employee_id, last_name, job_id, commission_pct, department_id
FROM contract_workers );
それから次のようにしてビューを問い合せます。
SELECT last_name
FROM all_employees_vw
WHERE department_id = 50;
ビューはUNION集合問合せのため、オプティマイザはビューの問合せをアクセス問合せブロックにマージすることはできません。そのかわりに、オプティマイザは、その述語であるWHERE句の条件department_id=50をビューのUNION集合問合せにプッシュすることで、アクセス文を変換できます。同等の変換された問合せは次のとおりです。
SELECT last_name
FROM ( SELECT employee_id, last_name, job_id, commission_pct, department_id
FROM employees
WHERE department_id=50
UNION
SELECT employee_id, last_name, job_id, commission_pct, department_id
FROM contract_workers
WHERE department_id=50 );
変換された問合せは、各問合せブロックで索引アクセスを考慮できるようになります。
副問合せのネスト解除では、オプティマイザはネストされた問合せを同等の結合文に変換してから、その結合を最適化します。この変換により、オプティマイザはアクセス・パス、結合方法および結合順序の選択時に副問合せの表を考慮できます。オプティマイザがこの変換を実行できるのは、結果の結合文が元の文と同じ行を戻すことが保証されており、副問合せにAVGなどの集計機能が含まれていない場合のみです。
たとえば、ユーザーshとして接続して、次の問合せを実行するとします。
SELECT *
FROM sales
WHERE cust_id IN ( SELECT cust_id
FROM customers );
customers.cust_id列はプライマリ・キーのため、オプティマイザは複雑な問合せを、同じデータを戻すことが保証されている次の結合文に変換できます。
SELECT sales.* FROM sales, customers WHERE sales.cust_id = customers.cust_id;
オプティマイザが複雑な文を結合文に変換できない場合は、親の文と副問合せを個別の文として、それぞれに対して実行計画を選択します。それからオプティマイザは副問合せを実行し、戻された行を使用して親の問合せを実行します。実行計画全体の実行速度を上げるため、オプティマイザはサブプランを効率的な順序で並べます。
マテリアライズド・ビューは、データベースがマテリアライズして表に保存した問合せの結果です。
ユーザーの問合せが、マテリアライズド・ビューに関連付けられた問合せと互換性がある場合、オプティマイザは、その問合せをマテリアライズド・ビューの観点からリライトできます。この手法では、ほとんどの問合せ結果がデータベースで再計算されるため、問合せの実行が改善されます。
オプティマイザは、ユーザー問合せと互換性のあるマテリアライズド・ビューを検索し、1つ以上のマテリアライズド・ビューを選択してユーザー問合せをリライトします。問合せをリライトするためのマテリアライズド・ビューの使用はコストベースです。したがって、マテリアライズド・ビューを使用せずに生成された計画のコストが、マテリアライズド・ビューを使用して生成された計画のコストより低い場合、問合せはリライトされません。
次のマテリアライズド・ビュー、cal_month_sales_mvについて検討します。これは各月の販売金額(ドル)を集計するものです。
CREATE MATERIALIZED VIEW cal_month_sales_mv ENABLE QUERY REWRITE AS SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc;
販売数は通常の月で約百万と仮定します。ビューには、各月の販売金額(ドル)について、あらかじめ計算されている集計があります。次の問合せを考えてみます。これは各月の販売金額の合計を問い合せるものです。
SELECT t.calendar_month_desc, SUM(s.amount_sold) FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc;
クエリー・リライトを行わない場合、データベースはsalesディレクトリにアクセスして販売金額の合計を計算する必要があります。この方法ではsalesからの何百万行もの読取りが行われるため、問合せのレスポンス時間は必然的に遅くなります。また結合では、データベースが何百万もの行で結合を計算する必要があるため、問合せレスポンスはさらに遅くなります。クエリー・リライトを使用すると、オプティマイザは次のように問合せを透過的にリライトします。
SELECT calendar_month, dollars FROM cal_month_sales_mv;
関連項目:
クエリー・リライトの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください
スター型変換は、スター・スキーマ内のファクト表の全表スキャンを回避するオプティマイザ変換です。
この項の内容は次のとおりです。
スター・スキーマはデータをファクトとディメンションに分割します。
ファクトは、販売などのイベントの測定結果で、通常は数値です。ディメンションは、日付、場所、製品などのファクトを識別するカテゴリです。
ファクト表には、スキーマのディメンション表の主キーで構成される複合キーがあります。ディメンション表は、問合せを制限する値を選択できるようにする検索表または参照表として機能します。
通常、図では、中央のファクト表が直線でディメンション表に結ばれる形で表示され、星型のように見えます。次の図では、ファクト表としてsales、ディメンション表としてproducts、times、customersおよびchannelsが示されています。
スノーフレーク・スキーマは、ディメンション表が他の表を参照するスター・スキーマです。スノーストーム・スキーマは、スノーフレーク・スキーマの組合せです。
関連項目:
スター・スキーマの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。
ファクト表とディメンション表の結合では、スター型変換により、ファクト表の全スキャンを回避できます。
スター型変換は、制約ディメンション行に結合される関連ファクト行のみをフェッチすることで、パフォーマンスを向上させます。場合によっては、問合せにディメンション表の他の列の制限的なフィルタが含まれていることがあります。フィルタの組合せにより、データベースがファクト表から処理するデータ・セットを大幅に減らすことができます。
スター型変換では、制約ディメンションに対応する、ビットマップ・セミ結合述語と呼ばれる副問合せ述語が追加されます。
オプティマイザは、ファクト結合列に索引が存在する場合に変換を実行します。副問合せによって提供されるキー値のビットマップANDおよびOR操作を駆動することによって、データベースでは、ファクト表から関連行のみを取り出せば済みます。ディメンション表の述語によってかなりのデータがフィルタ処理で除外される場合、変換は、ファクト表の全スキャンよりも効率的であると考えられます。
データベースがファクト表から関連行を取り出した後、データベースで、元の述語を使用してディメンション表にそれらの行を後戻り結合する必要がある場合があります。次の条件が当てはまる場合、データベースでは、ディメンション表の後戻り結合を回避できます。
ディメンション表のすべての述語がセミ結合副問合せ述語の一部である。
副問合せから選択された列が一意である。
ディメンション列がSELECTリストやGROUP BY句などの中にない。
STAR_TRANSFORMATION_ENABLED初期化パラメータは、スター型変換を制御します。このパラメータの値は、次のいずれかです。
true
オプティマイザは、ファクト表と制約ディメンション表を自動的に識別することで、スター型変換を実行します。オプティマイザは、変換された計画のコストが他の計画のコストよりも低い場合にのみ、スター型変換を実行します。さらに、オプティマイザは、マテリアライズによりパフォーマンスが向上する場合は常に、一時表の変換を自動的に試行します(「一時表の変換: シナリオ」を参照)。
false (デフォルト)
オプティマイザは、スター型変換を実行しません。
TEMP_DISABLE
この値は、オプティマイザが一時表の変換を試行しないこと以外は、trueと同じです。
関連項目:
STAR_TRANSFORMATION_ENABLED初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
このシナリオでは、スター問合せのスター型変換を示します。
例5-6 スター問合せ
次の問合せでは、カリフォルニアのすべての市における、1999年の第1四半期と第2四半期のインターネットでの売上の総額を検索します。
SELECT c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s,
times t,
customers c,
channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'CA'
AND ch.channel_desc = 'Internet'
AND t.calendar_quarter_desc IN ('1999-01','1999-02')
GROUP BY c.cust_city, t.calendar_quarter_desc;
次に出力の例を示します。
CUST_CITY CALENDA SALES_AMOUNT ------------------------------ ------- ------------ Montara 1999-02 1618.01 Pala 1999-01 3263.93 Cloverdale 1999-01 52.64 Cloverdale 1999-02 266.28 . . .
この例では、salesはファクト表であり、他の表はディメンション表です。sales表に製品のすべての売上ごとに1つの行が含まれるため、この表には何十億もの売上レコードが含まれている可能性があります。ただし、特定の四半期については、カリフォルニアでインターネットを通じて顧客に販売された製品はあまり多くありません。
例5-7 スター型変換
この例は、例5-6の問合せのスター型変換を示しています。この変換により、salesの全表スキャンは回避されます。
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND c.cust_state_province = 'CA'
AND t.calendar_quarter_desc IN ('1999-01','1999-02')
AND s.time_id IN ( SELECT time_id
FROM times
WHERE calendar_quarter_desc IN('1999-01','1999-02') )
AND s.cust_id IN ( SELECT cust_id
FROM customers
WHERE cust_state_province='CA' )
AND s.channel_id IN ( SELECT channel_id
FROM channels
WHERE channel_desc = 'Internet' )
GROUP BY c.cust_city, t.calendar_quarter_desc;
例5-8 スター型変換の実行計画の一部
この例は、例5-7のスター型変換の編集済の実行計画を示しています。
行26は、sales表に全表スキャンのかわりに索引アクセス・パスがあることを示しています。channels(行14)、times(行19)およびcustomers(行24)の副問合せから得られる各キー値について、データベースは、salesファクト表の索引からビットマップを取得します(行15、20、25)。
ビットマップの各ビットは、ファクト表の行に対応します。副問合せからのキー値がファクト表の行内の値と同じ場合、ビットが設定されます。たとえば、ビットマップ101000... (省略記号は、残りの列の値が0であることを示します)の場合、ファクト表の行1および3に、副問合せからのキー値と一致する値があります。
行12、17および22の操作が副問合せからのキーに対して繰り返され、対応するビットマップが取得されます。例5-7では、customers副問合せにより、州がCAである顧客のIDが検索されます。ビットマップ101000... は、customers表の副問合せからの顧客IDキー値103515に対応すると仮定します。また、customers副問合せにより、ビットマップが010000...であるキー値103516が生成されると仮定します。これは、salesの行2のみが、副問合せからのキー値と一致する値を持つことを意味します。
データベースでは、(OR演算子を使用して)各副問合せのビットマップがマージされます(行11、16、21)。customersの例では、2つのビットマップのマージ後、customers副問合せの単一のビットマップ111000...が生成されます。
101000... # bitmap corresponding to key 103515 010000... # bitmap corresponding to key 103516 --------- 111000... # result of OR operation
行10で、マージされたビットマップにAND演算子が適用されます。データベースですべてのOR操作が実行され、結果として生成されたchannelsのビットマップが100000...であると仮定します。このビットマップおよびcustomers副問合せからのビットマップでAND操作が実行されると、結果は次のようになります。
100000... # channels bitmap after all OR operations performed 111000... # customers bitmap after all OR operations performed --------- 100000... # bitmap result of AND operation for channels and customers
行9では、データベースで、最終ビットマップの対応するROWIDが生成されます。データベースは、ROWIDを使用してsalesファクト表から行を取り出します(行26)。ここで示した例では、データベースは1つのROWIDのみを生成します。このROWIDは、最初の行に対応し、したがって、sales表全体をスキャンするのではなく、単一の行のみをフェッチします。
-------------------------------------------------------------------------------
| Id | Operation | Name
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | HASH GROUP BY |
|* 2 | HASH JOIN |
|* 3 | TABLE ACCESS FULL | CUSTOMERS
|* 4 | HASH JOIN |
|* 5 | TABLE ACCESS FULL | TIMES
| 6 | VIEW | VW_ST_B1772830
| 7 | NESTED LOOPS |
| 8 | PARTITION RANGE SUBQUERY |
| 9 | BITMAP CONVERSION TO ROWIDS|
| 10 | BITMAP AND |
| 11 | BITMAP MERGE |
| 12 | BITMAP KEY ITERATION |
| 13 | BUFFER SORT |
|* 14 | TABLE ACCESS FULL | CHANNELS
|* 15 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX
| 16 | BITMAP MERGE |
| 17 | BITMAP KEY ITERATION |
| 18 | BUFFER SORT |
|* 19 | TABLE ACCESS FULL | TIMES
|* 20 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX
| 21 | BITMAP MERGE |
| 22 | BITMAP KEY ITERATION |
| 23 | BUFFER SORT |
|* 24 | TABLE ACCESS FULL | CUSTOMERS
|* 25 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX
| 26 | TABLE ACCESS BY USER ROWID | SALES
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="C"."CUST_ID")
3 - filter("C"."CUST_STATE_PROVINCE"='CA')
4 - access("ITEM_2"="T"."TIME_ID")
5 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01'
OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
14 - filter("CH"."CHANNEL_DESC"='Internet')
15 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
19 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01'
OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
20 - access("S"."TIME_ID"="T"."TIME_ID")
24 - filter("C"."CUST_STATE_PROVINCE"='CA')
25 - access("S"."CUST_ID"="C"."CUST_ID")
Note
-----
- star transformation used for this statement
例5-8では、オプティマイザは、表channelsをsales表に後戻り結合しません。それは、表channelsが外部で参照されず、channel_idが一意であるためです。ただし、オプティマイザが後戻り結合をなくすことができない場合、データベースでは、一時表に副問合せの結果が格納されて、ビットマップ・キーの生成および後戻り結合のためにディメンションを再スキャンすることが回避されます。さらに、問合せがパラレルに実行される場合、データベースでは、各パラレル実行サーバーで、副問合せを再度実行するのではなく、一時表から結果を選択できるようにするために、結果がマテリアライズされます。
例5-9 一時表を使用したスター型変換
この例では、customersの副問合せの結果が一時表にマテリアライズされます。
SELECT t1.c1 cust_city, t.calendar_quarter_desc calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s, sh.times t, sys_temp_0fd9d6621_e7e24 t1
WHERE s.time_id=t.time_id
AND s.cust_id=t1.c0
AND (t.calendar_quarter_desc='1999-q1' OR t.calendar_quarter_desc='1999-q2')
AND s.cust_id IN ( SELECT t1.c0
FROM sys_temp_0fd9d6621_e7e24 t1 )
AND s.channel_id IN ( SELECT ch.channel_id
FROM channels ch
WHERE ch.channel_desc='internet' )
AND s.time_id IN ( SELECT t.time_id
FROM times t
WHERE t.calendar_quarter_desc='1999-q1'
OR t.calendar_quarter_desc='1999-q2' )
GROUP BY t1.c1, t.calendar_quarter_desc
customersを一時表sys_temp_0fd9d6621_e7e24と置き換え、列cust_idおよびcust_cityへの参照を一時表の対応する列と置き換えます。データベースでは、(c0 NUMBER, c1 VARCHAR2(30))という2つの列を持つ一時表が作成されます。これらの列は、customers表のcust_idおよびcust_cityに対応します。データベースでは、前述の問合せの実行の開始時に、次の問合せを実行することで、一時表が移入されます。SELECT c.cust_id, c.cust_city FROM customers WHERE c.cust_state_province = 'CA'
例5-10 一時表を使用したスター型変換の実行計画の一部
次の例は、例5-9の問合せの編集済の実行計画を示しています。
-------------------------------------------------------------------------------
| Id | Operation | Name
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | TEMP TABLE TRANSFORMATION |
| 2 | LOAD AS SELECT |
|* 3 | TABLE ACCESS FULL | CUSTOMERS
| 4 | HASH GROUP BY |
|* 5 | HASH JOIN |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_C716F
|* 7 | HASH JOIN |
|* 8 | TABLE ACCESS FULL | TIMES
| 9 | VIEW | VW_ST_A3F94988
| 10 | NESTED LOOPS |
| 11 | PARTITION RANGE SUBQUERY |
| 12 | BITMAP CONVERSION TO ROWIDS|
| 13 | BITMAP AND |
| 14 | BITMAP MERGE |
| 15 | BITMAP KEY ITERATION |
| 16 | BUFFER SORT |
|* 17 | TABLE ACCESS FULL | CHANNELS
|* 18 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX
| 19 | BITMAP MERGE |
| 20 | BITMAP KEY ITERATION |
| 21 | BUFFER SORT |
|* 22 | TABLE ACCESS FULL | TIMES
|* 23 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX
| 24 | BITMAP MERGE |
| 25 | BITMAP KEY ITERATION |
| 26 | BUFFER SORT |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_C716F
|* 28 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX
| 29 | TABLE ACCESS BY USER ROWID | SALES
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C"."CUST_STATE_PROVINCE"='CA')
5 - access("ITEM_1"="C0")
7 - access("ITEM_2"="T"."TIME_ID")
8 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR
"T"."CALENDAR_QUARTER_DESC"='1999-02'))
17 - filter("CH"."CHANNEL_DESC"='Internet')
18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
22 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR
"T"."CALENDAR_QUARTER_DESC"='1999-02'))
23 - access("S"."TIME_ID"="T"."TIME_ID")
28 - access("S"."CUST_ID"="C0")
この計画の行1、2および3では、customers副問合せが一時表にマテリアライズされます。行6では、データベースで、(副問合せではなく)一時表がスキャンされて、ファクト表からビットマップが作成されます。行27では、customersをスキャンするかわりに、後戻り結合のために、一時表がスキャンされます。フィルタは一時表のマテリアライズの間に適用されているため、データベースでは、一時表のcustomersにフィルタを適用する必要はありません。
インメモリー集計の基本的なアプローチは、スキャン中に集計することです。
典型的なスター・クエリーの場合など、集計に関係する問合せブロック、および単一の大きな表から複数の小さな表への結合を最適化するために、変換ではKEY VECTORおよびVECTOR GROUP BY操作を使用します。これらの操作では、結合と集計に効率的なインメモリー配列を使用します。基礎となる表がインメモリー列表の場合に特に効果的です。
この項の内容は次のとおりです。
小さな表と大きな表の間の結合の結果を集計する問合せのパフォーマンスを向上させるために、VECTOR GROUP BY変換は、CPU使用率、特にCPUキャッシュを最適化します。
データベースは最初の集計(SQLエンジンが最大量の行を処理する必要のある場所)までの作業を加速します。表スキャンと結合操作の完了を待機せずに、大きな表のスキャンで、ベクター結合およびgroup-by操作(集計)が同時に発生することがあります。
通常の分析問合せはファクト表から集計し、ファクト表を1つ以上のディメンションに結合します。
このタイプの問合せは、大量のデータをスキャンし、オプションでフィルタ処理を行い、1列から40列までのGROUP BYを実行します。オプティマイザは、キー・ベクター(個別の結合キー)のサイズや個別のグループ化キーの数などの考慮事項に基づいて、ベクター変換を使用するかどうかを決定します。ディメンション結合キーのカーディナリティが低い場合に、オプティマイザはこの変換を選択する傾向があります。
VECTOR GROUP BY集計では、大きな表で実行する行ごとの処理時間を短縮するために、小さな表の処理に前もって時間が割かれます。通常の分析問合せでは、次に示す処理ステージに行が分散されるため、この最適化が可能です。
表のフィルタ処理と行セットの生成
行セットの結合
行の集計
ステージ間の作業単位をデータ・フロー演算子(DFO)と呼びます。VECTOR GROUP BY集計では、ディメンションごとにDFOを使用して、キー・ベクター構造と一時表を作成します。ファクト表のメジャー列を集計する場合、データベースではこのキー・ベクターを使用して、ファクト結合キーをその稠密グループ化キーに変換します。遅延マテリアライズ・ステップは、稠密グループ化キーで一時表に結合します。
キー・ベクターは、稠密結合キーと稠密グループ化キー間をマップするデータ構造です。
稠密キーは、ネイティブ整数として格納され、値の範囲を持つ数値キーです。稠密結合キーは、結合列が特定のファクト表またはディメンションから取得されるすべての結合キーを表します。稠密グループ化キーは、グループ化列が特定のファクト表またはディメンションから取得されるすべてのグループ化キーを表します。キー・ベクターは、高速参照を可能にします。
例5-11 キー・ベクター
hr.locations表に、次のようなcountry_idの値があるとします(結果のうち冒頭の一部のみを示しています)。
SQL> SELECT country_id FROM locations; CO -- IT IT JP JP US US US US CA CA CN
複合分析問合せは、フィルタWHERE country_id='US'をlocations表に適用します。このフィルタのキー・ベクターは、次の1次元配列のようになります。
0 0 0 0 1 1 1 1 0 0 0
前の配列では、1はcountry_id='US'の稠密グループ化キーです。0の値は、このフィルタに一致しないlocationsの行を示します。問合せでフィルタWHERE country_id IN ('US','JP')を使用する場合、配列は次のようになります。ここで、2はJPの稠密グループ化キー、1はUSの稠密グループ化キーです。
0 0 2 2 1 1 1 1 0 0 0
通常、VECTOR GROUP BY集計は各ディメンションを順番に処理してから、ファクト表を処理します。
インメモリー集計を実行する場合、データベースは次のように処理を行います。
各ディメンションを次の順序で処理します。
一意の稠密グループ化キーを検索します。
キー・ベクターを作成します。
一時表を作成します(CURSOR DURATION MEMORY)。
次の図に、DFO 0のディメンション表のスキャンで開始し、一時表の作成で終了する、このフェーズのステップを示します。最も単純な形式のパラレルGROUP BYまたは結合処理では、データベースは自身のDFO内で各結合またはGROUP BYを処理します。
ファクト・テーブルを処理します。
前のフェーズで作成したキー・ベクターを使用して、すべての結合と集計を処理します。
結果を各一時表に再び結合します。
図5-3に、2つのディメンションがあるファクト表の結合のフェーズ2を示します。DFO 0では、データベースはファクト表の全スキャンを実行し、各ディメンションのキー・ベクターを使用して不一致の行をフィルタで除外します。DFO 2は、DFO 0の結果をDFO 1と結合します。DFO 4は、DFO 2の結果をDFO 3と結合します。
VECTOR GROUP BY集計では、新規SQLまたはパブリック初期化パラメータは不要です。
次のヒントのペアを使用できます。
問合せブロック・ヒント
VECTOR_TRANSFORMでは、コストにかかわらず、指定した問合せブロックに対してベクター変換を有効にします。NO_VECTOR_TRANSFORMは、指定した問合せブロックからのベクター変換を無効にします。
表ヒント
次のヒントのペアを使用できます。
VECTOR_TRANSFORM_FACTでは、ベクター変換で生成されたファクト表に、指定したFROM式が含まれます。NO_VECTOR_TRANSFORM_FACTでは、ベクター変換で生成されたファクト表から、指定したFROM式が除外されます。
VECTOR_TRANSFORM_DIMSでは、ベクター変換で生成された有効なディメンションに、指定したFROM式が含まれます。NO_VECTOR_TRANSFORM_DIMSでは、ベクター変換で生成された有効なディメンションから、指定したFROM式が除外されます。
関連項目:
VECTOR_TRANSFORM_FACTおよびVECTOR_TRANSFORM_DIMSヒントの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
この項では、VECTOR GROUP BY集計の仕組みの概念的な例を示します。
注意:
シナリオに使用しているスキーマ表はサンプルではなく、表示されている実行計画も実際のものではありません。
この項の内容は次のとおりです。
このシナリオのサンプル・スター・スキーマには、sales_onlineファクト表と、2つのディメンション表geographyおよびproductsが含まれます。
geographyの各行は、geog_id列で一意に識別されます。productsの各行は、prod_id列で一意に識別されます。sales_onlineの各行は、geog_id列、prod_id列および販売量で一意に識別されます。
表5-1 geography表のサンプル行
| country | state | city | geog_id |
|---|---|---|---|
USA |
WA |
seattle |
2 |
USA |
WA |
spokane |
3 |
USA |
CA |
SF |
7 |
USA |
CA |
LA |
8 |
表5-2 products表のサンプル行
| manuf | category | subcategory | prod_id |
|---|---|---|---|
Acme |
sport |
bike |
4 |
Acme |
sport |
ball |
3 |
Acme |
electric |
bulb |
1 |
Acme |
electric |
switch |
8 |
表5-3 sales_online表のサンプル行
| prod_id | geog_id | amount |
|---|---|---|
8 |
1 |
100 |
9 |
1 |
150 |
8 |
2 |
100 |
4 |
3 |
110 |
2 |
30 |
130 |
6 |
20 |
400 |
3 |
1 |
100 |
1 |
7 |
120 |
3 |
8 |
130 |
4 |
3 |
200 |
仕事上でマネージャに、「各サブカテゴリのAcme製品が、ワシントンでオンライン販売された数と、カリフォルニアで販売された数はいくつだったか」と聞かれたとします。この質問に答えるには、sales_onlineファクト表の分析問合せでproductsおよびgeographyディメンション表を次のように結合します。
SELECT p.category, p.subcategory, g.country, g.state, SUM(s.amount)
FROM sales_online s, products p, geography g
WHERE s.geog_id = g.geog_id
AND s.prod_id = p.prod_id
AND g.state IN ('WA','CA')
AND p.manuf = 'ACME'
GROUP BY category, subcategory, country, state
この問合せのVECTOR GROUP BY集計の最初のフェーズでは、データベースはワシントンまたはカリフォルニア州の市について、各市/州の組合せに対する稠密グループ化キーを作成します。
表5-6では、1はUSA,WAグループ化キーで、2はUSA,CAグループ化キーです。
表5-4 geographyの稠密グループ化キー
| country | state | city | geog_id | dense_gr_key_geog |
|---|---|---|---|---|
USA |
WA |
seattle |
2 |
1 |
USA |
WA |
spokane |
3 |
1 |
USA |
CA |
SF |
7 |
2 |
USA |
CA |
LA |
8 |
2 |
geography表のキー・ベクターは、表5-5の最後の列で表される配列のようになります。値は、geography稠密グループ化キーです。したがって、キー・ベクターは、sales_onlineのどの行がgeography.stateフィルタ基準(CAまたはWAの州の販売)を満たし、各行がどの国/州グループ(USA、WAグループまたはUSA、CAグループ)に属するかを示します。
表5-5 オンライン販売
| prod_id | geog_id | amount | geographyのキー・ベクター |
|---|---|---|---|
8 |
1 |
100 |
0 |
9 |
1 |
150 |
0 |
8 |
2 |
100 |
1 |
4 |
3 |
110 |
1 |
2 |
30 |
130 |
0 |
6 |
20 |
400 |
0 |
3 |
1 |
100 |
0 |
1 |
7 |
120 |
2 |
3 |
8 |
130 |
2 |
4 |
3 |
200 |
1 |
内部的に、データベースは次のような一時表を作成します。
CREATE TEMPORARY TABLE tt_geography AS
SELECT MAX(country), MAX(state), KEY_VECTOR_CREATE(...) dense_gr_key_geog
FROM geography
WHERE state IN ('WA','CA')
GROUP BY country, state
表5-6に、tt_geography一時表の行を示します。USA、WAの組合せに対する稠密グループ化キーは1で、USA、CAの組合せに対する稠密グループ化キーは2です。
表5-6 tt_geography
| country | state | dense_gr_key_geog |
|---|---|---|
USA |
WA |
1 |
USA |
CA |
2 |
データベースでは、Acme製品の個々のカテゴリ/サブカテゴリの組合せに対して稠密グループ化キーが作成されます。
たとえば、表5-7では、4がAcme electric switchの稠密グループ化キーです。
表5-7 products表のサンプル行
| manuf | category | subcategory | prod_id | dense_gr_key_prod |
|---|---|---|---|---|
Acme |
sport |
bike |
4 |
1 |
Acme |
sport |
ball |
3 |
2 |
Acme |
electric |
bulb |
1 |
3 |
Acme |
electric |
switch |
8 |
4 |
products表のキー・ベクターは、表5-8の最後の列で表される配列のようになります。値は、products稠密グループ化キーを表します。たとえば、4はAcme electric switchのオンライン販売を表します。したがって、キー・ベクターは、sales_onlineのどの行がproductsフィルタ基準(Acme製品の販売)を満たすかを示します。
表5-8 キー・ベクター
| prod_id | geog_id | amount | productsのキー・ベクター |
|---|---|---|---|
8 |
1 |
100 |
4 |
9 |
1 |
150 |
0 |
8 |
2 |
100 |
4 |
4 |
3 |
110 |
1 |
2 |
30 |
130 |
0 |
6 |
20 |
400 |
0 |
3 |
1 |
100 |
2 |
1 |
7 |
120 |
3 |
3 |
8 |
130 |
2 |
4 |
3 |
200 |
1 |
内部的に、データベースは次のような一時表を作成します。
CREATE TEMPORTARY TABLE tt_products AS SELECT MAX(category), MAX(subcategory), KEY_VECTOR_CREATE(...) dense_gr_key_prod FROM products WHERE manuf = 'ACME' GROUP BY category, subcategory
表5-9に、この一時表の行を示します。
表5-9 tt_products
| category | subcategory | dense_gr_key_prod |
|---|---|---|
sport |
bike |
1 |
sport |
ball |
2 |
electric |
bulb |
3 |
electric |
switch |
4 |
このフェーズでは、データベースはファクト表を処理します。
オプティマイザは、元の問合せを、キー・ベクターにアクセスする次の同等の問合せに変換します。
SELECT KEY_VECTOR_PROD(prod_id),
KEY_VECTOR_GEOG(geog_id),
SUM(amount)
FROM sales_online
WHERE KEY_VECTOR_PROD_FILTER(prod_id) IS NOT NULL
AND KEY_VECTOR_GEOG_FILTER(geog_id) IS NOT NULL
GROUP BY KEY_VECTOR_PROD(prod_id), KEY_VECTOR_GEOG(geog_id)
前の変換は、はるかに複雑な内部SQLの正確なレンディションではなく、基本概念を示すように設計された概念的な表現です。
このフェーズでは、グループ化キーの各組合せの販売量を取得します。
データベースは、キー・ベクターを使用して、ファクト表から不要な行をフィルタで除外します。表5-10では、最初の3列がsales_online表を表します。最後の2列は、geographyおよびproducts表の稠密グループ化キーを提供します。
表5-10 sales_online表の稠密グループ化キー
| prod_id | geog_id | amount | dense_gr_key_prod | dense_gr_key_geog |
|---|---|---|---|---|
7 |
1 |
100 |
4 |
|
9 |
1 |
150 |
||
8 |
2 |
100 |
4 |
1 |
4 |
3 |
110 |
1 |
1 |
2 |
30 |
130 |
||
6 |
20 |
400 |
||
3 |
1 |
100 |
2 |
|
1 |
7 |
120 |
3 |
2 |
3 |
8 |
130 |
2 |
2 |
4 |
3 |
200 |
1 |
1 |
表5-11に示すように、データベースはsales_onlineから、どちらの稠密グループ化キーもnull値でない行のみを取得しており、すべてのフィルタ基準を満たす行が示されています。
表5-11 sales_online表からフィルタされた行
| geog_id | prod_id | amount | dense_gr_key_prod | dense_gr_key_geog |
|---|---|---|---|---|
2 |
8 |
100 |
4 |
1 |
3 |
4 |
110 |
1 |
1 |
3 |
4 |
200 |
1 |
1 |
7 |
1 |
120 |
3 |
2 |
8 |
3 |
130 |
2 |
2 |
データベースでは、多次元配列を使用して集計を実行します。
表5-12では、geographyグループ化キーは水平で、productsグループ化キーは垂直です。データベースは、各稠密グループ化キーの組合せの交差部分の値を加算します。たとえば、geographyグループ化キー1とproductsグループ化キー1の交差部分では、110と200の合計は310です。
表5-12 集計配列
| dgkp/dgkg | 1 | 2 |
|---|---|---|
1 |
|
|
2 |
|
|
3 |
|
|
4 |
|
この例では、仕事上の問いは「各カレンダ年に販売されたカテゴリごとの製品数はいくつか」というものです。
times、productsおよびsalesの各表を結合する次の問合せを作成します。
SELECT t.calendar_year, p.prod_category, SUM(quantity_sold) FROM times t, products p, sales s WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id GROUP BY t.calendar_year, p.prod_category;
例5-12 VECTOR GROUP BY実行計画
次の例に、現在のカーソルに含まれる実行計画を示します。手順4と8では、ディメンション表timesおよびproductsのキー・ベクターを作成しています。手順17と18では、前に作成したキー・ベクターを使用しています。手順3、7および15では、VECTOR GROUP BY操作を行っています。
SQL_ID 0yxqj2nq8p9kt, child number 0 ------------------------------------- SELECT t.calendar_year, p.prod_category, SUM(quantity_sold) FROM times t, products p, sales f WHERE t.time_id = f.time_id AND p.prod_id = f.prod_id GROUP BY t.calendar_year, p.prod_category Plan hash value: 2377225738 ------------------------------------------------------------------------------------------------------ |Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop| ------------------------------------------------------------------------------------------------------ | 0|SELECT STATEMENT | | | |285(100)| | | | | 1| TEMP TABLE TRANSFORMATION | | | | | | | | | 2| LOAD AS SELECT |SYS_TEMP_0FD9D6644_11CBE8| | | | | | | | 3| VECTOR GROUP BY | | 5| 80 | 3(100)|00:00:01| | | | 4| KEY VECTOR CREATE BUFFERED | :KV0000 |1826|29216| 3(100)|00:00:01| | | | 5| TABLE ACCESS INMEMORY FULL | TIMES |1826|21912| 1(100)|00:00:01| | | | 6| LOAD AS SELECT |SYS_TEMP_0FD9D6645_11CBE8| | | | | | | | 7| VECTOR GROUP BY | | 5| 125 | 1(100)|00:00:01| | | | 8| KEY VECTOR CREATE BUFFERED | :KV0001 | 72| 1800| 1(100)|00:00:01| | | | 9| TABLE ACCESS INMEMORY FULL | PRODUCTS | 72| 1512| 0 (0)| | | | |10| HASH GROUP BY | | 18| 1440|282 (99)|00:00:01| | | |11| HASH JOIN | | 18| 1440|281 (99)|00:00:01| | | |12| HASH JOIN | | 18| 990 |278(100)|00:00:01| | | |13| TABLE ACCESS FULL |SYS_TEMP_0FD9D6644_11CBE8| 5| 80 | 2 (0)|00:00:01| | | |14| VIEW | VW_VT_AF278325 | 18| 702 |276(100)|00:00:01| | | |15| VECTOR GROUP BY | | 18| 414 |276(100)|00:00:01| | | |16| HASH GROUP BY | | 18| 414 |276(100)|00:00:01| | | |17| KEY VECTOR USE | :KV0000 |918K| 20M|276(100)|00:00:01| | | |18| KEY VECTOR USE | :KV0001 |918K| 16M|272(100)|00:00:01| | | |19| PARTITION RANGE ALL | |918K| 13M|257(100)|00:00:01|1|28| |20| TABLE ACCESS INMEMORY FULL| SALES |918K| 13M|257(100)|00:00:01|1|28| |21| TABLE ACCESS FULL |SYS_TEMP_0FD9D6645_11CBE8| 5 | 125| 2 (0)|00:00:01| | | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 11 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_11"="C2") 12 - access("ITEM_8"=INTERNAL_FUNCTION("C0") AND "ITEM_9"="C2") Note ----- - vector transformation used for this statement 45 rows selected.
表拡張では、オプティマイザが、パーティション表のread-mostly部分では索引を使用するが、表のアクティブ部分では索引を使用しない計画を生成します。
この項の内容は次のとおりです。
索引ベースの計画を使用すると、パフォーマンスを高めることができますが、索引のメンテナンスによりオーバーヘッドが発生します。多くのデータベースで、DMLはデータの一部にのみ影響を及ぼします。
表拡張では、更新のボリュームが大きい表に対して索引ベースの計画を使用します。read-mostlyデータのみで索引を作成し、アクティブ・データの索引オーバーヘッドを排除できます。こうして、表拡張でパフォーマンスを向上させ、索引のメンテナンスを回避できます。
表のパーティション化により、表拡張が可能になります。
パーティション表にローカル索引が存在する場合、オプティマイザは、特定のパーティションに対してその索引を使用不可とマークすることができます。実際、一部のパーティションは索引付けされていません。
表拡張では、オプティマイザは、問合せをUNION ALL文に変換します。一部の副問合せは索引付けされたパーティションにアクセスし、他の副問合せは索引付けされていないパーティションにアクセスします。オプティマイザは、パーティションで使用可能な最も効率的なアクセス方法を選択できます。このとき、そのアクセス方法が、問合せでアクセスされるすべてのパーティションに対して存在するかどうかは関係ありません。
オプティマイザは、必ずしも表拡張を選択するとはかぎりません。
表拡張はコストベースです。
データベースは、拡張された表の各パーティションに、UNION ALLのすべてのブランチで1回だけアクセスしますが、それに結合される表には各ブランチでアクセスします。
セマンティクスの問題により、拡張が無効になる場合があります。
たとえば、外部結合の右側にある表は、表拡張の対象としては無効です。
EXPAND_TABLEヒントを使用して表拡張を制御できます。ヒントは、コストベースの決定を上書きしますが、セマンティクス・チェックは上書きしません。
関連項目:
SQLヒントの詳細は、『Oracle Database SQL言語リファレンス』を参照してください
オプティマイザは、問合せで使用されている述語に基づいて、各表からどのパーティションにアクセスする必要があるかを追跡します。パーティション・プルーニングにより、オプティマイザは、表拡張を使用してより最適な計画を生成できるようになります。
前提条件
この例では、次のことを前提としています。
time_id列でレンジ・パーティション化されているsh.sales表に対してスター・クエリーを実行します。
表拡張のメリットを確認するために、特定のパーティションで索引を無効にします。
表拡張を使用する手順は次のとおりです。
shユーザーとしてデータベースにログインします。
次の問合せを実行します。
SELECT *
FROM sales
WHERE time_id >= TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
AND prod_id = 38;
DBMS_XPLANを問い合せて計画をEXPLAINします。
SET LINESIZE 150 SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'BASIC,PARTITION'));
次の計画のPstartおよびPstop列に示されているように、オプティマイザは、フィルタから、表内の28パーティションのうちの16のパーティションにのみアクセスする必要があると判断します。
Plan hash value: 3087065703
--------------------------------------------------------------------------
|Id| Operation | Name |Pstart|Pstop|
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |
| 1| PARTITION RANGE ITERATOR | | 13 | 28 |
| 2| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 13 | 28 |
| 3| BITMAP CONVERSION TO ROWIDS | | | |
|*4| BITMAP INDEX SINGLE VALUE |SALES_PROD_BIX| 13 | 28 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PROD_ID"=38)
オプティマイザは、アクセス対象のパーティションを決定した後、それらのパーティションのすべてで使用可能な索引について検討します。前述の計画では、オプティマイザは、sales_prod_bixビットマップ索引を使用することを選択しました。
sales表のSALES_1995パーティションの索引を無効にします。
ALTER INDEX sales_prod_bix MODIFY PARTITION sales_1995 UNUSABLE;
このDDLにより、パーティション1の索引が無効になります。このパーティションには、1996年以前のすべての売上が含まれています。
注意:
USER_IND_PARTITIONSビューを問い合せて、パーティションの情報を取得できます。
売上の問合せを再度実行し、DBMS_XPLANを問い合せて計画を取得します。
出力から、計画が変更されなかったことがわかります。
Plan hash value: 3087065703
---------------------------------------------------------------------------
|Id| Operation | Name |Pstart|Pstop
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |
| 1| PARTITION RANGE ITERATOR | | 13 | 28 |
| 2| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 13 | 28 |
| 3| BITMAP CONVERSION TO ROWIDS | | | |
|*4| BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX| 13 | 28 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PROD_ID"=38)
索引が無効にされたパーティションは問合せに関係しないので、計画は同じです。問合せでアクセスされるすべてのパーティションが索引付けされている場合、データベースでは、索引を使用して問合せに答えられます。問合せは、28のうち16のパーティションにのみアクセスするため、パーティション1の索引を無効にしても計画に影響しません。
パーティション28(SALES_Q4_2003)の索引を無効にします(これは、問合せでアクセスする必要のあるパーティションです)。
ALTER INDEX sales_prod_bix MODIFY PARTITION sales_q4_2003 UNUSABLE; ALTER INDEX sales_time_bix MODIFY PARTITION sales_q4_2003 UNUSABLE;
問合せでアクセスする必要のあるパーティションの索引を無効にすることにより、(表拡張をしなければ)問合せでこの索引を使用できなくなります。
DBMS_XPLANを使用して計画を問い合せます。
次の計画に示されているように、オプティマイザは、索引を使用しません。
Plan hash value: 3087065703
---------------------------------------------------------------------------
| Id| Operation | Name |Pstart|Pstop
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE ITERATOR | | 13 | 28 |
|*2 | TABLE ACCESS FULL | SALES | 13 | 28 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PROD_ID"=38)
前述の例では、問合せで16のパーティションにアクセスします。これらのうち15のパーティションで、索引が使用可能ですが、最後のパーティションでは索引は使用できません。オプティマイザは、いずれか1つのアクセス・パスを選択する必要があるため、どのパーティションでも索引を使用できません。
表拡張を使用すると、オプティマイザは、元の問合せを次のようにリライトします。
SELECT *
FROM sales
WHERE time_id >= TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
AND time_id < TO_DATE('2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
AND prod_id = 38
UNION ALL
SELECT *
FROM sales
WHERE time_id >= TO_DATE('2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
AND time_id < TO_DATE('2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')
AND prod_id = 38;
この問合せでは、UNION ALL内の最初の問合せブロックは、索引付けされているパーティションにアクセスし、2番目の問合せブロックは、索引付けされていないパーティションにアクセスします。2つの副問合せにより、アクセス先のすべてのパーティションの表スキャンを使用するよりも、索引の使用がより最適である場合は、オプティマイザは、最初の問合せブロックで索引を使用することを選択できます。
DBMS_XPLANを使用して計画を問い合せます。
計画は次のようになります。
Plan hash value: 2120767686
---------------------------------------------------------------------------
|Id| Operation | Name |Pstart|Pstop|
---------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | |
| 1| VIEW | VW_TE_2 | | |
| 2| UNION-ALL | | | |
| 3| PARTITION RANGE ITERATOR | | 13| 27|
| 4| TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES | 13| 27|
| 5| BITMAP CONVERSION TO ROWIDS | | | |
|*6| BITMAP INDEX SINGLE VALUE | SALES_PROD_BIX| 13| 27|
| 7| PARTITION RANGE SINGLE | | 28| 28|
|*8| TABLE ACCESS FULL | SALES | 28| 28|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("PROD_ID"=38)
8 - filter("PROD_ID"=38)
この計画に示されているように、2つの問合せブロックでUNION ALLが使用されます(手順2)。1つ目の問合せブロックのパーティション13から27までのアクセスには、索引が選択されます(手順6)。パーティション28では索引を使用できないため、2つ目の問合せブロックでは全表スキャンが選択されます(手順8)。
スター型変換により、特定のタイプの問合せで、大きなファクト表の大部分にアクセスすることを回避できます(「スター型変換」を参照)。スター型変換では、いくつかの索引を定義する必要があります。それらの索引により、アクティブに更新される表ではオーバーヘッドが生じる可能性があります。表拡張では、オプティマイザが表の索引付けされた部分でのみスター型変換を検討できるようにするために、アクティブでないパーティションでのみ索引を定義することが可能です。
前提条件
この例では、次のことを前提としています。
「スター型変換: シナリオ」で使用されているものと同じスキーマを問い合せます。
salesの最後のパーティションは、多くの場合タイム・パーティション表と同様に、アクティブに更新されます。
オプティマイザで表拡張を使用します。
スター・クエリーで表拡張を使用する手順は次のとおりです。
次のように、最後のパーティションの索引を無効にします。
ALTER INDEX sales_channel_bix MODIFY PARTITION sales_q4_2003 UNUSABLE; ALTER INDEX sales_cust_bix MODIFY PARTITION sales_q4_2003 UNUSABLE;
次のスター・クエリーを実行します。
SELECT t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'CA'
AND ch.channel_desc = 'Internet'
AND t.calendar_quarter_desc IN ('1999-01','1999-02')
GROUP BY t.calendar_quarter_desc;
DBMS_XPLANを使用してカーソルを問い合せます。次の計画が表示されます。
--------------------------------------------------------------------------- |Id| Operation | Name | Pstart| Pstop | --------------------------------------------------------------------------- | 0| SELECT STATEMENT | | | | | 1| HASH GROUP BY | | | | | 2| VIEW |VW_TE_14 | | | | 3| UNION-ALL | | | | | 4| HASH JOIN | | | | | 5| TABLE ACCESS FULL |TIMES | | | | 6| VIEW |VW_ST_1319B6D8 | | | | 7| NESTED LOOPS | | | | | 8| PARTITION RANGE SUBQUERY | |KEY(SQ)|KEY(SQ)| | 9| BITMAP CONVERSION TO ROWIDS| | | | |10| BITMAP AND | | | | |11| BITMAP MERGE | | | | |12| BITMAP KEY ITERATION | | | | |13| BUFFER SORT | | | | |14| TABLE ACCESS FULL |CHANNELS | | | |15| BITMAP INDEX RANGE SCAN|SALES_CHANNEL_BIX|KEY(SQ)|KEY(SQ)| |16| BITMAP MERGE | | | | |17| BITMAP KEY ITERATION | | | | |18| BUFFER SORT | | | | |19| TABLE ACCESS FULL |TIMES | | | |20| BITMAP INDEX RANGE SCAN|SALES_TIME_BIX |KEY(SQ)|KEY(SQ)| |21| BITMAP MERGE | | | | |22| BITMAP KEY ITERATION | | | | |23| BUFFER SORT | | | | |24| TABLE ACCESS FULL |CUSTOMERS | | | |25| BITMAP INDEX RANGE SCAN|SALES_CUST_BIX |KEY(SQ)|KEY(SQ)| |26| TABLE ACCESS BY USER ROWID |SALES | ROWID | ROWID | |27| NESTED LOOPS | | | | |28| NESTED LOOPS | | | | |29| NESTED LOOPS | | | | |30| NESTED LOOPS | | | | |31| PARTITION RANGE SINGLE | | 28 | 28 | |32| TABLE ACCESS FULL |SALES | 28 | 28 | |33| TABLE ACCESS BY INDEX ROWID|CHANNELS | | | |34| INDEX UNIQUE SCAN |CHANNELS_PK | | | |35| TABLE ACCESS BY INDEX ROWID |CUSTOMERS | | | |36| INDEX UNIQUE SCAN |CUSTOMERS_PK | | | |37| INDEX UNIQUE SCAN |TIMES_PK | | | |38| TABLE ACCESS BY INDEX ROWID |TIMES | | | ---------------------------------------------------------------------------
この計画では、表拡張が使用されます。最後のパーティションを除くすべてのパーティションにアクセスするUNION ALLブランチは、スター型変換を使用します。パーティション28の索引は無効化されているため、データベースは、全表スキャンを使用して最後のパーティションにアクセスします。
結合の因数分解として知られるコストベースの変換では、オプティマイザが、UNION ALL問合せのブランチの共通の計算を因数分解できます。
この項の内容は次のとおりです。
UNION ALL問合せは、データベース・アプリケーション、特にデータ統合アプリケーションでは一般的です。多くの場合、UNION ALL問合せのブランチは、同じ実表を参照します。結合の因数分解を使用しない場合、オプティマイザは、UNION ALL問合せの各ブランチを単独で評価します。そのため、データ・アクセスや結合などの処理が繰り返し実行されることになります。結合の因数分解の変換では、UNION ALLブランチ間で共通の計算を共有できます。大きな実表を追加でスキャンすることを回避できるため、パフォーマンスが大幅に向上します。
結合の因数分解では、3つ以上のUNION ALLブランチから複数の表を因数分解できます。次の例を使用して、結合の因数分解について説明します。
例5-13 UNION ALL問合せ
次の問合せでは、4つの表(t1、t2、t3およびt4)および2つのUNION ALLブランチの問合せを示しています。
SELECT t1.c1, t2.c2 FROM t1, t2, t3 WHERE t1.c1 = t2.c1 AND t1.c1 > 1 AND t2.c2 = 2 AND t2.c2 = t3.c2 UNION ALL SELECT t1.c1, t2.c2 FROM t1, t2, t4 WHERE t1.c1 = t2.c1 AND t1.c1 > 1 AND t2.c3 = t4.c3
前述の問合せで、表t1は、フィルタ述語t1.c1 > 1および結合述語t1.c1 = t2.c1と同様に、両方のUNION ALLブランチにあります。変換が行われない場合、データベースでは、表t1のスキャンとフィルタリングを2回(各ブランチに対して1回)実行する必要があります。
例5-14 因数分解された問合せ
次の問合せでは、結合の因数分解を使用して例5-13の問合せを変換します。SELECT t1.c1, VW_JF_1.item_2 FROM t1, (SELECT t2.c1 item_1, t2.c2 item_2 FROM t2, t3 WHERE t2.c2 = t3.c2 AND t2.c2 = 2 UNION ALL SELECT t2.c1 item_1, t2.c2 item_2 FROM t2, t4 WHERE t2.c3 = t4.c3) VW_JF_1 WHERE t1.c1 = VW_JF_1.item_1 AND t1.c1 > 1
この場合、表t1が因数分解されたため、データベースでは、t1の表のスキャンおよびフィルタリングが1回のみ実行されます。t1のサイズが大きい場合、この因数分解により、t1を2回スキャンおよびフィルタリングするための大きなパフォーマンス・コストを回避できます。
注意:
UNION ALL問合せのブランチに、DISTINCTファンクションを使用する句がある場合、結合の因数分解は無効です。
結合の因数分解では、結合順序の可能性を高めることができます。
例5-15 5つの表を含む問合せ
次の問合せで、ビューVは、例5-13の問合せと同じです。
SELECT *
FROM t5, (SELECT t1.c1, t2.c2
FROM t1, t2, t3
WHERE t1.c1 = t2.c1
AND t1.c1 > 1
AND t2.c2 = 2
AND t2.c2 = t3.c2
UNION ALL
SELECT t1.c1, t2.c2
FROM t1, t2, t4
WHERE t1.c1 = t2.c1
AND t1.c1 > 1
AND t2.c3 = t4.c3) V
WHERE t5.c1 = V.c1
結合の因数分解の前に、データベースでは、まずt1、t2およびt3を結合して、その後それらをt5に結合する必要があります。 例5-16 ビューVからのt1の因数分解
次の問合せに示されているように、結合の因数分解で、ビューVからt1を因数分解する場合、データベースでは、t1をt5に結合できます。
SELECT * FROM t5, ( SELECT t1.c1, VW_JF_1.item_2 FROM t1, (SELECT t2.c1 item_1, t2.c2 item_2 FROM t2, t3 WHERE t2.c2 = t3.c2 AND t2.c2 = 2 UNION ALL SELECT t2.c1 item_1, t2.c2 item_2 FROM t2, t4 WHERE t2.c3 = t4.c3) VW_JF_1 WHERE t1.c1 = VW_JF_1.item_1 AND t1.c1 > 1 ) WHERE t5.c1 = V.c1
前述の問合せ変換により、新しい結合順序が作成されます。ただし、結合の因数分解では、特定の結合順序が設定されます。たとえば、前述の問合せでは、表t2およびt3は、ビューVW_JF_1のUNION ALL問合せの最初のブランチにあります。データベースでは、t1(これはVW_JF_1ビュー内で定義されていません)と結合する前に、t2をt3に結合する必要があります。設定された結合順序は、必ずしも最適な結合順序とはかぎりません。このため、オプティマイザは、コストベースの変換フレームワークを使用して結合の因数分解を実行します。オプティマイザは、結合の因数分解を使用した計画のコストと、結合の因数分解を使用しない計画のコストを計算し、最もコストの低い計画を選択します。
例5-17 ビュー定義が削除されたビューVからのt1の因数分解
次の問合せは例5-16の問合せと同じですが、因数分解をわかりやすくするために、ビュー定義が削除されています。
SELECT * FROM t5, (SELECT t1.c1, VW_JF_1.item_2 FROM t1, VW_JF_1 WHERE t1.c1 = VW_JF_1.item_1 AND t1.c1 > 1) WHERE t5.c1 = V.c1
データベースは、外部結合、アンチ結合およびセミ結合の因数分解をサポートしていますが、サポートしているのは、そのような結合の右の表に対してのみです。たとえば、結合の因数分解では、t2を因数分解することで、次のUNION ALL問合せを変換できます。
SELECT t1.c2, t2.c2 FROM t1, t2 WHERE t1.c1 = t2.c1(+) AND t1.c1 = 1 UNION ALL SELECT t1.c2, t2.c2 FROM t1, t2 WHERE t1.c1 = t2.c1(+) AND t1.c1 = 2
次の例は、変換を示しています。表t2は、副問合せのUNION ALLブランチに表示されなくなります。
SELECT VW_JF_1.item_2, t2.c2
FROM t2, (SELECT t1.c1 item_1, t1.c2 item_2
FROM t1
WHERE t1.c1 = 1
UNION ALL
SELECT t1.c1 item_1, t1.c2 item_2
FROM t1
WHERE t1.c1 = 2) VW_JF_1
WHERE VW_JF_1.item_1 = t2.c1(+)
