Create new table Index?

[Former Member]

Hi Friends,

I have a question on creating an index in the table.

Let me describe what I am trying to do here.

I am calling a function module SAP_WAPI_WORKITEMS_TO_OBJECT to get the workitem attached to the Invoice and I notices it is taking some time using SQL trace. The table is SWW_WI2OBJ and it uses the following fields to fetch a record..'client, catid, instid, typeid, removed, top_wi_id'.

Hence I went to the table and created an index similar to the above. But when I trace it again, it still picks up the Unique index. Is there any way I can make this index as unique? or how do I make this work. I tried to make this Index Unique but am getting an error.

Any help is greatly appreciated.

Thanks

[Former Member]

Hi Friends,

I have a question on creating an index in the table.

Let me describe what I am trying to do here.

I am calling a function module SAP_WAPI_WORKITEMS_TO_OBJECT to get the workitem attached to the Invoice and I notices it is taking some time using SQL trace. The table is SWW_WI2OBJ and it uses the following fields to fetch a record..'client, catid, instid, typeid, removed, top_wi_id'.

Hence I went to the table and created an index similar to the above. But when I trace it again, it still picks up the Unique index. Is there any way I can make this index as unique? or how do I make this work. I tried to make this Index Unique but am getting an error.

Any help is greatly appreciated.

Thanks

[Former Member]

In a good database design, all indexes should be unique.

Do you mean that in SQL trace after creating your custom index, the program is still using the core/primary key index ?

[Former Member]

yes..it is still using the core unique index.

since this is within the function module..I have no control. Hence I traced the report and checked the select statement and created an index with those fields in the same order.

[Former Member]

Huh??

Why create a custom index that is exactly the same as the primary key index? What benefit are you expecting from this?

I am confused now.

[Former Member]

Hi Rachana,

In your custom code you can explicitely specify the index to be used.

Have a look at the following code.

SELECT COUNT(*)
FROM crmd_orderadm_h
WHERE process_type IN git_t_type
AND object_type = gc_subobject
AND object_id IN git_t_no
%_HINTS ORACLE 'INDEX("CRMD_ORDERADM_H" "CRMD_ORDERADM_H~OID")'.

Here I am using the index OID defined for the table CRMD_ORDERADM_H.

<b>Reward points if it helps.</b>

Regards,

Amit Mishra

[Former Member]

HI

see this example -->

REPORT z_generic_test_program .

TABLES: csks.

START-OF-SELECTION.

SELECT * UP TO 10 ROWS FROM csks

WHERE kokrs <> space AND

kostl <> space

%_HINTS ORACLE 'index(<b>csks"J</b>")'.

WRITE: / csks.

ENDSELECT.

after you create the Index in the SAP database, the index need to be there in the Oracle ....

Thanks

Sudheer

[Former Member]

If your goal is make the program run faster... then you must identify what fields are passed into the SELECT statement. If all fields in the primary key are filled in, then you can not make the SQL any faster. At this point, you must look to running "stats" on the table, having your DBAs look at DB server performance, etc.

If all of the primary key fields are not filled in, then you should analyze what fields are available to the SELECT stmnt and create a new custom index on those fields. This will allow the DB Optimizer to consider using your new index (since you can not change the program to use the HINT variant on the SELECT statement).

Don't forget those points.

[Former Member]

Well there already is an index on most of the fields in this select. It seems unlikely that creating an index with additional fields will help much. I'd look at the calling program and see if you can simplify things there.

Also have alook at notes 903665 and 427727. They both deal with performance issues with this FM.

Rob

Message was edited by: Rob Burbank

[Former Member]

Correct. Creating an index with "additional" fields will not help.

Creating an index on the fields that are available when the SELECT executes will help.

Remember to check if the primary key is full loaded. If it is... then you must get DBAs involved.

If not, create the new index with fields that are available when the SELECT executes.