Q

Data compression explained

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?

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close