実行計画の解析方法(2)
本ページでは3種の表結合時の実行計画の解析方法を記載します。関連ページ：実行計画の解析方法(1)
結合を含む実行計画を読む上での前提知識
・11gR2現在、結合は一表ずつしかできず結合方法は3種類のみ(ネステッドループ結合、ハッシュ結合、マージ結合)。この3種の結合に対して結合目的に応じた様々なオプション(外部結合(～ OUTER)、セミ結合(～ SEMI)、アンチ結合(～ ANTI)、直積(MERGE JOIN CARTESIAN)等)が付与される場合がある。
・ネステッドループやハッシュ結合では結合順序が大きな性能差の要因となる場合があるためどちらが実行計画の上のほうに位置しているかは重要な情報になる。(上にあるのが外部表(最初にアクセスするほう)と考えると分かりやすい)
本ページで使用した検証用データの定義
本ページで記載されているSQLは全て以下のSQLで作成されたテーブルとインデックスを使用しています。CREATE TABLE TBL1 (COL1 NUMBER ,COL2 NUMBER); BEGIN FOR I IN 1..10000 LOOP INSERT INTO TBL1 VALUES(I,MOD(I,10)); END LOOP; COMMIT; END; / ALTER TABLE TBL1 ADD CONSTRAINT PK_TBL1 PRIMARY KEY(COL1); CREATE TABLE TBL2 AS SELECT * FROM TBL1; ALTER TABLE TBL2 ADD CONSTRAINT PK_TBL2 PRIMARY KEY(COL1); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST',TABNAME=>'TBL1',CASCADE=>TRUE); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TEST',TABNAME=>'TBL2',CASCADE=>TRUE);統計情報は以下の通りで実データと合致した値となっています。
select table_name,blocks ,num_rows from user_tables where table_name LIKE 'TBL%'; TABLE_NAME BLOCKS NUM_ROWS ------------------------------------------------------------ ---------- ---------- TBL1 20 10000 TBL2 21 10000 select table_name,NUM_ROWS,BLEVEL ,LEAF_BLOCKS, DISTINCT_KEYS ,AVG_LEAF_BLOCKS_PER_KEY , AVG_DATA_BLOCKS_PER_KEY ,CLUSTERING_FACTOR from user_indexes where table_name LIKE 'TBL%'; TABLE_NAME NUM_ROWS BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR ---------- ---------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- TBL2 10000 1 20 10000 1 1 23 TBL1 10000 1 18 10000 1 1 17 select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT, utl_raw.CAST_TO_NUMBER(LOW_VALUE) LOW_VALUE,utl_raw.CAST_TO_NUMBER(HIGH_VALUE) HIGH_VALUE, DENSITY,NUM_NULLS,AVG_COL_LEN from user_tab_columns where table_name LIKE 'TBL%'; TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS AVG_COL_LEN ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------- TBL1 COL1 10000 1 10000 .0001 0 4 TBL1 COL2 10 0 9 .1 0 3 TBL2 COL1 10000 1 10000 .0001 0 4 TBL2 COL2 10 0 9 .1 0 3
例1 ネステッドループ結合
この実行計画はTBL1とTBL2というテーブルをネステッドループで結合する実行計画です。 TBL2へのアクセスが実行計画の上にきていることからTBL2が外部表(駆動表)、TBL1が内部表であることがわかります。Id=3でTBL2のPK索引からアクセスしていますが、TBL2への「TABLE ACCESS BY INDEX ROWID」のオペレーションがないため表へはアクセスしていません。 これは最終的に戻す結果がTBL2.COL2の件数だけであるため表を参照せずとも索引だけでSQLの結果を返すことができるためです。 また、b.col1=1という条件がないにもかかわらずtbl2.col1=1の索引ユニークスキャンができているのはa.col1=b.col1かつa.col1=1という条件からb.col1=1も成り立つためORACLEが内部的に条件を補完しているためです。(推移律)
この実行計画の処理の流れは以下の通りです。
①PK_TBL2のcol1=1のユニークスキャンにより1行取り出す(Id=3)
②PK_TBL1のcol1=1のユニークスキャンにより条件に一致するrowidを取得する(Id=5)
③②で取得したrowidから表の行を取得する(Id=4)
④①の1行と③の１行を結合する(Id=2)
⑤結合後の件数をカウントする(Id=1)。このケースの駆動表の結合対象行数は1行であるためこれで終了だが複数行あった場合は②～⑤を繰り返す
select count(a.col2) from tbl1 a,tbl2 b where a.col1=b.col1 and a.col1=1;
実行計画
----------------------------------------------------------
Plan hash value: 1064122550
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | NESTED LOOPS | | 1 | 11 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_TBL2 | 1 | 4 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TBL1 | 1 | 7 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_TBL1 | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."COL1"=1)
5 - access("A"."COL1"=1)
例2 ハッシュ結合
この実行計画はTBL1とTBL2をハッシュ結合する実行計画です。 TBL1へのアクセスが上のほうにきていることからTBL1が外部表(結合対象行がPGA上にハッシュ展開される側)であることがわかります。この実行計画の処理の流れは以下の通りです。
①TBL1をフルスキャンし、全件PGA上にハッシュテーブル展開する(あまり件数が多すぎる場合は一時表領域に書き出される)(Id=3)。 この例では結合条件に索引列ではないcol2も指定されているため表へのアクセスが必要です。col1のみの条件であればインデックスファストフルスキャンで処理可能です。
②TBL2をフルスキャンし、1行ずつ①で作成したハッシュテーブルと結合していく(Id=4)。 このSQLの場合TBL2のデータはハッシュテーブルに展開されませんが、さらに他の表とハッシュ結合するような場合①と②を結合したデータがさらにハッシュテーブル展開されます。
③「②」で結合できる行が見つかった場合行数をカウントアップする(Id=1)。②～③を全件数分処理します。
select count(b.col2) from tbl1 a,tbl2 b where a.col1=b.col1 and a.col2=b.col2;
----------------------------------------------------------
Plan hash value: 970933070
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 17 (6)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | HASH JOIN | | 10000 | 136K| 17 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TBL1 | 10000 | 70000 | 8 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TBL2 | 10000 | 70000 | 8 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2")
例3 マージ結合
この実行計画はTBL1とTBL2をマージ結合する実行計画です。 基本的にハッシュ結合やネステッドループのほうが性能が良いためソートがかかわる場合や結合件数が多い非等価結合の場合でなければマージ結合が選択されるケースは少なくなります。 マージ結合は結合対象となる行をそれぞれソートした上で結合を行いますのでどちらのテーブルが実行計画の上であってもあまり差異がありません。①インデックスファストフルスキャンによりPK_TBL2の全ブロックを読み込む(Id=3)。
②「①」の行をcol1でソートした結果をPGA上に保持しておく(Id=2)。
③インデックスファストフルスキャンによりPK_TBL2の全ブロックを読み込む(Id=5)。
④「③」の行をcol1でソートした結果をPGA上に保持しておく(Id=4)。
⑤「②④」を結合する(Id=1)。このSQLではorder by句が指定されていますがマージ結合の過程でソート済みの状態で結合されるためSORT ORDER BYオペレーションはでてきません。
select a.col1 from tbl1 a,tbl2 b where a.col1=b.col1 order by a.col1;
実行計画
----------------------------------------------------------
Plan hash value: 3415121672
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 80000 | 18 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 10000 | 80000 | 18 (17)| 00:00:01 |
| 2 | SORT JOIN | | 10000 | 40000 | 9 (12)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| PK_TBL2 | 10000 | 40000 | 8 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 10000 | 40000 | 8 (13)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| PK_TBL1 | 10000 | 40000 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."COL1"="B"."COL1")
filter("A"."COL1"="B"."COL1")
折りたたみ
展開