非等值sql的优化

今天快下班的时候，群里有个朋友在问，这个sql非常慢，如何解决:

原sql如下:

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, my_bds_mobile_prefix_ch
 where org_id >= lower_mobile_prefix
   and org_id <= upper_mobile_prefix
   and rec_type = 20
   and rownum<3001

dr_bps_guna_yyyymm --80万
my_bds_mobile_prefix_ch --6万

相对应的表结构如

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 226470588

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT    |                         |     1 |   213 |  3000   (4)| 00:00:37 |
|*  1 |  COUNT STOPKEY      |                         |       |       |            |          |
|   2 |   NESTED LOOPS      |                         |     1 |   213 |  3000   (4)| 00:00:37 |
|   3 |    TABLE ACCESS FULL| MY_BDS_MOBILE_PREFIX_CH |     1 |    32 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| DR_BPS_GUNA_YYYYMM      |   852 |   150K|  2998   (4)| 00:00:36 |
-----------------------------------------------------------------------------------------------

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

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

   1 - filter(ROWNUM<3001)
   4 - filter(TO_NUMBER("REC_TYPE")=20 AND "LOWER_MOBILE_PREFIX"<=TO_NUMBER("ORG_ID")
              AND "UPPER_MOBILE_PREFIX">=TO_NUMBER("ORG_ID"))

Note
-----
   - dynamic sampling used for this statement

22 rows selected.

create table DR_BPS_GUNA_YYYYMM
(
  REC_TYPE    CHAR(2) not null,
  ORG_ID      VARCHAR2(25) not null,
  TRM_ID      VARCHAR2(25),
  FORWARD_ID  VARCHAR2(25),
  CALL_DATE   VARCHAR2(15) not null,
  DATE_STR    VARCHAR2(16)
)

-- Create/Recreate indexes
create index DR_BPS_GUNA_IDX on DR_BPS_GUNA_YYYYMM (ORG_ID, TRM_ID, CALL_DATE)
  tablespace AUDIT_STORE_TS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create table
create table MY_BDS_MOBILE_PREFIX_CH
(
  LOWER_MOBILE_PREFIX NUMBER(11) not null,
  UPPER_MOBILE_PREFIX NUMBER(11) not null,
  AREA_CODE           VARCHAR2(8) not null
)
tablespace AUDIT_STORE_TS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    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

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