Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to change secondary index to primary index while selection

Former Member
0 Kudos

hi,

I am seeing the secondary index used in selection for mseg table (in trace i can see that)but i want to use primary index.Can anyone suggest how to use primary index.

I have used all primary keys fields and seconadry index fields in the select statment.

Regards,

Thanigaiarasu

7 REPLIES 7

former_member186746
Active Contributor
0 Kudos

Depending on the order of the keys in the where clause the database optimizer decided which index to use.

First check if the where clause should change. If this doesn't work you can force the database optimizer to choose an explicit index. In that case use the HINTS addition (search this forum for good examples ).

Kind regards, Rob Dielemans

0 Kudos

>

> Depending on the order of the keys in the where clause the database optimizer decided which index to use.

Order doesn't matter. If MBLNR and MJAHR are uniquely specified, the primary key should be used. But if MBLNR is specified in a SELECT-OPTIONS and is uses a wide range, then the optimizer will try to decide and may use a secondary index.

Rob

Former Member
0 Kudos

Hi,

Usage of index is decided by the database using some optimization rules. Normally if you supply the promary keys as part of the where clause (here MBLNR and so on), the query will take the primary index.

-Ashim

former_member194613
Active Contributor
0 Kudos

>Depending on the order of the keys in the where clause the database optimizer decided which index to use.

no !

You must be more specific, primary key and secondary key can both be o.k. It can happen that the secondary key is used,

because it is smaller and as good as the primary key. So don't try to do cosmetics.

Siegfried

former_member192616
Active Contributor
0 Kudos

Hi Thanigaiarasu,

let me answer with a question:

"Why would you want to use the primary index instead of the secondary index?"

I'm asking this because normally the optimizer picks up the most efficient execution plan (e.g. picks the best index). Of course

there are exceptions... and that is why i'm asking. Have you compared both options primary

index and secondary index and found out that the optimizer is choosing not the most efficient execution plan?

KInd regards,

Hermann

former_member194613
Active Contributor
0 Kudos

> I have used all primary keys fields and seconadry index fields in the select statment.

all primary key fields usually lead to the primary key, because it is unique key, which most secondary keys are not.

But specify used more precisely, with IN-Lists or > <, between like or whatever.

And maybe the secondary key has one unique key field, then it should win, if used with =

Former Member
0 Kudos

You cannot change secondary index to primary index. primary index is unique which contains ID 0. for best proformance you should not add duplicate entires in more than 1 indexes.