查看: 3350|回复: 17

请教以下SQL语句如何优化

[复制链接]
论坛徽章:
16
数据库板块每日发贴之星
日期:2007-05-10 01:02:022012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262011新春纪念徽章
日期:2011-02-18 11:43:352010广州亚运会纪念徽章:乒乓球
日期:2010-10-14 10:59:16ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25数据库板块每日发贴之星
日期:2010-05-09 01:01:01数据库板块每日发贴之星
日期:2010-03-14 01:01:09数据库板块每日发贴之星
日期:2007-11-13 01:04:45ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44
发表于 2010-7-10 13:14 | 显示全部楼层 |阅读模式
select distinct ne_state.ID peer_name,
                         to_char(ne_state.ne_state) peer_state,
                         (case
                           when ne_state.ne_state = 0 then
                            to_char(0)
                           else
                            (select distinct to_char(nvl(ne_active.active, 0))
                               from T1,
                                    (select ID,
                                            decode(action,
                                                   'active',
                                                   1,
                                                   'de-active',
                                                   0,
                                                   0) active,
                                            max(log_time)
                                       from T1
                                      where action = 'active'
                                         or action = 'de-active'
                                      group by (ID, action)) ne_active
                              where T1.ID = ne_active.ID(+)
                                and T1.ID = ne_state.ID)
                         end) peer_active,
                         (case
                           when ne_state.ne_state = 0 then
                            to_char(0)
                           else
                            (to_char(nvl((select count(*)
                                           from T2
                                          where T2.result <> 1
                                            and ID = ne_state.ID
                                            and log_time between
                                                trunc(sysdate) and sysdate
                                          group by (ID)),
                                         0)))
                         end) err_cnt,
                         (case
                           when ne_state.ne_state = 0 then
                            to_char(0)
                           else
                            (to_char(nvl((select count(*)
                                           from T2 in_dnl
                                          where in_dnl.direction = 'recv'
                                            and in_dnl.ID =
                                                ne_state.ID
                                            and log_time between
                                                trunc(sysdate) and sysdate),
                                         0)))
                         end) recv_cnt,
                         (case
                           when ne_state.ne_state = 0 then
                            to_char(0)
                           else
                            (to_char(nvl((select sum(length)
                                           from T2 in_dnl
                                          where in_dnl.direction = 'recv'
                                            and in_dnl.ID =
                                                ne_state.ID
                                            and log_time between
                                                trunc(sysdate) and sysdate),
                                         0)))
                         end) recv_byte,
                         (case
                           when ne_state.ne_state = 0 then
                            to_char(0)
                           else
                            (to_char(nvl((select count(*)
                                           from T2 in_dnl
                                          where in_dnl.direction = 'send'
                                            and in_dnl.ID =
                                                ne_state.ID
                                            and log_time between
                                                trunc(sysdate) and sysdate),
                                         0)))
                         end) send_cnt,
                         (case
                           when ne_state.ne_state = 0 then
                            to_char(0)
                           else
                            (to_char(nvl((select sum(length)
                                           from T2 in_dnl
                                          where in_dnl.direction = 'send'
                                            and in_dnl.ID =
                                                ne_state.ID
                                            and log_time between
                                                trunc(sysdate) and sysdate),
                                         0)))
                         end) send_byte
           from T2,
                (select distinct dsl1.ID ID,
                                 nvl(ne_disconnect_info.ne_state, 1) ne_state
                   from T1 dsl1,
                        (select distinct dnl.ID ID,
                                         decode(action,
                                                'disconnect',
                                                0,
                                                'connect',
                                                0,
                                                1) ne_state
                           from T1 dsl, T2 dnl
                          where dsl.ID = dnl.ID
                            and ((dsl.action = 'disconnect' and
                                dsl.cause = 'CLOSE PORT') or
                                (dsl.action = 'connect' and
                                dsl.cause = 'CONNECT FAIL'))
                            and dsl.log_time =
                                (select max(log_time)
                                   from T1
                                  where ID = dnl.ID
                                    and log_type = 'PORT_IN_TURN')) ne_disconnect_info
                  where dsl1.ID = ne_disconnect_info.ID(+)) ne_state
          where ne_state.ID = T2.ID(+);
         

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1721014953
--------------------------------------------------------------------------------
| Id   | Operation                     | Name                | Rows  | Bytes |Tem
--------------------------------------------------------------------------------
|    0  | SELECT STATEMENT              |                     |     6 |   414 |
|    1  |  HASH UNIQUE                  |                     | 61105 |  3222K|  7
|*  2  |   HASH JOIN RIGHT OUTER       |                     | 61105 |  3222K|
|    3  |    VIEW                       |                     |     1 |    30 |
|    4  |     SORT GROUP BY             |                     |     1 |    48 |
|*  5  |      TABLE ACCESS FULL        | T1                  |   611 | 29328 |
|*  6  |    INDEX RANGE SCAN           | IDX_T1_PEER_LOGTIME | 61105 |  1432K|
|    7  |  SORT GROUP BY NOSORT         |                     |     1 |    41 |
|*  8  |   FILTER                      |                     |       |       |
|*  9  |    TABLE ACCESS BY INDEX ROWID| T2                  |    11 |   451 |
|* 10 |     INDEX RANGE SCAN          | IDX_T2_LOGTIME      |   227 |       |
|   11 |  SORT AGGREGATE               |                     |     1 |    45 |
|* 12 |   FILTER                      |                     |       |       |
|* 13 |    TABLE ACCESS BY INDEX ROWID| T2                  |     2 |    90 |
|* 14 |     INDEX RANGE SCAN          | IDX_T2_LOGTIME      |   227 |       |
|   15 |  SORT AGGREGATE               |                     |     1 |    58 |
|* 16 |   FILTER                      |                     |       |       |
|* 17 |    TABLE ACCESS BY INDEX ROWID| T2                  |     2 |   116 |
|* 18 |     INDEX RANGE SCAN          | IDX_T2_LOGTIME      |   227 |       |
|   19 |  SORT AGGREGATE               |                     |     1 |    45 |
|* 20 |   FILTER                      |                     |       |       |
|* 21 |    TABLE ACCESS BY INDEX ROWID| T2                  |     2 |    90 |
|* 22 |     INDEX RANGE SCAN          | IDX_T2_LOGTIME      |   227 |       |
|   23 |  SORT AGGREGATE               |                     |     1 |    58 |
|* 24 |   FILTER                      |                     |       |       |
|* 25 |    TABLE ACCESS BY INDEX ROWID| T2                  |     2 |   116 |
|* 26 |     INDEX RANGE SCAN          | IDX_T2_LOGTIME      |   227 |       |
|   27 |  HASH UNIQUE                  |                     |     6 |   414 |
|* 28 |   HASH JOIN RIGHT OUTER       |                     |   510M|    32G|
|   29 |    VIEW                       |                     |     6 |   144 |
|   30 |     HASH UNIQUE               |                     |     6 |   882 |
|* 31 |      HASH JOIN                |                     |     6 |   882 |
|* 32 |       HASH JOIN               |                     |    18 |  2268 |
|   33 |        VIEW                   | VW_SQ_1             |    42 |  1386 |
|   34 |         HASH GROUP BY         |                     |    42 |  3024 |
|* 35 |          TABLE ACCESS FULL    | T1                  | 25664 |  1804K|
|* 36 |        TABLE ACCESS FULL      | T1                  | 25664 |  2330K|
|   37 |       INDEX FAST FULL SCAN    | IDX_T2_LOGTIME      |   127K|  2611K|
|* 38 |    HASH JOIN RIGHT OUTER      |                     |   510M|    21G|  4
|   39 |     INDEX FAST FULL SCAN      | IDX_T2_LOGTIME      |   127K|  2611K|
|   40 |     TABLE ACCESS FULL         | T1                  |  2566K|    58M|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."ID"="NE_ACTIVE"."ID"(+))
   5 - filter(("ACTION"='active' OR "ACTION"='de-active') AND "ID"=:B1)
   6 - access("T1"."ID"=:B1)
   8 - filter(TRUNC(SYSDATE@!)<=SYSDATE@!)
   9 - filter("T2"."RESULT"<>1)
  10 - access("ID"=:B1 AND "LOG_TIME">=TRUNC(SYSDATE@!) AND "LOG_TIME"<=SYSDATE@
  12 - filter(TRUNC(SYSDATE@!)<=SYSDATE@!)
  13 - filter("IN_DNL"."DIRECTION"='recv')
  14 - access("IN_DNL"."ID"=:B1 AND "LOG_TIME">=TRUNC(SYSDATE@!) AND "LOG_TIME"<
  16 - filter(TRUNC(SYSDATE@!)<=SYSDATE@!)
  17 - filter("IN_DNL"."DIRECTION"='recv')
  18 - access("IN_DNL"."ID"=:B1 AND "LOG_TIME">=TRUNC(SYSDATE@!) AND "LOG_TIME"<
  20 - filter(TRUNC(SYSDATE@!)<=SYSDATE@!)
  21 - filter("IN_DNL"."DIRECTION"='send')
  22 - access("IN_DNL"."ID"=:B1 AND "LOG_TIME">=TRUNC(SYSDATE@!) AND "LOG_TIME"<
  24 - filter(TRUNC(SYSDATE@!)<=SYSDATE@!)
  25 - filter("IN_DNL"."DIRECTION"='send')
  26 - access("IN_DNL"."ID"=:B1 AND "LOG_TIME">=TRUNC(SYSDATE@!) AND "LOG_TIME"<
  28 - access("DSL1"."ID"="NE_DISCONNECT_INFO"."ID"(+))
  31 - access("DSL"."ID"="DNL"."ID" AND "ID"="DNL"."ID")
  32 - access("DSL"."LOG_TIME"="VW_COL_1")
  35 - filter("LOG_TYPE"='PORT_IN_TURN')
  36 - filter("DSL"."ACTION"='connect' AND "DSL"."CAUSE"='CONNECT FAIL' OR "DSL"."ACTI
              "DSL"."CAUSE"='CLOSE PORT')
  38 - access("DSL1"."ID"="T2"."ID"(+))

76 rows selected




SQL> SELECT COUNT(*) FROM T1;

  COUNT(*)
----------
   2566393

SQL> SELECT COUNT(*) FROM T2;

  COUNT(*)
----------
    127327


并且表和索引都分析过了
SQL> analyze table T1 compute statistics for table for all indexes for all indexed columns;
Table analyzed
SQL> analyze table T2 compute statistics for table for all indexes for all indexed columns;
Table analyzed

索引情况
create index IDX_T1_PEER_LOGTIME on T1 (ID, LOG_TIME);
create index IDX_T2_LOGTIME on T2 (ID, LOG_TIME);
论坛徽章:
16
数据库板块每日发贴之星
日期:2007-05-10 01:02:022012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262011新春纪念徽章
日期:2011-02-18 11:43:352010广州亚运会纪念徽章:乒乓球
日期:2010-10-14 10:59:16ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25数据库板块每日发贴之星
日期:2010-05-09 01:01:01数据库板块每日发贴之星
日期:2010-03-14 01:01:09数据库板块每日发贴之星
日期:2007-11-13 01:04:45ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44
 楼主| 发表于 2010-7-10 13:17 | 显示全部楼层
格式有点问题,传一个附件上去一下
整个SQL复杂无比,但是其实就两张表,T1和T2。只不过都是子查询搞N个套来套去的。
好像优化起来好困难啊,一时都没啥好的解决方法。
总共执行了1628 秒

主要是耗在一堆CASE WHEN构造的字段上,如果去掉这个,快很多。

[ 本帖最后由 fjliangrq 于 2010-7-10 13:32 编辑 ]

SQL语句优化.TXT

11.65 KB, 下载次数: 8

使用道具 举报

回复
论坛徽章:
16
数据库板块每日发贴之星
日期:2007-05-10 01:02:022012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262011新春纪念徽章
日期:2011-02-18 11:43:352010广州亚运会纪念徽章:乒乓球
日期:2010-10-14 10:59:16ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25数据库板块每日发贴之星
日期:2010-05-09 01:01:01数据库板块每日发贴之星
日期:2010-03-14 01:01:09数据库板块每日发贴之星
日期:2007-11-13 01:04:45ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44
 楼主| 发表于 2010-7-10 13:49 | 显示全部楼层
如果把所有的CASE WHEN 去掉,就是构造出的新字段的SQL部分都去掉,时间从1628到变为284秒,快了不少

select distinct ne_state.ID peer_name,
                         to_char(ne_state.ne_state) peer_state                       
           from T2,
                (select distinct dsl1.ID ID,
                                 nvl(ne_disconnect_info.ne_state, 1) ne_state
                   from T1 dsl1,
                        (select distinct dnl.ID ID,
                                         decode(action,
                                                'disconnect',
                                                0,
                                                'connect',
                                                0,
                                                1) ne_state
                           from T1 dsl, T2 dnl
                          where dsl.ID = dnl.ID
                            and ((dsl.action = 'disconnect' and
                                dsl.cause = 'CLOSE PORT') or
                                (dsl.action = 'connect' and
                                dsl.cause = 'CONNECT FAIL'))
                            and dsl.log_time =
                                (select max(log_time)
                                   from T1
                                  where ID = dnl.ID
                                    and log_type = 'PORT_IN_TURN')) ne_disconnect_info
                  where dsl1.ID = ne_disconnect_info.ID(+)) ne_state
          where ne_state.ID = T2.ID(+);
         
284秒完成

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2532008278
--------------------------------------------------------------------------------
| Id  | Operation                 | Name           | Rows  | Bytes |TempSpc| Cos
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                |     6 |   414 |       | 699
|   1 |  HASH UNIQUE              |                |     6 |   414 |       | 699
|*  2 |   HASH JOIN RIGHT OUTER   |                |   510M|    32G|       | 330
|   3 |    VIEW                   |                |     6 |   144 |       | 151
|   4 |     HASH UNIQUE           |                |     6 |   882 |       | 151
|*  5 |      HASH JOIN            |                |     6 |   882 |       | 151
|*  6 |       HASH JOIN           |                |    18 |  2268 |       | 149
|   7 |        VIEW               | VW_SQ_1        |    42 |  1386 |       |  74
|   8 |         HASH GROUP BY     |                |    42 |  3024 |       |  74
|*  9 |          TABLE ACCESS FULL| T1             | 25664 |  1804K|       |  74
|* 10 |        TABLE ACCESS FULL  | T1             | 25664 |  2330K|       |  74
|  11 |       INDEX FAST FULL SCAN| IDX_T2_LOGTIME |   127K|  2611K|       |   1
|* 12 |    HASH JOIN RIGHT OUTER  |                |   510M|    21G|  4104K| 150
|  13 |     INDEX FAST FULL SCAN  | IDX_T2_LOGTIME |   127K|  2611K|       |   1
|  14 |     TABLE ACCESS FULL     | T1             |  2566K|    58M|       |  74

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DSL1"."ID"="NE_DISCONNECT_INFO"."ID"(+))
   5 - access("DSL"."ID"="DNL"."ID" AND "ID"="DNL"."ID")
   6 - access("DSL"."LOG_TIME"="VW_COL_1")
   9 - filter("LOG_TYPE"='PORT_IN_TURN')
  10 - filter("DSL"."ACTION"='connect' AND "DSL"."CAUSE"='CONNECT FAIL' OR
              "DSL"."ACTION"='disconnect' AND "DSL"."CAUSE"='CLOSE PORT')
  12 - access("DSL1"."ID"="T2"."ID"(+))

32 rows selected


但是奇怪的是COST代价和把CASE WHEN语句去掉前的SQL一样,都是很高的代价,这个有点奇怪,ORACLE的代价一点都不准确!
从F5看执行计划来看(这个没贴出来,我只搞EXPLAIN PLAN FOR的)
,前者代价69968,后者是58000,差不了太多,ORACLE的COST太假了。

[ 本帖最后由 fjliangrq 于 2010-7-10 14:22 编辑 ]

使用道具 举报

回复
论坛徽章:
16
数据库板块每日发贴之星
日期:2007-05-10 01:02:022012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262011新春纪念徽章
日期:2011-02-18 11:43:352010广州亚运会纪念徽章:乒乓球
日期:2010-10-14 10:59:16ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25数据库板块每日发贴之星
日期:2010-05-09 01:01:01数据库板块每日发贴之星
日期:2010-03-14 01:01:09数据库板块每日发贴之星
日期:2007-11-13 01:04:45ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44
 楼主| 发表于 2010-7-10 14:26 | 显示全部楼层
估计此类语句只有修改SQL一条道路了!
等价改写,减少扫描次数!唉。。。。

使用道具 举报

回复
论坛徽章:
3
2010新春纪念徽章
日期:2010-01-04 08:33:082011新春纪念徽章
日期:2011-02-18 11:43:35ITPUB十周年纪念徽章
日期:2011-11-01 16:24:04
发表于 2010-7-10 23:12 | 显示全部楼层
这么多嵌套。。。头晕眼花的。。。

使用道具 举报

回复
论坛徽章:
0
发表于 2010-7-10 23:17 | 显示全部楼层
确实看起来比较晕。。

使用道具 举报

回复
论坛徽章:
13
2010广州亚运会纪念徽章:轮滑
日期:2010-09-03 12:44:53马上有房
日期:2014-04-04 13:51:34马上加薪
日期:2014-04-04 13:35:40优秀写手
日期:2014-03-14 06:00:13夏利
日期:2013-08-05 18:32:18复活蛋
日期:2013-06-25 17:22:592013年新春福章
日期:2013-02-25 14:51:24蛋疼蛋
日期:2013-01-08 18:08:502011新春纪念徽章
日期:2011-02-18 11:43:33生肖徽章2007版:兔
日期:2011-01-20 12:58:49
发表于 2010-7-11 00:12 | 显示全部楼层

回复 #3 fjliangrq 的帖子

t1应该是大表,却每次都执行全表扫描,而t2却用到了索引,是不是表连接顺序不对?
还有就是内层的distinct 能不能去掉。

[ 本帖最后由 hotdog04 于 2010-7-11 00:25 编辑 ]

使用道具 举报

回复
论坛徽章:
16
数据库板块每日发贴之星
日期:2007-05-10 01:02:022012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262011新春纪念徽章
日期:2011-02-18 11:43:352010广州亚运会纪念徽章:乒乓球
日期:2010-10-14 10:59:16ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25数据库板块每日发贴之星
日期:2010-05-09 01:01:01数据库板块每日发贴之星
日期:2010-03-14 01:01:09数据库板块每日发贴之星
日期:2007-11-13 01:04:45ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44
 楼主| 发表于 2010-7-11 09:06 | 显示全部楼层
感谢楼上兄弟的答复,谢谢!
感觉这里的索引如何建合理是一个比较头疼的问题啊。
另外等价该写这仅两张表,绕来绕去无数次的乱七八糟的代码应该是王道,但是挑战太大了,好难!

[ 本帖最后由 fjliangrq 于 2010-7-11 09:08 编辑 ]

使用道具 举报

回复
论坛徽章:
16
数据库板块每日发贴之星
日期:2007-05-10 01:02:022012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262011新春纪念徽章
日期:2011-02-18 11:43:352010广州亚运会纪念徽章:乒乓球
日期:2010-10-14 10:59:16ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25数据库板块每日发贴之星
日期:2010-05-09 01:01:01数据库板块每日发贴之星
日期:2010-03-14 01:01:09数据库板块每日发贴之星
日期:2007-11-13 01:04:45ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44
 楼主| 发表于 2010-7-11 22:53 | 显示全部楼层
顶顶更健康!

使用道具 举报

回复
论坛徽章:
16
数据库板块每日发贴之星
日期:2007-05-10 01:02:022012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262011新春纪念徽章
日期:2011-02-18 11:43:352010广州亚运会纪念徽章:乒乓球
日期:2010-10-14 10:59:16ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25数据库板块每日发贴之星
日期:2010-05-09 01:01:01数据库板块每日发贴之星
日期:2010-03-14 01:01:09数据库板块每日发贴之星
日期:2007-11-13 01:04:45ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44
 楼主| 发表于 2010-7-14 06:11 | 显示全部楼层
顶顶更健康!

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表