5 問合せ変換
オプティマイザは、複数の問合せ変換技法を使用します。この章では、重要な一部について説明します。
この章のトピックは、次のとおりです:
関連項目
5.1 OR拡張
OR拡張で、オプティマイザは、トップレベルの分離を含む問合せブロックを2つ以上の分岐を含むUNION ALL問合せの形式に変換します。オプティマイザは、分離をコンポーネントに分割して各コンポーネントをUNION ALL問合せの分岐に関連付けて、この目標を達成します。
オプティマイザでは、様々な理由でOR拡張が選択されます。たとえば、より効率的なアクセス・パスや、デカルト積を回避する代替の結合方法を有効にできます。通常と同じように、オプティマイザは、変換された文のコストが元の文のコストよりも低い場合にのみ、拡張を実行します。
以前のリリースでは、オプティマイザはCONCATENATION演算子を使用してOR拡張を実行しました。Oracle Database 12cリリース2(12.2)からは、オプティマイザはかわりにUNION-ALL演算子を使用します。フレームワークには、次の拡張機能があります。
-
様々な変換の相互作用を有効化します
-
問合せ構造の共有を回避します
-
様々な検索戦略の検索を有効化します
-
コスト注釈の再利用を提供します
-
標準SQL構文をサポートします
例5-1 変換された問合せ: UNION ALL条件
この例を準備するには、管理者としてデータベースにログインし、次の文を実行して一意の制約をhr.departments.department_name列に追加し、100,000行をhr.employees表に追加します。
ALTER TABLE hr.departments ADD CONSTRAINT department_name_uk UNIQUE (department_name);
DELETE FROM hr.employees WHERE employee_id > 999;
DECLARE
v_counter NUMBER(7) := 1000;
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO hr.employees
VALUES (v_counter,null,'Doe','Doe' || v_counter || '@example.com',null,'07-JUN-02','AC_ACCOUNT',null,null,null,50);
v_counter := v_counter + 1;
END LOOP;
END;
/
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'hr', tabname => 'employees');次に、ユーザーhrとして接続し、employeesおよびdepartments表を結合する次の問合せを実行します。
SELECT *
FROM employees e, departments d
WHERE (e.email='SSTILES' OR d.department_name='Treasury')
AND e.department_id = d.department_id;
OR拡張を使用しない場合、オプティマイザは1つの単位としてe.email='SSTILES' OR d.department_name='Treasury'を処理します。結果として、オプティマイザはe.emailまたはd.department_name列の索引を使用できないため、employeesおよびdepartmentsの全表スキャンを実行します。
OR拡張を使用する場合、次の例に示されているように、選言述語を2つの独立した述語に分割します。
SELECT *
FROM employees e, departments d
WHERE e.email = 'SSTILES'
AND e.department_id = d.department_id
UNION ALL
SELECT *
FROM employees e, departments d
WHERE d.department_name = 'Treasury'
AND e.department_id = d.department_id;この変換により、e.emailおよびd.department_name列を索引キーとして使用できます。データベースでは、次の実行計画に示されているように、2つの全表スキャンのかわりに2つの一意の索引を使用してデータをフィルタするため、パフォーマンスが向上します。
Plan hash value: 2512933241
-----------------------------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |122 (100)| |
| 1 | VIEW |VW_ORE_19FF4E3E |9102|1679K|122 (5) |00:00:01|
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 1 | 78 | 4 (0) |00:00:01|
| 4 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 57 | 3 (0) |00:00:01|
|*5 | INDEX UNIQUE SCAN | EMP_EMAIL_UK | 1 | | 2 (0) |00:00:01|
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 21 | 1 (0) |00:00:01|
|*7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0) | |
| 8 | NESTED LOOPS | |9101| 693K|118 (5) |00:00:01|
| 9 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 21 | 1 (0) |00:00:01|
|*10| INDEX UNIQUE SCAN |DEPARTMENT_NAME_UK| 1 | | 0 (0) | |
|*11| TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES |9101| 506K|117 (5) |00:00:01|
|*12| INDEX RANGE SCAN |EMP_DEPARTMENT_IX |9101| | 35 (6) |00:00:01|
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."EMAIL"='SSTILES')
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
10 - access("D"."DEPARTMENT_NAME"='Treasury')
11 - filter(LNNVL("E"."EMAIL"='SSTILES'))
12 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
35 rows selected.
5.2 ビューのマージ
ビューのマージでは、オプティマイザは、ビューを表す問合せブロックを、それを包含する問合せブロックにマージします。
ビューのマージにより、オプティマイザが追加の結合順序、アクセス方法およびその他の変換を考慮できるようになり、計画を向上させることができます。たとえば、ビューがマージされ、複数の表が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リファレンス』を参照してください
5.2.1 ビューのマージにおける問合せブロック
オプティマイザは、個別の問合せブロックによるネストされた副問合せまたはマージされていないビューをそれぞれ表します。
データベースは、問合せブロックを下位から上位へ順番に最適化します。このため、データベースは最も内側の問合せブロックを最初に最適化し、その計画の一部を生成して、問合せ全体を表す外側の問合せブロックの計画を生成します。
パーサーは、問合せで参照される各ビューを個別の問合せブロックに拡張します。ブロックは、基本的にはビュー定義を表しますが、このため必然的にビューの結果も表すことになります。オプティマイザの1つのオプションは、ビューの問合せブロックを個別に分析し、サブプランを生成して、実行計画全体の生成にビュー・サブプランを使用することで、残りの問合せを処理することです。ただし、この手法では、ビューが個別に最適化されるため、最適ではない実行計画となる可能性があります。
ビューのマージにより、パフォーマンスが向上する場合があります。「例5-2」に示すように、ビューのマージでは、ビューから外部の問合せブロックに表がマージされ、内部の問合せブロックが削除されます。そのため、ビューの個別の最適化は必要ありません。
5.2.2 単純ビューのマージ
単純ビューのマージでは、オプティマイザは選択表示結合ビューをマージします。
たとえば、employees表の問合せにはdepartments表とlocations表を結合する副問合せが含まれます。
マージ後は追加の結合順序およびアクセス・パスが使用可能になるため、単純ビューのマージにより、多くの場合、より最適な計画が作成されます。次の理由により、単純ビューのマージでは、ビューが有効にならない場合があります。
-
次のような、選択表示結合ビューに含まれていない構成メンバーがビューに含まれている。
-
GROUP BY -
DISTINCT -
外部結合
-
MODEL -
CONNECT BY -
集合演算子
-
集計
-
-
ビューの
SELECTリスト内に副問合せが含まれている。 -
外側の問合せブロックにPL/SQLファンクションが含まれている。
-
ビューが外部結合に含まれており、ビューをマージできるかどうかを判断する複数の追加の妥当性要件の1つを満たさない。
例5-2 単純ビューのマージ
次の問合せでは、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/)を参照してください。
5.2.3 複合ビューのマージ
ビューのマージでは、オプティマイザは、GROUP BYおよびDISTINCTビューを含むビューをマージします。単純ビューのマージと同様、複合マージでは、オプティマイザは、追加の結合順序とアクセス・パスを考慮することができます。
オプティマイザは、結合の評価が実行されるまで、GROUP BYまたはDISTINCT操作の評価を遅延することができます。これらの操作を遅延すると、データの特性によって、パフォーマンスが向上したり低下したりすることがあります。結合でフィルタが使用される場合、結合後まで操作を遅延することで、操作が実行されるデータセットを減らすことができます。早期に操作を評価することで、以降の結合で処理されるデータの量を減らすことができます。そうしないと、結合により、操作で処理されるデータの量が増える可能性があります。オプティマイザは、コストを使用してビューのマージを評価し、コストがより低い場合にのみビューをマージします。
コスト以外でも、オプティマイザは、次の理由で複合ビューのマージを実行できない場合があります。
-
外部問合せ表に、ROWID列または一意列がない。
-
ビューが
CONNECT BY問合せブロックにある。 -
ビューに
GROUPING SETS、ROLLUPまたはPIVOT句が含まれている。 -
ビューまたは外側の問合せブロックに
MODEL句が含まれている。
例5-3 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-4 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操作が遅延されるなどがあります。
5.3 述語のプッシュ
述語のプッシュでは、オプティマイザは関連する述語を、それを含む問合せブロックからビューの問合せブロックにプッシュします。
マージされていないビューでは、この手法によって、マージされていないビューのサブプランが改善されます。データベースは、プッシュされた述語を使用して索引にアクセスしたり、フィルタとして使用したりできます。
たとえば、次のように表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 );
変換された問合せは、各問合せブロックで索引アクセスを考慮できるようになります。
5.4 副問合せのネスト解除
副問合せのネスト解除では、オプティマイザはネストされた問合せを同等の結合文に変換してから、その結合を最適化します。
この変換により、オプティマイザはアクセス・パス、結合方法および結合順序の選択時に副問合せの表を考慮できます。オプティマイザがこの変換を実行できるのは、結果の結合文が元の文と同じ行を戻すことが保証されており、副問合せに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;
オプティマイザが複雑な文を結合文に変換できない場合は、親の文と副問合せを個別の文として、それぞれに対して実行計画を選択します。それからオプティマイザは副問合せを実行し、戻された行を使用して親の問合せを実行します。実行計画全体の実行速度を上げるため、オプティマイザはサブプランを効率的な順序で並べます。
5.5 マテリアライズド・ビューを使用したクエリー・リライト
マテリアライズド・ビューとは、表に格納された問合せ結果のことです。
ユーザーの問合せがマテリアライズド・ビューに関連付けられた問合せと互換性があることをオプティマイザが検出すると、データベースはその問合せをマテリアライズド・ビューの観点からリライトできます。この手法では、ほとんどの問合せ結果がデータベースで再計算されるため、問合せの実行が改善されます。
オプティマイザは、ユーザー問合せと互換性があるマテリアライズド・ビューを検索し、コストベースのアルゴリズムを使用して、問合せをリライトするマテリアライズド・ビューを選択します。オプティマイザは計画の生成時に、マテリアライズド・ビューを使用して生成された計画よりも、マテリアライズド・ビューのコストが低くならないときには問合せをリライトしません。
この項では、次の項目について説明します。
関連項目:
クエリー・リライトについてさらに学習するには、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください
5.6 スター型変換
スター型変換は、スター・スキーマ内のファクト表の全表スキャンを回避するオプティマイザ変換です。
この項では、次の項目について説明します。
5.6.1 スター・スキーマについて
スター・スキーマはデータをファクトとディメンションに分割します。
ファクトは、販売などのイベントの測定結果で、通常は数値です。ディメンションは、日付、場所、製品などのファクトを識別するカテゴリです。
ファクト表には、スキーマのディメンション表の主キーで構成される複合キーがあります。ディメンション表は、問合せを制限する値を選択できるようにする検索表または参照表として機能します。
通常、図では、中央のファクト表が直線でディメンション表に結ばれる形で表示され、星型のように見えます。次の図では、ファクト表としてsales、ディメンション表としてproducts、times、customersおよびchannelsが示されています。
スノーフレーク・スキーマは、ディメンション表が他の表を参照するスター・スキーマです。スノーストーム・スキーマは、スノーフレーク・スキーマの組合せです。
関連項目:
スター・スキーマについてさらに学習するには、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。
5.6.2 スター型変換の目的
ファクト表とディメンション表の結合では、スター型変換によりファクト表のフル・スキャンを回避できます。
スター型変換では、制約ディメンション行に結合される関連ファクト行のみをフェッチすることでパフォーマンスが向上します。場合によって、問合せがディメンション表の他の列に限定的なフィルタを持つことがあります。フィルタの組合せで、データベースがファクト表から処理するデータ・セットを動的に削減できます。
5.6.3 スター型変換の仕組み
スター型変換では、制約ディメンションに対応する、ビットマップ・セミ結合述語と呼ばれる副問合せ述語が追加されます。
オプティマイザは、ファクト結合列に索引が存在する場合に変換を実行します。副問合せによって提供されるキー値のビットマップANDおよびOR操作を駆動することによって、データベースでは、ファクト表から関連行のみを取り出せば済みます。ディメンション表の述語によってかなりのデータがフィルタ処理で除外される場合、変換は、ファクト表のフル・スキャンよりも効率的であると考えられます。
データベースがファクト表から関連行を取り出した後、データベースで、元の述語を使用してディメンション表にそれらの行を後戻り結合する必要がある場合があります。次の条件が当てはまる場合、データベースでは、ディメンション表の後戻り結合を回避できます。
-
ディメンション表のすべての述語がセミ結合副問合せ述語の一部である。
-
副問合せから選択された列が一意である。
-
ディメンション列が
SELECTリストやGROUP BY句などの中にない。
5.6.4 スター型変換の制御
STAR_TRANSFORMATION_ENABLED初期化パラメータは、スター型変換を制御します。
このパラメータの値は、次のいずれかです。
-
trueオプティマイザは、ファクト表と制約ディメンション表を自動的に識別することで、スター型変換を実行します。オプティマイザは、変換された計画のコストが他の計画のコストよりも低い場合にのみ、スター型変換を実行します。さらに、オプティマイザは、マテリアライズによりパフォーマンスが向上する場合は常に、一時表の変換を自動的に試行します(「一時表の変換: シナリオ」を参照)。
-
false(デフォルト)オプティマイザは、スター型変換を実行しません。
-
TEMP_DISABLEこの値は、オプティマイザが一時表の変換を試行しないこと以外は、
trueと同じです。
関連項目:
STAR_TRANSFORMATION_ENABLED初期化パラメータについて学習するには、Oracle Databaseリファレンスを参照してください
5.6.5 スター型変換: シナリオ
このシナリオでは、スター問合せのスター型変換を示します。
例5-5 スター問合せ
次の問合せでは、カリフォルニアのすべての市における、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-6 スター型変換
この例は、例5-5 の問合せのスター型変換を示しています。この変換により、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-7 スター型変換の実行計画の一部
この例は、例5-6のスター型変換の編集済の実行計画を示しています。
行26は、sales表に全表スキャンのかわりに索引アクセス・パスがあることを示しています。channels(行14)、times(行19)およびcustomers(行24)の副問合せから得られる各キー値について、データベースは、salesファクト表の索引からビットマップを取得します(行15、20、25)。
ビットマップの各ビットは、ファクト表の行に対応します。副問合せからのキー値がファクト表の行内の値と同じ場合、ビットが設定されます。たとえば、ビットマップ101000... (省略記号は、残りの列の値が0であることを示します)の場合、ファクト表の行1および3に、副問合せからのキー値と一致する値があります。
行12、17および22の操作が副問合せからのキーに対して繰り返され、対応するビットマップが取得されます。例5-6では、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.6.6 一時表の変換: シナリオ
前述のシナリオでは、表channelsは外部で参照されず、channel_idは一意であるため、この表はsales表に後戻り結合されません。
ただし、オプティマイザが後戻り結合をなくすことができない場合、データベースでは、一時表に副問合せの結果が格納されて、ビットマップ・キーの生成および後戻り結合のためにディメンションを再スキャンすることが回避されます。さらに、問合せがパラレルに実行される場合、データベースでは、各パラレル実行サーバーで、副問合せを再度実行するのではなく、一時表から結果を選択できるようにするために、結果がマテリアライズされます。
例5-8 一時表を使用したスター型変換
この例では、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-9 一時表を使用したスター型変換の実行計画の一部
次の例は、例5-8の問合せの編集済の実行計画を示しています。
---------------------------------------------------------------------------
| 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にフィルタを適用する必要はありません。
5.7 インメモリー集計(VECTOR GROUP BY)
インメモリー集計のキーとなる最適化は、スキャン中に集計することです。
典型的なスター・クエリーの場合など、集計に関係する問合せブロック、および単一の大きな表から複数の小さな表への結合を最適化するために、変換ではKEY VECTORおよびVECTOR GROUP BY操作を使用します。これらの操作では、結合と集計に効率的なインメモリー配列を使用します。基礎となる表がインメモリー列表の場合に特に効果的です。
関連項目:
インメモリー集計についてさらに学習するには、Oracle Database In-Memoryガイドを参照してください
5.8 cursor-duration一時表
問合せの中間結果をマテリアライズするため、Oracle Databaseは、問合せのコンパイル中にcursor-duration一時表をメモリー内に暗黙的に作成する場合があります。
この項では、次の項目について説明します。
5.8.1 cursor-duration一時表の目的
複雑な問合せは同じ問合せブロックを複数回処理することがあり、これによって不要なパフォーマンス・オーバーヘッドが作成されます。
このシナリオを回避するため、Oracle Databaseでは、問合せ結果の一時表を自動的に作成してカーソルの継続期間中、メモリー内に格納できます。WITH句問合せ、スター型変換、グループ化セットなどの複合操作の場合、この最適化により、繰返し使用される副問合せからの中間結果のマテリアライズが向上します。このように、cursor-duration一時表により、パフォーマンスが向上し、I/Oが最適化されます。
5.8.2 cursor-duration一時表の仕組み
cursor-duration一時表の定義はメモリー内に存在します。表の定義はカーソルに関連付けられており、カーソルを実行しているセッションでのみ参照可能です。
cursor-duration一時表を使用する場合、データベースでは次のステップを実行します。
-
cursor-duration一時表を使用する計画を選択します
-
一意の名前を使用して一時表を作成します
-
一時表を参照するために問合せを再度書き込みます
-
メモリーがなくなるまでデータをメモリーにロードすると、ディスク上に一時セグメントが作成されます
-
問合せを実行して、一時表からデータを戻します
-
表を切り捨て、メモリーおよびディスク上の一時セグメントを解放します
ノート:
cursor-duration一時表のメタデータは、カーソルがメモリー内にあるかぎりメモリー内に常駐します。メタデータはデータ・ディクショナリには格納されません。つまり、データ・ディクショナリ・ビューを介して参照することはできません。メタデータは明示的に削除できません。
前述のシナリオはメモリーの可用性に依存します。シリアル問合せの場合、一時表はPGAモリーを使用します。
cursor-duration一時表の実装はソートと似ています。もうメモリーが使用できない場合、データベースはデータを一時セグメントに書き込みます。cursor-duration一時表の場合、違いは次のとおりです。
-
データベースは、行ソースがアクティブでなくなるときではなく問合せの最後にメモリーおよび一時セグメントを解放します。
-
メモリー内のデータは、メモリーおよび一時セグメント間でデータを移動できるソートと異なり、メモリーに残ります。
データベースがcursor-duration一時表を使用する場合、キーワードCURSOR DURATION MEMORYが実行計画に表示されます。
5.8.3 cursor-duration一時表: 例
同じ副問合せを繰り返すWITH問合せは、cursor-duration一時表の利点を活用できる場合があります。
次の問合せでは、WITH句を使用して3つの副問合せブロックが作成されます。
WITH
q1 AS (SELECT department_id, SUM(salary) sum_sal FROM hr.employees GROUP BY department_id),
q2 AS (SELECT * FROM q1),
q3 AS (SELECT department_id, sum_sal FROM q1)
SELECT * FROM q1
UNION ALL
SELECT * FROM q2
UNION ALL
SELECT * FROM q3;次の計画例は、変換を示しています。
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'BASIC +ROWS +COST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows |Cost (%CPU)|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |6 (100)|
| 1 | TEMP TABLE TRANSFORMATION | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6606_1AE004 | | |
| 3 | HASH GROUP BY | | 11 | 3 (34)|
| 4 | TABLE ACCESS FULL | EMPLOYEES |107 | 2 (0) |
| 5 | UNION-ALL | | | |
| 6 | VIEW | | 11 | 2 (0) |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_1AE004 | 11 | 2 (0) |
| 8 | VIEW | | 11 | 2 (0) |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_1AE004 | 11 | 2 (0) |
| 10 | VIEW | | 11 | 2 (0) |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_1AE004 | 11 | 2 (0) |
-------------------------------------------------------------------------------------------前述の計画で、ステップ1のTEMP TABLE TRANSFORMATIONは、データベースがcursor-duration一時表を使用して問合せを実行したことを示しています。ステップ2のCURSOR DURATION MEMORYキーワードは、メモリーが使用可能であればデータベースがメモリーを使用してSYS_TEMP_0FD9D6606_1AE004の結果を格納したことを示しています。メモリーが使用できなかった場合は、データベースは一時データをディスクに書き込みます。
5.9 表拡張
表拡張では、オプティマイザが、パーティション表のread-mostly部分では索引を使用するが、表のアクティブ部分では索引を使用しない計画を生成します。
この項では、次の項目について説明します。
5.9.1 表拡張の目的
索引ベースの計画はパフォーマンスを改善できますが、索引メンテナンスがオーバーヘッドを作成します。多くのデータベースで、DMLが影響を及ぼすのはほんの一部のデータのみです。
表拡張では、更新のボリュームが大きい表に対して索引ベースの計画を使用します。read-mostlyデータにのみ索引を作成して、アクティブ・データの索引オーバーヘッドを排除できます。このように、表拡張はパフォーマンスを改善する一方で索引メンテナンスを回避します。
5.9.2 表拡張の仕組み
表のパーティション化により、表拡張が可能になります。
パーティション表にローカル索引が存在する場合、オプティマイザは、特定のパーティションに対してその索引を使用不可とマークすることができます。実際、一部のパーティションは索引付けされていません。
表拡張では、オプティマイザは、問合せをUNION ALL文に変換します。一部の副問合せは索引付けされたパーティションにアクセスし、他の副問合せは索引付けされていないパーティションにアクセスします。オプティマイザは、パーティションで使用可能な最も効率的なアクセス方法を選択できます。このとき、そのアクセス方法が、問合せでアクセスされるすべてのパーティションに対して存在するかどうかは関係ありません。
オプティマイザは、必ずしも表拡張を選択するとはかぎりません。
-
表拡張はコストベースです。
データベースは、拡張された表の各パーティションに、
UNION ALLのすべてのブランチで1回だけアクセスしますが、それに結合される表には各ブランチでアクセスします。 -
セマンティクスの問題により、拡張が無効になる場合があります。
たとえば、外部結合の右側にある表は、表拡張の対象としては無効です。
EXPAND_TABLEヒントを使用して表拡張を制御できます。ヒントは、コストベースの決定を上書きしますが、セマンティクス・チェックは上書きしません。
関連項目:
-
SQLヒントについてさらに学習するには、『Oracle Database SQL言語リファレンス』を参照してください
5.9.3 表拡張: シナリオ
オプティマイザは、問合せで使用されている述語に基づいて、各表からどのパーティションにアクセスする必要があるかを追跡します。パーティション・プルーニングにより、オプティマイザは、表拡張を使用してより最適な計画を生成できるようになります。
前提条件
この例では、次のことを前提としています。
-
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パーティションの索引を無効にします。iこの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)。
5.9.4 表拡張とスター型変換: シナリオ
スター型変換により、特定のタイプの問合せで、大きなファクト表の大部分にアクセスすることを回避できます。
スター型変換では、いくつかの索引を定義する必要があります。それらの索引により、アクティブに更新される表ではオーバーヘッドが生じる可能性があります。表拡張では、オプティマイザが表の索引付けされた部分でのみスター型変換を検討できるようにするために、アクティブでないパーティションでのみ索引を定義することが可能です。
前提条件
この例では、次のことを前提としています。
-
「スター型変換: シナリオ」で使用されているものと同じスキーマを問い合せます。
-
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の索引は無効化されているため、データベースは、全表スキャンを使用して最後のパーティションにアクセスします。
関連トピック
5.10 結合の因数分解
結合の因数分解として知られるコストベースの変換では、オプティマイザが、UNION ALL問合せのブランチの共通の計算を因数分解できます。
この項では、次の項目について説明します。
5.10.1 結合の因数分解の目的
UNION ALL問合せは、データベース・アプリケーション、特にデータ統合アプリケーションでは一般的です。
多くの場合、UNION ALL問合せのブランチは、同じ実表を参照します。結合の因数分解を使用しない場合、オプティマイザは、UNION ALL問合せの各ブランチを単独で評価します。そのため、データ・アクセスや結合などの処理が繰り返し実行されることになります。結合の因数分解の変換では、UNION ALLブランチ間で共通の計算を共有できます。大きな実表を追加でスキャンすることを回避できるため、パフォーマンスが大幅に向上します。
5.10.2 結合の因数分解の仕組み
結合の因数分解では、3つ以上のUNION ALLブランチから複数の表を因数分解できます。
次の例を使用して、結合の因数分解について説明します。
例5-10 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-11 因数分解された問合せ
次の問合せでは、結合の因数分解を使用して例5-10の問合せを変換します。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.10.3 因数分解と結合順序: シナリオ
結合の因数分解で、結合順序の可能性を高めることができます。
例5-12 5つの表を含む問合せ
次の問合せで、ビューVは、例5-10の問合せと同じです。
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.c1t1、t2およびt3を結合して、その後それらをt5に結合する必要があります。
例5-13 ビュー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-14 ビュー定義が削除されたビューVからのt1の因数分解
次の問合せは例5-13の問合せと同じですが、因数分解をわかりやすくするために、ビュー定義が削除されています。
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
5.10.4 外部結合の因数分解: シナリオ
データベースは、外部結合、アンチ結合およびセミ結合の因数分解をサポートしていますが、サポートしているのは、そのような結合の右の表に対してのみです。
たとえば、結合の因数分解では、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(+)