topic Re: table INDEX in Application Development and Automation Discussions

table INDEX

Former Member — Wed, 22 Apr 2009 07:33:44 GMT

Hi,

i have created an index (Z04) in table RESB and I want to know if this index is being used in my customized program (ZPROGRAM)

Is there a SAP tcode wherein I can check if ZPROGRAM is using my Z04 index??

thanks!

Re: table INDEX

Former Member — Wed, 22 Apr 2009 07:35:51 GMT

Hi ,

go to t-code db05 and check whether your index is using or not..

regards,

Prabhudas

Re: table INDEX

Former Member — Wed, 22 Apr 2009 07:48:50 GMT

Please do run time analysis using TRACE ST05.

This will provide all the info regaring database hits and the index used.

Re: table INDEX

Former Member — Wed, 22 Apr 2009 07:49:52 GMT

Hi,

Thanks!

but im confused of what is being displayed.. there is an existing Z01, Z02, Z03 in RESB and i created Z04.. how can i determine if it uses Z04?

the result that being displayed in tcode DB05 is

Date/time of analysis: 2009.04.22 00:42:38

Analyzed table: RESB

Total number of rows: 2,936,188 rows

Requested bytes per row: 2,015 bytes per row

Requested bytes to be buffered 100%: 5,916,418,820 bytes

Current buffering mode: no buffering

client 320 9,231 rows 18,600,465 bytes

Client 320

Rows per Distinct 1 11 101 1,001 10,001 100,001 more than

generic key values - 10 - 100 - 1,000 - 10,000 - 100,000 -1,000,000 1,000,000

Key fields Number of areas that are specified by the generic key and contain the given number of rows

1 MANDT The following distribution table applies to client 320

2 RSNUM 1,626 1,492 134

3 RSPOS 9,231 9,231

4 RSART 9,231 9,231

Re: table INDEX

Former Member — Wed, 22 Apr 2009 07:51:40 GMT

Hello,

Use Sql trace of the program.

ST05

Re: table INDEX

MarcinPciak — Wed, 22 Apr 2009 08:02:59 GMT

Hi,

Using index access depens on factors evaluated by DB optimizer. It is he, who decides wheter to access table in certain case using index or without it. One query can lead to access using it, whereas the other can not use it.

To check this (by query) go to ST05 -> check SQL trace -> Trace on and run your program (having the query). Then go back here and use Trace off . Dipslay the list with Trace list and look for your query (the one with FETCH operation for select statement). Place the cursor on it and choose Explain SQL from application toolbar.

Now, depending on DB your system work you can see different views. I.e. for ORACLE you will statement expanded in a tree, you can see here how it is accessed.

TABLE ACCESS BY INDEX ROWID SBOOK -> table is accessed via index

INDEX RANGE SCAN SBOOK~0 -> here table is accessed using primary index (~0)

If you find similar query for your index i.e. SBOOK~Z04 then you are sure the table is accessed using your index.

Regards

Marcin

Re: table INDEX

Former Member — Wed, 22 Apr 2009 08:24:11 GMT

i use st05 and notice that when i click the RESB OPEN, it is using the first index which is Z01... how can i make sure that the program will use index Z04?

Re: table INDEX

Former Member — Wed, 22 Apr 2009 08:31:28 GMT

check the index is used by your program using tcode DB05.

Regards,

Joan

Re: table INDEX

Former Member — Wed, 22 Apr 2009 08:42:25 GMT

Hi Goto ST05 it will list out all the index being used in the Table.

Analsyse step by step.it will list out including secodry indexes also.

Re: table INDEX

Former Member — Wed, 22 Apr 2009 10:30:59 GMT

> check the index is used by your program using tcode DB05

used ???? not really, ST05 shows the used index, which is the 01 Index in your case.

Only some databases show in the Explain (double-click on table) all available indexes,

nobody knows whether they are used at all.

Your index ... does it fit to the statement?

Is is really created or only a DDIC diefinition? Are also the statistics created?

Check DB02, whether index exists on the database.

What database do you use?

Siegfried

Re: table INDEX

HermannGahm — Wed, 22 Apr 2009 11:11:10 GMT

Hi ravolarte,

Which db are you using?

Have the statistics been reated for your index?

How looks your where condition?

What are the fields in index Z01 and index Z04?

Kind egards,

Hermann