SAP BRSPACE Online Reorganizations

1. Which situations a table reorganization be useful ?

• Reduction of table size
• Cleanup of table fragmentation
• Change of table storage parameters (e.g. INITRANS)
• Move of table to a different tablespace
• Reduction of chained and migrated rows
• Reduction of number of allocated extents
• Transition from LONG to LOB columns (Oracle >= 10g)
• Reduction of hot spots on disk level
• Activation of Transparent Data Encryption
• Change of table structure

2. Which situations  a tablespace reorganization be useful ?

• Activation of LMTS
• Activation of ASSM
• Cleanup of freespace block corruption
• Reduction of database size
• Reduction of backup size
• Reduction of filesystem size
• Reduction of fragmentation
• Reduction of number of datafiles
• Increase of number of datafiles
• Transition to new tablespace layout
• Other change to tablespace layout
• Optimization of filesystem layout

3. How does a BRSPACE online reorganization work?

• Determination of CREATE commands for target table and indexes based on DBMS_METADATA.GET_DDL
• Creation of target table with naming convention <source_table>#$
• Export of source table CBO statistics based on DBMS_STATS.EXPORT_TABLE_STATS
• Copy of source table data to target table based on DBMS_REDEFINITION.START_REDEF_TABLE. This package will implicitly create a materialized view log MLOG$_<source_table> to track all changes that are performed on the source table while the reorganization is running.
• Creation of target indexes with naming convention <source_index>#$
• Import of source table CBO statistics to target table based on DBMS_STATS.IMPORT_TABLE_STATS
• Finalizing of the online reorganization using DBMS_REDEFINITION.FINISH_REDEF_TABLE. In this step all changes in the materialized log are applied to the target table and the names of source and target table are exchanged.
• The source table is dropped.
• The ?#$“ suffix of the target table indexes is removed.

4. What are the advantages of a BRSPACE Online Reorganization?

• It uses the advantages of DBMS_REDEFINITION:

          a. The reorganization can be performed in parallel to production operation.
              This means the table is accessible and records can be inserted / updated / deleted.

„          b. If anything goes wrong during the reorganization the source table still 

              exists unchanged and no restore / recovery is necessary.
          c.„ Compared to an offline reorganization via export / import all data has to be 

              copied only once and not twice

• „ It provides possibilities for client and server side parallelism in order to speed up the reorganization if sufficient system resources exist and the reorganization should finish within a specific time window.
• „ It has already proven to be a reliable reorganization tool at many customers.
• „ It performs a lot of useful activities implicitly that would require a lot of manual efforts and impose the risk of errors if DBMS_REDEFINITION is used directly.

5. What are the restrictions of a BRSPACE Online Reorganization?

• It is only available as of Oracle 9i.
• It cannot handle tables with LONG or LONG RAW columns. As of 10g it is possible to convert them during an online reorganization on the fly to LOB columns (if SAP >= 6.40 is used).
• Temporarily twice the space is needed because both source and target table and indexes exist in parallel.
• If a table has no primary key constraint (or no unique index with NOT NULL columns) a ROWID based online reorganization is necessary that requires overhead (ROWID index, ROWID column).
• The setup of the materialized view is an overhead that can negatively impact the performance of the reorganization of many very small tables.
• Structure changes on the source table and its indexes must not be performed during the reorganization (as they would be lost).

6. How is a normal BRSPACE Online Reorganization started?

• Table reorganization:

          brspace -f tbreorg -t <table_name>

• Tablespace reorganization:

          brspace -f tbreorg -s <source_tsp> -t "*" -n <target_tsp>

• Additional useful options exist, e.g.:

         a) -p <degree>

             => BRSPACE parallelism degree

         b) -e <degree>

             => PX parallelism degree

         c) -l <category>

             => Initial extent size category

         d) -i <target_ind_tsp>

             => Definition of target index tablespace (if different from target table tablespace)

7. Reference:

    Note 646681 and the BRSPACE online documentation.