查询数据量在5000-10000条之间
全局临时表建表TS_R_OTHER_DAY_TMP语句如下:
-- Create table
create global temporary table TS_R_OTHER_DAY_TMP
(
report_id VARCHAR2(30),
item_code VARCHAR2(10),
item_order NUMBER(4),
valuen1 NUMBER(20,2),
valuen2 NUMBER(20,2),
valuen3 NUMBER(20,2),
valuec1 VARCHAR2(100),
valuec2 VARCHAR2(100)
)
on commit delete rows;sql insert 脚本:
----第一段 insert
INSERT INTO ts_r_other_day_tmp(valuen1,valuen2,REPORT_ID )
SELECT a.trade_id,a.user_id, to_char(MAX(b.start_date),'yyyymmddhh24miss') start_date
FROM tf_b_trade_finish_detail_in a, tf_f_user_item b
WHERE a.parent_net_type_code = '1'
AND a.user_id = b.user_id
AND b.partition_id = MOD(a.user_id,10000)
AND b.attr_code='MOFFICE_ID'
GROUP BY a.trade_id,a.user_id
;
----第二段 insert
INSERT INTO tf_b_trade_finish_attr_in(trade_id,user_id, attr_type,
attr_code,attr_value)
SELECT a.valuen1,b.user_id,'MOFFICE_ID',
b.attr_value,
c.rsrv_str1
FROM ts_r_other_day_tmp a,tf_f_user_item b,td_m_area_moffice c
WHERE a.valuen2= b.user_id
AND b.partition_id = MOD(a.valuen2,10000)
AND b.attr_code ='MOFFICE_ID'
AND b.start_date = to_date(a.REPORT_ID ,'yyyymmddhh24miss')
AND b.attr_value = c.moffice_id
;
COMMIT; ----务必提交!!! 在plsql窗口中单独执行sql insert,用时1分钟,但是把这个sql放到存储过程里面的时候,执行了半个小时还没有执行完毕,看session信息,语句就停留在第二段insert上面。求高人指点迷津。附第二段sql insert的执行计划:
全局临时表建表TS_R_OTHER_DAY_TMP语句如下:
-- Create table
create global temporary table TS_R_OTHER_DAY_TMP
(
report_id VARCHAR2(30),
item_code VARCHAR2(10),
item_order NUMBER(4),
valuen1 NUMBER(20,2),
valuen2 NUMBER(20,2),
valuen3 NUMBER(20,2),
valuec1 VARCHAR2(100),
valuec2 VARCHAR2(100)
)
on commit delete rows;sql insert 脚本:
----第一段 insert
INSERT INTO ts_r_other_day_tmp(valuen1,valuen2,REPORT_ID )
SELECT a.trade_id,a.user_id, to_char(MAX(b.start_date),'yyyymmddhh24miss') start_date
FROM tf_b_trade_finish_detail_in a, tf_f_user_item b
WHERE a.parent_net_type_code = '1'
AND a.user_id = b.user_id
AND b.partition_id = MOD(a.user_id,10000)
AND b.attr_code='MOFFICE_ID'
GROUP BY a.trade_id,a.user_id
;
----第二段 insert
INSERT INTO tf_b_trade_finish_attr_in(trade_id,user_id, attr_type,
attr_code,attr_value)
SELECT a.valuen1,b.user_id,'MOFFICE_ID',
b.attr_value,
c.rsrv_str1
FROM ts_r_other_day_tmp a,tf_f_user_item b,td_m_area_moffice c
WHERE a.valuen2= b.user_id
AND b.partition_id = MOD(a.valuen2,10000)
AND b.attr_code ='MOFFICE_ID'
AND b.start_date = to_date(a.REPORT_ID ,'yyyymmddhh24miss')
AND b.attr_value = c.moffice_id
;
COMMIT; ----务必提交!!! 在plsql窗口中单独执行sql insert,用时1分钟,但是把这个sql放到存储过程里面的时候,执行了半个小时还没有执行完毕,看session信息,语句就停留在第二段insert上面。求高人指点迷津。附第二段sql insert的执行计划:
INSERT /*+append*/ INTO ts_r_other_day_tmp(valuen1,valuen2,REPORT_ID )
SELECT a.trade_id,a.user_id, to_char(MAX(b.start_date),'yyyymmddhh24miss') start_date
FROM tf_b_trade_finish_detail_in a, tf_f_user_item b
WHERE a.parent_net_type_code = '1'
AND a.user_id = b.user_id
AND b.partition_id = MOD(a.user_id,10000)
AND b.attr_code='MOFFICE_ID'
GROUP BY a.trade_id,a.user_id
;
----第二段 insert
INSERT /*+append*/ INTO tf_b_trade_finish_attr_in(trade_id,user_id, attr_type,
attr_code,attr_value)
SELECT a.valuen1,b.user_id,'MOFFICE_ID',
b.attr_value,
c.rsrv_str1
FROM ts_r_other_day_tmp a,tf_f_user_item b,td_m_area_moffice c
WHERE a.valuen2= b.user_id
AND b.partition_id = MOD(a.valuen2,10000)
AND b.attr_code ='MOFFICE_ID'
AND b.start_date = to_date(a.REPORT_ID ,'yyyymmddhh24miss')
AND b.attr_value = c.moffice_id
;
COMMIT; ----务必提交!!!
10046或者sql_trace随便用个!
2.使用nologging选项
3.使用并行选项
4.一个insert做到一个commit;
2)如果REPORT_ID选择性好,可将REPORT_ID在临时表中定义为日期型,先转换成日期后再插入临时表。