cancel
Showing results for 
Search instead for 
Did you mean: 

creating and rebuilding index with oracle table

Former Member
0 Kudos
2,033

Hi

What is the best way to create and rebuild index with SAP -Oracle table ? I do understand that I can create index with SQL, with brspace and with SE11, but want to use the best method. Also want to know if there is any risk involved with online rebuilding of index. if there is any third party tool available for rebuilding index online without affecting the performance of the system

Any feedback will be appreciated

Al Mamun

Accepted Solutions (1)

Accepted Solutions (1)

volker_borowski2
Active Contributor
0 Kudos

Hi,

with brspace there is no way to create indexes, just to rebuild.

The "normal" way is to create the index in SE11 in development and do a transport.

However this is not alway what you want.

If you are faceing an actual performance problem in production, it might not be possible to

create the index via transport, because it is not done utilizing the ONLINE condition.

In this case you need to do sqlplus.

If the table in charge is very large, createing an index can run for hours and doing it via

transport will lock the table, which might no be a wanted situation.

In this case, you can create the index with sqlplus (parallel/online) before you do the

import to production. The conversion phase will find the index in place and all is ok.

If you only need the index for a short period and want to drop it again, -> sqlplus, online, parallel, nologging

I use around 25 indexes for executing BDLS after systemcopy or clienttranport.

It takes around 35 minutes to create them (parallel 12-24), but it cuts down BDLS from 20+hours to 45 minutes.

After this I drop them again.

So it depends on what is required.

But the first option is the one to go in general.

Volker

Former Member
0 Kudos

Hi Volker ,

Thanks for your reply. What I understand from your reply- to avoid the transport request ( with new index ) table locking the during import, following steps can be followed as below :

1. First I create index with SE11 with transport request in development

2. Create index with sqlplus (parallel on line) in production database

3. Move the transport to production

i.e. the transport request will find the index on production and it will take care, it will happen on line and there will be no table locking.

Please confirm if my understanding is right

Regards

Al Mamun

fidel_vales
Employee
Employee
0 Kudos

Hi,

Yes, and do you know that is explained in the first of the notes I provided you?

Exactly that.

Thanks for reading them, you will see that, some times, the notes confirm the information you get here. And some times you can find information on them faster than positing here

My 2 cents

Regards

Fidel

volker_borowski2
Active Contributor
0 Kudos

> 1. First I create index with SE11 with transport request in development

> 2. Create index with sqlplus (parallel on line) in production database

> 3. Move the transport to production

I'rather go:

1. First I create index with SE11 with transport request in development

1.1 use dbms_metadata.get_ddl to get create index statement from dev

1.2 adjust as needed (owner, tablespace, online,parallel, ...)

> 2. Create index with sqlplus (parallel on line) in production database

Wait for a suitable time slot to apply the transport. Allthough if the index is in

place, you will not face a conversion, but it might happen, the the table gets activated/re-activated.

In this case, some re-compiling of programs might occur which will result in dumps,

like LOAD_PROGRAM_LOST if these programs are active,

so you should always find a suitable slot for importing DDIC objects.

> 3. Move the transport to production

Volker

And yes, Fidel is correct, these notes do help in addition.

Answers (1)

Answers (1)

fidel_vales
Employee
Employee
0 Kudos

but want to use the best method.

there is no such thing as the "best" it will depend on what do you want.

There are a lot of SAP notes addressing this issue, why don't you read them?

334224 Important notes for creating indexes

771929 FAQ: Index fragmentation

332677 Rebuilding fragmented indexes

541538 FAQ: Reorganization

682926 Composite SAP note: Problems with "create/rebuild index"