INSERT INTO T260_TMP_ARRIV_JS T260
(
T260_CREATE_YMD,
T260_UPDATE_YMD,
T260_TRAN_KBN,
T260_TANTO_CD,
T260_TERM_CD,
T260_WAREH_CD,
T260_OWNER_CD,
T260_WORK_ID,
T260_STATES,
T260_KBN,
T260_SENDCONFIRM_FLG,
T260_FIRST_KBN,
T260_SLIP_NO,
T260_ACCEPT_NO,
T260_ITEM_CD,
T260_ORDER_UNITS,
T260_UNITS,
T260_GOOD_UNITS,
T260_BAD_UNITS,
T260_MEGIN_CD,
T260_REASON_STR,
T260_UNITS_SJ,
T260_FIRST_NM,
T260_FORM_NM,
T260_COST_PRICE,
T260_ITEM_NM,
T260_CK_STATUS)
SELECT
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
'1',
'91234567' /*Param*/,
NULL,
'01' /*Param*/,
'005' /*Param*/,
'20090715033347' /*Param*/,
CASE
WHEN T048.T048_ACCEPT_REPORT_FLG ='1' THEN '送信済'
WHEN T048.T048_SENDCONFIRM_FLG = '1' THEN '確定'
WHEN W1.T049_UNITS > 0 THEN '棚入済'
WHEN T048.T048_ARRIVAL_UNITS > 0 THEN '入荷検品済'
WHEN T048.T048_ARRIVAL_UNITS = 0 THEN '未検品'
END ,
CASE
WHEN T048.T048_ORDER_UNITS = T048.T048_UNITS AND T048.T048_ORDER_UNITS = T048.T048_ARRIVAL_UNITS AND T048.T048_ORDER_UNITS = NVL(W1.T049_UNITS, 0) THEN '0'
WHEN T048.T048_ORDER_UNITS <> T048.T048_UNITS OR T048.T048_ORDER_UNITS <> T048.T048_ARRIVAL_UNITS OR T048.T048_ORDER_UNITS <> NVL(W1.T049_UNITS, 0) THEN '1'
END ,
CASE
WHEN NVL(T048.T048_SENDCONFIRM_FLG,'0') = '0' THEN '未'
WHEN NVL(T048.T048_SENDCONFIRM_FLG,'0') = '1' THEN '済'
END ,
CASE
WHEN NVL(T048.T048_FIRST_KBN,'0') = '1' THEN '初回入荷'
WHEN NVL(T048.T048_FIRST_KBN,'0') = '2' THEN '複回入荷'
END ,
T048.T048_SLIP_NO,
T048.T048_ACCEPT_NO,
T048.T048_ITEM_CD,
T048.T048_ORDER_UNITS,
T048.T048_UNITS,
NVL(W2.GOODNUM, 0),
NVL(W2.BADNUM, 0),
T048.T048_MEGIN_CD,
M033.M033_REASON_STR,
NVL(W1.T049_UNITS, 0),
W3.M202_FIRST_NM,
M015.M015_FORM_NM,
T048.T048_COST_PRICE,
(M018_ITEM_NM1 || M018_ITEM_NM2) M018_ITEM_NM
,'1'
FROM
T048_ARRIV_SCH T048
LEFT JOIN
M033_REASON M033
ON
M033.M033_REASON_KBN = '2'
AND T048.T048_MEGIN_CD = M033.M033_REASON_CD
AND M033.M033_TRAN_KBN <> '3'
LEFT JOIN (SELECT
T047_ARRIVAL_MGT_ID,
T047_LINE_NO,
MAX(T047_WAREH_CD) T047_WAREH_CD,
MAX(T047_OWNER_CD) T047_OWNER_CD,
SUM(T047_GOOD_NUM) GOODNUM,
SUM(T047_BAD_NUM) BADNUM
,MAX(T047_ARRIVAL_SCH_YMD) T047_ARRIVAL_SCH_YMD
FROM
T047_ARRIV_JS T047
WHERE
T047.T047_TRAN_KBN <> '3'
AND T047.T047_HISTORY_NO = '00'
AND T047.T047_CANCEL_FLG = '0'
AND T047.T047_WAREH_CD = '01' /*Param*/
AND T047.T047_OWNER_CD = '005' /*Param*/
GROUP BY
T047_ARRIVAL_MGT_ID,
T047_LINE_NO) W2
ON
T048.T048_ARRIVAL_MGT_ID = W2.T047_ARRIVAL_MGT_ID
AND T048.T048_WAREH_CD = W2.T047_WAREH_CD
AND T048.T048_OWNER_CD = W2.T047_OWNER_CD
AND T048.T048_LINE_NO = W2.T047_LINE_NO
LEFT JOIN
(SELECT
MAX(T048.T048_WAREH_CD) T048_WAREH_CD,
MAX(T048.T048_OWNER_CD) T048_OWNER_CD,
T048.T048_ARRIVAL_MGT_ID,
T048.T048_LINE_NO,
SUM(T049_UNITS) T049_UNITS
,T049.T049_STORE_TR_YMD AS T049_STORE_TR_YMD
FROM
T048_ARRIV_SCH T048
LEFT JOIN
T047_ARRIV_JS T047
ON
T048.T048_ARRIVAL_MGT_ID = T047.T047_ARRIVAL_MGT_ID
AND T048.T048_WAREH_CD = T047.T047_WAREH_CD
AND T048.T048_OWNER_CD = T047.T047_OWNER_CD
AND T048.T048_LINE_NO = T047.T047_LINE_NO
AND T047.T047_TRAN_KBN <> '3'
AND T047.T047_HISTORY_NO = '00'
AND T047.T047_CANCEL_FLG = '0'
LEFT JOIN
T049_STORE_JS T049
ON
T047.T047_ARRIVAL_ID = T049.T049_ARRIVAL_ID
AND T048.T048_WAREH_CD = T049.T049_WAREH_CD
AND T048.T048_OWNER_CD = T049.T049_OWNER_CD
AND T049.T049_TRAN_KBN <> '3'
WHERE
T048.T048_TRAN_KBN <> '3'
AND T048.T048_WAREH_CD = '01' /*Param*/
AND T048.T048_OWNER_CD = '005' /*Param*/
AND T049_STORE_TR_YMD = '20090713' /*Param*/
GROUP BY
T048.T048_ARRIVAL_MGT_ID,
T048.T048_LINE_NO,
T049.T049_STORE_TR_YMD) W1
ON
T048.T048_ARRIVAL_MGT_ID = W1.T048_ARRIVAL_MGT_ID
AND T048.T048_OWNER_CD = W1.T048_OWNER_CD
AND T048.T048_WAREH_CD = W1.T048_WAREH_CD
AND T048.T048_LINE_NO = W1.T048_LINE_NO
LEFT JOIN
M018_ITEM M018
ON
T048.T048_ITEM_CD = M018.M018_ITEM_CD
AND T048.T048_WAREH_CD = M018.M018_WAREH_CD
AND T048.T048_OWNER_CD = M018.M018_OWNER_CD
AND M018.M018_TRAN_KBN <> '3'
LEFT JOIN
M015_FORM_CONT M015
ON
T048.T048_FORM_CD = M015.M015_FORM_CD
AND M015.M015_TRAN_KBN <> '3'
LEFT JOIN
(SELECT MAX(M202_FIRST_NM) M202_FIRST_NM,MAX(M202_WAREH_CD) M202_WAREH_CD,MAX(M202_OWNER_CD) M202_OWNER_CD,M202_FIRST_CD FROM M202_MD WHERE M202_TRAN_KBN <> '3' GROUP BY M202_FIRST_CD) W3
ON
SUBSTR(T048.T048_ITEM_CD,0,1) = W3.M202_FIRST_CD
AND T048.T048_WAREH_CD = W3.M202_WAREH_CD
AND T048.T048_OWNER_CD = W3.M202_OWNER_CD
WHERE
T048.T048_TRAN_KBN <> '3'
AND T048.T048_WAREH_CD = '01' /*Param*/
AND T048.T048_OWNER_CD = '005' /*Param*/
AND W1.T049_STORE_TR_YMD = '20090713' /*Param*/ T048有2000条T049有11万T047只有几条 这样的检索要8分钟,不知道是为什么,麻烦大家帮我分析一下
(
T260_CREATE_YMD,
T260_UPDATE_YMD,
T260_TRAN_KBN,
T260_TANTO_CD,
T260_TERM_CD,
T260_WAREH_CD,
T260_OWNER_CD,
T260_WORK_ID,
T260_STATES,
T260_KBN,
T260_SENDCONFIRM_FLG,
T260_FIRST_KBN,
T260_SLIP_NO,
T260_ACCEPT_NO,
T260_ITEM_CD,
T260_ORDER_UNITS,
T260_UNITS,
T260_GOOD_UNITS,
T260_BAD_UNITS,
T260_MEGIN_CD,
T260_REASON_STR,
T260_UNITS_SJ,
T260_FIRST_NM,
T260_FORM_NM,
T260_COST_PRICE,
T260_ITEM_NM,
T260_CK_STATUS)
SELECT
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),
'1',
'91234567' /*Param*/,
NULL,
'01' /*Param*/,
'005' /*Param*/,
'20090715033347' /*Param*/,
CASE
WHEN T048.T048_ACCEPT_REPORT_FLG ='1' THEN '送信済'
WHEN T048.T048_SENDCONFIRM_FLG = '1' THEN '確定'
WHEN W1.T049_UNITS > 0 THEN '棚入済'
WHEN T048.T048_ARRIVAL_UNITS > 0 THEN '入荷検品済'
WHEN T048.T048_ARRIVAL_UNITS = 0 THEN '未検品'
END ,
CASE
WHEN T048.T048_ORDER_UNITS = T048.T048_UNITS AND T048.T048_ORDER_UNITS = T048.T048_ARRIVAL_UNITS AND T048.T048_ORDER_UNITS = NVL(W1.T049_UNITS, 0) THEN '0'
WHEN T048.T048_ORDER_UNITS <> T048.T048_UNITS OR T048.T048_ORDER_UNITS <> T048.T048_ARRIVAL_UNITS OR T048.T048_ORDER_UNITS <> NVL(W1.T049_UNITS, 0) THEN '1'
END ,
CASE
WHEN NVL(T048.T048_SENDCONFIRM_FLG,'0') = '0' THEN '未'
WHEN NVL(T048.T048_SENDCONFIRM_FLG,'0') = '1' THEN '済'
END ,
CASE
WHEN NVL(T048.T048_FIRST_KBN,'0') = '1' THEN '初回入荷'
WHEN NVL(T048.T048_FIRST_KBN,'0') = '2' THEN '複回入荷'
END ,
T048.T048_SLIP_NO,
T048.T048_ACCEPT_NO,
T048.T048_ITEM_CD,
T048.T048_ORDER_UNITS,
T048.T048_UNITS,
NVL(W2.GOODNUM, 0),
NVL(W2.BADNUM, 0),
T048.T048_MEGIN_CD,
M033.M033_REASON_STR,
NVL(W1.T049_UNITS, 0),
W3.M202_FIRST_NM,
M015.M015_FORM_NM,
T048.T048_COST_PRICE,
(M018_ITEM_NM1 || M018_ITEM_NM2) M018_ITEM_NM
,'1'
FROM
T048_ARRIV_SCH T048
LEFT JOIN
M033_REASON M033
ON
M033.M033_REASON_KBN = '2'
AND T048.T048_MEGIN_CD = M033.M033_REASON_CD
AND M033.M033_TRAN_KBN <> '3'
LEFT JOIN (SELECT
T047_ARRIVAL_MGT_ID,
T047_LINE_NO,
MAX(T047_WAREH_CD) T047_WAREH_CD,
MAX(T047_OWNER_CD) T047_OWNER_CD,
SUM(T047_GOOD_NUM) GOODNUM,
SUM(T047_BAD_NUM) BADNUM
,MAX(T047_ARRIVAL_SCH_YMD) T047_ARRIVAL_SCH_YMD
FROM
T047_ARRIV_JS T047
WHERE
T047.T047_TRAN_KBN <> '3'
AND T047.T047_HISTORY_NO = '00'
AND T047.T047_CANCEL_FLG = '0'
AND T047.T047_WAREH_CD = '01' /*Param*/
AND T047.T047_OWNER_CD = '005' /*Param*/
GROUP BY
T047_ARRIVAL_MGT_ID,
T047_LINE_NO) W2
ON
T048.T048_ARRIVAL_MGT_ID = W2.T047_ARRIVAL_MGT_ID
AND T048.T048_WAREH_CD = W2.T047_WAREH_CD
AND T048.T048_OWNER_CD = W2.T047_OWNER_CD
AND T048.T048_LINE_NO = W2.T047_LINE_NO
LEFT JOIN
(SELECT
MAX(T048.T048_WAREH_CD) T048_WAREH_CD,
MAX(T048.T048_OWNER_CD) T048_OWNER_CD,
T048.T048_ARRIVAL_MGT_ID,
T048.T048_LINE_NO,
SUM(T049_UNITS) T049_UNITS
,T049.T049_STORE_TR_YMD AS T049_STORE_TR_YMD
FROM
T048_ARRIV_SCH T048
LEFT JOIN
T047_ARRIV_JS T047
ON
T048.T048_ARRIVAL_MGT_ID = T047.T047_ARRIVAL_MGT_ID
AND T048.T048_WAREH_CD = T047.T047_WAREH_CD
AND T048.T048_OWNER_CD = T047.T047_OWNER_CD
AND T048.T048_LINE_NO = T047.T047_LINE_NO
AND T047.T047_TRAN_KBN <> '3'
AND T047.T047_HISTORY_NO = '00'
AND T047.T047_CANCEL_FLG = '0'
LEFT JOIN
T049_STORE_JS T049
ON
T047.T047_ARRIVAL_ID = T049.T049_ARRIVAL_ID
AND T048.T048_WAREH_CD = T049.T049_WAREH_CD
AND T048.T048_OWNER_CD = T049.T049_OWNER_CD
AND T049.T049_TRAN_KBN <> '3'
WHERE
T048.T048_TRAN_KBN <> '3'
AND T048.T048_WAREH_CD = '01' /*Param*/
AND T048.T048_OWNER_CD = '005' /*Param*/
AND T049_STORE_TR_YMD = '20090713' /*Param*/
GROUP BY
T048.T048_ARRIVAL_MGT_ID,
T048.T048_LINE_NO,
T049.T049_STORE_TR_YMD) W1
ON
T048.T048_ARRIVAL_MGT_ID = W1.T048_ARRIVAL_MGT_ID
AND T048.T048_OWNER_CD = W1.T048_OWNER_CD
AND T048.T048_WAREH_CD = W1.T048_WAREH_CD
AND T048.T048_LINE_NO = W1.T048_LINE_NO
LEFT JOIN
M018_ITEM M018
ON
T048.T048_ITEM_CD = M018.M018_ITEM_CD
AND T048.T048_WAREH_CD = M018.M018_WAREH_CD
AND T048.T048_OWNER_CD = M018.M018_OWNER_CD
AND M018.M018_TRAN_KBN <> '3'
LEFT JOIN
M015_FORM_CONT M015
ON
T048.T048_FORM_CD = M015.M015_FORM_CD
AND M015.M015_TRAN_KBN <> '3'
LEFT JOIN
(SELECT MAX(M202_FIRST_NM) M202_FIRST_NM,MAX(M202_WAREH_CD) M202_WAREH_CD,MAX(M202_OWNER_CD) M202_OWNER_CD,M202_FIRST_CD FROM M202_MD WHERE M202_TRAN_KBN <> '3' GROUP BY M202_FIRST_CD) W3
ON
SUBSTR(T048.T048_ITEM_CD,0,1) = W3.M202_FIRST_CD
AND T048.T048_WAREH_CD = W3.M202_WAREH_CD
AND T048.T048_OWNER_CD = W3.M202_OWNER_CD
WHERE
T048.T048_TRAN_KBN <> '3'
AND T048.T048_WAREH_CD = '01' /*Param*/
AND T048.T048_OWNER_CD = '005' /*Param*/
AND W1.T049_STORE_TR_YMD = '20090713' /*Param*/ T048有2000条T049有11万T047只有几条 这样的检索要8分钟,不知道是为什么,麻烦大家帮我分析一下
好像里面的表不 至 T047 T048 T049如果 T049 11万行,且是最大的表。就在其它表 T049 和其它表的 连接列上建索引。 在T049 上建索引 ,要建 联合索引. create index idx_x on table(f1,f2,f3)这样的。insert 逻辑太复杂了 能不能 优化一点。
看不懂。 最好能分解成 不同的 insert 分批进行。
其他的M开头的表都是Master就是一些基础信息表,不是业务表了
如果有多个列 要联合 索引create index idx_x on 大表(f1,f2,f3)
试试吧。
这个SQL的处理其实就是从多个表抽数据,插入到一个表中的处理,我认为好像不能多次插
结合google 查里面的 index scan,needloop ,hash join 慢慢就能看懂了。 也就会优化了。
仅仅是 大表里面的 字段对么
这个 一般 数据库自已选择的。
这个就叫执行计划.可人为在 sql 里写入 oracle hint 来修改 执行计划.google oracle hint 就能找到相应的内容。
支持下,很痛苦的
横向尽量少写几张表,可以加快查询速度
还有,就是逐段的排查
发表下自己的看法, 不一定正确。 M033.M033_REASON_KBN = '2'
AND T048.T048_MEGIN_CD = M033.M033_REASON_CD
AND M033.M033_TRAN_KBN <> '3'
1.可以把关联条件写在前面,filter条件 写在后面, filter效果越好的越靠后
2.尽量不用<> 不容易用到索引
3. 不知道有在关联条件上建立索引没
4.SUBSTR(T048.T048_ITEM_CD,0,1) = W3.M202_FIRST_CD
这个不要在字段上用索引, 或者你需要建立函数索引
1、避免对索引字段使用计算公式,否则索引会失效
2、少用left join,是否可以考虑用inner join代替
3、对相关表进行分析(analyze table)
4、考虑建适当索引
看你的sql。
有两点:
1、insert /*+append*/ 提示下加快插入!
2、在select /*+rule*/ 你这么长的sql,Oracle解析就需要花费长时间!(不敢保证有效)