主要的表连接类型:
- 嵌套循环
- 哈希连接
- 排序合并
各种连接类型的差异:
- 访问次数:
- 嵌套循环:驱动表查询n条记录,被驱动表访问n次。
SQL> create table t1(id number not null,n number,contents varchar2(4000)); Table created. SQL> create table t2(id number not null,t1_id number not null, n number,contents varchar2(2000)); Table created. SQL> execute dbms_random.seed(0); PL/SQL procedure successfully completed. SQL> insert into t1 select rownum,rownum,dbms_random.string('a',50) from dual connect by level<=100 order by dbms_random.random; 100 rows created. SQL> insert into t2 select rownum,rownum,rownum,dbms_random.string('b',50) from dual connect by level<=100000 order by dbms_random.random; 100000 rows created. SQL> commit; Commit complete. SQL> alter session set statistics_level =all; SQL> select /*+ leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id; SQL> set linesize 1000 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.37 | 100K| | 1 | NESTED LOOPS | | 1 | 100 | 100 |00:00:00.37 | 100K| | 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 14 | |* 3 | TABLE ACCESS FULL| T2 | 100 | 1 | 100 |00:00:00.37 | 100K| 3 - filter("T1"."ID"="T2"."T1_ID") 24 rows selected. --注t1被访问一次,t2被访问100次。
- 哈希连接:驱动表和被驱动表都是最多被访问1次。
SQL> select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id; SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+ leading(t1) use_hash(t2)*/ * from t1,t2 where t1.id=t2.t1_id | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.05 | 1019 | | | | |* 1 | HASH JOIN | | 1 | 100 | 100 |00:00:00.05 | 1019 | 821K| 821K| 1244K (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | | | 3 | TABLE ACCESS FULL| T2 | 1 | 93984 | 100K|00:00:00.01 | 1012 | | | | ---------------------------------------------------------------------------------------------------------------- 1 - access("T1"."ID"="T2"."T1_ID") -- 两个表最多访问1次
- 排序合并:两个表都是最多被访问1次,而且没有驱动和被驱动的概念。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+ordered use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.05 | 1012 | | | | | 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.05 | 1012 | | | | | 2 | SORT JOIN | | 1 | 100 | 100 |00:00:00.01 | 7 | 13312 | 13312 |12288 (0)| | 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | | |* 4 | SORT JOIN | | 100 | 93984 | 100 |00:00:00.05 | 1005 | 9762K| 1209K| 8677K (0)| | 5 | TABLE ACCESS FULL| T2 | 1 | 93984 | 100K|00:00:00.01 | 1005 | | | | ----------------------------------------------------------------------------------------------------------------- 4 - access("T1"."ID"="T2"."T1_ID") filter("T1"."ID"="T2"."T1_ID") 27 rows selected.
- 嵌套循环:驱动表查询n条记录,被驱动表访问n次。
- 驱动顺序:
- 嵌套循环:有驱动顺序
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+ leading(t2) use_nl(t1)*/ * from t1,t2 where t1.id=t2.t1_id | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.57 | 701K| | 1 | NESTED LOOPS | | 1 | 100 | 100 |00:00:00.57 | 701K| | 2 | TABLE ACCESS FULL| T2 | 1 | 93984 | 100K|00:00:00.01 | 1012 | |* 3 | TABLE ACCESS FULL| T1 | 100K| 1 | 100 |00:00:00.54 | 700K| 3 - filter("T1"."ID"="T2"."T1_ID") 24 rows selected.
- 哈希连接:有驱动顺序
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+ leading(t2) use_hash(t1)*/ * from t1,t2 where t1.id=t2.t1_id | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.07 | 1019 | | | | |* 1 | HASH JOIN | | 1 | 100 | 100 |00:00:00.07 | 1019 | 10M| 2143K| 16M (0)| | 2 | TABLE ACCESS FULL| T2 | 1 | 93984 | 100K|00:00:00.01 | 1005 | | | | | 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 14 | | | | ---------------------------------------------------------------------------------------------------------------- 24 rows selected.
- 排序合并:无驱动顺序,可以看到所有的数据都是一样的没有差别
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.04 | 1012 | | | | | 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.04 | 1012 | | | | | 2 | SORT JOIN | | 1 | 100 | 100 |00:00:00.01 | 7 | 13312 | 13312 |12288 (0)| | 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | | |* 4 | SORT JOIN | | 100 | 93984 | 100 |00:00:00.04 | 1005 | 9762K| 1209K| 8677K (0)| | 5 | TABLE ACCESS FULL| T2 | 1 | 93984 | 100K|00:00:00.01 | 1005 | | | | 4 - access("T1"."ID"="T2"."T1_ID") filter("T1"."ID"="T2"."T1_ID") 27 rows selected. SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+leading(t2) use_merge(t1)*/ * from t1,t2 where t1.id=t2.t1_id | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.05 | 1012 | | | | | 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.05 | 1012 | | | | | 2 | SORT JOIN | | 1 | 93984 | 101 |00:00:00.05 | 1005 | 9762K| 1209K| 8677K (0)| | 3 | TABLE ACCESS FULL| T2 | 1 | 93984 | 100K|00:00:00.01 | 1005 | | | | |* 4 | SORT JOIN | | 101 | 100 | 100 |00:00:00.01 | 7 | 13312 | 13312 |12288 (0)| | 5 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | | 27 rows selected.
- 嵌套循环:有驱动顺序
- 排序差异:
- 嵌套循环:无需排序
- 哈希连接:无须排序(hash_area_size),消耗的资源是用来建立哈希表。
- 排序合并:需要排序(sort_area_size)
- 限制场景:
- 嵌套循环:无任何限制
- 哈希连接:连接条件是<>><或like导致无法使用
- 排序合并:连接条件是><或like导致无法使用
索引与连接的经典优化
- 嵌套循环:驱动表限制条件有索引,被驱动表连接条件有索引:
select * from t1,t2 where t1.id=t2.t1_id and t1.n=19 and t2.n=2932 --这里 t1.id=t2.t1_id是连接条件; t1.n=19 and t2.n=2932是限制条件。索引要建在t1(n) 和t2(t1_id)上。
- 适用于:两表返回记录少;而且被驱动表仅仅匹配少量记录。就算大表,加上索引以后也能很快定位。
- 不等值查询,不得不用,因为其他索引失效。
- 哈希连接:索引列在表连接中无特殊要求,和单表情况无异
- 连接条件起不到什么作用,但是限制条件列需要加上索引。
- 在PGA中增大hash_area_size也是可行的,默认128k。
- 排序合并:连接条件上的索引可以消除排序
--开始没有建索引 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.06 | 1012 | | | | | 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.06 | 1012 | | | | | 2 | SORT JOIN | | 1 | 100 | 100 |00:00:00.01 | 7 | 13312 | 13312 |12288 (0)| | 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | | |* 4 | SORT JOIN | | 100 | 100K| 100 |00:00:00.06 | 1005 | 9762K| 1209K| 8677K (0)| | 5 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.02 | 1005 | | | | --在t1上建索引 SQL> create index idx_1 on t1(id); SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.06 | 1021 | | | | | 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.06 | 1021 | | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 100 | 100 |00:00:00.01 | 16 | | | | | 3 | INDEX FULL SCAN | IDX_1 | 1 | 100 | 100 |00:00:00.01 | 8 | | | | |* 4 | SORT JOIN | | 100 | 100K| 100 |00:00:00.06 | 1005 | 9762K| 1209K| 8677K (0)| | 5 | TABLE ACCESS FULL | T2 | 1 | 100K| 100K|00:00:00.02 | 1005 | | | | --在t2上建索引,发现没有起到作用。 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); select /*+leading(t1) use_merge(t2)*/ * from t1,t2 where t1.id=t2.t1_id | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.06 | 1021 | | | | | 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.06 | 1021 | | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 100 | 100 |00:00:00.01 | 16 | | | | | 3 | INDEX FULL SCAN | IDX_1 | 1 | 100 | 100 |00:00:00.01 | 8 | | | | |* 4 | SORT JOIN | | 100 | 100K| 100 |00:00:00.06 | 1005 | 9762K| 1209K| 8677K (0)| | 5 | TABLE ACCESS FULL | T2 | 1 | 100K| 100K|00:00:00.02 | 1005 | | | |
--将两张表次序对调,索引idx_1排序的功能彻底失效。看来还是有bug。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+leading(t2) use_merge(t1)*/ * from t1,t2 where t1.id=t2.t1_id
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 100 |00:00:00.07 | 1012 | | | |
| 1 | MERGE JOIN | | 1 | 100 | 100 |00:00:00.07 | 1012 | | | |
| 2 | SORT JOIN | | 1 | 100K| 101 |00:00:00.07 | 1005 | 9762K| 1209K| 8677K (0)|
| 3 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 1005 | | | |
|* 4 | SORT JOIN | | 101 | 100 | 100 |00:00:00.01 | 7 | 13312 | 13312 |12288 (0)|
| 5 | TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |