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: 

Plz help me

Former Member
0 Kudos

Hi,

What is the diff bet Primary index and secondary index?

and what will be effect of performance of a program when u r using either a Primary index or secondary index.

Thanks & Regards,

K P

8 REPLIES 8

athavanraja
Active Contributor
0 Kudos

primary index are the key fields of a table.

secondary indexes are created by combining other fields.

you cannot choose a particular index to be used by a sql.

its decided by the db optimizer.

performance wise , i guess both will be same.

Regards

Raja

Former Member
0 Kudos

Hi

Do you mean the dictionary table index?

The primary index is the index the system create using the keys of a dictionary table (it's created by default), the secondary index is a index composed by several fields in order the selection you need.

So when you do a select single using all key fields, you'll use the primary index.

But if you need a select not using key fields and you want to improve the performance you can create an your own index.

For example you need to do a select a BKPF record using only BELNR and BLART.

SELECT * FROM BKPF WHERE BELNR =

AND BLART =

....

Now if you create a new index for BKPF with BELNR and BLART, when you do a select like that, you'll use this index improving select performance.

Remember you'll use your index only if you insert the fields in where condition in the same order of the index.

So if you wrote:

SELECT * FROM BKPF WHERE BLART =

AND BELNR =

You doesn't use the index.

You can use your index also if you don't use all fields:

For example if you have a index with BUKRS, GJAHR, BLART, BUDAT.

Here you're using the index

SELECT * FROM BKPF WHERE BUKRS =

AND GJAHR =

AND BLART =

AND BUDAT =

Here you're using the index

SELECT * FROM BKPF WHERE BUKRS =

AND GJAHR =

AND BLART =

But the index needs its own space on database because when a new record is inserted, the system'll do an entry in BKPF and one for your index.

So you should create a new index only if it's really necessary.

Max

Message was edited by: max bianchi

Message was edited by: max bianchi

0 Kudos

... just one addition:

the primary index of a relational database table is unique - so that the database record is identified by it's primary index.

A secondary index is not necessarily unique, it depends on definition. Usually it is not unique, more than one record may have the same secondary index.

A secondary index is used to speed up the access when you don't know the exact primary key. On the other hand it will slow down the database update because all updates will also update every secondary index.

In the dictionary (SE11/SE12) you can click on index to see existing indexes. This is kind of exhausting because first you see all existing indexes listed by name and then the detail view is to be double-clicked one by one.

Much more convenient is to go to the runtime object an scrolling down to the end: After all field definitions you can see all index definitions.

Hope it helps.

C.

0 Kudos

and another addition...

primary index is always there, but secondary indexes should be activated in order for them to be used.

Srinivas

Former Member
0 Kudos

KP,

Assuse If u have Secondary(Filed1,F4,F5) Index defined in addition to Primary Index(comibation of Key Fields:f1,F2,F3).

And in the program, if u have a SELECT stmt with WHERE condition having <b>fields from Secondary Index</b>, then the <b>SAP and DB will IGNORE the primary index</b> and go for the <b>SECONDARY INDEX way of Data Record Selection</b>.

Thanks

Kam

Note: Allot points for all worthful postings

Former Member
0 Kudos

Hi,

Primary index is created by SAP which consist of all the primary keys.

Secondary indexes are created manually. We do not need to explicitly mention details for secondary index in the SELECT WHERE condition. System identifies it automatically during runtime.

Performancewise using Primary or secondary index has no difference.

Former Member
0 Kudos

Hi KP,

The performance of the index is really based on the where condition available in the select query.

SAP(/Database) always selects appropriate index based on the where condition.

If you are trying to select data based on a where condition that is very different from Primary key of the table, you can think of creating a secondary index using the fields.

As such if all the key can be provided, primary index is the fastest.

Hope this helps.

Thanks,

Prashanth

Former Member
0 Kudos

Because the primary key fully describes a single record, using all primary key fields in a select is the most efficient way to retrieve a record (SELECT SINGLE). A secondary key, even when fully entered, tends to be less selective and returns more records.

The important thing (as our DBAs have explained to me on many occasions) is selectivity. When designing a secondary index, build one that returns as few records as possible.

Having said that, it is also important how you use an index when building a select statement. If your organization has one company code, and you select from BKPF on BUKRS alone, the select will return a large amount of data. The select statement should return as few records as possible.

It's also unwise to create a secondary index on an SAP table that is heavily updated to improve performance for a single report. There is overhead associated with maintaining the index - every time a record is updated, the index must also be updated. This tends to slow down the update.

You can specify which index to use by including a database hint in the select statement. I think this is generally used for testing and debugging - not for production.

Rob

Message was edited by: Rob Burbank