Select is not using the Index

[Former Member]

Hi,

I have a Z-Table. The Primary key is:

EQUNR and LFDNR

I created now a new index with the field: EQUNR BSTNK .

Now I entered 3 equnrs and bstnk like 07* in SE16n

Normally what I thought should happen is that it uses the index to read the three equipments and than checks the field bstnk.

Instead it does a sequentiel read of the hole table that contains 12mio entries!

When I enter the bstnk more qualified like 0712354* the search is fast (1sec). Why is SAP bypassing the index and does a sequentiel read instead?!

[HermannGahm]

Hi Daniel,

we need more information from you.

What is the cardinality (nr. of distinct values) for equnr and lfdnr?

What database are you running on?

Your 2nd try in SE16 migth be faster because of the data was cached

(and not cached for your 1st check with 07*). If you try the first value

again as a third check is it slow or fast?

Kind regards,

Hermann

[Former Member]

Hi,

Try by adding another Field i.e, MANDT (order must be MANDT,EQUNR, BSTNK).

Regards,

Raghava Channooru

[Former Member]

Hi,

Try by adding another Field i.e, MANDT (order must be MANDT,EQUNR, BSTNK).


still the index is not trigerring try to use index hints in select query..


SELECT [..] FROM [..] 
WHERE <CONDITION>
%_hints oracle u2018INDEX(u201C<TABLE name>u201D u201C<table>~indexu201D)u2019.

regards,

Prabhu

[Former Member]

Depending on the Database you are using, you may need to generate the DB stats. This will allow the Query Optimiser to pick it up and (we hope) use it when translating the ABAP SELECT.

For Oracle - ask your basis guys to run RSANAORA to generate the statistics. It should not be necessary to have to use HINTS within your ABAP SELECTS.

[Former Member]

as it is a Z-table only you can know about the distribution of EQUNR and LFDNR

As the 2 values are together the primary key, the combined values are unique.

Assume you have 1.000.000 records in the table, the can be

10 different EQUNR and 100. 000 different LFDNR or

100.000 different EQUNR and 10 different LFDNR

In the first case the knowledge of the EQUNR does not help much, the SE16 will always use a scan and show the

first 200 hit

in the second, the knowledge of EQUNR tells you nearly everything. No secondary index would be necessary.

Why is SAP bypassing the index

It is probably not SAP, it is your database, and there are 6 possiblities (3 IBM, Oracle, MSSQL and MaxDB from SAP)

In the book mentioned below you can find a lot more information on index usage etc.

Siegfried

-

-

---

Werbung in eigener Sache:

Wenn Sie mit der Performance Ihres ABAP Programms nicht zufrieden sind,

werfen Sie doch einen Blick in mein neues Buch:

Siegfried Boes: Performance-Optimierung von ABAP®-Programmen

Nov. 2009 464 Seiten 59u20AC

Leseproben und weitere Informationen: http://www.dpunkt.de/buecher/3096.html

-

-

---

[Former Member]

In ST05, you can verify that the longer "like" string utilized your new index? What really happened is that on the 2nd attempt you read buffered data, since the 07* had already obtained everything from the db table.

As noted: get run stats generated, then provide equality condition for the two fields (not "like"). Then you'll see the index used unless the underlying dbms determines that another path is more efficient. And, if you're obtaining more than some arbitrary percentage of the rows in the table, a full table scan may be more efficient so the db optimizer could choose to do that anyway, based on your where clause.

[Former Member]

Hi I checked,

ST05 the fast run was using an Index, the second didn't!

Thanks to John. After I started a runstat on this table it worked.

But the weird thing is when I use a inner join between my Z-Table and the EQUI table it doesn't use the index.

When I first select all equnrs in EQUI and then select the data from my z-Table with for all entries it uses my new index!

Example:

EQUNR has Index on field SERNR

Z-Table has Index on EQUNR + BSTNK

in sernrs I have ~5 full qualified sernos

in bstnk I have something like 07*

3 of these serialnumbers are in a bstnk that begins with 07.

          select *
            into corresponding fields of table search_result
            from equi as a
            inner join z-table as b on b~equnr = a~equnr
            where   a~sernr     in sernrs
              and   b~bstnk     in bstnks

After 5 min I have to kill the process. in SM50 I can see sequential read...

When I do this:

        select equnr
          from equi
          into corresponding fields of table lt_search_result
          where sernr in sernrs.

          select *
            from z_table
            into corresponding fields of table lt_search_result
            for all entries in lt_search_result
            where equnr = lt_search_result-equnr
              and bstnk in bstnks.

It takes 2sec and I got the result.

Edited by: Daniel Winter on Apr 21, 2010 8:51 AM

Edited by: Daniel Winter on Apr 21, 2010 8:51 AM