This discussion is archived
2 Replies Latest reply: Nov 22, 2012 8:00 PM by sagargole RSS

Table compression

706234 Newbie
Currently Being Moderated
Hi,

I have two question about compression.

Statement: ALTER TABLE X MODIFY PARTITION 2007JAN COMPRESS FOR QUERY HIGH; will make that new rows, added to the partition will automaticly compressed? If not, where to check that new rows will be compressed or not.

Statement ALTER TABLE X MOVE PARTITION 2007JAN COMPRESS FOR QUERY HIGH; will compress existing data in the table, but it will make that now rows will compressed automaticly after adding?
  • 1. Re: Table compression
    gsalem Explorer
    Currently Being Moderated
    hi,
    Statement: ALTER TABLE X MODIFY PARTITION 2007JAN COMPRESS FOR QUERY HIGH; will make that new rows, added to the partition will automaticly compressed? If not, where to check that new rows will be compressed or not.
    when you modify the compression attribute of any segment, new data will be compressed, old one remains as is. Remember that data is compressed (HCC or BASIC), only upon direct insert.

    Statement ALTER TABLE X MOVE PARTITION 2007JAN COMPRESS FOR QUERY HIGH; will compress existing data in the table, but it will make that now rows will compressed automaticly after adding?

    existing data will get compressed, as well as any new data added via direct insert.

    rgds
  • 2. Re: Table compression
    sagargole Newbie
    Currently Being Moderated
    New rows will be automatically compressed if they are loaded using direct inserts. Secondly, in case you update any rows in the compressed partition or table the compression level for those rows will be modified from EHCC compression type to OLTP compression type.

    You can use the following query to check with what level of compression a particular row has been compressed with. You will require the rowid of that particular row.

    select decode(
         DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( <SCHEMA_NAME>, <TABLE_NAME>, <ROWID>),
         1, 'No Compression',
         2, 'Basic/OLTP Compression',
         4, 'HCC Query High',
         8, 'HCC Query Low',
         16, 'HCC Archive High',
         32, 'HCC Archive Low',
         'Unknown Compression Level') compression_type
         from dual;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points