I need to first select <b>100,000</b> records from table A and then base on this records goto select <b>100,000</b> records from table B. I did tried on below:
SELECT field1 field2 field3 INTO TABLE itab1 FROM A PACKAGE SIZE 1000 WHERE field1 = p_test. IF NOT itab1 IS INITIAL. SELECT fielda fieldb fieldc INTO TABLE itab2 FROM B FOR ALL ENTRIES IN itab1 WHERE fielda = itab1-field1 AND fieldb = itab1-field2. ENDIF. ENDSELECT.
SELECT afield1 afield2 afield3 bfielda bfieldb bfieldc
INTO TABLE itab
FROM A as a INNER JOIN B as b
ON afield1 = bfielda AND
afield2 = bfieldb
WHERE a~field1 = p_test.
For method 1, as we all known that FOR ALL ENTRIES will contruct an OR selection and it will dramatically reduce the performance when process large data set(100,000 rec as case above).
While for method 2, i just try to compare the performance on the same set of data with merely INNER JOIN statement. The result is Method 1 and Method 2 is producing the similar processing time.
Im in problem now, because method 2 will procude the bad result if the processing data is huge(just like case above). Since both Method 1 and 2 producing the similar result, hence im giving the assumption where Method 1 is bad as well.
So, im here seeking your opinion on how should i fine tune the requirement above.
Thanks in advance.