sql语句执行问题
东邪
1,347次浏览
编辑于2015年01月26日 10:43:51
下面语句在手工执行时属于毫秒级，是0行插入。但是em里面执行了30分钟没有结束，而且看实例锁，发现涉及到的表全部加锁，造成其他业务无法正常进行。 /* Formatted on 2015/1/26 8:16:18 (QP5 v5.227.12220.39754) */
INSERT INTO TM_AP_MATCHSOURCETARGET (FTFromId,
FTID,
FSFromId,
FSID,
FAMOUNT)
SELECT 'IV_PURCHASEIC' AS FTFromId,
TO_NUMBER (
SUBSTR (
SUBSTR (SYS_CONNECT_BY_PATH (t0.FTID, '/'), 2),
0,
INSTR (
SUBSTR (SYS_CONNECT_BY_PATH (t0.FTID, '/') || '/', 2),
'/')
- 1))
AS FTID,
stabFrom.FFORMID AS FSFromId,
t0.FSID,
amount.FAMOUNT
FROM T_BF_INSTANCEENTRY t0
INNER JOIN T_BF_INSTANCEAMOUNT amount
ON t0.FROUTEID = amount.FROUTEID
INNER JOIN T_BF_TABLEDEFINE ttabFrom
ON t0.FTTABLENAME = ttabFrom.FTABLENUMBER
INNER JOIN T_BF_TABLEDEFINE stabFrom
ON t0.FSTABLENAME = stabFrom.FTABLENUMBER
WHERE stabFrom.FFORMID IN ('AP_Payable')
CONNECT BY PRIOR stabFrom.FFORMID = ttabFrom.FFORMID
AND PRIOR t0.FSID = t0.FTID
START WITH ttabFrom.FFORMID = 'IV_PURCHASEIC'
AND t0.FTID IN
(SELECT entrytemp.FEntryID
FROM T_IV_PURCHASEICENTRY entrytemp
INNER JOIN TM_AP_MATCHFORMIDID temp
ON entrytemp.FID = temp.FID)
INSERT INTO TM_AP_MATCHSOURCETARGET (FTFromId,
FTID,
FSFromId,
FSID,
FAMOUNT)
SELECT 'IV_PURCHASEIC' AS FTFromId,
TO_NUMBER (
SUBSTR (
SUBSTR (SYS_CONNECT_BY_PATH (t0.FTID, '/'), 2),
0,
INSTR (
SUBSTR (SYS_CONNECT_BY_PATH (t0.FTID, '/') || '/', 2),
'/')
- 1))
AS FTID,
stabFrom.FFORMID AS FSFromId,
t0.FSID,
amount.FAMOUNT
FROM T_BF_INSTANCEENTRY t0
INNER JOIN T_BF_INSTANCEAMOUNT amount
ON t0.FROUTEID = amount.FROUTEID
INNER JOIN T_BF_TABLEDEFINE ttabFrom
ON t0.FTTABLENAME = ttabFrom.FTABLENUMBER
INNER JOIN T_BF_TABLEDEFINE stabFrom
ON t0.FSTABLENAME = stabFrom.FTABLENUMBER
WHERE stabFrom.FFORMID IN ('AP_Payable')
CONNECT BY PRIOR stabFrom.FFORMID = ttabFrom.FFORMID
AND PRIOR t0.FSID = t0.FTID
START WITH ttabFrom.FFORMID = 'IV_PURCHASEIC'
AND t0.FTID IN
(SELECT entrytemp.FEntryID
FROM T_IV_PURCHASEICENTRY entrytemp
INNER JOIN TM_AP_MATCHFORMIDID temp
ON entrytemp.FID = temp.FID)
推荐阅读