Skip to Content
0
Former Member
Jul 18, 2008 at 02:55 PM

Deciding Where clause field in JOIN statement

574 Views

Hi all,

I have a doubt of using fields in WHERE clause during JOIN statement. If we join two tables and fetched data based on

a field which is present in both tables, then which table's field should be used in WHERE clause? I thought it will take same time

irrespective of table but it behaves very different. For testing this, i used two tables VBRK and S136. The Select Statements are

as follows

This one is taking less time

SELECT vbrk~vbeln
       vbrk~fkart
       vbrk~vbtyp
       s136~spmon
  FROM vbrk
  JOIN s136
    ON vbrk~knumv = s136~knumv
  INTO TABLE gt_vbrk
 WHERE s136~knumv IN s_knumv.

while the following is taking much more time,


SELECT vbrk~vbeln
       vbrk~fkart
       vbrk~vbtyp
       s136~spmon
  FROM vbrk
  JOIN s136
    ON vbrk~knumv = s136~knumv
  INTO TABLE gt_vbrk
 WHERE vbrk~knumv IN s_knumv.

When i did a performance trace there is no difference in index usage!

both of the traces showing the following result.

SELECT STATEMENT ( Estimated Costs = 454 , Estimated #Rows = 9 )

3 HASH JOIN

( Estim. Costs = 454 , Estim. #Rows = 9 )

1 INDEX FAST FULL SCAN S136~0

( Estim. Costs = 2 , Estim. #Rows = 9 )

2 TABLE ACCESS FULL VBRK

( Estim. Costs = 451 , Estim. #Rows = 2.503 )

How to decide which one to use? and why it is behaving different? is it related to size of the db table?