查看: 2240|回复: 5

语句优化问题

[复制链接]
论坛徽章:
11
生肖徽章2007版:虎
日期:2009-08-14 13:50:18ITPUB十周年纪念徽章
日期:2011-11-01 16:24:512010广州亚运会纪念徽章:自行车
日期:2011-03-19 13:35:152011新春纪念徽章
日期:2011-02-18 11:42:492010世博会纪念徽章
日期:2010-10-17 20:10:522010系统架构师大会纪念
日期:2010-09-03 16:39:572010新春纪念徽章
日期:2010-03-01 11:04:592010新春纪念徽章
日期:2010-01-04 08:33:08参与WIN7挑战赛纪念
日期:2009-11-06 16:05:25生肖徽章2007版:狗
日期:2009-09-10 11:32:03
发表于 2010-3-30 16:08 | 显示全部楼层 |阅读模式
请问:下面语句如何优化?版本是10.2.0.1,linux系统
SELECT task_id,
       tm_tick,
       point_total,
       point_succ,
       (point_total - point_succ) AS point_err,
       (CASE
         WHEN point_base = 0 THEN
          0
         ELSE
          ROUND(ts_total / point_base, 0)
       END) AS ts_total,
       point_base,
       point_trim,
       (CASE
         WHEN point_total = 0 THEN
          0
         ELSE
          point_succ / point_total
       END) AS percent_succ
  FROM (SELECT task_id,
               TO_DATE('2010-03-18 00:00:00', 'YYYY-MM-DD HH24-MI-SS') +
               FLOOR((tm_base -
                     TO_DATE('2010-03-18 00:00:00', 'YYYY-MM-DD HH24-MI-SS')) *
                     6.0000000000E0) * 1.6666666667E-1 As tm_tick,
               SUM(point_total) AS point_total,
               SUM(CASE
                     WHEN error_code > 600000 THEN
                      0
                     ELSE
                      point_total
                   END) AS point_succ,
               SUM(CASE
                     WHEN per < 0.05 OR per > 0.95 OR error_code > 600000 THEN
                      0
                     ELSE
                      point_total
                   END) AS point_base,
               SUM(CASE
                     WHEN per < 0.05 OR per > 0.95 OR error_code > 600000 THEN
                      0
                     ELSE
                      ts_total * point_total
                   END) AS ts_total,
               SUM(CASE
                     WHEN per < 0.05 OR per > 0.95 THEN
                      point_total
                     ELSE
                      0
                   END) AS point_trim
          FROM (SELECT
                 task_id,
                 tm_base,
                 ts_user as ts_total,
                 point_total,
                 error_code,
                 percent_rank() over(partition by task_id order by ts_user) as per
                  From NB_PAGE_23771
                 WHERE is_noise = 0
                   AND (task_id = 42645 OR task_id = 42646 OR task_id = 43444 OR
                       task_id = 43463 OR task_id = 43481 OR task_id = 43482 OR
                       task_id = 43485 OR task_id = 43503)
                   AND (isp_id in (17, 16, 26, 25, 27, 21))
                   AND tm_base >=
                       TO_DATE('2010-03-18 00:00:00', 'YYYY-MM-DD HH24-MI-SS')
                   AND tm_base <=
                       TO_DATE('2010-03-25 00:00:00', 'YYYY-MM-DD HH24-MI-SS'))
         GROUP BY task_id,
                  TO_DATE('2010-03-18 00:00:00', 'YYYY-MM-DD HH24-MI-SS') +
                  FLOOR((tm_base -
                        TO_DATE('2010-03-18 00:00:00',
                                 'YYYY-MM-DD HH24-MI-SS')) * 6.0000000000E0) *
                  1.6666666667E-1)
ORDER BY tm_tick, task_id

Execution Plan
-----------------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name          | Rows  | Bytes |TempSpc|Cost (%CPU)| Pstart| Pstop |

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

|   0 | SELECT STATEMENT              |               | 24777 |  1572K|       | 5475   (2)|       |       |

|   1 |  SORT ORDER BY                |               | 24777 |  1572K|  4104K| 5475   (2)|       |       |

|   2 |   HASH GROUP BY               |               | 24777 |  1572K|  4104K| 5475   (2)|       |       |

|   3 |    PARTITION HASH INLIST      |               | 24777 |  1572K|       | 4697   (2)|KEY(I) |KEY(I) |

|   4 |     VIEW                      |               | 24777 |  1572K|       | 4697   (2)|       |       |

|   5 |      WINDOW SORT              |               | 24777 |   701K|  2344K| 4697   (2)|       |       |

|   6 |       PARTITION RANGE ITERATOR|               | 24777 |   701K|       | 4495   (2)|    78 |    79 |

|   7 |        TABLE ACCESS FULL      | NB_PAGE_23771 | 24777 |   701K|       | 4495   (2)|       |       |

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


Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      11929  consistent gets
          0  physical reads
          0  redo size
       7226  bytes sent via SQL*Net to client
       1467  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        132  rows processed
        
        
Execution Plan
-------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                | Name               | Rows  |Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |

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

|   0 | SELECT STATEMENT                         |                    | 24777 | 1572K|       | 24225   (1)|       |       |

|   1 |  SORT ORDER BY                           |                    | 24777 | 1572K|  4104K| 24225   (1)|       |       |

|   2 |   HASH GROUP BY                          |                    | 24777 | 1572K|  4104K| 24225   (1)|       |       |

|   3 |    INLIST ITERATOR                       |                    |       |      |       |            |       |       |

|   4 |     PARTITION HASH ITERATOR              |                    | 24777 | 1572K|       | 23446   (1)|KEY(I) |KEY(I) |

|   5 |      VIEW                                |                    | 24777 | 1572K|       | 23446   (1)|       |       |

|   6 |       WINDOW SORT                        |                    | 24777 |  701K|  2344K| 23446   (1)|       |       |

|   7 |        PARTITION RANGE ITERATOR          |                    | 24777 |  701K|       | 23245   (1)|    78 |    79 |

|   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| NB_PAGE_23771      | 24777 |  701K|       | 23245   (1)|       |       |

|   9 |          INDEX RANGE SCAN                | IN_PAGE_PERF_23771 | 26098 |      |       |   125   (1)|       |       |

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


Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      11963  consistent gets
         72  physical reads
          0  redo size
       7226  bytes sent via SQL*Net to client
       1514  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        132  rows processed        
        
        
Execution Plan
--------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |

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

|   0 | SELECT STATEMENT                         |                     | 24777 |  1572K|       | 23914   (1)|       |       |

|   1 |  SORT ORDER BY                           |                     | 24777 |  1572K|  4104K| 23914   (1)|       |       |

|   2 |   HASH GROUP BY                          |                     | 24777 |  1572K|  4104K| 23914   (1)|       |       |

|   3 |    INLIST ITERATOR                       |                     |       |       |       |            |       |       |

|   4 |     PARTITION HASH ITERATOR              |                     | 24777 |  1572K|       | 23136   (1)|KEY(I) |KEY(I) |

|   5 |      VIEW                                |                     | 24777 |  1572K|       | 23136   (1)|       |       |

|   6 |       WINDOW SORT                        |                     | 24777 |   701K|  2344K| 23136   (1)|       |       |

|   7 |        PARTITION RANGE ITERATOR          |                     | 24777 |   701K|       | 22934   (1)|    78 |    79 |

|   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| NB_PAGE_23771       | 24777 |   701K|       | 22934   (1)|       |       |

|   9 |          INDEX RANGE SCAN                | IN_PAGE_ERROR_23771 | 25545 |       |       |   125   (1)|       |       |

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


Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      11929  consistent gets
          0  physical reads
          0  redo size
       7226  bytes sent via SQL*Net to client
       1515  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        132  rows processed        
        
        
---------以下为对表nb_page_23771_int的查询情况(结构和索引都相同)--------------        
Execution Plan
-----------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                               | Name                   | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |

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

|   0 | SELECT STATEMENT                        |                        |     6 |   390 |    59  (14)|       |       |

|   1 |  SORT ORDER BY                          |                        |     6 |   390 |    59  (14)|       |       |

|   2 |   HASH GROUP BY                         |                        |     6 |   390 |    59  (14)|       |       |

|   3 |    VIEW                                 |                        | 16136 |  1024K|    54   (6)|       |       |

|   4 |     WINDOW SORT                         |                        | 16136 |   456K|    54   (6)|       |       |

|   5 |      PARTITION RANGE ITERATOR           |                        | 16136 |   456K|    51   (0)|    78 |    79 |

|   6 |       INLIST ITERATOR                   |                        |       |       |            |       |       |

|   7 |        TABLE ACCESS BY LOCAL INDEX ROWID| NB_PAGE_23771_INT      | 16136 |   456K|    51   (0)|    78 |    79 |

|   8 |         INDEX RANGE SCAN                | IN_PAGE_PERF_23771_INT |  2107 |       |    12   (0)|    78 |    79 |

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


Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
      12259  consistent gets
          0  physical reads
          0  redo size
       7226  bytes sent via SQL*Net to client
       1471  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        132  rows processed        
        
      Execution Plan
---------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                               | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |

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

|   0 | SELECT STATEMENT                        |                         | 27030 |  1715K|       | 28147   (1)|       |       |

|   1 |  SORT ORDER BY                          |                         | 27030 |  1715K|  4472K| 28147   (1)|       |       |

|   2 |   HASH GROUP BY                         |                         | 27030 |  1715K|  4472K| 28147   (1)|       |       |

|   3 |    VIEW                                 |                         | 27030 |  1715K|       | 27298   (1)|       |       |

|   4 |     WINDOW SORT                         |                         | 27030 |   765K|  2552K| 27298   (1)|       |       |

|   5 |      PARTITION RANGE ITERATOR           |                         | 27030 |   765K|       | 27079   (1)|    78 |    79 |

|   6 |       INLIST ITERATOR                   |                         |       |       |       |            |       |       |

|   7 |        TABLE ACCESS BY LOCAL INDEX ROWID| NB_PAGE_23771_INT       | 27030 |   765K|       | 27079   (1)|    78 |    79 |

|   8 |         INDEX RANGE SCAN                | IN_PAGE_ERROR_23771_INT | 27744 |       |       |   133   (1)|    78 |    79 |

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


Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      12148  consistent gets
         64  physical reads
          0  redo size
       7226  bytes sent via SQL*Net to client
       1527  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        132  rows processed  
        
       -----以下是索引的定义-------
alter table NB_PAGE_23771_INT   add constraint PK_NB_PAGE_23771_INT primary key (ID)   using index   tablespace NETBEN;
-- Create/Recreate indexes
create index IN_PAGE_ERROR_23771_INT on NB_PAGE_23771_INT (TASK_ID, TM_BASE, ERROR_CODE, CITY_ID);
create index IN_PAGE_PERF_23771_INT on NB_PAGE_23771_INT (TASK_ID, TM_BASE, CITY_ID, ISP_ID);
create index IN_PAGE_TRANID_23771_INT on NB_PAGE_23771_INT (TRAN_ID);        


数据量
select num_rows from user_tables where table_name='NB_PAGE_23771';

  NUM_ROWS
----------
  28688994
  
  NB_PAGE_23771_INT表按tm_base分区,range型,7天一个区
  nb_page_23771表按tm_base分区,range型,每个分区有4个hash子分区

[ 本帖最后由 syzxlyx_cu 于 2010-3-30 16:39 编辑 ]
论坛徽章:
86
2015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14
发表于 2010-3-30 20:16 | 显示全部楼层
nb_page_23771表的HASH子分区是根据什么建的?是否是因为这个,导致索引的CLUSTER FACTOR大?

使用道具 举报

回复
论坛徽章:
86
2015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14
发表于 2010-3-30 20:17 | 显示全部楼层
select CLUSTERING_FACTOR
  from user_indexes
where index_name='IN_PAGE_PERF_23771_INT';

select CLUSTERING_FACTOR
  from user_indexes
where index_name='IN_PAGE_PERF_23771';

使用道具 举报

回复
论坛徽章:
11
生肖徽章2007版:虎
日期:2009-08-14 13:50:18ITPUB十周年纪念徽章
日期:2011-11-01 16:24:512010广州亚运会纪念徽章:自行车
日期:2011-03-19 13:35:152011新春纪念徽章
日期:2011-02-18 11:42:492010世博会纪念徽章
日期:2010-10-17 20:10:522010系统架构师大会纪念
日期:2010-09-03 16:39:572010新春纪念徽章
日期:2010-03-01 11:04:592010新春纪念徽章
日期:2010-01-04 08:33:08参与WIN7挑战赛纪念
日期:2009-11-06 16:05:25生肖徽章2007版:狗
日期:2009-09-10 11:32:03
 楼主| 发表于 2010-3-31 11:07 | 显示全部楼层
SQL> select CLUSTERING_FACTOR
  2    from user_indexes
  3  where index_name='IN_PAGE_PERF_23771_INT';

CLUSTERING_FACTOR
-----------------
         26229290


SQL> select CLUSTERING_FACTOR   from user_indexes
  2  where index_name='IN_PAGE_PERF_23771';

CLUSTERING_FACTOR
-----------------
         26055157

使用道具 举报

回复
论坛徽章:
11
生肖徽章2007版:虎
日期:2009-08-14 13:50:18ITPUB十周年纪念徽章
日期:2011-11-01 16:24:512010广州亚运会纪念徽章:自行车
日期:2011-03-19 13:35:152011新春纪念徽章
日期:2011-02-18 11:42:492010世博会纪念徽章
日期:2010-10-17 20:10:522010系统架构师大会纪念
日期:2010-09-03 16:39:572010新春纪念徽章
日期:2010-03-01 11:04:592010新春纪念徽章
日期:2010-01-04 08:33:08参与WIN7挑战赛纪念
日期:2009-11-06 16:05:25生肖徽章2007版:狗
日期:2009-09-10 11:32:03
 楼主| 发表于 2010-3-31 11:08 | 显示全部楼层
hash分区是按task_id字段分区的

使用道具 举报

回复
论坛徽章:
11
生肖徽章2007版:虎
日期:2009-08-14 13:50:18ITPUB十周年纪念徽章
日期:2011-11-01 16:24:512010广州亚运会纪念徽章:自行车
日期:2011-03-19 13:35:152011新春纪念徽章
日期:2011-02-18 11:42:492010世博会纪念徽章
日期:2010-10-17 20:10:522010系统架构师大会纪念
日期:2010-09-03 16:39:572010新春纪念徽章
日期:2010-03-01 11:04:592010新春纪念徽章
日期:2010-01-04 08:33:08参与WIN7挑战赛纪念
日期:2009-11-06 16:05:25生肖徽章2007版:狗
日期:2009-09-10 11:32:03
 楼主| 发表于 2010-3-31 16:36 | 显示全部楼层
up

使用道具 举报

回复

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

本版积分规则 发表回复

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号
  
快速回复 返回顶部 返回列表