非等值sql的优化(二)

minextents 1

maxextents unlimited

);

insert /*+ append */ into dr_bps_guna_yyyymm_ch_01

select file_type,file_sn,rec_type,org_id,imsi,trm_id,forward_id,call_date,duration,
base_fee,toll_fee,
toll_add,other_fee,mss_vas_fee,toll_type,indb_date,date_str,area_code

from dr_bps_guna_yyyymm a , my_bds_mobile_prefix_ch b

where a.org_id >= b.lower_mobile_prefix

and a.org_id <= b.upper_mobile_prefix

and a.rec_type = 20

and rownum<3001

分析思路:

从b表选取一行，然后循环去a表执行:

相当于要执行3000个这样的查询:

insert /*+ append */ into dr_bps_guna_yyyymm_ch_01

select file_type,file_sn,rec_type,org_id,imsi,trm_id,forward_id,call_date,duration,
base_fee,toll_fee,
toll_add,other_fee,mss_vas_fee,toll_type,indb_date,date_str,area_code

from dr_bps_guna_yyyymm a , my_bds_mobile_prefix_ch b

where a.org_id >= 1（假如是1）

and a.org_id <= 2 (假如是2)

and a.rec_type = 20

and rownum<3001

优化步骤:

1.建立(rec_type,org_id)索引

2.查询条件为:

insert /*+ append */ into dr_bps_guna_yyyymm_ch_01

select /*+use_nl(a,b) leading(b) file_type,file_sn,rec_type,org_id,imsi,trm_id,forward_id,
call_date,duration,
base_fee,toll_fee,
toll_add,other_fee,mss_vas_fee,toll_type,indb_date,date_str,area_code

from dr_bps_guna_yyyymm a , my_bds_mobile_prefix_ch b

where a.org_id >= to_char(b.lower_mobile_prefix)

and a.org_id <= to_char(b.lower_mobile_prefix)

and a.rec_type = 20

and rownum<3001

修改完之后一秒钟就出来了。。。