Create new index

[Former Member]

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

[RichHeilman]

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

[Former Member]

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

[RichHeilman]

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]

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]

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]

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

Did OSS recommend the new index?

Rob

[Former Member]

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

[Former Member]

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

Rob

[Former Member]

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

Rob

[Former Member]

Yes you are right.

Alia

[Former Member]

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

rob

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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]

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.