Using custom index on a table

[MariusStoica]

Hi gurus,

I have a question related to index usage while reading a able in SAP.

In my example I have the newly created index "ZC2" for table MSEG like this:

and when I call the select, it looks like this:

SELECT matnr mjahr bwart charg aufnr mblnr zeile shkzg menge werks
   FROM mseg
   INTO CORRESPONDING FIELDS OF TABLE gt_mseg
   FOR ALL ENTRIES IN gt_mkpf
   WHERE mblnr = gt_mkpf-mblnr
     AND mjahr = gt_mkpf-mjahr
     AND zeile NE ''
     AND ( bwart = '131' OR bwart = '132' OR  bwart = '261' OR bwart = '262')
     AND aufnr NE ''.

I have all the fields in the index, in the same order ... and even so, the index isn't used

and in details I see this

Is there something I'm doing wrong? Nothing I did in the SELECT statement changed the index choice of the SQL statement.

Any clues?

Thank you in advance,

Marius

[MariusStoica]

Hi gurus,

I have a question related to index usage while reading a able in SAP.

In my example I have the newly created index "ZC2" for table MSEG like this:

and when I call the select, it looks like this:

SELECT matnr mjahr bwart charg aufnr mblnr zeile shkzg menge werks
   FROM mseg
   INTO CORRESPONDING FIELDS OF TABLE gt_mseg
   FOR ALL ENTRIES IN gt_mkpf
   WHERE mblnr = gt_mkpf-mblnr
     AND mjahr = gt_mkpf-mjahr
     AND zeile NE ''
     AND ( bwart = '131' OR bwart = '132' OR  bwart = '261' OR bwart = '262')
     AND aufnr NE ''.

I have all the fields in the index, in the same order ... and even so, the index isn't used

and in details I see this

Is there something I'm doing wrong? Nothing I did in the SELECT statement changed the index choice of the SQL statement.

Any clues?

Thank you in advance,

Marius

[Former Member]

Hi Marius,

There is no need to do anything once you create a secondary index in your table if you are using the sap default database 'MAXDB'. The query optimizer will do the necessary things.

If you are using a different database then try the following code:

SELECT carrid connid cityfrom

  FROM spfli INTO (xcarrid, xconnid, xcityfrom)

  WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'

  %_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

  WRITE: / xcarrid, xconnid, xcityfrom.

ENDSELECT.

[ThomasZloch]

Your new index is of no use because it starts with the same columns as they appear in the primary index (MSEG~0). The primary index is fully selective, so there is no reason for the CBO to chose your new index over the primary one.

A new index only makes sense if it provides efficient, alternative access paths to the data.

Thomas

[former_member182354]

Hi,

    Create another index on BWART and AUFNR and check once.

Raghav

[Former Member]

Hi,

As Thomas mentioned, your new index won't benefit you as it contains all the primary keys of MSEG in the same order. Still, just for curiosity's sake if you want to force program to use your new index use database hints.

Database hints for MSSQL is provided in SAPNOTE 133381 .

Regards,

DPM