topic Re: Table Index in Application Development and Automation Discussions

Table Index

Former Member — Thu, 05 Mar 2009 20:27:13 GMT

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.

Re: Table Index

former_member376453 — Thu, 05 Mar 2009 20:36:10 GMT

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

Re: Table Index

Former Member — Thu, 05 Mar 2009 20:53:45 GMT

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

Re: Table Index

Former Member — Thu, 05 Mar 2009 21:22:48 GMT

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é

Re: Table Index

Former Member — Fri, 06 Mar 2009 01:37:03 GMT

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

Re: Table Index

Former Member — Fri, 06 Mar 2009 02:09:11 GMT

Sorry Forgot to add ' at the end..

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

Re: Table Index

Former Member — Fri, 06 Mar 2009 09:45:34 GMT

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

Re: Table Index

Former Member — Fri, 06 Mar 2009 10:46:42 GMT

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

Re: Table Index

Former Member — Sun, 08 Mar 2009 07:47:50 GMT

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

~Satya

Re: Table Index

Former Member — Mon, 09 Mar 2009 12:23:32 GMT

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