表大小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
47SQL> select count(*) from agent.TB_AGENT_INFO;
COUNT(*)
----------
1751
SQL> select count(*) from TB_CHANNEL_INFO ;
COUNT(*)
----------
1807
SQL> select count(*) from TB_USER_CHANNEL;
COUNT(*)
----------
7269
SQL> select count(*) from OSS_USER_STATION;
COUNT(*)
----------
2149
SQL> select count(*) from tb_user_zgy ;
COUNT(*)
----------
43
SQL> select count(*) from act.tb_user_agent_relat;
COUNT(*)
----------
29612
SQL> select count(*) from agent.base_data_user_info ;
COUNT(*)
----------
30005
SQL> select count(*) from agent.base_data_invest_info;
COUNT(*)
----------
3530163
慢的sql1
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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108select a.city,
a.agent_id,
a.username,
a.real_name,
phone,
zgy_name,
login_count,
user_count,
count(distinct b.invest_id) user_invested,
sum(b.order_amount / 100) invest_amount
from (select a.city,
a.agent_id,
a.username,
a.real_name, -- 业主姓名
a.phone, -- 业主手机号
d.real_name zgy_name, -- 所属专管员
count(distinct case
when c.str_day <= '20160821' then
c.login_name
end) login_count,
count(distinct case
when c.str_day <= '20160821' then
decode(c.status, 1, c.invest_id, null)
end) user_count
from (select agent_id, city, username, real_name, phone
from agent.TB_AGENT_INFO
where agent_id in
(SELECT agent_id
FROM (SELECT distinct *
FROM TB_CHANNEL_INFO t
START WITH t.CHANNEL_ID in
(select CHANNEL_ID
from TB_USER_CHANNEL
where USER_ID = 596)
CONNECT BY PRIOR
t.CHANNEL_ID = t.PARENT_CHANNEL_ID)
WHERE agent_id IS NOT NULL)) a
left join oss_user_station e
on a.agent_id = e.agent_id
and e.user_type = 0
left join tb_user_zgy d
on e.username = d.username
left join act.tb_user_agent_relat c
on a.agent_id = c.agent_id
group by a.city,
a.username,
a.real_name,
a.phone,
d.real_name,
a.agent_id) a
left join (select invest_id, order_amount, agent_id, str_day
from agent.base_data_invest_info
where str_day >= '20150801' and str_day<='20160821') b
on a.agent_id = b.agent_id
group by a.city,
a.agent_id,
a.username,
a.real_name,
a.phone,
a.zgy_name,
a.login_count,
a.user_count
这个查询可以看成两部分，第一部分一堆小表关联的a和唯一的一个大表再做关联
man
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55M| 6616M| | 3934K (1)| 13:06:52 |
| 1 | HASH GROUP BY | | 55M| 6616M| | 3934K (1)| 13:06:52 |
| 2 | VIEW | VW_DAG_1 | 55M| 6616M| | 3934K (1)| 13:06:52 |
| 3 | HASH GROUP BY | | 55M| 6301M| 7681M| 3934K (1)| 13:06:52 |
| 4 | VIEW | VM_NWVW_0 | 55M| 6301M| | 2456K (1)| 08:11:15 |
| 5 | SORT GROUP BY | | 55M| 10G| 11G| 2456K (1)| 08:11:15 |
|* 6 | HASH JOIN RIGHT OUTER | | 55M| 10G| | 21643 (2)| 00:04:20 |
| 7 | TABLE ACCESS FULL | TB_USER_AGENT_RELAT | 27937 | 1200K| | 102 (0)| 00:00:02 |
|* 8 | HASH JOIN OUTER | | 3374K| 511M| | 21392 (1)| 00:04:17 |
|* 9 | HASH JOIN SEMI | | 1712 | 188K| | 2007 (1)| 00:00:25 |
|* 10 | HASH JOIN RIGHT OUTER | | 1712 | 173K| | 32 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | TB_USER_ZGY | 43 | 903 | | 3 (0)| 00:00:01 |
|* 12 | HASH JOIN RIGHT OUTER | | 1712 | 138K| | 29 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | OSS_USER_STATION | 1075 | 25800 | | 6 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | TB_AGENT_INFO | 1712 | 98K| | 23 (0)| 00:00:01 |
| 15 | VIEW | VW_NSO_1 | 16271 | 143K| | 1975 (1)| 00:00:24 |
|* 16 | VIEW | | 16271 | 143K| | 1975 (1)| 00:00:24 |
| 17 | HASH UNIQUE | | 16271 | 8882K| 10M| 1975 (1)| 00:00:24 |
|* 18 | CONNECT BY WITHOUT FILTERING (UNIQUE)| | | | | | |
|* 19 | HASH JOIN RIGHT SEMI | | 530 | 146K| | 29 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | TB_USER_CHANNEL | 600 | 7800 | | 7 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | TB_CHANNEL_INFO | 1807 | 476K| | 22 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | TB_CHANNEL_INFO | 1807 | 476K| | 22 (0)| 00:00:01 |
|* 23 | TABLE ACCESS FULL | BASE_DATA_INVEST_INFO | 3374K| 148M| | 19375 (1)| 00:03:53 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("AGENT_ID"="C"."AGENT_ID"(+))
8 - access("AGENT_ID"="AGENT_ID"(+))
9 - access("AGENT_ID"="AGENT_ID")
10 - access("C"."USERNAME"="D"."USERNAME"(+))
12 - access("AGENT_ID"="C"."AGENT_ID"(+))
13 - filter("C"."USER_TYPE"(+)=0)
16 - filter("AGENT_ID" IS NOT NULL)
18 - access("T"."PARENT_CHANNEL_ID"=PRIOR "T"."CHANNEL_ID")
19 - access("T"."CHANNEL_ID"="CHANNEL_ID")
20 - filter("USER_ID"=596)
23 - filter("STR_DAY"(+)>='20150801' AND "STR_DAY"(+)<='20160821')