topic Indexing error .... in Application Development and Automation Discussions

Indexing error ....

Former Member — Sat, 27 Oct 2007 11:10:13 GMT

Hi Experts,

I have written a program and checked with Code Inspector. It gives error : Large Table AFRU : No field of a table index in where condition.

SELECT aufnr vornr ltxa1 budat xmnga

grund aufpl aplzl erzet

FROM afru INTO TABLE it_afru

WHERE ltxa1 <> space

AND aufnr IN aufnr1

AND budat IN date1

AND grund IN grund

AND erzet IN time.

How to give indexing ?

Yab

Re: Indexing error ....

former_member195698 — Sat, 27 Oct 2007 18:42:36 GMT

There are two types of Indexes on a table

1) Primary index - Default ( Key fields of a table )

2) Secondary Index - Custom Index (Created to improve the access performance)

Your select query might not be based on the Primary key fields, that why the Index of the table is not being used for fetching the data from the table using index.

Regards,

Abhishek

Re: Indexing error ....

Former Member — Sun, 28 Oct 2007 04:03:09 GMT

Hi Yusuf,

This is a good message to get. It is telling you that your WHERE clause contains no fields in any of the indexes your system may have on this table.

You have a couple of options, incorporate one or more fields that are in one of the active indexes, you can create a new secondary index based on the fields in your WHERE clause (it's not necessary to put all the fields of your WHERE clause into an index but you need help from Basis or performance team if you have, to help determine which fields to create an index with. Basis can analyze the program as it runs to determine the best fields for a new index).

Thirdly you can do nothing. But it will be very slow.

Hope this helps.

Filler

Re: Indexing error ....

Former Member — Mon, 29 Oct 2007 07:47:29 GMT

there is no field in the primary key and probably no secondary key.

To create an index you should ask somebody with experience. For example the

field with the '<>' can not use an index.

Indexes can be created in the DDIC (SE11), but must be activated on the database. The order of field must reflect the selectivity (most selective first).

The selectivity can be checked (after creation of draft index version, activation and creation of the index statistics) in the SQL Trace, see explain. Then you could decide which order is optimal. However, to make it really complicated, you should have an eye on other SELECTS working with that table, too.

Overall not recommended for beginners.

Siegfried

Re: Indexing error ....

Former Member — Mon, 29 Oct 2007 11:39:30 GMT

Hi Siegfried,

Thanks, Now it is very clear about indexing.

Yusuf.