topic Make a SELECT statement using an INDEX in Application Development and Automation Discussions

Make a SELECT statement using an INDEX

Former Member — Thu, 21 Jan 2010 13:29:32 GMT

Hi,

I need to read data from LTAP, using an index that i've in table structure.

i've M,V and Z1 index and i need to use 'Z1' at this particulary SELECT. How can i do this ? ...it's the first time that i've to use.

Can someone help me ?

Thanks

Re: Make a SELECT statement using an INDEX

Former Member — Thu, 21 Jan 2010 13:59:47 GMT

Hi Ricardo,

Are you sure that your query is written correctly in order to use your index? Generally, databases use the correct index if the query is correct too!

But if you really want to force an index, you have to use database hints. For example, for Oracle database, you will have something like this :

SELECT * FROM ltap
  %_HINTS ORACLE 'INDEX("LTAP", "LTAP~Z1")'.

Best regards,

Samuel

Re: Make a SELECT statement using an INDEX

Former Member — Thu, 21 Jan 2010 14:26:28 GMT

a database hint is the last choice, you should write your WHERE-.clause and design your index in such a way, that the optimzer chooses the correct index.

Copy your WHERE clause and show the available indexes including statistics.

Re: Make a SELECT statement using an INDEX

Former Member — Thu, 21 Jan 2010 14:34:52 GMT

Hi, I've this at select stament

SELECT tanum vbeln matnr vlpla nsola ndifa posnr tapos APPENDING

CORRESPONDING

FIELDS OF TABLE lt_ltap FROM ltap

WHERE lgnum EQ l_lgnum AND

vbeln EQ lt_aux-vbeln.

and this index at table structure

M index

MANDT

LGNUM

PQUIT

MATNR

V Index

MANDT

LGNUM

PQUIT

VLTYP

VLPLA

Z1 Index

MANDT

VBELN

POSNR

Re: Make a SELECT statement using an INDEX

Former Member — Thu, 21 Jan 2010 14:41:55 GMT

what is your database, check system, status.

Can you run an SQL Trace, (tcode ST05) ?

What does the explain for this statement show?

Simple test, use SE16 (table data), type in lgnum und vbeln (first get existing numbers),

open in other mode ST05, start, ... stop when finished, display.

Check for OPEN line for this table and show 'explain'. Search which index is mentioned (I can hardly say more as there

six different databases with very different explains).

Siegfried

Re: Make a SELECT statement using an INDEX

Former Member — Thu, 21 Jan 2010 15:00:53 GMT

Hi, I've made what you said and my report are using correct index (Z1). I'm going analyze my ABAP, trying to find my performance problem.

Thanks,