DB uses wrong index

[Former Member]

Hi,

I have a custom table, with 2 indexes:

1) Z01 with the ff. fields: client, plant, doc1, movmt_typ

2) Z02 with only one field: doc1

my select statement is below:

select single * from zcustom where doc1 = p_doc.

when i check on st05, the system uses the Z01 index instead of Z02 which has the field in my where condition.

i need to know why the system uses Z01 and not Z02.

Thansks!

[HermannGahm]

Hi Freishz,

which DB (version) are you using? (Probably not MAXDB or MSSQL, but one of the following: DB2, DB4, DB6, ORACLE, right?).

Depending on your DB the following question might be interesting:

Do both of your indexes have good statistics?

Could you post the statistics for both indexes?

Without more information i assume the reason is in the statistics... .

Kind regards,

Hermann

[ThomasZloch]

In addition to that, why did you not include the client in index Z02? Depending on how many clients you have and the data distribution per client this may or may not make a difference for the index selection.

It might be worth a try to include the client in that index and check the result.

Thomas

[Sandeep_Panghal]

If you really want to use Z02 index then try using HINTS and the particular index.

[HermannGahm]

Hi Sandeep,

i think a hint should be the very last option. I prefer to understand the optimizer before trying to hint the opimizer. Maybe the optimizer is correct with the cost calculation and maybe the index Z01 leads to a better runtime as well... as Thomas pointed out this could easily be the case if we consider multiple clients... .

Kind regards,

Hermann

[Sandeep_Panghal]

Hi Hermann,

Yes HINTS should not be used until and unless this is the last resort .

But my suggestion was based on assumption that user wants to use Z02 ONLY.

Thanks

Regards,

Sandeep

[Former Member]

first check whether the index exists on the DB and not only in DDIC and check statistics, use DB02,

or ST05 (with Oracle or IBM).

Check system -> status, if you are not aware what DB you use.

[Former Member]

Hi,

Ask your basis team to update the database statistics for this table.

Regards,

Ankur Parab