Simple select query on VBRP

[flowers_candys]

Hi Gurus

I have following query:

IF NOT i_item[] IS INITIAL.

SELECT

vbeln

posnr

aubel

aupos

FROM vbrp

INTO TABLE it_vbrp

FOR ALL ENTRIES IN i_item

WHERE aubel = i_item-vbeln AND

aupos = i_item-posnr.

ENDIF.

When I do SQL trace using ST05, it shows followig statistics:

SELECT STATEMENT ( Estimated Costs = 89 , Estimated #Rows = 1 )

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID VBRP

( Estim. Costs = 88 , Estim. #Rows = 1 )

Estim. CPU-Costs = 1,391,775 Estim. IO-Costs = 88

Filter Predicates

1 INDEX RANGE SCAN VBRP~0

( Estim. Costs = 1 , Estim. #Rows = 4,149 )

Search Columns: 1

Estim. CPU-Costs = 180,203 Estim. IO-Costs = 1

Access Predicates

I was wondering inspite of using primary index of VBRP, why is it giving me such a high estimated cost of 89???

Please help!!!

Thanks

[ThomasZloch]

It is accessing by primary key, but you are not submitting any values for it! Rather use table VBFA to find subsequent documents in sales flow, this is indexed the other way round compared to VBRP.

Thomas

[flowers_candys]

Hi Thomas,

What do you mean by you are not submitting any values for it? Could you please ellaborate on that?

Also, one strange observation:

If I use following statement, then Estimated costs comes to be 63

SELECT vbeln posnr vgbel vgpos

FROM lips

INTO TABLE it_lips

FOR ALL ENTRIES IN i_item

WHERE vgbel = i_item-vbeln AND

vgpos = i_item-posnr

%_HINTS ORACLE 'INDEX("LIPS" "LIPS~0")'.

However, if I use following code, then Estimated costs comes to be 33

SELECT vbeln posnr vgbel vgpos

FROM lips

INTO TABLE it_lips.

SORT i_item BY vbeln posnr.

LOOP AT it_lips INTO wa_lips.

READ TABLE i_item INTO wa_item

WITH KEY

vbeln = wa_lips-vgbel

posnr = wa_lips-vgpos

BINARY SEARCH.

IF sy-subrc IS NOT INITIAL.

DELETE it_lips.

ENDIF.

ENDLOOP.

It is really difficult to understand why that way, as I am not even using any where clause.

Please help!

Thanks

Edited by: flowers candys on Feb 9, 2009 7:12 PM

Edited by: flowers candys on Feb 9, 2009 7:33 PM

[Former Member]

The lesson you can take from this is that you have to be very careful when using database hints. In most cases, you should avoid them.

Rob

[ThomasZloch]

Back to your initial example:

SELECT
vbeln
posnr
aubel
aupos
FROM vbrp
INTO TABLE it_vbrp
FOR ALL ENTRIES IN i_item
WHERE aubel = i_item-vbeln AND
aupos = i_item-posnr.

What I meant was, you are selecting from table VBRP with fields AUBEL and AUPOS. These are not part of any primary or secondary index. The trace shows that the primary index is used, just because there is no better alternative!

Don't get lost in these estimated costs. Why don't you hear my recommendation and try a different approach, namely using table VBFA, because this one is sorted by AUBEL and AUPOS (referring to the logical content, the field names are different).

Almost never use hints, as Rob said.

Thomas

[flowers_candys]

Thanks Thomas...

I have now used VBFA in my select query...

and thanks Rob, once again!!!

Appreciate it!

[P561888]

Hi ,

Use the temp table and delete the duplicate entries.

i_item_temp[] = i_items.

sort i_item_temp by vbeln posnr.

DELETE ADJACENT DUPLICATES FROM i_item_temp by comparing vbeln posnr.

IF NOT i_item_temp[] IS INITIAL.

SELECT

vbeln

posnr

aubel

aupos

FROM vbrp

INTO TABLE it_vbrp

FOR ALL ENTRIES IN i_item_temp

WHERE aubel = i_item_temp-vbeln AND

aupos = i_item_temp-posnr.

ENDIF.

Check this one.

Regards,

Bharani