35.读书笔记收获不止Oracle之嵌套循环表连接与索引
表连接的研究中,索引是非常重要的一部分,对提升表连接性能起到至关重要的作用。
1. 嵌套循环与索引
sql>alter session set statistics_level=all;
sql>select /*+leading(t1) use_nl(t2)*/ * from t1,t2 wheret1.id=t2.t1_id and t1.n=19;
sql> select * from table(dbms_xplan.display_cursor(null,null,'allstatslast'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
sql_ID 2w8kmgu3tmxhq,child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2)*/ * fromt1,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 | Buff
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ers |
--------------------------------------------------------------------------------
-----
| 0| SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1
014 |
| 1| NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1
014 |
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 | 1
006 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified byoperation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
2- filter("T1"."N"=19)
3- filter("T1"."ID"="T2"."T1_ID")
22 rows selected.
1.1不用HINT
sql>select * from t1,t2 where t1.id=t2.t1_id and t1.n=19;
sql> select * fromtable(dbms_xplan.display_cursor(null,'allstats last'));
--------------------------------------------------------------------------------
sql_ID g7rb3y8bmguur,child number 0
-------------------------------------
select * from t1,t2 where t1.id=t2.t1_idand t1.n=19
Plan hash value: 1838229974
--------------------------------------------------------------------------------
--------------------------------
| Id| Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
ers |OMem | 1Mem | Used-Mem |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------
| 0| SELECT STATEMENT | | 1 | | 1 |00:00:00.06 | 1
013 | | | |
|* 1| HASH JOIN | | 1 | 1 | 1 |00:00:00.06 | 1
013 | 960K| 960K|385K (0)|
|* 2| TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
7 | | | |
| 3| TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1
006 | | | |
--------------------------------------------------------------------------------
--------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1- access("T1"."ID"="T2"."T1_ID")
2- filter("T1"."N"=19)
21 rows selected.
没有HINT,就走Hash Join。其实两者的消耗应该是差不多的。
1.2T1表键索引后
sql>create index t1_n on t1(n);
sql> select /*+leading(t1) use_nl(t2)*/ * from t1,t2 wheret1.id=t2.t1_id and t1.n=19;
sql> select * fromtable(dbms_xplan.display_cursor(null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
sql_ID 4n9w3nnj61xk0,t2 where t1.id=t2.t1_id
and t1.n=19
Plan hash value: 2987075831
--------------------------------------------------------------------------------
-----------------------
| Id| Operation | Name | Starts | E-Rows | A-Rows |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
A-Time | Buffers |
--------------------------------------------------------------------------------
-----------------------
| 0| SELECT STATEMENT | | 1 | |1 |
00:00:00.01 | 1009 |
| 1| NESTED LOOPS | | 1 | 1 |1 |
00:00:00.01 | 1009 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2| TABLE ACCESS BY INDEX ROWID BATCHED|T1 | 1 | 1 |1 |
00:00:00.01 | 3 |
|* 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 |
--------------------------------------------------------------------------------
-----------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
3- access("T1"."N"=19)
4- filter("T1"."ID"="T2"."T1_ID")
23 rows selected.
22 rows selected.
T1表走的是索引了。
1.3T2表建索引
sql>create index t2_t1_id on t2(t1_id);
sql> select /*+leading(t1) use_nl(t2)*/ * from t1,t2 wheret1.id=t2.t1_id and t1.n=19;
sql> select * fromtable(dbms_xplan.display_cursor(null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
sql_ID 2w8kmgu3tmxhq,t2 where t1.id=t2.t1_id
and t1.n=19
Plan hash value: 342856344
--------------------------------------------------------------------------------
-------------------------------------
| Id| Operation | Name | Starts | E-Rows | A-R
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------
-------------------------------------
| 0| SELECT STATEMENT | | 1| |
1|00:00:00.03 | 7 | 4 |
| 1| NESTED LOOPS | | 1| 1 |
1|00:00:00.03 | 7 | 4 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2| NESTED LOOPS | | 1| 1 |
1|00:00:00.03 | 6 | 4 |
| 3| TABLE ACCESS BY INDEX ROWID BATCHED|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.03 | 3 | 4 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6| TABLE ACCESS BY INDEX ROWID |T2 | 1 | 1 |
1|00:00:00.01 | 1 | 0 |
--------------------------------------------------------------------------------
-------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
4- access("T1"."N"=19)
5- access("T1"."ID"="T2"."T1_ID")
25 rows selected.
T2表也走索引了。现在不用HINT,ORACLE也会自己走索引了,因为代价已经很低了。
如下:
sql>select * from t1,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
sql_ID g7rb3y8bmguur,t2 where t1.id=t2.t1_idand t1.n=19
Plan hash value: 342856344
--------------------------------------------------------------------------------
----------------------------
| Id| Operation | Name | Starts | E-Rows | A-R
ows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------
| 0| SELECT STATEMENT | | 1| |
1|00:00:00.01 | 6 |
| 1| NESTED LOOPS | | 1| 1 |
1|00:00:00.01 | 6 |
| 2| NESTED LOOPS | | 1| 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1|00:00:00.01 | 5 |
| 3| TABLE ACCESS BY INDEX ROWID BATCHED|T1 | 1 | 1 |
1|00:00:00.01 | 2 |
|* 4| INDEX RANGE SCAN | T1_N | 1 | 1 |
1|00:00:00.01 | 1 |
|* 5| INDEX RANGE SCAN | T2_T1_ID | 1 | 1 |
1|00:00:00.01 | 3 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6| TABLE ACCESS BY INDEX ROWID |T2 | 1 | 1 |
1|00:00:00.01 | 1 |
--------------------------------------------------------------------------------
----------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
4- access("T1"."N"=19)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
5- access("T1"."ID"="T2"."T1_ID")
Note
-----
-this is an adaptive plan
28 rows selected.
2. 最实用NL连接的场景
两表关联返回的记录不多。
遇到一些不等值查询,只能试用NL连接。
当然最好:驱动表的限制条件所在的列有索引,被驱动表的连接条件所在的列也有索引。
原文链接:https://www.f2er.com/oracle/207043.html