This is in continuation of my previous blog http://scn.sap.com/people/muniraju.h/blog/2012/06/04/bdls-in-less-than-2-hours--part-3
Part - 4
Putting together all possible optimization and automation; below are the brief steps we articulated:-
1 | Put database in NOARCHIVELOG mode |
2 | Create PSAPBDLS tablespace of 5 GB |
3 | Enable parallelism for all tables invloved in BDLS |
4 | Enable parallellism for all indexes of the tables invloved in BDLS |
5 | Create bitmap indexes with PARALLEL NOLOGGING COMPUTE STATITICS for tables with total number of rows > 10000 |
6 | Calculate rows to be updated by BDLS |
7 | Build CTAS command lines with decode funtions |
8 | Perform coversion of tables where rows to update > 1000 using CTAS |
9 | Save original index definitions |
10 | Drop original indexes |
11 | Rename original table to old |
12 | Rename newly created tables to original |
13 | Recreate indexes |
14 | Verify and Drop old table |
15 | Run BDLS in SAP using report RBDLS450 in parallel (A*....Z*, etc) using BDC sessions. |
16 | Clean up |
17 | Drop bitmap indexes |
18 | Revert parallelism to original state for all tables and indexes |
19 | Drop function created, if any |
20 | Drop extra tables created, if any |
21 | Run update statistics |
22 | Put database back on ARCHIVELOG mode |
With automation and combining above optimization approach, BDLS was achieved in 1 hour and 25 minutes.
Activity | Time |
Create PSAPBDLS tablespace of 5 GB | 5 minutes |
Bitmap indexes | 30 Minutes |
CTAS for 15 tables | 15 Minutes |
BDLS run in parallel | 30 Minutes |
Cleanup Bitmap indexes, tablespace, etc | 5 minutes |
TOTAL | 1:25 hours |
You may also plan to create bitmap indexes on source system so that these indexes are already available on target system. This will save 30 minutes.
And, BDLS is achievable in less than 1 hour :cool:
