topic Re: DB uses wrong index in Application Development and Automation Discussions

DB uses wrong index

Former Member — Wed, 17 Jun 2009 06:41:43 GMT

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!

Re: DB uses wrong index

HermannGahm — Wed, 17 Jun 2009 06:49:33 GMT

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

Re: DB uses wrong index

ThomasZloch — Wed, 17 Jun 2009 07:30:49 GMT

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

Re: DB uses wrong index

Sandeep_Panghal — Wed, 17 Jun 2009 10:26:56 GMT

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

Re: DB uses wrong index

HermannGahm — Wed, 17 Jun 2009 11:07:37 GMT

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

Re: DB uses wrong index

Sandeep_Panghal — Wed, 17 Jun 2009 11:16:18 GMT

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

Re: DB uses wrong index

Former Member — Wed, 17 Jun 2009 13:24:54 GMT

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.

Re: DB uses wrong index

Former Member — Wed, 17 Jun 2009 13:28:11 GMT

Hi,

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

Regards,

Ankur Parab