This website is not affiliated with, sponsored by, or approved by SAP AG.

Secondary index madness.

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

Moderators: Snowy, thx4allthefish

Secondary index madness.

Postby DrSidewalk » Wed Jul 16, 2014 10:30 am

There's a secondarty index on MKPF names BUD. It has the MANDT, BUDAT and MBLNR fields as keys.

In one of our really old programs we are access this table with a WHERE clause like so.

WHERE budat >= l_start_date.

Now, I have run a quick SQL query beforehand to return the maximum date in the table and then used:

WHERE budat BETWEEN l_start_date AND l_end_date.

This yields much faster extracts, but I do not know why.

I then added yet another field to ensure that we are providing all three fields to secondary index, so the WHERE clause now looks like this:

WHERE budat BETWEEN l_start_date AND l_end_date
AND mblnr GE '0'.

The two statements bring down the estimated cost (ST05) from 8,000+ to 89. I know that a lower cost does not always mean a faster extact, but in this case it seems to.

So, my question is why would a BETWEEN statement be more effecient than the statement BUDAT GE l_start_date?. That's what I'm trying to find out.
DrSidewalk
 
Posts: 180
Joined: Thu May 03, 2012 9:35 am

Re: Secondary index madness.

Postby Snowy » Tue Jul 22, 2014 10:15 am

you need to check which index is being used for both of these requests. My feeling is that different indexes are being used.

also, you must tell us on which database you are running.
SapFans Moderator

Search: http://www.sapfans.com/forums/search.php
Notes: http://service.sap.com/notes
Help: http://help.sap.com
Rules: http://www.sapfans.com/forums/viewtopic.php?t=344127
Snowy
 
Posts: 28767
Joined: Mon Oct 21, 2002 2:33 pm
Location: 3.1415926535

Re: Secondary index madness.

Postby DrSidewalk » Wed Jul 30, 2014 5:57 am

It's Oracle we have as a database, can provide version, but will be pretty recently updated.

The explain path shows the same index being used for both SQL statements, the one where "BUDAT >=" and the 2nd statement where "BUDAT BETWEEN a AND b".
Very odd.

As the 2nd SQL statement is slightly better in terms of estimated cost etc I am using the "BUDAT BETWEEN a AND b" clause , but I have no idea why it's better, and that's what worries me. ;)

Thanks for your help.

Dr Sidewalk.
DrSidewalk
 
Posts: 180
Joined: Thu May 03, 2012 9:35 am


Return to Basis

Who is online

Users browsing this forum: No registered users and 4 guests



cron


loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.