Table Index

[Former Member]

Hi,

I need to understand something. I'm trying to improve performance on a report. The most selections do not use table keys to acess, so, somebody create some indexes with the idea to make the selection faster. Here is one example:

SELECT *

FROM coep

INTO TABLE t_coep

WHERE bukrs IN s_bukrs

AND gjahr = p_gjahr

AND perio IN r_perio

AND kstar IN s_kstar.

The table COEP has a index with fields bukrs, gjahr, perio e kstar. But, after use ST22 I discover that the database is not using this index. Its use the "normal one".

I know that is not a good programming pratice use select * (I will take out and put only the fields that I want).

Well, somebody com explain to me how the indexes works or have some document about it.

I appreciate any kind of help.

[former_member376453]

Here is the SAP documentation: http://help.sap.com/saphelp_erp2005/helpdata/EN/77/415d363640933fe10000009b38f839/frameset.htm

[Former Member]

Moved to correct forum.

You have to realize that the index is chosen at run time and may not correspond to the fields you have in the SELECT.

Rob

[Former Member]

Hi,

It must verify that the index exists in the Data Base. Execute the SE11 transaction with the COEP table and presses de INDEXES button, double click in the index and check status "Index xxxx exists in database system xxxxxx" or "Index does not exist in database system xxxxxx".

hope this information is help to you.

Regards,

José

[Former Member]

Hi Bruno,

Try the below,

u can tell the select query 'which index to use'.

and use your INDEX number in place of '3' here.

SELECT *

FROM coep

INTO TABLE t_coep

WHERE bukrs IN s_bukrs

AND gjahr = p_gjahr

AND perio IN r_perio

AND kstar IN s_kstar

%_HINTS ORACLE 'INDEX("COEP" "COEP~3").

Jey

[Former Member]

Hi,

I would use hints ONLY in 2 cases

• for testing purposes (force the usage of an index to see the response time changes )

• a known bug inside the database handling the optimization of index access to the table (i.e. as described in some SAP standard table notes)

in all other cases: never use index hints.

You will write the access plan kind-of "hard-coded" to the database.

If you have data volume changes inside the table the optimizer has no chance to develop an appropriate

plan. It is fixed to your all-purpose index - what definitley is not true (data volumes change,

filter condition change in a real system).

bye

yk

[Former Member]

Sorry Forgot to add ' at the end..

%_HINTS ORACLE 'INDEX("COEP" "COEP~3")'.

[Former Member]

Hi,

check below link for performance

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/abap/abap%2bperformance%2band%2btuning

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/abap/performance%2btuning

Regards,

Madhu

[former_member194613]

it is not really recommended to create index, if you have not much experience.

There are advantages and disadvantages of new indexes, you have to take care.

Without statistics it will not be chosen.

Siegfried

[Former Member]

Check this [link>>>|http://help.sap.com/saphelp_erp2005/helpdata/EN/cf/21eb20446011d189700000e8322d00/frameset.htm]

~Satya