|
不好意思。原来没有分析t_terminal,只分析了大表,这个表没分析,分析完后,
|* 2 | TABLE ACCESS FULL | T_TERMINAL | 1302 | 46872 | 504 (2)| 00
:00:07 |
应该对了
SQL> l
1 select t.id,tt.term_name,t.device_id,t.speed,
2 to_char(t.alarm_time,'yyyy-MM-dd hh24:mi:ss') alarm_time,tcase.max_speed
3 /*+ use_hash(t, tcase.id) */ from t_area_locrecord t,t_terminal tt, t_speed_case tcase
4 where
5 tt.ent_code='empRoot'
6 and tt.device_id is not null
7 and t.ALARM_TIME>sysdate-1
8 and t.speed>tcase.max_speed
9 and t.device_id=tt.device_id
10 and t.alarm_type='1'
11* and t.case_id=tcase.id
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 3527672269
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 902K| 61M| 315K (3)| 01
:03:06 |
|* 1 | HASH JOIN | | 902K| 61M| 315K (3)| 01
:03:06 |
|* 2 | TABLE ACCESS FULL | T_TERMINAL | 1302 | 46872 | 504 (2)| 00
:00:07 |
|* 3 | HASH JOIN | | 902K| 30M| 314K (3)| 01
:03:00 |
| 4 | TABLE ACCESS FULL| T_SPEED_CASE | 2689 | 21512 | 7 (0)| 00
:00:01 |
|* 5 | TABLE ACCESS FULL| T_AREA_LOCRECORD | 18M| 481M| 314K (3)| 01
:02:58 |
--------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."DEVICE_ID"="TT"."DEVICE_ID")
2 - filter("TT"."ENT_CODE"='empRoot')
3 - access("T"."CASE_ID"="TCASE"."ID")
filter("T"."SPEED">"TCASE"."MAX_SPEED")
5 - filter("T"."ALARM_TYPE"='1' AND "T"."ALARM_TIME">SYSDATE@!-1)
Note
-----
- 'PLAN_TABLE' is old version |
|