View or secondary Index. Please guide me..

[praveen_kumar334]

Hi Team,

I have one select statement on MSEG table for which the where condition is based on non-key field. Now my concern is shall i create secondary index on MSEG table(I don't have access to create this)  or shall i create view on the top of the MSEG.

In the view, i will make the where fields as key fields.

Please correct me which approach to follow....

Regards,

RaviChandra.

[VenuAnumayam]

Usually MSEG is a huge table. So that will cause a performance issue. SAP already provided indices for MSEG. Try to use them. Creating a secondary index is not always the solution.

First, try to get the data from MKPF (Header table) and use that to fetch the data from MSEG.

What non-key fields you want to use?

[praveen_kumar334]

HI Ravi,

thanks for the reply,

what is the impact if we create view on the top of the table ?

Thanks

[matt]

You are making a very common error. You are asking how to use your preferred solution (creating a view) rather than thinking about the problem as a whole, and how that can be solved. As Vikram points out below, a view is unlikely to help.

Usually the issue that you are actually facing (performance issue on MSEG), is resolved exactly as Ravi Kumar has said.

[Former Member]

Praveen,

A view does not hold data. When you access the view, the data is fetched from the DB. Below is the documentation for your reference.

Database Views -  ABAP Dictionary - SAP Library

In your case you should consider:

1. Tweaking your logic, if possible to use a better key on the where clause

2. Select data from the header table - MKPF and then hit item table - MSEG.

3. Create a Secondary Index.

Hope this helps.

Thanks,

Vikram.M

[former_member192467]

Hi,

The best way is to go for a secondary index usage. A view will not give you that performance. But if you are in a HANA DB, you can consider creating CDS views.

Regards,

Renjith

[saurabh_chikate]

Hi Praveen ,

Ravi kumar and Vikram's solution is optimal.

Just keep in mind that while creating secondary index, check if there are not many index already present.

As it will slow down insert operation.

Regards,

Saurabh

[matt]

saurabh chikate wrote:

Hi Praveen ,

Ravi kumar and Vikram's solution is optimal.
Just keep in mind that while creating secondary index, check if there are not many index already present.
As it will slow down insert operation.

Regards,
Saurabh

This is not well expressed and may be misleading.

1) You have to be careful when you are creating secondary indexes that there are not already existing indexes which are similar,. The problem is that the optimiser can sometimes choose the wrong index when running other transactions, because of your index. This can lead to performance problems.

2) The more indexes you have on a table, the longer modification (insert, delete) operations take. This does not usually lead to performance issues, but is something to be aware of - i.e. don't create unnecessary indexes.

[former_member186746]

Hi,

In addition to the replies. I will add the following

Please check if archiving is active in your system and if not initiate the discussion on it about residence time, etc.

Kind regards, Rob Dielemans

[Former Member]

You haven't mentioned which "non-key" fields you want to use. Many large SAP tables have secondary index tables already created for them. Have you checked to see if this is the case here?

Rob

[Former Member]

Agreeing to what others are saying, I would add this - How about extracting MSEG data (into an internal table) using the index keys that are available, and then filtering out your undesired records from the internal table while LOOPing through it?

-Amit.