今天快下班的时候,群里有个朋友在问,这个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
修改完之后一秒钟就出来了。。。