Correct table Index not being picked while execution

[Former Member]

Hi,

For a particular custom table there are 5 indexes.

Piece of code.

SELECT rbukrs rzzrjvnam rzzdevcode rzzaltkt racct rcntr

rzzrecid rzzlocation rzzproject rzzafeno rzzebeln

rzzoutline rzzagrtyp rzzblart hsl ryear poper docnr docln

refdocnr sgtxt rrcty "Add ARM 02Mar07 / +v106

FROM zsiopa

INTO TABLE int_zsiopa1

WHERE rrcty NE '2' AND "exclude record type 2

rldnr EQ 'SL' AND

ryear = p_year AND

poper IN rng_months AND

rbukrs EQ p_bukrs AND

racct IN s_accnt AND

rzzrecid in s_reccin AND

rzzrjvnam IN s_vent AND

rzzaltkt IS NOT NULL

%_HINTS ORACLE 'index("zsiopa" "zsiopa~5")'.

As per the where condition 5th index in the table was created.

But when checked the trace report from ST05,it uses 4th index on execution .

Can someone suggest what can be done?

Regards,

Anushankari

[Former Member]

what fields are in the fourth index and in the fifth index????

WHERE rrcty NE '2' AND "exclude record type 2
rldnr EQ 'SL' AND
ryear = p_year AND
poper IN rng_months AND
rbukrs EQ p_bukrs AND
racct IN s_accnt AND
rzzrecid in s_reccin AND
rzzrjvnam IN s_vent AND
rzzaltkt IS NOT NULL

AND this is not your statement, the executed statement is displayed in the detail of the SQL trace, also above the explain in Oracle but without values.

=> which is INs are not used ????

Siegfried

[Former Member]

Hi Siegfried,

4th Index has RACCT, RCNTR.

5th Index has RRCTY,RYEAR,POPER,RBUKRS,RACCT,RZZRECID,RZZRJVNAM,RZZALTKT.

Regards,

Anushankari

[Former Member]

How would you use an not equal condition on an index ??????

> rrcty NE '2'

Means it can be all but 2.

Assume you are in front of book shelf, the author is not 'Sharespeare', what does it help?

[Former Member]

plus ... read my answer completely !!!! What is the actual WHERE-condition !! IN-clauses are no WHERE-condition, they are filled during execution. They can be =, <, >, also <>, or empty !!!! This is absolutely important, but completely ignored by every second poster in this forum. Be aware of the difference between Open SQL and database SQL.

[HermannGahm]

Hi,

please do what Siegfired said. Post the SQL statement as it is sent to the database with variables and times... .

Besides that:

You have hinted your statement with this hint:

%_HINTS ORACLE 'index("zsiopa" "zsiopa~5")'.

A hint, gives the optimizer an hint what index should be used.

The optimizer checks your hint syntatically and semantically.

Please note that index and table names are not lower case in ORACLE.

Suggestion:

Write your hint like this:

%_HINTS ORACLE 'INDEX("ZSIOPA" "ZSIOPA~5")'.

and the desired index should be choosen by the optimzer.

But please note: hints are only checked syntacially and

semantically... it is not checked for efficiency.

check out this example in ST05 - Explain one SQL statement...:

SELECT

/*+

INDEX("T100" "T100~001")

*/

*

FROM

T100

WHERE

arbgb <> 'something'

SELECT STATEMENT ( Estimated Costs = 393.815 , Estimated #Rows = 2.011.334 )

2 TABLE ACCESS BY INDEX ROWID T100

( Estim. Costs = 393.815 , Estim. #Rows = 2.011.334 )

Estim. CPU-Costs = 2.986.180.574 Estim. IO-Costs = 393.348

1 INDEX FULL SCAN T100~001

( Estim. Costs = 1.437 , Estim. #Rows = 2.011.334 )

Estim. CPU-Costs = 110.654.191 Estim. IO-Costs = 1.420

Filter Predicates

whenever the hint is syntatically and semantically correct

and the specified action (forced usage of an index) is technically

posible, ORACLE will do what you have told to do, regardless how

inefficient it may be.

When you use hints, you should know what you do. Your NE condition

on the first indexfield does not sound like a good idea to me because you

have to scan the whole index like in the examle above...

Kind regards,

Hermann

[Former Member]

Hello Anu,

Don't use 'NE' condition in the where clause. Instead delete the unwanted records from internal table int_zsiopa1 after selecting.

As you said that you have already created an index on the table. Check if stats run was executed on that table.

The query then should pick proper index and there should not be a need to provide any hint.

Regards,

Anand

[Former Member]

> Don't use 'NE' condition in the where clause. Instead delete the unwanted records from internal table int_zsiopa1 after selecting.

This is often written here, but completely incorrect.

The NE is not used by the optimizer and the index, but it reduces the transferred amount of data!!!!

It can happen that the internal table is much much smaller.

Leave the NE in the WHERE-condition, bt be aware that no index can use for the search, i.e it can be one of the last index fields

but not the first one.

Siegfried