Selection on Indexed fields with Not Equal conditions

[Former Member]

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

[stefan_koehler · Accepted solution]

Hello Alfonso,

>> Why do the optimizer can't access to the index range correctly?

1) Does the table columns have histograms?

2) What is the execution plan?

3) Is the FOR ALL ENTRIES clause executed as an IN-List or as an OR concatenation?

4) Are the indexes bitmap indexes?

5) What are the access and what are the filter predicates?

There are a lot of information missing to help you.

Regards

Stefan

[Former Member · Accepted solution]

Hallo Stefan,

Thanks for your reply.

I try to answer to your questions now:

1) the table does not contain any histogram, it's a simple ODS with all the invoice positions

2) my plan is extract the invoice positions filtered for the customers /bic/zcbpa and the registration date /bic/zczdn included in the interval described from ztlci_dat_rp and ztlci_dat_ch in the invoice header ODS (extracted before), including only some document types in a range.

3) as you can see from the code, the FOR ALL ENTRIES must be executed in full AND conditions, and there are some data (i checked) that respect this conditions.

2) as i explained in the point 2, i must filter the data in the ODS (active data) from the Customer and registration date from the header invoice internal table, and the document types from a range l_r_zsut declared "...TYPE RANGE OF /BIC/OIZSUT".

I hope you'll help me...

Thanks

Alfonso

[stefan_koehler · Accepted solution]

Hello Alfonso,

i think you missunderstand me.

1) Ok .. no histograms

2) I mean the execution if you run the statement in ST05 or with "set autotrace on" in sqlplus

3) No, i mean what is the exact statement that is executed on the database... the database interface can convert the FOR ALL ENTRIES into IN-LISTs or into OR-concatenation.

4) I mean the filter and access predicates of the explain plan of oracle

All my questions were regarding to the database itself, not to any logical things.

Regards

Stefan

[Former Member · Accepted solution]

Hallo Stefan,

Sorry for the missunderstanding...I'm in a hurry...

I mean:

2) I run the execution with the Trace ON in transaction ST05 opened in another session.

3) the FOR ALL ENTRIES statement became as follows:

SELECT

/*+

USE_CONCAT INDEX("/BIC/AZOSU00" "/BIC/AZOSU0001")

*/

"/BIC/ZCLTT" , "/BIC/ZCBPA" , "/BIC/ZCACN" , "/BIC/ZCZDN" ,

"/BIC/ZCPRV" , "/BIC/ZNFAT" , "/BIC/ZCSUT" , "/BIC/ZKIUT"

FROM

"/BIC/AZOSU00"

WHERE

"/BIC/ZCLTT" <> :A0 AND "/BIC/ZCNPL" <> :A1 AND "/BIC/ZCSUT" IN (

:A2 , :A3 , :A4 , :A5 , :A6 , :A7 , :A8 , :A9 ) AND "/BIC/ZCZDN" >= :A10

AND "/BIC/ZCZDN" <= :A11 AND "/BIC/ZCLPA" <> :A12 AND

"/BIC/ZCBPA" = :A13

4) the explaination of the DB access:

- INDEX FULL SCAN /BIC/AZOSU0001

( Estim. Costs = 26 , Estim. #Rows = 1 )

Then:

- TABLE ACCESS FULL /BIC/AZOSU00

I hope that this explaination can help you for resolve my problem.

Thanks

Alfonso

[stefan_koehler · Accepted solution]

Hello Alfonso,

thanks for the statement on database.. but the explain plan looks a little bit strange to me, because of the IN-INLISTs and the execution plan in combination with the USE_CONCAT HINT.

We need to know the exact execution plan.. please copy the statement into ST05 and make an explain plan ... and post it in the "[code]" tags here.

The easiest and fastest way to help you is an "autotrace on" and a CBO Trace.

I am right that the index is a bitmap index?

Regards

Stefan

[Former Member · Accepted solution]

Hallo Stefan,

Thanks for your reply.

Something is changed from the last day, I reactivated the ODS in the Test System with the Program RSDG_ODSO_ACTIVATE and now the selection uses the index, but the extraction is really slow.

Here the complete SQL Statement and Execution plan:

SQL Statement

SELECT

/*+

USE_CONCAT INDEX("/BIC/AZOSU00" "/BIC/AZOSU0001")

*/

"/BIC/ZCLTT" , "/BIC/ZCBPA" , "CACONT_ACC" , "/BIC/ZCZDN" , "/BIC/ZCPRV" ,

"/BIC/ZNFAT" , "/BIC/ZCSUT" , "/BIC/ZKIUT"

FROM

"/BIC/AZOSU00"

WHERE

"/BIC/ZCLTT" <> :A0 AND "/BIC/ZCNPL" <> :A1 AND "/BIC/ZCSUT" IN ( :A2 , :A3 , :A4 ,

:A5 , :A6 , :A7 , :A8 , :A9 ) AND "/BIC/ZCZDN" >= :A10 AND "/BIC/ZCZDN" <= :A11 AND

"/BIC/ZCLPA" <> :A12 AND "/BIC/ZCBPA" = :A13 OR "/BIC/ZCLTT" <> :A14 AND

"/BIC/ZCNPL" <> :A15 AND "/BIC/ZCSUT" IN ( :A16 , :A17 , :A18 , :A19 , :A20 , :A21 , :A22

, :A23 ) AND "/BIC/ZCZDN" >= :A24 AND "/BIC/ZCZDN" <= :A25 AND "/BIC/ZCLPA" <> :A26

AND "/BIC/ZCBPA" = :A27 OR "/BIC/ZCLTT" <> :A28 AND "/BIC/ZCNPL" <> :A29 AND

"/BIC/ZCSUT" IN ( :A30 , :A31 , :A32 , :A33 , :A34 , :A35 , :A36 , :A37 ) AND

"/BIC/ZCZDN" >= :A38 AND "/BIC/ZCZDN" <= :A39 AND "/BIC/ZCLPA" <> :A40 AND

"/BIC/ZCBPA" = :A41 OR "/BIC/ZCLTT" <> :A42 AND "/BIC/ZCNPL" <> :A43 AND

"/BIC/ZCSUT" IN ( :A44 , :A45 , :A46 , :A47 , :A48 , :A49 , :A50 , :A51 ) AND

"/BIC/ZCZDN" >= :A52 AND "/BIC/ZCZDN" <= :A53 AND "/BIC/ZCLPA" <> :A54 AND

"/BIC/ZCBPA" = :A55 OR "/BIC/ZCLTT" <> :A56 AND "/BIC/ZCNPL" <> :A57 AND

"/BIC/ZCSUT" IN ( :A58 , :A59 , :A60 , :A61 , :A62 , :A63 , :A64 , :A65 ) AND

"/BIC/ZCZDN" >= :A66 AND "/BIC/ZCZDN" <= :A67 AND "/BIC/ZCLPA" <> :A68 AND

"/BIC/ZCBPA" = :A69

Execution Plan

SELECT STATEMENT ( Estimated Costs = 9.699 , Estimated #Rows = 1.759 )

5 11 CONCATENATION

5 2 TABLE ACCESS BY INDEX ROWID /BIC/AZOSU00

( Estim. Costs = 1.939 , Estim. #Rows = 351 )

1 INDEX FULL SCAN /BIC/AZOSU0001

( Estim. Costs = 1.559 , Estim. #Rows = 351 )

Search Columns: 2

5 4 TABLE ACCESS BY INDEX ROWID /BIC/AZOSU00

( Estim. Costs = 1.939 , Estim. #Rows = 351 )

3 INDEX FULL SCAN /BIC/AZOSU0001

( Estim. Costs = 1.559 , Estim. #Rows = 351 )

Search Columns: 2

5 6 TABLE ACCESS BY INDEX ROWID /BIC/AZOSU00

( Estim. Costs = 1.939 , Estim. #Rows = 351 )

5 INDEX FULL SCAN /BIC/AZOSU0001

( Estim. Costs = 1.559 , Estim. #Rows = 351 )

Search Columns: 2

5 8 TABLE ACCESS BY INDEX ROWID /BIC/AZOSU00

( Estim. Costs = 1.939 , Estim. #Rows = 351 )

7 INDEX FULL SCAN /BIC/AZOSU0001

( Estim. Costs = 1.559 , Estim. #Rows = 351 )

Search Columns: 2

5 10 TABLE ACCESS BY INDEX ROWID /BIC/AZOSU00

( Estim. Costs = 1.939 , Estim. #Rows = 351 )

9 INDEX FULL SCAN /BIC/AZOSU0001

( Estim. Costs = 1.559 , Estim. #Rows = 351 )

Search Columns: 2

What's wrong in this selection?

I hope you'll resolve my problem.

Thanks in advance.

Regards

Alfonso

[Former Member]

Problem solved deleting the NOT EQUAL conditions from the SELECT statement and adapting the index to the EQUAL/IN <range> conditions.

[Former Member]

Hi Alfonso,

please try the following query instead of yours:

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 OR /bic/zcltt > space )
   AND ( /bic/zcnpl < space OR /bic/zcnpl > space)
   AND /bic/zcsut IN l_r_zsut
   AND /bic/zczdn >= i_act-z_dat_rp
   AND /bic/zczdn <= i_act-z_dat_ch
   AND ( /bic/zclpa < 'H' OR /bic/zclpa > 'H' )
   AND /bic/zcbpa = i_act-/bic/zcbpa

Normally optimizers are much more friendly if you substitute <> by < OR >.

Regards

Ralph

[Former Member]

Hallo Ralph,

Thanks for your reply.

I tried to choose the "OR" condition instead of "<>", but the performance didn't change.

I solved my problem extracting all fields without the NOT EQUAL conditions, adapting the index to the EQUAL conditions only.

Then I filtered the results with a LOOP...WHERE statement.

Performance seems to be OK now.

Thanks everybody.

Regards

Alfonso