topic create index in Application Development and Automation Discussions

create index

Former Member — Mon, 21 Aug 2006 14:22:36 GMT

Hi all,

how to create index for table GLPCA for fields RYEAR and POPER and how to write select statement using those indexes.

Re: create index

Former Member — Mon, 21 Aug 2006 14:24:50 GMT

Use transaction SE11. There is a pushbutton 'INDEXES'. To use it, the SQL optimizer should use it itself.

However, there is an abap command in which you can force the index to be used. Will try and find out. Can't remember it.

Re: create index

Former Member — Mon, 21 Aug 2006 14:31:37 GMT

Generally, and specifically with Oracle, if you include the fields of the index in the where clause of your SQL, the index will be chosen as part of the plan. But the optimizer makes the final decision.

After you create your index, ask your DBA to create statistics for it, so that the optimizer knows all about it. Generally, you should let the optimizer decide the best course of action, and avoid "Hints"

Look for function modules like "DBEXPLAIN" that will give you an idea of what the optimizer will do.

e.g.

SELECT...

FROM GLPCA

WHERE RYEAR = l_var1

and POPER = l_VAR2

Re: create index

Former Member — Mon, 21 Aug 2006 15:19:16 GMT

If you know the controlling area (KOKRS), you can add that to the select statement. That should speed it up.

It's not generally a good idea to add a custom index to a standard SAP table to speed up a single custom report.

Rob