I have a very simple program with 2 nested SELECTs, but with huge amount of data, and as the worst thing, on a cluster table BSEG with non-key access. I need to get the first set of data based on a certain criteria, and then the second set based on the first set. I can't use index tables such as BSID etc. Here is my program:
SELECT bukrs belnr gjahr FROM bseg INTO ls_bseg1 WHERE bukrs = '0100' AND projk NE space AND ( koart = 'S' OR koart = 'A' ). SELECT bukrs belnr gjahr buzei rebzg rebzz dmbtr FROM bseg INTO ls_bseg2 WHERE bukrs = ls_bseg1-bukrs AND belnr = ls_bseg1-belnr AND gjahr = ls_bseg1-gjahr AND koart = 'K' AND augdt > p_augdt. PERFORM write_line USING ls_bseg2. ENDSELECT. ENDSELECT.
Could anyone offer an advice on better performance? I will also try with internal tables, but I expect a short dump because of memory occupancy. I also have in mind using file on app server.