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?