SQL Statement
SELECT
"VBELN", "ERDAT", "ERZET", "AUART", "VKORG", "VTWEG", "VKBUR", "KNUMV",
"VDATU", "BSTNK", "BSARK", "KUNNR"
FROM
"VBAK"
WHERE
"MANDT" = ? AND ( "ERDAT" = ? AND "ERZET" > ? OR "ERDAT" > ? ) AND "AUART"
IN ( ?, ? ) AND "VKORG" IN ( ?, ?, ?, ?, ?, ?, ?, ? ) AND "VTWEG" IN ( ?,
?, ?, ?, ?, ?, ? ) AND "BSARK" = ? WITH UR
Access Plan Opt Level = 5 ; Parallelism = None
0 SELECT STATEMENT ( Estimated Costs = 3,258E+07 [timerons] )
1 RETURN
2 NLJOIN
3 [O] TBSCAN
4 SORT
5 TBSCAN GENROW
6 <i> FETCH VBAK
7 IXSCAN VBAK~ZL1 #key columns: 2
Source Code : /AMS/OUSCSR_ORDER_EXCEP_FORM
SELECT vbeln "Sales document no
*-Begin of Mod-013
>>
erdat
erzet
*-End of Mod-013
<<
auart "Sales document type
vkorg "sales org
vtweg "dist chnl
vkbur "Sales office
knumv "No of document condition
vdatu "Requested delivery date
bstnk "Purchase order no
bsark "Purchase order type
kunnr "Sold to number
INTO TABLE fp_i_vbak
FROM vbak
WHERE vbeln IN s_vbeln
AND erdat GE v_start_date
AND auart IN s_ordty
AND vkorg IN s_salorg
AND vtweg IN s_discha
AND vkbur IN s_saloff
AND bsark IN s_purty
AND kunnr IN s_soldto .
*-Begin Of Mod-013
>>
IF sy-subrc IS INITIAL.
AND erzet > v_start_time )
OR erdat > v_start_date )
DELETE fp_i_vbak WHERE erdat LT v_start_date .
DELETE fp_i_vbak WHERE erdat EQ v_start_date
AND erzet LT v_start_time.
The index used is :
6 <i> FETCH VBAK
7 IXSCAN VBAK~ZL1 #key columns: 2
NONUNIQUE Index VBAK~ZL1
Column Name # Distinct
MANDT 1
VKORG 154
ERDAT 2.312
BSTNK 8.529.428
KUNNR 818.388
Please optimize the SQL. Only 2 key columns used .. MANDT , VKORG. This is not optimal. The code
AND ( ( erdat = v_start_date AND erzet > v_start_time )
OR erdat > v_start_date )
should be reviewed. This is confusing the DB2 optimizer.
-
Hi All please look this issue any one suggest me where is problem with select querry and how improve performance of vbak select querry.