We are running an Oracle 9i data warehouse on the Sun Solaris operating system. I have a few questions on data compression. When you do 'Alter table move compress':
  1. How does this work -- do you need additinal resource like more space, etc.?
  2. Can this operation be parallelized?
  3. What happens if the machine dies halfway through the operation?

    Requires Free Membership to View

1) No, other than enough space to have two complete copies of the table no additional space is required. This option is only valid for tables with repeating values as Oracle only does inter-row compression not intra-row compression for tables.

In the case for an existing table where the blocksize would increase (due to addition of tokens to the block header) the block is not compressed so it is possible to have both compressed and uncompressed blocks as a result.

Also, once compressed you cannot alter a tables structure (add, drop columns, resize columns, etc.) and as a final limit, there are some column types that will not be affected by compression. Table compression works for all data types except:

  • all variants of LOBs
  • data types derived from LOBs, such as VARRAYs stored out of line
  • XML data type stored in a CLOB

2) Yes, I believe so, I have not seen any restriction statements preventing this. Here is a test case:

 SQL> alter table test_comp compress move;

 Table altered.

 SQL> select * from V$PQ_SYSSTAT;

 STATISTIC                           VALUE
 ------------------------------ ----------
 Servers Busy                            0
 Servers Idle                            0
 Servers Highwater                       0
 Server Sessions                         0
 Servers Started                         0
 Servers Shutdown                        0
 Servers Cleaned Up                      0
 Queries Initiated                       0
 DML Initiated                           0
 DDL Initiated                           0
 DFO Trees                               0

 STATISTIC                           VALUE
 ------------------------------ ----------
 Sessions Active                         0
 Local Msgs Sent                         0
 Distr Msgs Sent                         0
 Local Msgs Recv'd                       0
 Distr Msgs Recv'd                       0

 16 rows selected.

 SQL> drop table test_comp;

 Table dropped.

 SQL> create table test_comp parallel (degree 2) as select * from
 dba_objects;

 Table created.

 SQL> select * from V$PQ_SYSSTAT;

 STATISTIC                           VALUE
 ------------------------------ ----------
 Servers Busy                            0
 Servers Idle                            8
 Servers Highwater                       8
 Server Sessions                         8
 Servers Started                         8
 Servers Shutdown                        0
 Servers Cleaned Up                      0
 Queries Initiated                       0
 DML Initiated                           0
 DDL Initiated                           1
 DFO Trees                               3

 STATISTIC                           VALUE
 ------------------------------ ----------
 Sessions Active                         0
 Local Msgs Sent                      1903
 Distr Msgs Sent                         0
 Local Msgs Recv'd                    2779
 Distr Msgs Recv'd                       0

 16 rows selected.

 SQL> alter table test_comp compress move parallel (degree 2);

 Table altered.

 SQL> select * from V$PQ_SYSSTAT;

 STATISTIC                           VALUE
 ------------------------------ ----------
 Servers Busy                            0
 Servers Idle                            8
 Servers Highwater                       8
 Server Sessions                        10
 Servers Started                         8
 Servers Shutdown                        0
 Servers Cleaned Up                      0
 Queries Initiated                       0
 DML Initiated                           0
 DDL Initiated                           2
 DFO Trees                               4

 STATISTIC                           VALUE
 ------------------------------ ----------
 Sessions Active                         0
 Local Msgs Sent                      1973
 Distr Msgs Sent                         0
 Local Msgs Recv'd                    2849
 Distr Msgs Recv'd                       0

 16 rows selected.

3) As with any rebuild/move operation, the original table is not removed or altered until the operation is successful. The temporary table used as the target for the rebuild is removed and any transactions in flight to support the rebuild/move are rolled back, either as a part of shutdown or when the instance restarts.

This was first published in April 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: