topic Re: Create new index in Application Development and Automation Discussions

Create new index

Former Member — Thu, 06 Apr 2006 01:45:58 GMT

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

Re: Create new index

RichHeilman — Thu, 06 Apr 2006 01:49:58 GMT

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

Re: Create new index

Former Member — Thu, 06 Apr 2006 02:08:27 GMT

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

Re: Create new index

Former Member — Thu, 06 Apr 2006 02:18:48 GMT

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

Re: Create new index

Former Member — Thu, 06 Apr 2006 02:26:32 GMT

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

Re: Create new index

RichHeilman — Thu, 06 Apr 2006 02:32:18 GMT

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

Re: Create new index

Former Member — Thu, 06 Apr 2006 02:53:58 GMT

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

Did OSS recommend the new index?

Rob

Re: Create new index

Former Member — Thu, 06 Apr 2006 03:11:54 GMT

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

Re: Create new index

Former Member — Thu, 06 Apr 2006 03:13:01 GMT

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

Rob

Re: Create new index

Former Member — Thu, 06 Apr 2006 03:24:57 GMT

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

Rob

Re: Create new index

Former Member — Thu, 06 Apr 2006 03:31:45 GMT

Yes you are right.

Alia

Re: Create new index

Former Member — Thu, 06 Apr 2006 03:35:21 GMT

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

rob

Re: Create new index

Former Member — Thu, 06 Apr 2006 04:12:56 GMT

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

Re: Create new index

Former Member — Thu, 06 Apr 2006 13:40:31 GMT

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

Re: Create new index

Former Member — Fri, 07 Apr 2006 08:12:17 GMT

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

Re: Create new index

Former Member — Fri, 07 Apr 2006 09:35:08 GMT

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.

Re: Create new index

Former Member — Fri, 07 Apr 2006 13:32:30 GMT

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