topic Re: Problem with Index.. in Application Development and Automation Discussions

Problem with Index..

Former Member — Tue, 19 Jun 2007 12:44:35 GMT

Hi All,

I created a secondary index in table MSEG with name "Z1".

Now when I am using the select statement

and specifying the index in it, still index "Z1" is not used.

It is still using the default index 'M'.

How can i make it use the index "Z1" ??

Re: Problem with Index..

Simha_ — Tue, 19 Jun 2007 12:48:38 GMT

Hi,

You can force the sql statement to use a particular index by using %_HINTS parameter.

For eg:

SELECT carrid connid cityfrom

FROM spfli INTO (xcarrid, xconnid, xcityfrom)

WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

WRITE: / xcarrid, xconnid, xcityfrom.

ENDSELECT.

Here "001" is the index..

Refer to the SAP Note #129385 and related notes for further information.

Cheers,

Simha.

Reward all the helpful asnwers..

Re: Problem with Index..

Former Member — Tue, 19 Jun 2007 12:49:01 GMT

Hi Prem ,

In this case you will have to use Oracle Hints , what this basically does is it tells the system how the database query must be executed.

Here is a sample

select matnr

into table it_1

from mara

%_hints oracle

'INDEX("MARA~L" )'.

Please check what is the database being used , if it is orcale then in your case the query will be

select <fields>

into table it_1

from MSEG

<b> %_hints oracle

'INDEX("MSEG~Z1" )'</b>.

Hope this helps , assign points if helpful.

Regards

Arun

Re: Problem with Index..

Former Member — Tue, 19 Jun 2007 13:02:19 GMT

yes i have used this select statement with %_HINTS ORACLE 'INDEX(MSEG"Z1")' and still it is not using the secondary index Z1..

Re: Problem with Index..

Former Member — Tue, 19 Jun 2007 13:03:52 GMT

i have used %_HINTS ORACLE 'INDEX(MSEG"Z1")' but still it is not selecting the index Z1

Re: Problem with Index..

Former Member — Wed, 20 Jun 2007 08:06:27 GMT

Hi Prem ,

Please check the format in which the command is to be written and try again , what you have written now is not correct.

Regards

Arun

Re: Problem with Index..

Former Member — Wed, 20 Jun 2007 08:57:08 GMT

First it must be stated that table design is a more logical work while index design is rather technical. In table design it might make sense to place certain fields (client, company code, ...) in the beginning. In index design, this is not advisable. Very important for an index is that it contains very selective fields in the beginning. Those are fields like object numbers. Not selective are client, company code, ...

Indexes should be small (few fields). The Database optimizer can combine two or more indexes to execute a query.

Indexes of one table should be disjoint (have few common fields), in order not to confuse the optimizer which index to use.

Note that each index slows the inserts into the table down. Updates are only slowed down if indexed fields are updated. In general, heavy inserted tables should have only few indexes while heavy selected tables might have more.

Primary index: the primary index contains key fiels of a table and a pointer to non-key fields of the table. The primary index is created automatically when a table is created in database and moreover you can further define reference to the primary index which are known as Secondary index.

9 indexes can be created for a table

example for performance in the select query ...

An index is defined on fields FIELD1, FIELD2, FIELD3 and FIELD4 of table BSPTAB in this order. This table is accessed with the SELECT statement:

SELECT * FROM BSPTAB WHERE FIELD1 = X1 AND FIELD2 = X2 AND FIELD4= X4.

Since FIELD3 is not specified more exactly, only the index sorting up to FIELD2 is of any use. If the database system accesses the data using this index, it will quickly find all the records for which FIELD1 = X1 and FIELD2 = X2. You then have to select all the records for which FIELD4 = X4 from this set.

How to Check if an Index is Used

Procedure

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.

reward points if it is usefull ...

Girish