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: 

Create new index

Former Member
0 Kudos
213

Hi all,

How to create new index in table vbfa? and how to use this new index in select statement?

I have problem in tuning performance for some reports and SAP has recommended us to create new index.

Thanks alots

Alia

1 ACCEPTED SOLUTION

Former Member
0 Kudos
111

HI Alia,

You can create index from SE11.

Few Imp points :

1 ) For an index to create , You have the necessary user authorizations and the user attribute NOT EXCLUSIVE.

2) Indexes provide access to the table data using non-key columns plus If duplicate keys have already been inserted in the table, it is no longer possible to create the index. You must identify the incorrect keys and delete them.

3) Having Multiple index on the same table will also effect the performance.

You can create your own index also keeping in mind the above points.

Check this may also help you

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21f0f0446011d189700000e8322d00/content.htm

Cheers

Sunny

Rewrd points, if found helpful

16 REPLIES 16

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos
111

You can create your index via SE11, enter the table name, click change, choose Go To, Indexes. Here create your index with the key fields that you want. To use the index, your select statement WHERE clause, you must have the key fields of the index in the order that they appear in the index. The "optimizer" will choose the index depending on your fields of the WHERE clause.

One thing to remember is that when you create indexes for tables, the update or insert of these tables may have a slower response then before, I for one have never seen a big problem as of yet.

Regards,

Rich Heilman

0 Kudos
111

Hi all,

Thanks a lot for the input. One question again that I am unclear. Report from SAP told us that : 'DB response time is OK and Total response time is high due to high wait time'.

What does it mean? I thought we are just concerned in DB response time when we want to tune program.

Thanks

Alia

0 Kudos
111

Maybe that means, that the execution of the statement isn't bad, but the system load prevents it from being executed in a timely fashion.

Please make sure to award points for any helpful answers that you may received and mark as solved when solved completely. Thanks.

Regards,

Rich Heilman

Former Member
0 Kudos
112

HI Alia,

You can create index from SE11.

Few Imp points :

1 ) For an index to create , You have the necessary user authorizations and the user attribute NOT EXCLUSIVE.

2) Indexes provide access to the table data using non-key columns plus If duplicate keys have already been inserted in the table, it is no longer possible to create the index. You must identify the incorrect keys and delete them.

3) Having Multiple index on the same table will also effect the performance.

You can create your own index also keeping in mind the above points.

Check this may also help you

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21f0f0446011d189700000e8322d00/content.htm

Cheers

Sunny

Rewrd points, if found helpful

ferry_lianto
Active Contributor
0 Kudos
111

Hi Alia,

Path: SE11 -> Enter table name -> Display -> Indexes.

and follow this link which will show you step by step.

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb47446011d189700000e8322d00/content.htm

Hope this will help.

Regards,

Ferry Lianto

Former Member
0 Kudos
111

I'd like to see what sort of select you're doing. Could you paste your code?

Did OSS recommend the new index?

Rob

0 Kudos
111

Hi Rob.

Yes they recommend us to create new index and following is the code:

SELECT vbelv posnv vbeln posnn vbtyp_n vbtyp_v

INTO TABLE i_vbfa2 FROM vbfa

FOR ALL ENTRIES IN i_data WHERE vbeln = i_data-vbeln AND posnn = i_data-posnr AND vbtyp_v = 'M'.

Alia

0 Kudos
111

Before creating an index, have a look at OSS note 185530.

Rob

0 Kudos
111

It looks like your trying to retrieve invoice documents. Is that correct?

Rob

0 Kudos
111

Yes you are right.

Alia

0 Kudos
111

Couldn't you look at the invoices to determine what the reference document is?

rob

0 Kudos
111

Hi Rob.

I want to get invoice no based on sales order so the ref.doc is sales order.

Is there something wrong with the select?

thanks

Alia

0 Kudos
111

It's been quite a while since I looked at SD, but if you need the sales document based on the billing document, I think you should be selecting from VBRP, not VBFA. If you need the billing document based on the sales document, then yes, you should look at VBFA but selecting on VBELV not VBELN.

You should always try to avoid adding an index to an SAP table - particulary ones like VBFA. It's an extremely large table and in addition to slowing down update processes, the index will take up a large portion of the table space for the table. So, you would also have to talk to your DBAs about this and see if they have anything to say about it.

Rob

0 Kudos
111

Hi Rob,

I have talked to the DBA guy and they ask me to go ahead what SAP recommended to us.

Question again, If I have two indexes (for eq: Z01 and Z02), how to use the index Z02 in SQL statement?

thank you

Alia

0 Kudos
111

Your database will decide the best index to use based on the select statement and the number of records. It's best not to use hints in a production environment.

Rob

Former Member
0 Kudos
111

Hello Alia,

U can use the following option....

SELECT x y z

INTO TABLE i_tab

FROM ZXYZ

WHERE abc = w_setclass

AND def = w_setname

%_HINTS ORACLE 'INDEX("&TABLE&" "SETHEADER" "SETHEADER~2")'.

Look at the SAP note 129385.