非等值sql的优化
今天快下班的时候，群里有个朋友在问，这个sql非常慢，如何解决:
原sql如下: www.2cto.com
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
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万
相对应的表结构如 www.2cto.com
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")
("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)
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
(
ini