Issues FOR ALL ENTRIES

Dear all,

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:

<b>Method 1</b>:

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.

<b>Method 2</b>

SELECT a_{field1 a}field2 a_{field3 b}fielda b_{fieldb b}fieldc

INTO TABLE itab

FROM A as a INNER JOIN B as b

ON a_{field1 = b}fielda AND

a_{field2 = b}fieldb

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.