topic Re: SQL Trace - Indexing table. in Application Development and Automation Discussions

SQL Trace - Indexing table.

Former Member — Wed, 18 Jun 2008 13:23:10 GMT

Hi,

I have created an index for a standard table. I want to test if the indexing is working properly. I have done a SQL trace on it in a program. Can you tell me how I would understand if the index was properly used.

Regards,

Arnab

Re: SQL Trace - Indexing table.

former_member195698 — Wed, 18 Jun 2008 13:31:48 GMT

In the Trace Records, keep the cursor on the REOPENu201D, u201COPENu201D,u201DPREPARE statement and click the "Explain" button.

It will give the details of the index used in the select query.

Regards,

Re: SQL Trace - Indexing table.

sachin_mathapati — Wed, 18 Jun 2008 13:37:21 GMT

Hi Arnab,

How to Check if an Index is Used ?

1.Open a second session and choose System ® Utilities ® Performance trace.

The Trace Requests screen appears.

2.Select Trace on.

The SQL trace is activated for your user, that is all the database operations under your user are recorded.

3.In the first window, perform the action in which the index should be used.

If your database system uses a cost-based optimizer, you should perform this action with as representative data as possible. A cost-based optimizer tries to determine the best index based on the statistics.

4. In the second session, choose Trace off and then Trace list.

Result

The format of the generated output depends on the database system used. You can determine the index that the database used for your action with the EXPLAIN function for the critical statements (PREPARE, OPEN, REPOPEN).

Reward Points if Helpful.

Regards,

Sachin M M

Re: SQL Trace - Indexing table.

Former Member — Thu, 19 Jun 2008 08:43:10 GMT

judging from your basic questions 2 days ago, you made a big step when you create already secondary indexes.

Design design is not so simple, and there was never a question.

For which table diod you create an index? And what are the fields in the index?

I hope that you read the SQL blog now, the index used appears in the EXPLAIN and only there.

You should not see 'PREPARE' and 'OPEN' statements, becuase as an experienced ST05 user

you will execute the program several times ebfore you trace it. Then you should also never see a T001W table.

Siegfried

Re: SQL Trace - Indexing table.

Former Member — Sat, 21 Jun 2008 10:04:46 GMT

hi check this...

/people/thomas.weiss/blog/2007/06/20/abap-trial-version-for-newbies-part-15-how-to-dynamically-create-test-data-for-our-database-table