topic Re: Update databse from internal table statement not using index in Application Development and Automation Discussions

Update databse from internal table statement not using index

Former Member — Wed, 31 Dec 2008 01:21:27 GMT

Hi Guys,

We are updating a databse table from a file. The file has a couple of fields which have data different from what the database has (non-primary fields :). We upload the file data into an internal table and then update the database table from internal table. At a time, internal table is supposed to have 10,000 records. I did SQL trace and found that the update statement is not making use of the databse index.

Should not the update statement here be using the table index (for primary key)?

Regards,

Munish

Re: Update databse from internal table statement not using index

abdul_hakim — Wed, 31 Dec 2008 06:15:44 GMT

While Updating the DB table using the either INSERT/UPDATE/MODIFY the index will never be used.

You need to ensure that you are not violating the update procedure(Unique entries) for the key fields.

Index will be used while reading(SELECT).

Cheers,

Hakim

Re: Update databse from internal table statement not using index

Former Member — Wed, 31 Dec 2008 06:31:57 GMT

Hi,

Do like below.

TABLES:

DATA_BASE_TABLE_NAME.

DATA:

WA TYPE DATA_BASE_TABLE_NAME.

LOOP AT ITAB INTO WA.

MODIFY DATA_BASE_TABLE_NAME FROM WA.

IF SY-SUBRC EQ 0.

COMMIT.

ENDIF.

ENDLOOP.

Hope this helps you.

Regards,

Rama Chary.Pammi

Re: Update databse from internal table statement not using index

Former Member — Thu, 01 Jan 2009 21:23:22 GMT

Hello Munish,

Update DB table do not use index. Try to do the mass update always, ref below link-

http://help.sap.com/saphelp_NW04/helpdata/en/fc/eb3a94358411d1829f0000e829fbfe/content.htm

look for 'Overwriting Several Lines Using an Internal Table '

Thank You,

Nishikant.

Re: Update databse from internal table statement not using index

Former Member — Fri, 02 Jan 2009 10:25:55 GMT

> While Updating the DB table using the either INSERT/UPDATE/MODIFY the index will never be used.

How do you know that?

Did you ever see the times for a SELECT with index and without index?

So how do the update manage it to be fast?

Siegfried

Re: Update databse from internal table statement not using index

Former Member — Fri, 02 Jan 2009 16:16:02 GMT

... as often there are recommendations in this forum which makes me wonder, how people overestimate their knowledge!!!

Updates and Deletes do of course use indexes, as can be seen in the SQL Trace (use explain).

Inserts don't use indexes, because in many databases inserts are just done somewhere, But also with the INSERT, the primary key is the constraint for the uniqueness condition, duplicate keys are not allowed.

Coming to the original question, what is you actually coding for the update?

What is the table, which fields are in the internal table and what are the indexes?

Siegfried

Re: Update databse from internal table statement not using index

Former Member — Fri, 02 Jan 2009 17:17:46 GMT

Hi Siegfried,

Inserts into tables with a primary key (or unique) constraint(s) will use the primary key (or unique key) index(es) to check uniqueness. You may prove this by defining a primary key without an index (in DB2 this is possible) and execute some inserts. You'll see that inserts getting pretty soon very slow. Only for inserts into a tables without unique keys the insert does not access an index.

Regards

Ralph

Re: Update databse from internal table statement not using index

Former Member — Tue, 17 Feb 2009 00:44:55 GMT

Hi Guys,

A big thanks to all of you! Now the original issue - apologies for the confusion - update was infact using the priamry index. Earlier I was updating database in a lot of 10,000 records. It was taking more time and besides even if update failed for one record, all 10,000 records had to be updated again. It was not to be a periodic activity. So I recoded the code to update database one record at a time and then capture failed updates in a file (single record update fails) - Read 10000 records from the file at a time and then update database one record at a time. That did the job!

Thanks,

Munish