语句为：

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