数据分布不均匀走HASH JOIN导致的性能问题

这个案例是JAVA开发说一个存储过程跑的很慢,之后我跑这个过程,然后通过脚本抓出了慢的SQL

表大小

1
2
3
tb_user_channel --1W
tb_channel_info --1W
base_data_login_info 19W

就是这条SQL,跑完要7分钟。base_data_login_info本来是@db_link,但是我在本地建了一个同样的表发现还是7分钟左右,所以排除了可能是由于db_link造成问题的可能性

1
2
3
4
5
6
select
count(distinct a.user_name),count(distinct a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

看一下这个sql返回多少行,结果秒杀,瞬间就出结果了

1
2
3
4
5
6
select
count(*)
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

45122行
之后单独跑

1
2
3
4
5
6
select
count(distinct a.user_name),count( a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform


1
2
3
4
5
6
select
count( a.user_name),count(distinct a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

都是秒杀
单独count distinct user_name 或 invest_id 都很快 ,一起count distinct就很慢了
那么这时候其实已经可以通过改写SQL提示性能了,改写如下

1
2
3
4
5
6
7
with t1 as
(select
a.user_name, a.invest_id
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform)

查看改写后的执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
select (select count(distinct user_name) from t1),(select count(distinct invest_id) from t1) from dual;
Plan hash value: 3790966246
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 746 (1)| 00:00:09 | | |
| 1 | SORT AGGREGATE | | 1 | 27 | | | | | |
| 2 | VIEW | VM_NWVW_2 | 40660 | 1072K| | 1589 (1)| 00:00:20 | | |
| 3 | SORT GROUP BY | | 40660 | 1072K| 6752K| 1589 (1)| 00:00:20 | | |
| 4 | VIEW | | 190K| 5021K| | 878 (1)| 00:00:11 | | |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D671E_EB8EA | 190K| 9M| | 878 (1)| 00:00:11 | | |
| 6 | SORT AGGREGATE | | 1 | 27 | | | | | |
| 7 | VIEW | VM_NWVW_3 | 41456 | 1093K| | 1593 (1)| 00:00:20 | | |
| 8 | SORT GROUP BY | | 41456 | 1093K| 6752K| 1593 (1)| 00:00:20 | | |
| 9 | VIEW | | 190K| 5021K| | 878 (1)| 00:00:11 | | |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D671E_EB8EA | 190K| 9M| | 878 (1)| 00:00:11 | | |
| 11 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 12 | LOAD AS SELECT | SYS_TEMP_0FD9D671E_EB8EA | | | | | | | |
|* 13 | HASH JOIN RIGHT SEMI | | 190K| 22M| | 744 (1)| 00:00:09 | | |
| 14 | VIEW | VW_NSO_1 | 11535 | 304K| | 258 (1)| 00:00:04 | | |
|* 15 | HASH JOIN | | 11535 | 360K| | 258 (1)| 00:00:04 | | |
|* 16 | TABLE ACCESS FULL | TB_USER_CHANNEL | 11535 | 157K| | 19 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS FULL | TB_CHANNEL_INFO | 11767 | 206K| | 238 (0)| 00:00:03 | | |
| 18 | REMOTE | BASE_DATA_LOGIN_INFO | 190K| 17M| | 486 (1)| 00:00:06 | AGENT | R->S |
| 19 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
13 - access("A"."CHANNEL_ID"="CHANNEL_RLAT")
15 - access("A"."CHANNEL_ID"="B"."CHANNEL_ID")
16 - filter("A"."USER_ID"=5002)
Remote SQL Information (identified by operation id):
----------------------------------------------------
18 - SELECT "USER_NAME","INVEST_ID","STR_DAY","CHANNEL_ID","PLATFORM" FROM "BASE_DATA_LOGIN_INFO" "A" WHERE
"STR_DAY"<='20160304' AND "STR_DAY">='20160301' AND "PLATFORM" IS NOT NULL (accessing 'AGENT' )

为了探究性能瓶颈我们继续看
慢的执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
select
count(distinct a.user_name),count(distinct a.invest_id)
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform
Plan hash value: 2367445948
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 754 (2)| 00:00:10 | | |
| 1 | SORT GROUP BY | | 1 | 130 | | | | |
|* 2 | HASH JOIN | | 4067K| 504M| 754 (2)| 00:00:10 | | |
|* 3 | HASH JOIN | | 11535 | 360K| 258 (1)| 00:00:04 | | |
|* 4 | TABLE ACCESS FULL| TB_USER_CHANNEL | 11535 | 157K| 19 (0)| 00:00:01 | | |
| 5 | TABLE ACCESS FULL| TB_CHANNEL_INFO | 11767 | 206K| 238 (0)| 00:00:03 | | |
| 6 | REMOTE | BASE_DATA_LOGIN_INFO | 190K| 17M| 486 (1)| 00:00:06 | AGENT | R->S |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."CHANNEL_ID"="CHANNEL_RLAT")
3 - access("A"."CHANNEL_ID"="B"."CHANNEL_ID")
4 - filter("A"."USER_ID"=5002)
Remote SQL Information (identified by operation id):
----------------------------------------------------
6 - SELECT "USER_NAME","INVEST_ID","STR_DAY","CHANNEL_ID","PLATFORM" FROM "BASE_DATA_LOGIN_INFO"
"A" WHERE "STR_DAY"<='20160304' AND "STR_DAY">='20160301' AND "PLATFORM" IS NOT NULL (accessing
'AGENT' )

快的执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
explain plan for
select
count( a.user_name),count(distinct a.invest_id)
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform
Plan hash value: 4282421321
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | | 2982 (1)| 00:00:36 | | |
| 1 | SORT AGGREGATE | | 1 | 40 | | | | | |
| 2 | VIEW | VW_DAG_0 | 41456 | 1619K| | 2982 (1)| 00:00:36 | | |
| 3 | HASH GROUP BY | | 41456 | 4250K| 20M| 2982 (1)| 00:00:36 | | |
|* 4 | HASH JOIN RIGHT SEMI| | 190K| 19M| | 744 (1)| 00:00:09 | | |
| 5 | VIEW | VW_NSO_1 | 11535 | 80745 | | 258 (1)| 00:00:04 | | |
|* 6 | HASH JOIN | | 11535 | 360K| | 258 (1)| 00:00:04 | | |
|* 7 | TABLE ACCESS FULL| TB_USER_CHANNEL | 11535 | 157K| | 19 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS FULL| TB_CHANNEL_INFO | 11767 | 206K| | 238 (0)| 00:00:03 | | |
| 9 | REMOTE | BASE_DATA_LOGIN_INFO | 190K| 17M| | 486 (1)| 00:00:06 | AGENT | R->S |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."CHANNEL_ID"="CHANNEL_RLAT")
6 - access("A"."CHANNEL_ID"="B"."CHANNEL_ID")
7 - filter("A"."USER_ID"=5002)
Remote SQL Information (identified by operation id):
----------------------------------------------------
9 - SELECT "USER_NAME","INVEST_ID","STR_DAY","CHANNEL_ID","PLATFORM" FROM "BASE_DATA_LOGIN_INFO" "A" WHERE
"STR_DAY"<='20160304' AND "STR_DAY">='20160301' AND "PLATFORM" IS NOT NULL (accessing 'AGENT' )

注意到快的执行计划用的是HASH JOIN SEMI 而 慢的执行计划用的是 HASH JOIN
我又跑慢的SQL,想查看等待时间分析问题,结果等待事件却是 SQL*Net message to client。。。
做个10046

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
alter session set events ‘10046 trace name context forever, level 8’;
alter session set tracefile_identifier='fan';
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 8';
SQL>.............
alter session set events '10046 trace name context off';
tkprof channel_ora_4917_fan.trc hehe sys=no waits=yes
select
count(distinct a.user_name),count(distinct a.invest_id)
from base_data_login_info a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1092.09 1236.55 0 3643 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1092.09 1236.56 0 3643 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT GROUP BY (cr=3643 pr=0 pw=0 time=1236559678 us)
410996039 410996039 410996039 HASH JOIN (cr=3643 pr=0 pw=0 time=406365130 us cost=1006 size=66968010 card=458685)
11535 11535 11535 HASH JOIN (cr=945 pr=0 pw=0 time=199182 us cost=258 size=369120 card=11535)
11535 11535 11535 TABLE ACCESS FULL TB_USER_CHANNEL (cr=67 pr=0 pw=0 time=21452 us cost=19 size=161490 card=11535)
11771 11771 11771 TABLE ACCESS FULL TB_CHANNEL_INFO (cr=878 pr=0 pw=0 time=30291 us cost=238 size=211806 card=11767)
45122 45122 45122 TABLE ACCESS FULL BASE_DATA_LOGIN_INFO (cr=2698 pr=0 pw=0 time=218144 us cost=747 size=2447922 card=21473)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 50.71 50.71

注意执行计划第二行 变成了40亿!
关联列数据分布
19W 表连接列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL> select channel_id,count(*) from base_data_login_info group by channel_id order by 2;
CHANNEL_ID COUNT(*)
-------------------------------------------------- ----------
011a1 2
003a1 3
021a1 3
006a1 12
024h2 16
013a1 19
007a1 24
012a1 25
005a1 27
EPT01 36
028h2 109
008a1 139
029a1 841
009a1 921
014a1 1583
000a1 1975
a0001 2724
004a1 5482
001a1 16329
026h2 160162
20 rows selected.

in里的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
select channel_rlat,count(*) from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002 group by channel_rlat order by 2 desc
channel_rlat count(*)
026h2 10984
024h2 7
002h2 6
023a2 2
007s001022001 1
007s001022002 1
007s001024007 1
007s001024009 1
007s001022009 1
001s001006 1
001s001008 1
001s001001001 1
001s001001003 1
001s001001007 1
001s001001014 1
007s001018003 1
007s001018007 1
007s001019005 1
007s001019008 1
001s001002011 1
007s001011003 1
007s001034 1
007s001023005 1
007s001011008 1

HASH JOIN 只适合数据分布均匀的列做关联,而这个关联列数据分布极度不均衡,相当于一个小笛卡尔及,在bucket里找死了

继续深入,为什么本来是半连接,CBO用了HASH JOIN?

单独跑这个是很快的

1
2
3
4
5
6
select
a.user_name,a.invest_id
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform

这样就慢了,因为视图合并了

1
2
3
4
5
6
7
select count(distinct a.user_name),count(distinct a.invest_id) from (
select
a.user_name,a.invest_id
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform ) a

CBO把它改写成了

1
2
3
4
5
select
count(distinct a.user_name),count(distinct a.invest_id)
from base_data_login_info@agent a,(select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002) b
where a.channel_id=b.channel_rlat
and a.str_day <= '20160304' and a.str_day >= '20160301'

半连接本身就可以改写成inner join,但是in里的表要distinct,举个例子

1
2
3
4
5
6
select * from dept where deptno in (select deptno from emp)
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO

可以改写为

1
2
select d.* from dept d inner join (select distinct deptno from emp ) e
on d.deptno=e.deptno

否则如果不distinct结果集就会有问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> select d.* from dept d inner join (select deptno from emp ) e
2 on d.deptno=e.deptno ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
20 RESEARCH DALLAS
30 SALES CHICAGO
30 SALES CHICAGO
30 SALES CHICAGO
30 SALES CHICAGO
30 SALES CHICAGO
30 SALES CHICAGO
已选择14行。

但是巧就巧的是,我这个SQL是 count(distinct a.user_name),count(distinct a.invest_id)
所以CBO把它改写成了inner join,还不用把in里面先去重,CBO真TM聪明,可惜聪明反被聪明误。我们这个关联列数据分布极度不均匀
那我们不想让CBO改写咋办呢

那么我们不让视图合并用 rownum>0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
select count(distinct a.user_name),count(distinct a.invest_id) from (
select
a.user_name,a.invest_id
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform and rownum>0) a
Plan hash value: 3295380261
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54 | 744 (1)| 00:00:09 | | |
| 1 | SORT GROUP BY | | 1 | 54 | | | | |
| 2 | VIEW | | 190K| 9M| 744 (1)| 00:00:09 | | |
| 3 | COUNT | | | | | | | |
|* 4 | FILTER | | | | | | | |
|* 5 | HASH JOIN RIGHT SEMI| | 190K| 22M| 744 (1)| 00:00:09 | | |
| 6 | VIEW | VW_NSO_1 | 11535 | 304K| 258 (1)| 00:00:04 | | |
|* 7 | HASH JOIN | | 11535 | 360K| 258 (1)| 00:00:04 | | |
|* 8 | TABLE ACCESS FULL| TB_USER_CHANNEL | 11535 | 157K| 19 (0)| 00:00:01 | | |
| 9 | TABLE ACCESS FULL| TB_CHANNEL_INFO | 11767 | 206K| 238 (0)| 00:00:03 | | |
| 10 | REMOTE | BASE_DATA_LOGIN_INFO | 190K| 17M| 486 (1)| 00:00:06 | AGENT | R->S |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(ROWNUM>0)
5 - access("A"."CHANNEL_ID"="CHANNEL_RLAT")
7 - access("A"."CHANNEL_ID"="B"."CHANNEL_ID")
8 - filter("A"."USER_ID"=5002)
Remote SQL Information (identified by operation id):
----------------------------------------------------
10 - SELECT "USER_NAME","INVEST_ID","STR_DAY","CHANNEL_ID","PLATFORM" FROM "BASE_DATA_LOGIN_INFO" "A"
WHERE "STR_DAY"<='20160304' AND "STR_DAY">='20160301' AND "PLATFORM" IS NOT NULL (accessing 'AGENT' )
with t1 as
(select /*+ materialize */
a.user_name, a.invest_id
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform)
select count(distinct user_name) ,count(distinct invest_id) from t1;
Plan hash value: 901326807
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54 | 1621 (1)| 00:00:20 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6720_EB8EA | | | | | | |
|* 3 | HASH JOIN RIGHT SEMI | | 190K| 22M| 744 (1)| 00:00:09 | | |
| 4 | VIEW | VW_NSO_1 | 11535 | 304K| 258 (1)| 00:00:04 | | |
|* 5 | HASH JOIN | | 11535 | 360K| 258 (1)| 00:00:04 | | |
|* 6 | TABLE ACCESS FULL | TB_USER_CHANNEL | 11535 | 157K| 19 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS FULL | TB_CHANNEL_INFO | 11767 | 206K| 238 (0)| 00:00:03 | | |
| 8 | REMOTE | BASE_DATA_LOGIN_INFO | 190K| 17M| 486 (1)| 00:00:06 | AGENT | R->S |
| 9 | SORT GROUP BY | | 1 | 54 | | | | |
| 10 | VIEW | | 190K| 9M| 878 (1)| 00:00:11 | | |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6720_EB8EA | 190K| 9M| 878 (1)| 00:00:11 | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."CHANNEL_ID"="CHANNEL_RLAT")
5 - access("A"."CHANNEL_ID"="B"."CHANNEL_ID")
6 - filter("A"."USER_ID"=5002)
Remote SQL Information (identified by operation id):
----------------------------------------------------
8 - SELECT "USER_NAME","INVEST_ID","STR_DAY","CHANNEL_ID","PLATFORM" FROM "BASE_DATA_LOGIN_INFO" "A" WHERE
"STR_DAY"<='20160304' AND "STR_DAY">='20160301' AND "PLATFORM" IS NOT NULL (accessing 'AGENT' )

再解释一下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create table emp2 as select * from emp;
insert into emp2 select * from emp2;
create table emp4 as select * from emp2;
SQL> select count(distinct a.job),count(distinct a.ename) from emp2 a where a.deptno in (select deptno from emp4);
COUNT(DISTINCTA.JOB) COUNT(DISTINCTA.ENAME)
-------------------- ----------------------
4 11
等价 于
SQL> select count(distinct a.job),count(distinct a.ename) from emp2 a,emp4 b where a.deptno=b.deptno;
COUNT(DISTINCTA.JOB) COUNT(DISTINCTA.ENAME)
-------------------- ----------------------
4 11
CBO把它改写成了这个 a.deptno=b.deptno hash join
Execution Plan
----------------------------------------------------------
Plan hash value: 3097773013
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 8 (13)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 39 | | |
|* 2 | HASH JOIN | | 242 | 9438 | 8 (13)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP2 | 22 | 572 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP4 | 22 | 286 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#