topic Re: Creating index and changing the table buffer in Application Development and Automation Discussions

Creating index and changing the table buffer

Former Member — Thu, 04 Oct 2012 11:38:13 GMT

Hi all

Will changing the table buffer or creating index in a able have any impact in the same table in production when transporting the changes.

We have abou 3.000.000 records in the table in the production system , and we need to create an index and fully buffer it to improve performance, but I am worried about the data being deleted.

Do we have yo use madat when creating index or only the field name

Thanks.

Re: Creating index and changing the table buffer

sandeep_katoch — Thu, 04 Oct 2012 11:50:23 GMT

Hi Malu,

You can follow this approach

Create index at OS level in development then create index in SAP data dictionary level test it.

If it works fine no need to transport the changes.

You can follow the same appraoch in production but please remember to create an index at OS level first then create it in DDIC.

We have to only use the fieldname to create index.

Rgds,

Sandeep Katoch

Re: Creating index and changing the table buffer

Former Member — Thu, 04 Oct 2012 12:27:02 GMT

HI Sandeep

Thank you for your quick answer

I am not sure I understood the approach , how to create index at OS level and DDIC.

I have created one index in se11 directlly in the table in one client and tried it in another client , the table does not seem to be affected , the data is still there , but I am not sure about transporting to production

Re: Creating index and changing the table buffer

sandeep_katoch — Thu, 04 Oct 2012 16:27:14 GMT

Hi Malu,

Ask someone from your basis team to create index at OS level (DB Level) then only the index will work . once the index is created at DB level create index in DDIC.

Follow same process in production too but please check in development that it is working fine.

Rgds,

sandeep katoch

Re: Creating index and changing the table buffer

schneidertho — Thu, 04 Oct 2012 18:42:27 GMT

Hi Malu, Hi Sandeep,

I have some doubts with the above approach for several reasons:

buffering tables (on the application server) is advisable for comparatively small tables. 3 mio entries are quite a lot from my perspective
as far as I recall the table buffers are organized according to the primary key. Hence I don't think that a combination of buffering and secondary key is useful
client field in index: I don't recall that I have ever created an index without MANDT. But I agree that leaving it out should not cause any trouble when the fields are unique cross-client. Example:

Index 1: MANDT, GUID
Index 2: GUID -> should be ok to leave out the MANDT here

I don't understand the suggestion to create the index first on DB level and then in DDIC. What is the advantage?

I would create an index on such a table during a time with low system load, since the database might lock the table during index creation.

Best regards

Thorsten

Re: Creating index and changing the table buffer

sandeep_katoch — Fri, 05 Oct 2012 04:25:13 GMT

Hi Thorsten,

Yes you are right we should create an index during low system load only.

But our approach was according to the table as he has suggested is very big and having so many entries that was why I recommended him to first create it on DB level and then on SAP because as far as my knowledge this saves the lock time on table.

Second point regarding usage of MANDT that I written because madat was wrongly understood by me so thanks for correction.

Rgds,

Sandeep Katoch

Re: Creating index and changing the table buffer

Former Member — Fri, 05 Oct 2012 06:51:24 GMT

Thank you guys for the god discussion

I will leav the buffering and keep the index , but my question is still about any impact in the table in production system when transporting the index

Thanks.

Re: Creating index and changing the table buffer

HermannGahm — Fri, 05 Oct 2012 07:30:43 GMT

Hi,

good idea to leave the buffering.

Regarding index creation on big tables in production systems:

Yes, it can be a good idea to create it on DB level (have a DBA do this for you) because there

you can specify options such as create index online , parallel, ...which will minimize the time and the risk of locks. If you create the index through SE11 or a transport usually the default syntax is used

(not online, not parallel).

The procedure is as follows:

create the index normally in SE14.
get the DDL statement (CREATE INDEX... ) for it e.g. from SE14 and the objectlog (maybe you need to first activate it) or use report RSORADDL (on ORACLE)
modify and Run the DDL in your production system on DB level (e.g. add online or parallelism)
transport the index (ddic definition)
transport log will contain a warning that the index exists already at db level but ddic will be imported

Many customers with big tabels / systems are doing it like this...

Kind regards,

Hermann

Re: Creating index and changing the table buffer

schneidertho — Fri, 05 Oct 2012 07:39:19 GMT

Hi Hermann,

thanks for explaining what it helps to do it on DB level. Now I understand the suggestion.

Cheers

Thorsten

Re: Creating index and changing the table buffer

Former Member — Fri, 05 Oct 2012 11:32:35 GMT

Thank you

closing