Selection on Indexed fields with Not Equal conditions

424 Views

Hallo everybody!

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:

SELECT /bic/zcltt

/bic/zcbpa

/bic/zcacc

/bic/zczdn

/bic/zkiut

FROM /bic/azosu00

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

%_HINTS

ORACLE 'USE_CONCAT'

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.

Regards

Alfonso