1 Reply Latest reply on Sep 20, 2011 1:57 PM by Marc Fielding

    Exadata Warehouse compression on table partition

    Saurabh Gupta-OC

      We have some huge non-partitioned tables and we want to apply exadata warehouse compression method on these tables. currently we are on exadata v2, oracle with linux env.

      As per my understanding we need to follow below steps:

      [A]- Partition table steps:
      1. create a new partion table
      2. apply dbms_redef for partiton the table
      3. dbms_gather stats for redef & partition process complete.

      -Compression table steps:
      1. create a new compressed table
      2. insert data with warehouse compression option.

      my doubts:
      1. Is the above steps are correct?
      2. what is the warehouse compression command for partition compression?
      3. Wt is the command to create a partitioned table with compressed option?
      4. Do I need to follow some other steps or I am missing any thing here?

      Please assist me in completing this task.

      Thank you all.

        • 1. Re: Exadata Warehouse compression on table partition
          Marc Fielding

          You have the same options for table reorganization under Exadata than any other Oracle 11gR2 environment. I would however suggest you do the partitioning and compression in one step, to avoid unnecessary steps. Perhaps something like this:

          - Extract the DDL for your existing table (using your own schema scripts or dbms_metadata.get_ddl)
          - Rename table, constraint, and index names to avoid naming conflicts
          - Change NOCOMPRESS in the DDL to the compression level you desire
          - Add partitioning to the DDL; you can use different compression levels for certain partitions if you like
          - Use DBMS_REDEFINITION to redefine the object
          - Rename indexes and constraints as necessary so the names make sense