Oracleテーブル接続方式の最適化方法（例あり）-mysql チュートリアル-php.cn

この記事の内容は、Oracle テーブル接続の最適化方法（例付き）に関するもので、一定の参考価値がありますので、困っている方は参考にしていただければ幸いです。

Oracleデータベースでは、2つのテーブル間のテーブル接続方法として、ソート・マージ接続、ネスト・ループ接続、ハッシュ接続、デカルト接続の4つがあります

1.ソート・マージ接続(ソート・マージ結合)

ソート・マージ・ジョインは、2 つのテーブルを接続する場合に、ソート (SORT) 操作とマージ (MERGE) 操作を使用して接続結果セットを取得するテーブル接続方法です。 t2 テーブルがテーブル接続を作成するときにソートマージ接続を使用すると、Oracle は次の手順を順番に実行します:

a. ターゲット SQL で指定された述語条件で t1 テーブルにアクセスし、結果は次のようになります。 t1テーブルの接続列を基準にソートし、ソート結果集合をs1

bとして記録対象SQLに指定された述語条件に基づいてt2テーブルにアクセスし、アクセス結果をソートt2 テーブルの接続列に追加すると、ソートされた結果セットは s2

c として記録されます。s1 と s2 をマージし、一致するレコードを最終的な結果セットとして取り出します

利点、接続のソートとマージの短所と応用シナリオ:

a. 通常の状況では、ハッシュ結合の効果はソートマージ結合より優れていますが、行ソースがソートされている場合は、ソートする必要はありません。ソート・マージ結合を実行する場合は、再度ソートしてください。この場合、ソート・マージの方がハッシュ結合よりもパフォーマンスが向上します。

b. 通常、ソート・マージ結合は、次の状況が発生した場合にのみ使用されます。

1) RBO モード

2) 不等値結合 (>,<,>=,<=)

3) ハッシュ結合が無効な場合 (_HASH_JOIN_ENABLED=false) )

例

SQL> select * from scott.emp t1,scott.emp t2 where t1.empno > t2.mgr;
 
89 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3950110903
 
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    62 |  4712 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                   |        |    62 |  4712 |     6  (17)| 00:00:01 |
|   2 |   SORT JOIN                   |        |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  5 |   SORT JOIN                   |        |    14 |   532 |     4  (25)| 00:00:01 |
|   6 |    TABLE ACCESS FULL          | EMP    |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR"))
       filter(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR"))
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       6612  bytes sent via SQL*Net to client
        575  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         89  rows processed
 
SQL>

ログイン後にコピー

2. ネストされたループの結合

ネストされたループの結合は、2 つのレベルのネストされたループ (外側のループ/内部) に依存する 2 つのテーブル接続の一種です。ループ) 接続結果セットのテーブル接続メソッドを取得するには

#t1 テーブルと t2 テーブルがテーブル接続を行うときにネストされたループ接続を使用する場合、Oracle は次の手順を順番に実行します。

a. まず、オプティマイザは、特定のルールに従って、t1 と t2 で誰が駆動テーブルで誰が被駆動テーブルであるかを決定します。駆動テーブルは外側のループに使用され、被駆動テーブルはメモリループに使用されます。。 t1 が駆動テーブル

b であるとします。ターゲット SQL で指定された述語条件を使用して駆動テーブル t1 にアクセスし、結果セット s1

c を取得します。s1 を走査し、同時に駆動テーブル t2 を走査、つまり取り出します。 s1 内のレコードは、接続条件に従って駆動テーブル t2 と照合されます。最終的に結果セットが返されます。

ネストされたループ接続の長所、短所、適用可能なシナリオ:

a. 高速な応答を実現できます。接続結果を返す前に、すべての接続操作が完了するのを待つ必要がなく、条件付きレコードができるだけ早く返されること

b. 駆動テーブルに対応する駆動結果セットのサイズが小さいことが適しています。レコードの数と同時に、駆動テーブルの接続に列に一意のインデックスがあります (または、駆動テーブルの接続列に適切な選択性を持つ非一意のインデックス)

#Example

SQL> select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,&#39;allstats,last&#39;));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dcsf9m1rzzga5, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno
 
Plan hash value: 4192419542
 
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |      32 |
|   1 |  NESTED LOOPS      |      |      1 |     14 |     14 |00:00:00.01 |      32 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      4 |     14 |00:00:00.01 |      25 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
- filter("T1"."DEPTNO"="T2"."DEPTNO")
rows selected.
 
SQL>

ログイン後にコピー

3. ハッシュ結合

ハッシュ結合は、2 つのテーブルが接続されている場合に、ハッシュ操作に依存して接続結果セットを取得するテーブル接続方法です。 Oracle 7.3 以降に導入されました

ハッシュ結合は、テーブル (通常は小さなテーブル) をハッシュしてハッシュリストに格納し、別のテーブルからレコードを抽出し、ハッシュ操作を実行して、ハッシュ内の対応する値を見つけることによって機能します。 list. 、一致するための

ハッシュ結合は CBO にのみ適用でき、同等の結合条件にのみ使用できます

ハッシュ結合は、小さなテーブルと大きなテーブルの結合に非常に適しています。特に、小さなテーブル内の結合列の選択性が非常に優れている場合、ハッシュ結合の実行時間は、大きなテーブルの全テーブルスキャンに費やされる時間とほぼ同じになる可能性があります。ハッシュ接続を行う場合、対応するハッシュテーブルはドライバーの結果セットはメモリ (PGA ワークスペース) に完全に収容できます。この時点で、ハッシュ接続の実行効率は非常に高くなります。

ハッシュ接続のパフォーマンスの問題は 10104 によって解決できます。診断するイベントの場合、関連する手順は次のとおりです。

メモリ内パーティションの数 (変更されている可能性があります): ハッシュパーティション

最終的なハッシュバケットの数: ハッシュバケットの数

バケットの合計:空のバケット: 空ではないバケット: ハッシュバケット内の空のレコードと空ではないレコードの状況

合計行数: 駆動結果セット内のレコードの数

バケット内の最大行数:ハッシュバケットに含まれるレコード数が最も多いレコード Number

Disabled bitmap filtering: ビットマップフィルタリングを有効にするかどうか

Example

SQL> select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,&#39;allstats,last&#39;));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0j83q86ara5u2, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno
 
Plan hash value: 615168685
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     14 |     14 |00:00:00.01 |      13 |  1321K|  1321K| 1070K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."DEPTNO"="T2"."DEPTNO")
 
 
21 rows selected.
 
SQL>