【読書ノート】【収穫、Oracleだけではない】ネストされたループとインデックス
1.1 HINT強制ネストループ方式で2つのテーブルを接続する
その結果、インデックスがない場合、Buffersは1014を生成することが分かった.2.1 HINTを使用しない場合、oracle自体がハッシュ接続を選択します
HINTを使用するとBuffersは1014を生成します
HINTを使用しない場合、Buffersは1020を生成します. .発生したbuffersがNESTED LOOPSより多くても、コンパイラはHASH JOINを選択します.
3.1 t 1のn列にインデックスを作成する
t 1のn列にインデックスが作成された後、生成されたBuffers:1012
インデックスの作成が容易になると、パフォーマンスが少し向上します.
両者の差はt 1テーブルにあり、
インデックスなし:TABLE ACCESS FULL 8
インデックスあり:INDEX RANGE SCAN 2
4.1 t 2のt 1_idにインデックスを作成し、パフォーマンスを向上させ続けます
新しいインデックスの後、buffersは1012から7に下がりました.パフォーマンスが大幅に向上しました.
まとめ:
Nested Loop接続に適合する場合:
1両表の関連付けで返されるレコードは多くありません.
理想的には、ドライバテーブルの結果セットは1つまたは複数のレコードのみを返し、被ドライバテーブルも1つまたは複数のレコードのみを一致させます.2つのテーブルのデータ量が大きくても、クエリーの速度は速いです.
2.いくつかの不等値クエリによってハッシュとソートの結合が制限され、やむを得ずNested Loop接続を使用してインデックスを確立する場合:
1.駆動テーブル(t 1)の制約(n)が存在するカラムインデックス(t 1_n)
2.被駆動テーブル(t 2)の接続条件(t 1_id)が存在するカラムインデックス(t 2_t 1_id)
select /*+leading(t1) use_nl(t2) */*
from t1,t2
where t1.id = t2.t1_id
and t1.n = 19;SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuqy42vhj5n6k, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2) */* from t1,t2 where t1.id = t2.t1_id and
t1.n = 19
Plan hash value: 1967407726
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1014 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 1 | 1 |00:00:00.01 | 1006 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"="T2"."T1_ID")
Note
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement
25 rows selected.その結果、インデックスがない場合、Buffersは1014を生成することが分かった.2.1 HINTを使用しない場合、oracle自体がハッシュ接続を選択します
select *
from t1,t2
where t1.id = t2.t1_id
and t1.n = 19;SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID agmm8r3nv4gcg, child number 0
-------------------------------------
select * from t1,t2 where t1.id = t2.t1_id and t1.n = 19
Plan hash value: 1838229974
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.83 | 1013 | 741K| 741K| 306K (0)|
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 3 | TABLE ACCESS FULL| T2 | 1 | 102K| 100K|00:00:00.20 | 1006 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
2 - filter("T1"."N"=19)
Note
-----
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- dynamic sampling used for this statement
24 rows selected.HINTを使用するとBuffersは1014を生成します
Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1014 |
HINTを使用しない場合、Buffersは1020を生成します. .発生したbuffersがNESTED LOOPSより多くても、コンパイラはHASH JOINを選択します.
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.83 | 1013 | 741K| 741K| 306K (0)|
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 7 | | | |3.1 t 1のn列にインデックスを作成する
SCOTT@ orcl>create index t1_n on t1(n);
Index created
select /*+leading(t1) use_nl(t2) */*
from t1,t2
where t1.id = t2.t1_id
and t1.n = 19;SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuqy42vhj5n6k, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2) */* from t1,t2 where t1.id = t2.t1_id and t1.n = 19
Plan hash value: 76617097
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1009 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 3 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 3 | INDEX RANGE SCAN | T1_N | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS FULL | T2 | 1 | 1 | 1 |00:00:00.01 | 1006 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."N"=19)
4 - filter("T1"."ID"="T2"."T1_ID")
Note
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement
25 rows selected.t 1のn列にインデックスが作成された後、生成されたBuffers:1012
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1009 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 3 |
Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1014 |
インデックスの作成が容易になると、パフォーマンスが少し向上します.
両者の差はt 1テーブルにあり、
インデックスなし:TABLE ACCESS FULL 8
インデックスあり:INDEX RANGE SCAN 2
4.1 t 2のt 1_idにインデックスを作成し、パフォーマンスを向上させ続けます
SCOTT@ orcl>create index t2_t1_id on t2(t1_id);
Index created
select /*+leading(t1) use_nl(t2) */*
from t1,t2
where t1.id = t2.t1_id
and t1.n = 19;SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuqy42vhj5n6k, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2) */* from t1,t2 where t1.id = t2.t1_id and t1.n = 19
Plan hash value: 2669480776
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 1 |00:00:00.01 | 7 | 1 |
| 2 | NESTED LOOPS | | 1 | 1 | 3 |00:00:00.01 | 6 | 1 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
|* 4 | INDEX RANGE SCAN | T1_N | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
|* 5 | INDEX RANGE SCAN | T2_T1_ID | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."N"=19)
5 - access("T1"."ID"="T2"."T1_ID")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
26 rows selected.-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1009 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 3 |新しいインデックスの後、buffersは1012から7に下がりました.パフォーマンスが大幅に向上しました.
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 1 |00:00:00.01 | 7 | 1 |
| 2 | NESTED LOOPS | | 1 | 1 | 3 |00:00:00.01 | 6 | 1 |
まとめ:
Nested Loop接続に適合する場合:
1両表の関連付けで返されるレコードは多くありません.
理想的には、ドライバテーブルの結果セットは1つまたは複数のレコードのみを返し、被ドライバテーブルも1つまたは複数のレコードのみを一致させます.2つのテーブルのデータ量が大きくても、クエリーの速度は速いです.
2.いくつかの不等値クエリによってハッシュとソートの結合が制限され、やむを得ずNested Loop接続を使用してインデックスを確立する場合:
1.駆動テーブル(t 1)の制約(n)が存在するカラムインデックス(t 1_n)
2.被駆動テーブル(t 2)の接続条件(t 1_id)が存在するカラムインデックス(t 2_t 1_id)