topic Re: DB Index in Application Development and Automation Discussions

DB Index

Former Member — Thu, 03 Apr 2008 12:05:04 GMT

Hi all,

i have created Secondary index for a Transparent table.How can i assured that while retrieving data from Table using Select query,secondary index that i created is working..

Thanks in advance.

Re: DB Index

Former Member — Thu, 03 Apr 2008 12:10:53 GMT

Hi Joe,

If you want to specify index in the SELECT , try this

Select ERDAT WERKS MATNR

from LIPS

into table Itab

Where erdat in s_erdat

and werks in s_werks

and Matnr in s_matnr

%_HINTS oracle ‘index(“ERDAT” “ERDAT~001”)’.

Note: here 001 is ur index name.

Re: DB Index

Sm1tje — Thu, 03 Apr 2008 12:12:09 GMT

The database system will use the optimal index but this does depend on your selection as well.

When doing a select make sure you are using your index fields (correct order).

You can also check this when doing a trace using transaction ST05. You can see if your index was used or not.

By the way: it is not recommended to use oracle hints, since this is a platform dependant statement. Will work though if you are using oracle database.

Edited by: Micky Oestreich on Apr 3, 2008 2:12 PM

Re: DB Index

Former Member — Thu, 03 Apr 2008 12:17:24 GMT

Hi Joe,

I believe this is an automatic task..based on your select query on the database the proper index is analysed and used...

the only requirement is that the select query must contain the fields in "where" clause exactly in the order mentioned in the required index..otherwise the index won't be taken.....but it can be traced using ST05

Please find the steps below

Open a transaction called ST05 in a separate session. This is used to analyze the SQL trace

Open the program (SE38) in a separate session and put a break point at the select statement where you would like to find out the index being used.Next, we execute the program and it stops at the break point. Then, you click on trace-on in the ST05 transaction. Press F5 (debugging) in the program to execute that select statement. After executing the select statement, click on trace-off in the ST05 transaction.

Next, Click on Trace list which displays basic trace list of the select statement we executed. We can find out which index being used by choosing the function EXPLAIN SQL after putting the cursor on one of statements (PREPARE, OPEN, REOPEN).

We get the complete execution plan for the SQL statement. This will tell you about which index is being used

Clicking on any one of these indexes would give you detailed information on that particular Index

Regards

Byju

Re: DB Index

Former Member — Thu, 03 Apr 2008 12:18:22 GMT

Hi Joe,

Make sure you use all keys specified in your DB Index...System will automatically use the index you have created.

Regards,

Mohaiyuddin

Re: DB Index

Former Member — Thu, 03 Apr 2008 12:19:51 GMT

Joe,

1. 'Activate' an SQL trace using ST05

2. Run your program

3. Deactivate and display the trace (using ST05 again)

4. Select the line in the trace display where the table is 'OPENed', and hit the 'explain' button

I hope this helps.

Points not needed ... a simple 'thank you' works for me

Duane