topic Re: Index in ABAP program in Application Development and Automation Discussions

Index in ABAP program

Former Member — Wed, 23 Jan 2008 15:44:01 GMT

Hi Guys,

I have a problem with index. I created index on VBRK table with fieldsd VKORG & FKDAT and in selection query i am using only these two fields in where condition.

Most of the times the application is running perfectly. But some times it is running too long and giving a dump.

I checked with basis & dba team, they are saying it is running on different index when it is giving a dump i.e., it is executing on different index some times.

Can anybody give me a suggestion why it is taking a different index?

Or is there any chance to give a HINT in select statement in ABAP or specifically mension the index name in select statement?

regards,

shylesh

Re: Index in ABAP program

matt — Wed, 23 Jan 2008 15:50:53 GMT

You can use database hints in ABAP. See OSS note 129385 for details.

matt

Re: Index in ABAP program

ThomasZloch — Wed, 23 Jan 2008 15:55:53 GMT

Hi,

this may occur when you are selecting on several different fields, not only those in the secondary index.

If too many fields are involved, the optimizer may not find the best index any more.

Possible reasons:

- table statistics are not up to date

- bug in the database software (Oracle 10 had some of those...)

- bad ABAP coding

Yes, you can use a hint to force the database to use a certain index, if you know what you're doing. But keep in mind that you are not database-independent any more with your code.

I have done this successfully with the BKPF table (90 million entries in our box...)

Greetings

Thomas

Re: Index in ABAP program

Former Member — Wed, 23 Jan 2008 16:03:05 GMT

Hi Thomas,

Thanks for quick reply.

Can you give me a sample code for this?

Shylesh

Re: Index in ABAP program

JozsefSzikszai — Wed, 23 Jan 2008 16:05:15 GMT

hi Sailesh,

did you include the MANDT field (client) into your index? It matters, when you have several clients in the system.

Re: Index in ABAP program

Former Member — Wed, 23 Jan 2008 16:06:09 GMT

There are only two SAP delivered indexes on this table - the primary index (VBELN) and a secondary on LCNUM. It seems unlikly that any SELECT you may be using will use either of these. So I suspect that the problem is that the values you are using in the WHERE would pick up too many records to use your index effectively, so it is going for a full table scan instead.

So in this case, my guess that using Oracle HINTS would do more harm than good. Try being more restrictive in the date range.

Rob

Re: Index in ABAP program

Former Member — Wed, 23 Jan 2008 16:20:09 GMT

I used MANDT as field in Index.

Rob, but why it is going to different index when i am using a custom one? Is SAP or Oracle will automatically use different Optimization process if it is taking long time?

Shylesh

Re: Index in ABAP program

Former Member — Wed, 23 Jan 2008 16:27:30 GMT

The query analyser makes the decision of which index to use (if any at all) based on your where clause and the statistical distribution of the field entries (which is why the statistics need to be up to date) If the where clause is too long or complex, it may decide that it is easier to table scan.

Re: Index in ABAP program

ThomasZloch — Wed, 23 Jan 2008 16:31:07 GMT

here's the code for BKPF and Oracle:

      SELECT ...
             INTO TABLE ...
             FROM BKPF
             WHERE ...
               %_HINTS ORACLE 'INDEX("BKPF" "BKPF~5")'.

replace with your table VBRK and the name of your index, e.g. VBRK~Z01.

If your DB is not Oracle, you'll have too lookup the syntax in SAP notes or online documentation.

Cheers

Thomas

Re: Index in ABAP program

Former Member — Wed, 23 Jan 2008 16:31:13 GMT

See Chris's answer.

Rob