Do I have a problem with my index/table?.

Basis (Basis Technology Modules: Basis Component/System Administration, GUIs)

Moderators: Snowy, thx4allthefish

Post Reply
Posts: 180
Joined: Thu May 03, 2012 9:35 am

Do I have a problem with my index/table?.

Post by DrSidewalk » Thu Jun 19, 2014 5:33 am

We are getting an estimated cost against our SQL statement of 16,000+.
When I change the line with the BETWEEN statement we get a estimated cost of 3.
The line was:
WHERE mkpf~budat BETWEEN lv_start_date AND lv_end_date
and gives an estimated cost of 16,673

The line was changed to:
WHERE mkpf~budat EQ lv_start_date
and now gives an estimated cost of 3 !!

That was done out of desperation to try and determine why the code was running so damn slow.
We eventually replaced the line with:

WHERE mkpf~budat IN r_budat (r_budat being a range table)
and this still gave an estimated cost of 3.

The explain path uses mkpf~bud which is a secondary index. However, it uses this index in both explain paths, both the one giving 16,673+ as an estimated cost and the also in the expain path where we used the IN clause with the range table, of which gave an estimated cost of 3.

My question is, why is this happening when the BETWEEN statement is used. As an ex Oracle developer BETWEEN always makes use of an index and does not bypass them. The explain path more or less proves that the index is not being bypassed as the mkpf~bud secondary index shows in both plans.
The secondary index itself is based on MANDT, BUDAT, MBLNR. I see no issue with this either.

Can anyone shed any light on this as I'm baffled as to what has happened here?.

Dr Sidewalk

Posts: 28796
Joined: Mon Oct 21, 2002 2:33 pm
Location: 3.1415926535

Re: Do I have a problem with my index/table?.

Post by Snowy » Fri Jun 20, 2014 12:12 pm

Survey says:


also supply version

Post Reply