DDIC

[Former Member]

I created a secondary index named Zsi1 for my table , How to use that in select query ?

[Former Member]

Hi Kumar ,

There is nothing special you have to write in the select statement , just gives the key feilds of the index and the system will do the rest.

Regadrs

Arun

[Former Member]

Hi,

Use that in the where condition of the select Statement of that table.

Fiorst find out in which program that table is used and where it is taking long time,.

To avoid that only we create secondary index.

go to that program and the select statement of that Table and in the where condition add these fields for which you created the Secondary index along with the primary index fields (if used in the where condition).

reward if useful

regards,

Anji

[Sathish]

The select query would have "Where" condition. Use in that.

Do make sure to update the statisctics of the table otherwise there will be negative impact during Insert or any other update operation on the table.

[Faaiez]

Kumar

If you use all the fields, in the order you specified them in the index, in your <b>where clause</b> the system will automatically select the correct index to use.

It is worth creating a secondary index when:

You want to select table entries based on fields that are not contained in an index, and the response times are very slow.

The EXPLAIN function in the SQL trace shows which index the system is using. You can generate a list of the database queries involved in an action by entering Transaction ST05 and choosing Trace on &#8594; Execute action &#8594; Trace off &#8594; List trace. If you execute the EXPLAIN SQL function on a EXEC, REEXEC, OPEN, REOPEN or PREPARE statement, the system returns a list containing the index used in the database query.

The field or fields of the new secondary index are so selective that each index entry corresponds to at most 5% of the total number of table entries. Otherwise, it is not worth creating the index.

The database table is accessed mainly for reading entries.

Using an index consisting of several fields

Even if an index consists of several fields, you can still use it when only a few of the fields actually appear in the WHERE clause. The sequence in which the fields are specified in the index is important. You can only use a field in the index if all of the preceding fields in the index definition are included in the WHERE condition.

An index can only support search criteria which describe the search value positively, such as EQ or LIKE. The response time of conditions including NEQ is not improved by an index.

Regards

[Former Member]

HI ,

zsi1 have 2 fields (sif1 , sif2 ) so how should i write my query is this correct .

select <f> from <table> into (itab) where sif1 = condition .

is this correct ?

[Former Member]

Yes this is correct.

[Former Member]

Ideally it should be:

select <f> from <table> into (itab) where sif1 = condition

<b>and sif2 = condition2.</b>

This will make sure that the right index is picked up. If you specify only sif1, the system may pick up another index which has sif1 as a key, if such an index exists.

Hope this helps.

Sudha

Message was edited by:

Sudha Mohan