on 01-29-2012 12:34 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
> 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.
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"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.