在线急等: 有个过程,执行时间较长,请大家帮忙解决一下吧:
大概说明一下:
大表TH_VEHICLE_ALARM 的条件(a.utc >= 1365264000000 AND a.utc < 1366992000000)数据量是8百万条左右,
其他几个表数据量都不大
表TH_VEHICLE_ALARM有按照utc做的分区,还有一个本地复合索引(idx_a),索引字段:UTC, VID, ALARM_START_UTC, ALARM_CODE
还有一个本地复合索引(idx_a2),索引字段:(VID, ALARM_START_UTC DESC, ALARM_CODE)
----------------------------------------------------------
sql:select VID,
VEHICLE_NO,
LEVEL_NAME,
ALARM_NAME,
ALARM_ID,
GPS_SPEED,
MAPLON,
MAPLAT,
ALARM_HANDLER_STATUS,
ALARM_HANDLER_STATUS_TYPE,
BASESTATUS,
ALARM_START_UTC,
ALARM_END_UTC,
ALARM_SRC,
STAFF_NAME,
ALARM_CODE,
ENT_ID
FROM (SELECT BB.*, ROWNUM r
FROM (select A1.VID,
A1.VEHICLE_NO,
B1.LEVEL_NAME,
B1.ALARM_NAME,
A1.ALARM_ID,
A1.GPS_SPEED,
A1.MAPLON,
A1.MAPLAT,
A1.ALARM_HANDLER_STATUS,
A1.ALARM_HANDLER_STATUS_TYPE,
A1.BASESTATUS,
A1.ALARM_START_UTC,
A1.ALARM_END_UTC,
A1.ALARM_SRC,
A1.STAFF_NAME,
A1.ALARM_CODE,
B1.ENT_ID
from (select
a.VID,
c.VEHICLE_NO,
a.ALARM_ID,
a.GPS_SPEED,
a.MAPLON,
a.MAPLAT,
a.ALARM_HANDLER_STATUS,
a.ALARM_HANDLER_STATUS_TYPE,
a.BASESTATUS,
a.ALARM_START_UTC,
a.ALARM_END_UTC,
a.ALARM_SRC,
a.ALARM_CODE,
o2.ent_id corp_id,
o2.ent_name corp_name,
o1.ent_id team_id,
o1.ent_name team_name,
'' STAFF_NAME
from TH_VEHICLE_ALARM a,
tb_vehicle c,
tb_organization o1,
tb_organization o2
where c.vid = a.vid
and c.ent_id = o1.ent_id
and o1.parent_id = o2.ent_id
AND (o1.ent_id in (10216, 162557, 10221, 10217, 160498, 10218, 10224, 10234, 10226, 10235, 118961, 10229, 10222, 10231, 10227, 10232, 10233, 119944, 10230, 10223, 118738, 10225, 10228, 201))
AND a.utc >= 1365264000000
AND a.utc < 1366992000000) A1,
(select AA.ALARM_CODE,
AA.ALARM_NAME,
DD.LEVEL_NAME,
CC.SYS_ALARM_LEVEL_ID,
CC.ENT_ID
from SYS_ALARM_TYPE AA
left join SYS_ALARM_TYPE AA1
on AA1.ALARM_CODE = AA.Parent_Code
left join TB_ALARM_ENT_INFO BB
on AA.ALARM_CODE = BB.SYS_ALARM_TYPE_ID
left join TB_ALARM_ENT_CONF CC
on BB.TB_ALARM_ENT_CONF_ID = CC.PID
left join SYS_ALARM_LEVEL DD
on DD.LEVEL_ID = CC.SYS_ALARM_LEVEL_ID
where 1 = 1
and CC.ENT_ID = 200) B1
WHERE A1.ALARM_CODE = B1.ALARM_CODE
order by a1.VEHICLE_NO asc, a1.alarm_start_utc desc
) BB
WHERE rownum < 30) tt
WHERE tt.r > 0
----------------------------------------------------------
Plan hash value: 708909978
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 22852 | 44937 (1)| 00:09:00 | | |
|* 1 | VIEW | | 29 | 22852 | 44937 (1)| 00:09:00 | | |
|* 2 | COUNT STOPKEY | | | | | | | |
| 3 | VIEW | | 551 | 417K| 44937 (1)| 00:09:00 | | |
|* 4 | SORT ORDER BY STOPKEY | | 551 | 99731 | 44937 (1)| 00:09:00 | | |
| 5 | NESTED LOOPS | | 551 | 99731 | 44937 (1)| 00:09:00 | | |
|* 6 | HASH JOIN | | 550 | 96800 | 44937 (1)| 00:09:00 | | |
| 7 | INLIST ITERATOR | | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | TB_ORGANIZATION | 24 | 240 | 7 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | PK_ORG | 24 | | 2 (0)| 00:00:01 | | |
|* 10 | HASH JOIN | | 14656 | 2375K| 44929 (1)| 00:09:00 | | |
|* 11 | TABLE ACCESS FULL | TB_VEHICLE | 876 | 18396 | 92 (7)| 00:00:02 | | |
|* 12 | HASH JOIN | | 94761 | 13M| 44835 (1)| 00:08:59 | | |
| 13 | NESTED LOOPS OUTER | | 28 | 1736 | 13 (16)| 00:00:01 | | |
| 14 | NESTED LOOPS OUTER | | 28 | 1624 | 13 (16)| 00:00:01 | | |
|* 15 | HASH JOIN | | 28 | 1288 | 12 (17)| 00:00:01 | | |
| 16 | MERGE JOIN | | 54 | 1296 | 8 (13)| 00:00:01 | | |
|* 17 | TABLE ACCESS BY INDEX ROWID| TB_ALARM_ENT_CONF | 4 | 56 | 2 (0)| 00:00:01 | | |
| 18 | INDEX FULL SCAN | PK_TB_ALARM_ENT_CONF | 43 | | 1 (0)| 00:00:01 | | |
|* 19 | SORT JOIN | | 2340 | 23400 | 6 (17)| 00:00:01 | | |
| 20 | TABLE ACCESS FULL | TB_ALARM_ENT_INFO | 2340 | 23400 | 5 (0)| 00:00:01 | | |
| 21 | TABLE ACCESS FULL | SYS_ALARM_TYPE | 61 | 1342 | 3 (0)| 00:00:01 | | |
| 22 | TABLE ACCESS BY INDEX ROWID | SYS_ALARM_LEVEL | 1 | 12 | 1 (0)| 00:00:01 | | |
|* 23 | INDEX UNIQUE SCAN | PK_LEVEL | 1 | | 0 (0)| 00:00:01 | | |
|* 24 | INDEX UNIQUE SCAN | PK_SYS_ALARM_TYPE | 1 | 4 | 0 (0)| 00:00:01 | | |
| 25 | PARTITION RANGE ITERATOR | | 422K| 33M| 44817 (1)| 00:08:58 | 284 | 303 |
| 26 | TABLE ACCESS FULL | TH_VEHICLE_ALARM | 422K| 33M| 44817 (1)| 00:08:58 | 284 | 303 |
|* 27 | INDEX UNIQUE SCAN | PK_ORG | 1 | 5 | 0 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TT"."R">0)
2 - filter(ROWNUM<30)
4 - filter(ROWNUM<30)
6 - access("O1"."ENT_ID"=TO_NUMBER("C"."ENT_ID"))
9 - access("O1"."ENT_ID"=201 OR "O1"."ENT_ID"=10216 OR "O1"."ENT_ID"=10217 OR "O1"."ENT_ID"=10218 OR
"O1"."ENT_ID"=10221 OR "O1"."ENT_ID"=10222 OR "O1"."ENT_ID"=10223 OR "O1"."ENT_ID"=10224 OR "O1"."ENT_ID"=10225 OR
"O1"."ENT_ID"=10226 OR "O1"."ENT_ID"=10227 OR "O1"."ENT_ID"=10228 OR "O1"."ENT_ID"=10229 OR "O1"."ENT_ID"=10230 OR
"O1"."ENT_ID"=10231 OR "O1"."ENT_ID"=10232 OR "O1"."ENT_ID"=10233 OR "O1"."ENT_ID"=10234 OR "O1"."ENT_ID"=10235 OR
"O1"."ENT_ID"=118738 OR "O1"."ENT_ID"=118961 OR "O1"."ENT_ID"=119944 OR "O1"."ENT_ID"=160498 OR "O1"."ENT_ID"=162557)
10 - access("C"."VID"="A"."VID")
11 - filter(TO_NUMBER("C"."ENT_ID")=10221 OR TO_NUMBER("C"."ENT_ID")=10217 OR TO_NUMBER("C"."ENT_ID")=10233 OR
TO_NUMBER("C"."ENT_ID")=118738 OR TO_NUMBER("C"."ENT_ID")=10216 OR TO_NUMBER("C"."ENT_ID")=162557 OR
TO_NUMBER("C"."ENT_ID")=160498 OR TO_NUMBER("C"."ENT_ID")=10218 OR TO_NUMBER("C"."ENT_ID")=10224 OR
TO_NUMBER("C"."ENT_ID")=10234 OR TO_NUMBER("C"."ENT_ID")=10226 OR TO_NUMBER("C"."ENT_ID")=10235 OR
TO_NUMBER("C"."ENT_ID")=118961 OR TO_NUMBER("C"."ENT_ID")=10229 OR TO_NUMBER("C"."ENT_ID")=10222 OR
TO_NUMBER("C"."ENT_ID")=10231 OR TO_NUMBER("C"."ENT_ID")=10227 OR TO_NUMBER("C"."ENT_ID")=10232 OR
TO_NUMBER("C"."ENT_ID")=119944 OR TO_NUMBER("C"."ENT_ID")=10230 OR TO_NUMBER("C"."ENT_ID")=10223 OR
TO_NUMBER("C"."ENT_ID")=10225 OR TO_NUMBER("C"."ENT_ID")=10228 OR TO_NUMBER("C"."ENT_ID")=201)
12 - access("A"."ALARM_CODE"="AA"."ALARM_CODE")
15 - access("AA"."ALARM_CODE"="BB"."SYS_ALARM_TYPE_ID")
17 - filter("CC"."ENT_ID"=200)
19 - access("BB"."TB_ALARM_ENT_CONF_ID"="CC"."PID")
filter("BB"."TB_ALARM_ENT_CONF_ID"="CC"."PID")
23 - access("DD"."LEVEL_ID"(+)="CC"."SYS_ALARM_LEVEL_ID")
24 - access("AA1"."ALARM_CODE"(+)="AA"."PARENT_CODE")
27 - access("O1"."PARENT_ID"="O2"."ENT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
196161 consistent gets
0 physical reads
0 redo size
3557 bytes sent via SQL*Net to client
1783 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
29 rows processed