ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 语句优化一例

语句优化一例

原创 Linux操作系统 作者:v_fantasy 时间:2009-04-21 19:27:00 0 删除 编辑

语句为:

select  '20000号拆出来的套餐订购 895'||count(*)
from uniteleacc.bb_user_product_info_t a,unitelecrm.bb_service_relation_t b,
tsubproduct_item_20000mid d, pitemrolemid c
where  a.f_user_id = b.user_id and b.if_valid = 1 and b.service_kind = 9
and a.f_city_code = d.f_use_city and a.f_item_id = d.f_item_id
and a.f_price_id = d.f_price_id and a.f_item_id = c.f_item_id
and a.f_city_code = c.f_city_code and c.f_service_kind = 9
and a.f_city_code = b.city_code and a.f_subproduct_id = '20000'
and a.f_city_code = '895';

执行计划为:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS  1     67988                          
  SORT AGGREGATE  1   66                            
    TABLE ACCESS BY INDEX ROWID UNITELECRM.BB_SERVICE_RELATION_T 1   16   45778                          
      NESTED LOOPS  1   66   67988                          
        HASH JOIN  1   50   22210                          
          HASH JOIN  1   38   22199                          
            TABLE ACCESS FULL BSS1.TSUBPRODUCT_ITEM_20000MID 7   98   3                          
            TABLE ACCESS BY INDEX ROWID UNITELEACC.BB_USER_PRODUCT_INFO_T 100   2 K 22196                          
              INDEX RANGE SCAN BSS1.USERPRODUCT_CITY 1 M   4192                          
          TABLE ACCESS FULL BSS1.PITEMROLEMID 522   6 K 10                          
        INDEX RANGE SCAN BSS1.IDX_SERVICE_RELATION_CITY 1 M   2567                          

执行起来非常慢,要好几个小时,因为索引的选择性都比较差,应该是最后一个nl的问题,于是

select  count(*)
from uniteleacc.bb_user_product_info_t a,
tsubproduct_item_20000mid d, pitemrolemid c
where  a.f_city_code = d.f_use_city and a.f_item_id = d.f_item_id
and a.f_price_id = d.f_price_id and a.f_item_id = c.f_item_id
and a.f_city_code = c.f_city_code and c.f_service_kind = 9
and a.f_subproduct_id = '20000' and a.f_city_code = '895'

速度很快,结果为1620,而b表13029285行,city_code字段distinct值为16,可见,确实是索引的选择性太差,而nl的次数太多导致,于是准备采用hint强制hash,写法如下:

select /*+ NO_QUERY_TRANSFORMATION  use_hash(x b) */count(*)
from (select  a.f_city_code,a.f_user_id
from uniteleacc.bb_user_product_info_t a,
bss1.tsubproduct_item_20000mid d, bss1.pitemrolemid c
where     a.f_city_code ='891' and  d.f_use_city='891'  and c.f_city_code='891'
and a.f_subproduct_id = '20000' and c.f_service_kind = 9
and a.f_item_id = d.f_item_id and a.f_item_id = c.f_item_id
) x,
unitelecrm.bb_service_relation_t b
where b.city_code='891' and x.f_user_id = b.user_id and b.if_valid = 1 and b.service_kind = 9

执行计划如下:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS  1     61927                          
  SORT AGGREGATE  1   31                            
    HASH JOIN  8   248   61927                          
      VIEW  8   120   22184                          
        HASH JOIN  8   352   22184                          
          HASH JOIN  8   256   22174                          
            TABLE ACCESS FULL BSS1.TSUBPRODUCT_ITEM_20000MID 7   63   3                          
            TABLE ACCESS BY INDEX ROWID UNITELEACC.BB_USER_PRODUCT_INFO_T 2 K 54 K 22170                          
              INDEX RANGE SCAN BSS1.USERPRODUCT_CITY 1 M   4192                          
          TABLE ACCESS FULL BSS1.PITEMROLEMID 475   5 K 10                          
      TABLE ACCESS BY INDEX ROWID UNITELECRM.BB_SERVICE_RELATION_T 267 K 4 M 39739                          
        INDEX RANGE SCAN BSS1.IDX_SERVICE_RELATION_CITY 1 M   2230                          

set timing on一下,只用了5到6s

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16158219/viewspace-591207/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle hint
请登录后发表评论 登录
全部评论

注册时间:2008-10-07

  • 博文量
    98
  • 访问量
    181416