031.笔记：表的连接和其他高级调整技术(2.双表连接等)-优快云博客

1.双表连接：等尺寸表（基于成本）
书中这一节主要是介绍，在无索引的情况下。表的执行顺序和表的连接方法。

示例：
select small1.col1,small2.col1
from small1,small2
where small1.col1=small2.col1;

如果示例中任何一张表都没有经过分析，Oracle将借助于以前版本的方法，即读取FROM子句中的最后一张表并使用排序合并连接（即使在Oracle 10g中也这样）。此
处强调这一点可以让您在遇到这种现象时，能意识到没有分析表。

【技巧】使用基于成本的优化器，并使用ORDERED提示时，FROM子句中的第一张表将作为驱动表。这取代了优化器进行选择驱动表。如果使用了排序合并连接，那么
表的顺序就不重要了，因为它们都不会作为驱动表。在小表连接中，使用ORDERED提示可以指定一张表作为驱动表，了解这点将帮助您解决大表连接中的难题，并帮助
您找到问题的所在。
【技巧】当设置了初始化参数后，优化器在原来使用排序合并连接的场所改为使用散列连接。在该连接中，第一张表用一建立散列表（在内存中），FROM子句中的第二张表
将被扫描，以确定是否与散列表匹配。FROM子句的第一张表（使用ORDERED提示）是在散列连接中读取的第一张表。

2.双表索引连接：等尺寸表（基于成本）
【技巧】若使用基于成本的优化器和嵌套循环连接作为连接方法，则FROM子句的第一张表就是驱动表（所有的条件都是等于的情况下），但只有ORDERED
提示可以确保该顺序。在嵌套循环连接中，选择有较小结果集的表（并一定是较小的表）作为驱动表，可以比使用其他结果集（从非驱动表中提取的）
执行更少的循环，通常也导致最佳的性能。

【驱动表小结】
1）散列连接的驱动表与FROM后表的顺序没有关系，可以用ORDERED固定；
2）排序合并连接不需要驱动表，因为两张表都需要排序；
实验发现，在连接键上有索引的情况下，有索引的表可以不用排序。
3）嵌套循环连接，测试发现，只有ORDERED提示可以确保FROM后的第一张表是驱动表。

【实验】表的读取顺序是否影响排序合并连接的效率？
例1：
1）不用索引
SQL> select /*+ordered use_merge(t t2)*/ t.dname,t2.deptname
2 from scott.dept2 t2,scott.dept t
3 where t.deptno=t2.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 210585084

--------------------------------------------------------------------------------
-----

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
-----

| 0 | SELECT STATEMENT | | 1 | 21 | | 285 (3)| 00:00
:04 |

| 1 | MERGE JOIN | | 1 | 21 | | 285 (3)| 00:00
:04 |

| 2 | SORT JOIN | | 3 | 30 | | 3 (34)| 00:00
:01 |

| 3 | TABLE ACCESS FULL| DEPT2 | 3 | 30 | | 2 (0)| 00:00
:01 |

|* 4 | SORT JOIN | | 43105 | 463K| 1704K| 282 (3)| 00:00
:04 |

| 5 | TABLE ACCESS FULL| DEPT | 43105 | 463K| | 86 (2)| 00:00
:02 |

--------------------------------------------------------------------------------
-----

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T"."DEPTNO"="T2"."DEPTNO")
filter("T"."DEPTNO"="T2"."DEPTNO")

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
409 consistent gets
0 physical reads
0 redo size
534 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
2）用索引
SQL> select /*+use_merge(t t2)*/ t.dname,t2.deptname
2 from scott.dept2 t2,scott.dept t
3 where t.deptno=t2.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2282053854

--------------------------------------------------------------------------------
-----------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

--------------------------------------------------------------------------------
-----------

| 0 | SELECT STATEMENT | | 1 | 21 | 258 (2)|
00:00:04 |

| 1 | MERGE JOIN | | 1 | 21 | 258 (2)|
00:00:04 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 43105 | 463K| 255 (2)|
00:00:04 |

| 3 | INDEX FULL SCAN | IDX_UNIQUE | 43105 | | 91 (2)|
00:00:02 |

|* 4 | SORT JOIN | | 3 | 30 | 3 (34)|
00:00:01 |

| 5 | TABLE ACCESS FULL | DEPT2 | 3 | 30 | 2 (0)|
00:00:01 |

--------------------------------------------------------------------------------
-----------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T"."DEPTNO"="T2"."DEPTNO")
filter("T"."DEPTNO"="T2"."DEPTNO")

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
534 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
例2：两个尺寸相同的表
1）
SQL> select /*+ordered use_merge(t t2) indexa(t2a )*/ t.dname,t2.dname
2 from scott.dept t2,scott.dept t
3 where t.deptno=t2.deptno;

43105 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1072030548

--------------------------------------------------------------------------------
-------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time |

--------------------------------------------------------------------------------
-------------------

| 0 | SELECT STATEMENT | | 43310 | 930K| | 53
7 (2)| 00:00:07 |

| 1 | MERGE JOIN | | 43310 | 930K| | 53
7 (2)| 00:00:07 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 43105 | 463K| | 25
5 (2)| 00:00:04 |

| 3 | INDEX FULL SCAN | IDX_UNIQUE | 43105 | | | 9
1 (2)| 00:00:02 |

|* 4 | SORT JOIN | | 43105 | 463K| 1704K| 28
2 (3)| 00:00:04 |

| 5 | TABLE ACCESS FULL | DEPT | 43105 | 463K| | 8
6 (2)| 00:00:02 |

--------------------------------------------------------------------------------
-------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T"."DEPTNO"="T2"."DEPTNO")
filter("T"."DEPTNO"="T2"."DEPTNO")

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
53251 consistent gets
90 physical reads
0 redo size
912429 bytes sent via SQL*Net to client
31988 bytes received via SQL*Net from client
2875 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
43105 rows processed
2）
SQL> select /*+ordered use_merge(t t2) no_index(t2 )*/ t.dname,t2.dname
2 from scott.dept t2,scott.dept t
3 where t.deptno=t2.deptno;

43105 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2254186229

--------------------------------------------------------------------------------
----

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
----

| 0 | SELECT STATEMENT | | 43310 | 930K| | 564 (3)| 00:00:
07 |

| 1 | MERGE JOIN | | 43310 | 930K| | 564 (3)| 00:00:
07 |

| 2 | SORT JOIN | | 43105 | 463K| 1704K| 282 (3)| 00:00:
04 |

| 3 | TABLE ACCESS FULL| DEPT | 43105 | 463K| | 86 (2)| 00:00:
02 |

|* 4 | SORT JOIN | | 43105 | 463K| 1704K| 282 (3)| 00:00:
04 |

| 5 | TABLE ACCESS FULL| DEPT | 43105 | 463K| | 86 (2)| 00:00:
02 |

--------------------------------------------------------------------------------

【实验小结】
1.例1中使用索引时consistent gets减少了97%
2.例2中使用索引反而使consistent gets大增
3.两个例子共同证明，使用索引的COST较低
4.通常是有索引的表不用排序，因为索引是有序的，减少了一次排序操作。

3.强制执行特殊的连接方法
USE_NL/USE_MERGE/USE_HASH
SWAP_JOIN_INPUTS获得正确的读取顺序
没有请求集群索引的提示

【实验】提示的优化级
1./*+swap_join_inputs ordered use_hash(t e) */
SQL> select /*+swap_join_inputs ordered use_hash(t e) */*
2 from scott.dept t,scott.emp e
3 where e.deptno=t.deptno;

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3214691363

--------------------------------------------------------------------------------
-----------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

--------------------------------------------------------------------------------
-----------

| 0 | SELECT STATEMENT | | 1 | 75 | 3 (0)|
00:00:01 |

| 1 | NESTED LOOPS | | 1 | 75 | 3 (0)|
00:00:01 |

| 2 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)|
00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 38 | 0 (0)|
00:00:01 |

|* 4 | INDEX UNIQUE SCAN | IDX_UNIQUE | 1 | | 0 (0)|
00:00:01 |

--------------------------------------------------------------------------------
-----------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("E"."DEPTNO"="T"."DEPTNO")

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
2017 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

2./*+ordered swap_join_inputs use_hash(t e) */
SQL> select /*+ordered swap_join_inputs use_hash(t e) */*
2 from scott.dept t,scott.emp e
3 where e.deptno=t.deptno;

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

--------------------------------------------------------------------------------
---

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
---

| 0 | SELECT STATEMENT | | 1 | 75 | | 195 (3)| 00:00:0
3 |

|* 1 | HASH JOIN | | 1 | 75 | 2112K| 195 (3)| 00:00:0
3 |

| 2 | TABLE ACCESS FULL| DEPT | 43105 | 1599K| | 87 (3)| 00:00:0
2 |

| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | | 3 (0)| 00:00:0
1 |

--------------------------------------------------------------------------------
---

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("E"."DEPTNO"="T"."DEPTNO")

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
414 consistent gets
597 physical reads
0 redo size
1750 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

【实验小结】当两个提示有冲突时，如/*+ordered swap_join_inputs use_hash(t e) */
中的ordered 和swap_join_inputs时，优化器会按写在前面的提示执行

4.在多表连接中除去连接记录（侯选行）
【技巧】在三表连接中，驱动表应当是交叉表，或者是在连接中和其他两张表都有连接条件限制的表。可以尝试使用限制条件最多的表（或者交叉表）
作为驱动表，这样当连接第三张表时，您从前两张表连接所获得的结果集将很小。

5.在大小不同的表间进行双表连接

【实验】多表关联，表1的附加条件可以通过连接传递给表2
SQL> select *
2 from scott.dept t
3 left join scott.dept_sort s on(s.deptno=t.deptno)
4 where t.deptno<500;

463 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1921359320

--------------------------------------------------------------------------------
-----------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

--------------------------------------------------------------------------------
-----------

| 0 | SELECT STATEMENT | | 459 | 34425 | 59 (4)|
00:00:01 |

|* 1 | HASH JOIN OUTER | | 459 | 34425 | 59 (4)|
00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 459 | 17442 | 4 (0)|
00:00:01 |

|* 3 | INDEX RANGE SCAN | IDX_UNIQUE | 459 | | 2 (0)|
00:00:01 |

|* 4 | TABLE ACCESS FULL | DEPT_SORT | 490 | 18130 | 55 (4)|
00:00:01 |

--------------------------------------------------------------------------------
-----------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("S"."DEPTNO"(+)="T"."DEPTNO")
3 - access("T"."DEPTNO"<500)
4 - filter("S"."DEPTNO"(+)<500)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
849 consistent gets
285 physical reads
0 redo size
33165 bytes sent via SQL*Net to client
715 bytes received via SQL*Net from client
32 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
463 rows processed

6.三表连接（基于成本）
这一节主要是介绍哪一张表是驱动表？
6.1使用EXPLAIN PLAN
explain plan for
select *
from dept t,dept_sort t1
where t.deptno=t1.deptno;
6.2
select * from table(dbms_xplan.display);
6.3
select lpad(' ',2*level)||operation oper, options,object_name
from plan_table
connect by prior id=parent_id
start with id=1
order by id;
【技巧】为确保正确地理解了EXPLAIN PLAN ，请运行已确认了驱动表的查询（含有嵌套子查询）

7.位图连接索引
【总结】位图索引
1）索引中所有值都在WHERE中；
2）count(*)或结果集比较小的SELECT *；
3）GROUP BY可以使用位图索引做count(*)计数统计；
4）GROUP BY不适合使用位图连接索引。

【实验】使用位图连接索引成功的例子
1）没有GROUP 的count(*)
create bitmap index bit_dim_dq
on dept(dim_city.dq)
from dept,dim_city
where dept.city=dim_city.city;

select count(*)
from scott.dept t
inner join scott.dim_city d on(d.city=t.city)
where d.dq='南方';
【小结】在select 后面加字段，where 后面加索引中没有的字段，都会使索引失效
2）组合位图连接索引
drop index bit_dim_dq2;
create bitmap index bit_dim_dq2
on dept(dim_city.dq,dept.deptno)
from dept,dim_city
where dept.city=dim_city.city;

create bitmap index bit_dim_dq3
on dept(dept.deptno,dim_city.dq)
from dept,dim_city
where dept.city=dim_city.city;

select t.deptno,count(*)
from scott.dept t, scott.dim_city d
where d.dq='北方' and t.city=d.city and t.deptno =200
group by t.deptno;
【小结】结合索引中主表的字段可能用在select 后面，也可以用在group by后面

附：应用场景：
要求：统计对公账户的数量
账户主表：存储账户的基本信息
账户附表：存储账户的类型（对公/对私）
两个表的账户号关连

create table t_zt
as
with t as (select row_number() over(partition by id order by user_yn) rn, id,user_yn from scott.t_group1)
select id,user_yn from t where t.rn=1;

alter table t_zt add constraint pry_t_zt primary key(id);

drop index idx_zt;
create bitmap index idx_zt
on t_group1(t2.user_yn)
from t_group1 t,t_zt t2
where t.id=t2.id;

SQL> select count(*)
2 from scott.t_group1 t,scott.t_zt t2
3 where t.id=t2.id and t2.user_yn='Y';

Execution Plan
----------------------------------------------------------
Plan hash value: 4182000059

--------------------------------------------------------------------------------
------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
------

| 0 | SELECT STATEMENT | | 1 | 5 | 90 (0)| 00:0
0:02 |

| 1 | SORT AGGREGATE | | 1 | 5 | |
|

| 2 | BITMAP CONVERSION COUNT | | 4327K| 20M| 90 (0)| 00:0
0:02 |

|* 3 | BITMAP INDEX SINGLE VALUE| IDX_ZT | | | |
|

--------------------------------------------------------------------------------
------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T"."SYS_NC00014$"='Y')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
67 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

--位图连接索引可以与普通位图索引连合使用
SQL> select COUNT(*)
2 from scott.t_group1 t,scott.t_zt t2
3 where t.id=t2.id and t2.user_yn='Y' and t.biz_date='86';

Execution Plan
----------------------------------------------------------
Plan hash value: 3571571994

--------------------------------------------------------------------------------
-------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |

--------------------------------------------------------------------------------
-------

| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:
00:01 |

| 1 | SORT AGGREGATE | | 1 | 9 | |
|

| 2 | BITMAP CONVERSION COUNT | | 7200 | 64800 | 2 (0)| 00:
00:01 |

| 3 | BITMAP AND | | | | |
|

|* 4 | BITMAP INDEX SINGLE VALUE| BIZ_D | | | |
|

|* 5 | BITMAP INDEX SINGLE VALUE| IDX_ZT | | | |
|

--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T"."BIZ_DATE"='86')
5 - access("T"."SYS_NC00014$"='Y')

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
69 consistent gets
2 physical reads
0 redo size
410 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed