topic Re: Correct table Index not being picked while execution in Application Development and Automation Discussions

Correct table Index not being picked while execution

Former Member — Thu, 27 Aug 2009 10:47:26 GMT

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

Re: Correct table Index not being picked while execution

Former Member — Thu, 27 Aug 2009 11:00:22 GMT

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

Re: Correct table Index not being picked while execution

Former Member — Thu, 27 Aug 2009 11:10:46 GMT

Hi Siegfried,

4th Index has RACCT, RCNTR.

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

Regards,

Anushankari

Re: Correct table Index not being picked while execution

Former Member — Thu, 27 Aug 2009 14:41:29 GMT

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?

Re: Correct table Index not being picked while execution

Former Member — Thu, 27 Aug 2009 14:45:17 GMT

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.

Re: Correct table Index not being picked while execution

HermannGahm — Thu, 27 Aug 2009 15:21:46 GMT

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

Re: Correct table Index not being picked while execution

Former Member — Sun, 30 Aug 2009 06:21:57 GMT

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

Re: Correct table Index not being picked while execution

Former Member — Sun, 30 Aug 2009 16:05:17 GMT

> 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