请教个SQL优化的问题，高手们帮忙

whf7907 · 发表于 2009-12-30 11:42

SQL> select count(*) from t_terminal where  ENT_CODE='empRoot'
  2  /

  COUNT(*)
----------
   1364

morong · 发表于 2009-12-30 11:42

应该是试试t.alarm_type='1' 不是试试t.alarm_type="1"

zergduan · 发表于 2009-12-30 11:44

原帖由 whf7907 于 2009-12-30 11:42 发表
SQL> select count(*) from t_terminal where  ENT_CODE='empRoot'
  2  /

  COUNT(*)
----------
   1364

hoho ~ cbo评估错误~...

morong · 发表于 2009-12-30 11:48

原帖由 whf7907 于 2009-12-30 11:40 发表
表和索引刚分析过。

分析还得看看分析得对不对。分析错了就会产生错误的执行计划。

whf7907 · 发表于 2009-12-30 11:48

改为
t.alarm_type='1'的执行计划

SQL> 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
12  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1639185357

-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |                | 56449 |  3803K| 88164 (1)| 00:17:38 |
|*  1 |  HASH JOIN                |                | 56449 |  3803K| 88164 (1)| 00:17:38 |
| 2 | TABLE ACCESS FULL       | T_SPEED_CASE    |  2689 | 21512 |    7 (0)| 00:00:01 |
|*  3 | TABLE ACCESS BY INDEX ROWID| T_AREA_LOCRECORD | 14201 | 388K| 49699 (1)| 00:09:57 |
| 4 | NESTED LOOPS             |                |  1128K| 65M| 88143 (1)| 00:17:38 |
|*  5 |    TABLE ACCESS FULL       | T_TERMINAL    | 80 |  2640 | 504 (2)| 00:00:07 |
|*  6 |    INDEX RANGE SCAN       | INDEX_DEVICEID | 78981 |    | 480 (1)| 00:00:06 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."CASE_ID"="TCASE"."ID")
   filter("T"."SPEED">"TCASE"."MAX_SPEED")
3 - filter("T"."ALARM_TYPE"='1' AND "T"."ALARM_TIME">SYSDATE@!-1)
5 - filter("TT"."ENT_CODE"='empRoot')
6 - access("T"."DEVICE_ID"="TT"."DEVICE_ID")

zergduan · 发表于 2009-12-30 11:49

收集ENT_CODE列的直方图看看

whf7907 · 发表于 2009-12-30 11:55

不好意思。如何收集列的直方图信息？

whf7907 · 发表于 2009-12-30 12:19

不好意思。原来没有分析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

morong · 发表于 2009-12-30 13:38

whf7907 · 发表于 2009-12-30 13:50

目前没有分区，准备改成分区表的。