查看: 3045|回复: 13

sql优化请教一下!

[复制链接]
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2010-11-3 15:14 | 显示全部楼层 |阅读模式
sql优化请教一下!

有这么语句话:

SELECT P1.Asset_Integ_Id,
       CASE
         when p2.area_Id is not null then
          p2.c5_area_Id
         when p2.area_Id is null and P1.Telecom_Area_Id = 1401 then
          -32519
         when p2.area_Id is null and P1.Telecom_Area_Id = 1402 then
          -32522
         when p2.area_Id is null and P1.Telecom_Area_Id = 1403 then
          -32521
         when p2.area_Id is null and P1.Telecom_Area_Id = 1404 then
          -32520
         when p2.area_Id is null and P1.Telecom_Area_Id = 1405 then
          -32523
         else
          -75
       END AS AREA_ID,
       P1.Telecom_Area_Id,
       P.Corp_User_Name
  FROM SXMART.OFR_MAIN_ASSET_N_HIST_E P1,
       SXMART.PAR_CUST_N_HIST_E       P,
       sxdm.sxmart_area_c5_sl         p2
WHERE P.CCUST_ID = P1.CCUST_ID(+)
   AND p1.Area_id = p2.Area_id(+)
   AND P.CCust_Stat_Name = '正式'
   AND P.START_DT <= '20100201'
   AND P.END_DT > '20100201'
   AND P1.Std_Prd_Lvl4_Id =ANY(11020410, 11020411)
   AND P1.Stat_Name <> '不活动'
   AND P1.START_DT <= '20100201'
   AND P1.END_DT > '20100201'
GROUP BY P1.Asset_Integ_Id,
          P1.Telecom_Area_Id,
          P.Corp_User_Name,
          p2.area_id,
          p2.c5_area_Id



其中SQL> select 5723/71918507 from dual;

5723/71918507
-------------
7.95761791884


此sql返回5723条记录。而关联到最大的表有71918507条记录
索引健全就是走不上

SELECT STATEMENT, GOAL = ALL_ROWS        37377        1462634        0        1454737                        118555384616
HASH GROUP BY        37377        1462634        1        1454737                        118555384616
  HASH JOIN RIGHT OUTER        37377        1461528        2        1453635                        118497209472
   TABLE ACCESS FULL        1936        12        3        12        xxxx        SXMART_AREA_C5_SL        960241
   HASH JOIN        37377        1461515        3        1453623                        118484714941
    TABLE ACCESS FULL        27794        1287926        4        1280963        xxxxx        OFR_MAIN_ASSET_N_HIST_E        104531697507
    TABLE ACCESS FULL        1938839        168129        4        167230        xxxxx        PAR_CUST_N_HIST_E        13496029254


加了hint后反而呢:

SELECT STATEMENT, GOAL = ALL_ROWS        37377        5219217        0        5214200                        75316091902
HASH GROUP BY        37377        5219217        1        5214200                        75316091902
  HASH JOIN RIGHT OUTER        37377        5218111        2        5213098                        75257916758
   TABLE ACCESS FULL        1936        12        3        12        SXDM        SXMART_AREA_C5_SL        960241
   HASH JOIN        37377        5218098        3        5213086                        75245422227
    TABLE ACCESS BY INDEX ROWID        27794        5044509        4        5040426        SXMART        OFR_MAIN_ASSET_N_HIST_E        61292404793
     INDEX RANGE SCAN        19609777        54915        5        54628        SXMART        IDX_MAIN_ASSET_START_DT        4310985624
    TABLE ACCESS FULL        1938839        168129        4        167230        SXMART        PAR_CUST_N_HIST_E        13496029254
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2010-11-3 15:15 | 显示全部楼层
加hint后基数反而增大了

使用道具 举报

回复
论坛徽章:
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-11-3 16:04 | 显示全部楼层
这格式,没法看

走索引可能无法过滤某些谓词,导致Card很大。

使用道具 举报

回复
论坛徽章:
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-11-3 16:08 | 显示全部楼层

SUNDOG315>select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SUNDOG315>create table t as select 1 id,object_name from dba_objects;

表已创建。

SUNDOG315>insert into t select 2,object_name from dba_objects;

已创建55098行。

SUNDOG315>commit;

提交完成。

SUNDOG315>create index t_idx on t(id);

索引已创建。

SUNDOG315>exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for
all columns',cascade => true);

PL/SQL 过程已成功完成。

SUNDOG315>select id,count(*) from t group by id;

        ID   COUNT(*)
---------- ----------
         1      55098
         2      55098

SUNDOG315>select count(*) from t where id=1 and object_name='OBJ$';

  COUNT(*)
----------
         1

SUNDOG315>set autot trace exp
SUNDOG315>select count(*) from t where id=1 and object_name='OBJ$';


执行计划
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    28 |   175   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |    28 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     2 |    56 |   175   (1)| 00:00:03 |      --过滤的谓词条件是"OBJECT_NAME"='OBJ$' AND "ID"=1
---------------------------------------------------------------------------

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

   2 - filter("OBJECT_NAME"='OBJ$' AND "ID"=1)

SUNDOG315>select /*+ index(t t_idx) */ count(*) from t where id=1 and object_nam
e='OBJ$';


执行计划
----------------------------------------------------------
Plan hash value: 1789076273

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    28 |   362   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE              |       |     1 |    28 |            |     |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T     |     2 |    56 |   362   (1)| 00:00:05 |
|*  3 |    INDEX RANGE SCAN          | T_IDX | 55098 |       |   108   (0)| 00:00:02 |              --仅过滤"ID"=1
--------------------------------------------------------------------------------------

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

   2 - filter("OBJECT_NAME"='OBJ$')
   3 - access("ID"=1)


[ 本帖最后由 sundog315 于 2010-11-3 16:09 编辑 ]

使用道具 举报

回复
论坛徽章:
70
ITPUB元老
日期:2007-07-19 08:57:15乌索普
日期:2016-06-24 14:29:16沸羊羊
日期:2015-02-12 09:15:562014年世界杯参赛球队:喀麦隆
日期:2014-05-20 16:06:36马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11迷宫蛋
日期:2013-04-24 13:52:55茶鸡蛋
日期:2013-04-19 13:54:282013年新春福章
日期:2013-02-25 14:51:24蛋疼蛋
日期:2013-02-19 14:05:00
发表于 2010-11-3 16:14 | 显示全部楼层
加 /* + first_rows */
hint 试下!

使用道具 举报

回复
论坛徽章:
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-11-3 16:23 | 显示全部楼层
楼主最好重新贴一下执行计划,这个格式,实在看不了

使用道具 举报

回复
认证徽章
论坛徽章:
764
2014年世界杯参赛球队: 加纳
日期:2014-05-20 17:24:592014年世界杯参赛球队:墨西哥
日期:2014-05-20 17:25:142014年世界杯参赛球队: 波黑
日期:2014-05-20 17:27:292014年世界杯参赛球队: 希腊
日期:2014-07-15 11:05:212014年世界杯参赛球队: 阿尔及利亚
日期:2014-07-15 11:05:212014年世界杯参赛球队: 厄瓜多尔
日期:2014-07-15 14:14:452014年世界杯参赛球队: 哥斯达黎加
日期:2014-06-16 15:29:332014年世界杯参赛球队: 智利
日期:2014-06-16 17:03:11 2014年世界杯参赛球队: 德国
日期:2014-06-16 17:10:512014年世界杯参赛球队: 葡萄牙
日期:2014-06-18 09:28:47
发表于 2010-11-3 16:44 | 显示全部楼层
不走索引的情况有很多啊,比如字段类型不匹配发生隐式转换

使用道具 举报

回复
论坛徽章:
32
奥运会纪念徽章:摔跤
日期:2012-08-23 11:03:05青年奥林匹克运动会-击剑
日期:2014-09-19 10:58:152014年世界杯参赛球队:巴西
日期:2014-07-07 12:19:232014年世界杯参赛球队: 瑞士
日期:2014-05-19 12:18:36马上有钱
日期:2014-04-08 12:12:232014年新春福章
日期:2014-04-04 14:20:47马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09红旗
日期:2014-02-14 15:15:55优秀写手
日期:2013-12-18 09:29:16
发表于 2010-11-4 09:35 | 显示全部楼层
p表上面的条件是这样的:
   AND P.START_DT <= '20100201'
   AND P.END_DT > '20100201'
你要走索引的话,这个过滤条件应该是很差的,这个的结果集有大量返回行,card不高才怪呢。

使用道具 举报

回复
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2010-11-4 10:20 | 显示全部楼层
START_DT 和AND P.END_DT上创建复合索引会不会好一点呢!

使用道具 举报

回复
认证徽章
论坛徽章:
58
生肖徽章2007版:马
日期:2009-11-06 23:12:33授权会员
日期:2013-01-10 14:38:592013年新春福章
日期:2013-02-25 14:51:24马自达
日期:2013-08-07 10:54:45红旗
日期:2013-08-09 13:48:48劳斯莱斯
日期:2013-09-12 15:56:37萤石
日期:2013-10-31 08:44:19优秀写手
日期:2013-12-18 09:29:13Jeep
日期:2014-01-14 10:53:432014年新春福章
日期:2014-02-18 16:43:09
发表于 2010-11-4 10:36 | 显示全部楼层
SQL> SELECT P1.Asset_Integ_Id,
  2         CASE
  3           when p2.area_Id is not null then
  4            p2.c5_area_Id
  5           when p2.area_Id is null and P1.Telecom_Area_Id = 1401 then
  6            -32519
  7           when p2.area_Id is null and P1.Telecom_Area_Id = 1402 then
  8            -32522
  9           when p2.area_Id is null and P1.Telecom_Area_Id = 1403 then
10            -32521
11           when p2.area_Id is null and P1.Telecom_Area_Id = 1404 then
12            -32520
13           when p2.area_Id is null and P1.Telecom_Area_Id = 1405 then
14            -32523
15           else
16            -75
17         END AS AREA_ID,
18         P1.Telecom_Area_Id,
19         P.Corp_User_Name
20    FROM SXMART.OFR_MAIN_ASSET_N_HIST_E P1,
21         SXMART.PAR_CUST_N_HIST_E       P,
22         sxdm.sxmart_area_c5_sl         p2
23   WHERE P.CCUST_ID = P1.CCUST_ID(+)
24     AND p1.Area_id = p2.Area_id(+)
25     AND P.CCust_Stat_Name = '正式'
26     AND P.START_DT <= '20100201'
27     AND P.END_DT > '20100201'
28     AND P1.Std_Prd_Lvl4_Id =ANY(11020410, 11020411)
29     AND P1.Stat_Name <> '不活动'
30     AND P1.START_DT <= '20100201'
31     AND P1.END_DT > '20100201'
32   GROUP BY P1.Asset_Integ_Id,
33            P1.Telecom_Area_Id,
34            P.Corp_User_Name,
35            p2.area_id,
36            p2.c5_area_Id
37  /
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 2877735221

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

| Id  | Operation              | Name                    | Rows  | Bytes |TempSp
c| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT       |                         | 37377 |  4818K|
|  1462K  (1)| 04:52:32 |

|   1 |  HASH GROUP BY         |                         | 37377 |  4818K|    10
M|  1462K  (1)| 04:52:32 |

|*  2 |   HASH JOIN RIGHT OUTER|                         | 37377 |  4818K|
|  1461K  (1)| 04:52:19 |

|   3 |    TABLE ACCESS FULL   | SXMART_AREA_C5_SL       |  1936 | 19360 |
|    12   (0)| 00:00:01 |

|*  4 |    HASH JOIN           |                         | 37377 |  4453K|  2392
K|  1461K  (1)| 04:52:19 |

|*  5 |     TABLE ACCESS FULL  | OFR_MAIN_ASSET_N_HIST_E | 27794 |  2062K|
|  1287K  (1)| 04:17:36 |

|*  6 |     TABLE ACCESS FULL  | PAR_CUST_N_HIST_E       |  1938K|    85M|
|   168K  (1)| 00:33:38 |

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


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

   2 - access("P2"."AREA_ID"(+)=TO_NUMBER("P1"."AREA_ID"))
   4 - access("P"."CCUST_ID"="P1"."CCUST_ID")
   5 - filter("P1"."START_DT"<='20100201' AND "P1"."END_DT">'20100201' AND
              "P1"."STAT_NAME"<>'不活动' AND (TO_NUMBER("P1"."STD_PRD_LVL4_ID")=
11020410 OR

              TO_NUMBER("P1"."STD_PRD_LVL4_ID")=11020411))
   6 - filter("P"."CCUST_STAT_NAME"='正式' AND "P"."START_DT"<='20100201' AND
              "P"."END_DT">'20100201')

使用道具 举报

回复

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

本版积分规则 发表回复

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