大家好,我执行一个select 语句需要18秒钟,但是利用select结果执行insert into需要耗时3个小时,select语句执行结果行数也才13行,不知道为什么?哪位大侠帮忙指点指点!!
select 语句:
select tg.id ,p.persons, sum(lb.LOGINAMOUNT) sum1, count(distinct tu.id) sum2,1,2
from userdaylogininfo lb, tuser tu ,tgroup tg,
(
select tgr.id,count(*) persons
from tuser tu,tgroup tgr
where tu.groupids like ',,9001,,9002,,'||tgr.id||'%' and tgr.parentid=9002
group by tgr.id
) p
where lb.us_id = tu.id and tg.parentID = 9002 and tu.groupids like ',,9001,,9002,,'||tg.id||'%'
and lb.LOGINDAY >= to_date('2012-03-01','yyyy-mm-dd') and p.id= tg.id
and lb.LOGINDAY < to_date('2012-04-01','yyyy-mm-dd')
group by tg.id,p.persons;
insert语句:
INSERT INTO LOGINSTATBYDEPTzsy (group_id,persons,loginTimes,loginPersons,datadate,type)
select tg.id ,p.persons, sum(lb.LOGINAMOUNT) sum1, count(distinct tu.id) sum2,1,2
from userdaylogininfo lb, tuser tu ,tgroup tg,
(
select tgr.id,count(*) persons
from tuser tu,tgroup tgr
where tu.groupids like ',,9001,,9002,,'||tgr.id||'%' and tgr.parentid=9002
group by tgr.id
) p
where lb.us_id = tu.id and tg.parentID = 9002 and tu.groupids like ',,9001,,9002,,'||tg.id||'%'
and lb.LOGINDAY >= to_date('2012-03-01','yyyy-mm-dd') and p.id= tg.id
and lb.LOGINDAY < to_date('2012-04-01','yyyy-mm-dd')
group by tg.id,p.persons;
select 语句执行计划如下:
SQL> select tg.id ,p.persons, sum(lb.LOGINAMOUNT) sum1, count(distinct tu.id) sum2,1,2
2 from userdaylogininfo lb, tuser tu ,tgroup tg,
3 (
4 select tgr.id,count(*) persons
5 from tuser tu,tgroup tgr
6 where tu.groupids like ',,9001,,9002,,'||tgr.id||'%' and tgr.parentid=9002
7 group by tgr.id
8 ) p
9 where lb.us_id = tu.id and tg.parentID = 9002 and tu.groupids like ',,9001,,9002,,'||tg.id||'%'
10 and lb.LOGINDAY >= to_date('2012-03-01','yyyy-mm-dd') and p.id= tg.id
11 and lb.LOGINDAY < to_date('2012-04-01','yyyy-mm-dd')
12 group by tg.id,p.persons;
ID PERSONS SUM1 SUM2 1 2
---------- ---------- ---------- ---------- ---------- ----------
10201 1086 170 48 1 2
10446 523 58 22 1 2
10540 915888 10348 2826 1 2
80937 260350 3066 832 1 2
102046 361958 1924 600 1 2
131886 43382 972 252 1 2
134205 159707 1757 479 1 2
144364 70376 2357 497 1 2
147347 26835 382 95 1 2
148353 75387 551 175 1 2
151942 10744 777 133 1 2
ID PERSONS SUM1 SUM2 1 2
---------- ---------- ---------- ---------- ---------- ----------
153152 36 3 2 1 2
863464 2213 8 4 1 2
已选择13行。
已用时间: 00: 00: 11.76
执行计划
----------------------------------------------------------
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 2825 | 27773 |
| 1 | SORT GROUP BY | | 25 | 2825 | 27773 |
| 2 | HASH JOIN | | 14849 | 1638K| 27771 |
| 3 | VIEW | | 25 | 475 | 6126 |
| 4 | HASH GROUP BY | | 25 | 1800 | 6126 |
| 5 | NESTED LOOPS | | 2372K| 162M| 5882 |
| 6 | TABLE ACCESS BY INDEX ROWID| TGROUP | 25 | 300 | 18 |
| 7 | INDEX RANGE SCAN | TGROUP_PARENTID_IDX | 25 | | 1 |
| 8 | INDEX RANGE SCAN | TUSER_GROUPIDS_IDX | 95846 | 5615K| 235 |
| 9 | HASH JOIN | | 14849 | 1363K| 21645 |
| 10 | MERGE JOIN CARTESIAN | | 296K| 8107K| 2141 |
| 11 | TABLE ACCESS BY INDEX ROWID | TGROUP | 25 | 300 | 18 |
| 12 | INDEX RANGE SCAN | TGROUP_PARENTID_IDX | 25 | | 1 |
| 13 | BUFFER SORT | | 11980 | 187K| 2122 |
| 14 | TABLE ACCESS FULL | USERDAYLOGININFO | 11980 | 187K| 85 |
| 15 | TABLE ACCESS FULL | TUSER | 1916K| 120M| 11823 |
----------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
79095 consistent gets
0 physical reads
0 redo size
636 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
13 rows processed
SQL>