主要是我认为没问题的地方都会慢的要死比如这段 SELECT A.VBELN,B.POSNR,A.VKORG,A.KUNNR,A.NETWR, A.KUNNR AS KUNAG,A.ERDAT, SUBSTR(A.ERDAT,1,4) JLFGJA, SUBSTR(A.ERDAT,5,2) JLFMON, B.MATNR,B.ARKTX,B.WERKS,B.VRKME, (CASE WHEN B.SHKZG='X' THEN -1*B.ZMENG ELSE B.ZMENG END) LFIMG, B.VBELN AS CK_VGBEL,B.POSNR AS CK_VGPOS,B.PSTYV,'' AS BWART, B.SHKZG,B.KNTTP, (CASE WHEN B.KNTTP='M' THEN B.VBELN ELSE '' END) MAT_KDAUF, (CASE WHEN B.KNTTP='M' THEN B.POSNR ELSE '' END) MAT_KDPOS FROM SAPSR3.VBAK A --522条数据 INNER JOIN SAPSR3.VBAP B ON A.MANDT = B.MANDT AND A.VBELN = B.VBELN WHERE B.UEPOS = '000000' AND A.AUART IN ('ZDR','ZRD','ZCR','ZDR1') A表522数据,B表100多万 就这语句取前1万条要70几s
SELECT A.VBELN,B.POSNR,A.VKORG,A.KUNNR,A.NETWR, A.KUNNR AS KUNAG,A.ERDAT, SUBSTR(A.ERDAT,1,4) JLFGJA, SUBSTR(A.ERDAT,5,2) JLFMON, B.MATNR,B.ARKTX,B.WERKS,B.VRKME, (CASE WHEN B.SHKZG='X' THEN -1*B.ZMENG ELSE B.ZMENG END) LFIMG, B.VBELN AS CK_VGBEL,B.POSNR AS CK_VGPOS,B.PSTYV,'' AS BWART, B.SHKZG,B.KNTTP, (CASE WHEN B.KNTTP='M' THEN B.VBELN ELSE '' END) MAT_KDAUF, (CASE WHEN B.KNTTP='M' THEN B.POSNR ELSE '' END) MAT_KDPOS FROM SAPSR3.VBAP B,SAPSR3.VBAK A --522条数据 WHERE B.MANDT = A.MANDT AND B.VBELN = A.VBELN AND B.UEPOS = '000000' AND A.AUART IN ('ZDR','ZRD','ZCR','ZDR1') 换成这段代码试试,看看有没有快点。数据量较小的表一般都是做为驱动表的,一般放在右侧。
SELECT A.VBELN,B.POSNR,A.VKORG,A.KUNNR,A.NETWR,
A.KUNNR AS KUNAG,A.ERDAT,
SUBSTR(A.ERDAT,1,4) JLFGJA,
SUBSTR(A.ERDAT,5,2) JLFMON,
B.MATNR,B.ARKTX,B.WERKS,B.VRKME,
(CASE WHEN B.SHKZG='X' THEN -1*B.ZMENG ELSE B.ZMENG END) LFIMG,
B.VBELN AS CK_VGBEL,B.POSNR AS CK_VGPOS,B.PSTYV,'' AS BWART,
B.SHKZG,B.KNTTP,
(CASE WHEN B.KNTTP='M' THEN B.VBELN ELSE '' END) MAT_KDAUF,
(CASE WHEN B.KNTTP='M' THEN B.POSNR ELSE '' END) MAT_KDPOS
FROM SAPSR3.VBAK A --522条数据
INNER JOIN SAPSR3.VBAP B ON A.MANDT = B.MANDT AND A.VBELN = B.VBELN
WHERE B.UEPOS = '000000'
AND A.AUART IN ('ZDR','ZRD','ZCR','ZDR1')
A表522数据,B表100多万
就这语句取前1万条要70几s
uepos的选择性如何?
对b表,在这三个字段中根据具体情况,在选择性较好的字段上建索引,或是组合索引
A.KUNNR AS KUNAG,A.ERDAT,
SUBSTR(A.ERDAT,1,4) JLFGJA,
SUBSTR(A.ERDAT,5,2) JLFMON,
B.MATNR,B.ARKTX,B.WERKS,B.VRKME,
(CASE WHEN B.SHKZG='X' THEN -1*B.ZMENG ELSE B.ZMENG END) LFIMG,
B.VBELN AS CK_VGBEL,B.POSNR AS CK_VGPOS,B.PSTYV,'' AS BWART,
B.SHKZG,B.KNTTP,
(CASE WHEN B.KNTTP='M' THEN B.VBELN ELSE '' END) MAT_KDAUF,
(CASE WHEN B.KNTTP='M' THEN B.POSNR ELSE '' END) MAT_KDPOS
FROM SAPSR3.VBAP B,SAPSR3.VBAK A --522条数据
WHERE B.MANDT = A.MANDT
AND B.VBELN = A.VBELN
AND B.UEPOS = '000000'
AND A.AUART IN ('ZDR','ZRD','ZCR','ZDR1')
换成这段代码试试,看看有没有快点。数据量较小的表一般都是做为驱动表的,一般放在右侧。
oracle 8以前才有这样的规则...