- How does this work -- do you need additinal resource like more space, etc.?
- Can this operation be parallelized?
- 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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation