Skip to Content
0
Former Member
Feb 18, 2011 at 10:04 AM

Performance issue with table VBAK.

273 Views

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.