I'm working with SAP NW04 with Oracle DB.
I created an ODS that contains some million records.
Now i need to select some data from this ODS by an external report.
In this report, I have to select this data filtering some key fields and selecting some non-key fields.
According to this, I created an index in the ODS respecting the selection criteria.
In a nutshell, this is my situation:
INTO TABLE l_t_inc_esr
FOR ALL ENTRIES IN i_act
WHERE /bic/zcltt <> space " KEY - Indexed field
AND /bic/zcnpl <> space " KEY - Indexed field
AND /bic/zcsut IN l_r_zsut " KEY - Indexed field
AND /bic/zczdn >= i_act-z_dat_rp " Indexed field
AND /bic/zczdn <= i_act-z_dat_ch " Indexed field
AND /bic/zclpa <> 'H' " Indexed field
AND /bic/zcbpa = i_act-/bic/zcbpa " Indexed field
ORACLE 'INDEX("/BIC/AZOSU00" "/BIC/AZOSU0001")'.
But the selection takes so long time.
Even if I forced the selection to the index /BIC/AZOSU0001 (it contains the 7 fields in selection as well, 3 of them are part of the primary key in the ODS), the system attempts to access with this index, but then it goes in full table scan.
Is it possible that, generally, an index doesn't work with the "NOT EQUAL" conditions?
Should i extract all the data without the "<>" condition, then filter it into the internal table?
Why do the optimizer can't access to the index range correctly?
Thank you in advance.