查看: 3228|回复: 4

语句优化,如何控制子查询先执行

[复制链接]
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2010-6-13 19:48 | 显示全部楼层 |阅读模式
语句如下:

   select
   decode(sum(v.period_prem), null, 0, sum(v.period_prem)) from
   (select pf.list_id, nvl(pf.period_prem, 0) period_prem
                       from t_product_fee     pf,
                            t_contract_master cm,
                            t_product_life    pl,
                            t_policy_fee      pfe
                      where pf.policy_id = cm.policy_id
                        and cm.validate_date <
                            ADD_MONTHS(to_date('201005', 'yyyymm'), -13)
                        and cm.validate_date >=
                            ADD_MONTHS(to_date('201005', 'yyyymm'), -14)
                        and pf.fee_id = pfe.fee_id
                        and pfe.received_time >=
                            ADD_MONTHS(cm.validate_date, 12)
                        and pfe.received_time <
                            ADD_MONTHS(to_date('201005', 'yyyymm'), 1)
                        and pfe.receiv_status = 2
                        and pf.charge_type = 1
                        and pf.product_id = pl.product_id
                        and (pl.period_type <> 2 or exists
                             (select 1
                                from t_life_basic cc
                               where cc.coverage_year <> 1
                                 and cc.product_id = pl.product_id))
                        and pf.fee_type in (43, 47, 48)
                        and pf.policy_year = 2
                        and cm.prem_status not in (2, 3, 4)
                        and cm.nb_agency_id in
                            (select agency_id
                               from t_agency
                              start with agency_id = 121
                             Connect By Prior agency_id = parent_id)) v
    where pkg_life_calc_basic_comm.f_is_effect(v.list_id) = '0';
虽然这样写了但是最后的条件pkg_life_calc_basic_comm.f_is_effect(v.list_id) = '0'还是在第一步就执行了执行计划如下:

Plan hash value: 1942344324

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                   |                              |      1 |      1 |      1 |00:00:09.43 |    1298K|
|*  2 |   FILTER                          |                              |      1 |        |      0 |00:00:09.43 |    1298K|
|*  3 |    HASH JOIN SEMI                 |                              |      1 |      1 |      0 |00:00:09.43 |    1298K|
|   4 |     NESTED LOOPS                  |                              |      1 |      1 |     79 |00:00:10.02 |    1298K|
|   5 |      NESTED LOOPS                 |                              |      1 |      1 |     79 |00:00:10.01 |    1298K|

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   6 |       NESTED LOOPS                |                              |      1 |      5 |     85 |00:00:10.23 |    1297K|
|*  7 |        TABLE ACCESS FULL          | T_PRODUCT_FEE                |      1 |     22 |  19155 |00:00:09.16 |    1259K|
|*  8 |        TABLE ACCESS BY INDEX ROWID| T_CONTRACT_MASTER            |  19155 |      1 |     85 |00:00:00.42 |  
|*  9 |         INDEX UNIQUE SCAN         | PK_T_CONTRACT_MASTER         |  19155 |      1 |  19155 |00:00:00.18 |   19157 |
|* 10 |       TABLE ACCESS BY INDEX ROWID | T_POLICY_FEE                 |     85 |      1 |     79 |00:00:00.01 |     25
|* 11 |        INDEX UNIQUE SCAN          | PK_T_POLICY_FEE              |     85 |      1 |     85 |00:00:00.01 |     172 |
|  12 |      TABLE ACCESS BY INDEX ROWID  | T_PRODUCT_LIFE               |     79 |      1 |     79 |00:00:00.01 |      81 |
|* 13 |       INDEX UNIQUE SCAN           | PK_T_PRODUCT_LIFE            |     79 |      1 |     79 |00:00:00.01 |       2 |
|  14 |     VIEW                          | VW_NSO_1                     |      1 |     58 |     54 |00:00:00.01 |      23 |
|* 15 |      FILTER                       |                              |      1 |        |     54 |00:00:00.01 |      23 |
|* 16 |       CONNECT BY WITH FILTERING   |                              |      1 |        |     54 |00:00:00.01 |      23 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|  17 |        TABLE ACCESS BY INDEX ROWID| T_AGENCY                     |      1 |        |      1 |00:00:00.01 |       2 |
|* 18 |         INDEX UNIQUE SCAN         | PK_T_AGENCY                  |      1 |      1 |      1 |00:00:00.01 |       1 |
|* 19 |        HASH JOIN                  |                              |      3 |        |     53 |00:00:00.01 |      21 |
|  20 |         CONNECT BY PUMP           |                              |      3 |        |     54 |00:00:00.01 |       0 |
|  21 |         TABLE ACCESS FULL         | T_AGENCY                     |      3 |     58 |    177 |00:00:00.01 |      21 |
|  22 |        TABLE ACCESS FULL          | T_AGENCY                     |      0 |     58 |      0 |00:00:00.01 |       0 |
|* 23 |    INDEX RANGE SCAN               | UNI_LIFE_BASIC__PRODUCT_INFO |      0 |     39 |      0 |00:00:00.01 |       0 |
Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   2 - filter((TO_NUMBER("PL"."PERIOD_TYPE")<>2 OR  IS NOT NULL))
   3 - access("CM"."NB_AGENCY_ID"="$nso_col_1")
   7 - filter(("PF"."POLICY_YEAR"=2 AND INTERNAL_FUNCTION("PF"."FEE_TYPE") AND TO_NUMBER("PF"."CHARG
              "PKG_LIFE_CALC_BASIC_COMM"."F_IS_EFFECT"("PF"."LIST_ID")=0))
   8 - filter(("CM"."NB_AGENCY_ID" IS NOT NULL AND "CM"."VALIDATE_DATE"<TO_DATE('2009-04-01 00:00:00
              hh24:mi:ss') AND "CM"."PREM_STATUS"<>2 AND "CM"."VALIDATE_DATE">=TO_DATE('2009-03-01 00:00:00
              hh24:mi:ss') AND ADD_MONTHS(INTERNAL_FUNCTION("CM"."VALIDATE_DATE"),12)<TO_DATE('2010-06-01 0
              hh24:mi:ss') AND "CM"."PREM_STATUS"<>3 AND "CM"."PREM_STATUS"<>4))
   9 - access("PF"."POLICY_ID"="CM"."POLICY_ID")
  10 - filter((TO_NUMBER("PFE"."RECEIV_STATUS")=2 AND "PFE"."RECEIVED_TIME"<TO_DATE('2010-06-01 00:0

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
              'yyyy-mm-dd hh24:mi:ss') AND "PFE"."RECEIVED_TIME">=ADD_MONTHS(INTERNAL_FUNCTION("CM"."VALIDA
  11 - access("PF"."FEE_ID"="PFE"."FEE_ID")
  13 - access("PF"."PRODUCT_ID"="PL"."PRODUCT_ID")
  15 - filter((TO_DATE('2009-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')>TO_DATE('2009-03-01 00:00:00'
              hh24:mi:ss') AND TO_DATE('2009-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<TO_DATE('2009-04-01
              hh24:mi:ss')))
  16 - filter("AGENCY_ID"=121)
  18 - access("AGENCY_ID"=121)
  19 - access("PARENT_ID"=NULL)
  23 - access("CC"."PRODUCT_ID"=:B1)
       filter("CC"."COVERAGE_YEAR"<>1)

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------


注意第7步啊
我就是不想"PKG_LIFE_CALC_BASIC_COMM"."F_IS_EFFECT"("PF"."LIST_ID")=0 如此早的执行,
想等到子查询完成后在执行,这个是调用一个方法,返回结果越多,越慢,最后执行是最好的。
有那些方法办到啊,谢谢.我加了提示,建了视图 没用可能提示加得不对。望指点,谢谢!
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2010-6-13 19:54 | 显示全部楼层
select /*+ no_merge(v) leading(v) */
   decode(sum(v.period_prem), null, 0, sum(v.period_prem)) from
   (select pf.list_id, nvl(pf.period_prem, 0) period_prem
                       from t_product_fee     pf,
                            t_contract_master cm,
                            t_product_life    pl,
                            t_policy_fee      pfe
                      where pf.policy_id = cm.policy_id
                        and cm.validate_date <
                            ADD_MONTHS(to_date('201005', 'yyyymm'), -13)
                        and cm.validate_date >=
                            ADD_MONTHS(to_date('201005', 'yyyymm'), -14)
                        and pf.fee_id = pfe.fee_id
                        and pfe.received_time >=
                            ADD_MONTHS(cm.validate_date, 12)
                        and pfe.received_time <
                            ADD_MONTHS(to_date('201005', 'yyyymm'), 1)
                        and pfe.receiv_status = 2
                        and pf.charge_type = 1
                        and pf.product_id = pl.product_id
                        and (pl.period_type <> 2 or exists
                             (select 1
                                from t_life_basic cc
                               where cc.coverage_year <> 1
                                 and cc.product_id = pl.product_id))
                        and pf.fee_type in (43, 47, 48)
                        and pf.policy_year = 2
                        and cm.prem_status not in (2, 3, 4)
                        and cm.nb_agency_id in
                            (select agency_id
                               from t_agency
                              start with agency_id = 121
                             Connect By Prior agency_id = parent_id)) v
    where pkg_life_calc_basic_comm.f_is_effect(v.list_id) = '0';
没用啊

使用道具 举报

回复
论坛徽章:
16
授权会员
日期:2005-11-01 10:49:02ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472011新春纪念徽章
日期:2011-02-18 11:43:322010年世界杯参赛球队:南非
日期:2010-05-12 11:08:572010新春纪念徽章
日期:2010-03-01 11:04:542009新春纪念徽章
日期:2009-01-04 14:52:28生肖徽章2007版:狗
日期:2008-10-31 12:50:13生肖徽章2007版:狗
日期:2008-10-24 18:01:04奥运会纪念徽章:排球
日期:2008-10-24 13:30:01生肖徽章2007版:狗
日期:2008-10-20 14:41:16
发表于 2010-6-14 11:01 | 显示全部楼层
用  no_unnest 试试
select /*+ no_unnest */  agency_id
                               from t_agency
                              start with agency_id = 121
                             Connect By Prior agency_id = parent_id

使用道具 举报

回复
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2010-6-16 10:45 | 显示全部楼层
我想的是这个大的子查询先执行啊
select pf.list_id, nvl(pf.period_prem, 0) period_prem
                       from t_product_fee     pf,
                            t_contract_master cm,
                            t_product_life    pl,
                            t_policy_fee      pfe
                      where pf.policy_id = cm.policy_id
                        and cm.validate_date <
                            ADD_MONTHS(to_date('201005', 'yyyymm'), -13)
                        and cm.validate_date >=
                            ADD_MONTHS(to_date('201005', 'yyyymm'), -14)
                        and pf.fee_id = pfe.fee_id
                        and pfe.received_time >=
                            ADD_MONTHS(cm.validate_date, 12)
                        and pfe.received_time <
                            ADD_MONTHS(to_date('201005', 'yyyymm'), 1)
                        and pfe.receiv_status = 2
                        and pf.charge_type = 1
                        and pf.product_id = pl.product_id
                        and (pl.period_type <> 2 or exists
                             (select 1
                                from t_life_basic cc
                               where cc.coverage_year <> 1
                                 and cc.product_id = pl.product_id))
                        and pf.fee_type in (43, 47, 48)
                        and pf.policy_year = 2
                        and cm.prem_status not in (2, 3, 4)
                        and cm.nb_agency_id in
                            (select agency_id
                               from t_agency
                              start with agency_id = 121
                             Connect By Prior agency_id = parent_id))

使用道具 举报

回复
认证徽章
论坛徽章:
9
ITPUB社区OCM联盟徽章
日期:2013-03-27 11:17:11奥运纪念徽章
日期:2013-06-18 09:13:52ITPUB社区千里马徽章
日期:2013-08-22 09:58:03大众
日期:2013-08-30 14:51:33路虎
日期:2013-12-01 18:25:42
发表于 2010-6-16 12:37 | 显示全部楼层
我已经改写如下:不知道大家觉得如何
select
   decode(sum(period_prem), null, 0, sum(period_prem)) from
     (select  decode(pkg_life_calc_basic_comm.f_is_effect(pf.list_id),'0',PF.list_id) list_id, decode(pkg_life_calc_basic_comm.f_is_effect(PF.list_id),'0',nvl(pf.period_prem, 0) )

period_prem
                          from t_product_fee     pf,
                                t_contract_master cm,
                                t_product_life    pl,
                                t_policy_fee      pfe
                          where pf.policy_id = cm.policy_id
                            and cm.validate_date <
                               ADD_MONTHS(to_date('201005', 'yyyymm'), -13)
                           and cm.validate_date >=
                               ADD_MONTHS(to_date('201005', 'yyyymm'), -14)
                           and pf.fee_id = pfe.fee_id
                           and pfe.received_time >=
                               ADD_MONTHS(cm.validate_date, 12)
                           and pfe.received_time <
                               ADD_MONTHS(to_date('201005', 'yyyymm'), 1)
                           and pfe.receiv_status = 2
                           and pf.charge_type = 1
                           and pf.product_id = pl.product_id
                           and (pl.period_type <> 2 or exists
                                (select 1
                                  from t_life_basic cc
                                  where cc.coverage_year <> 1
                                    and cc.product_id = pl.product_id))
                           and pf.fee_type in (43, 47, 48)
                          and pf.policy_year = 2
                          and cm.prem_status not in (2, 3, 4)
                           and cm.nb_agency_id in
                              (select    agency_id
                                from t_agency
                                 start with agency_id = 121
                                Connect By Prior agency_id = parent_id));

使用道具 举报

回复

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

本版积分规则 发表回复

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