oracle 11g 新特性 表壓縮

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create user paul identified by paul;

使用者已建立。

SQL> grant connect,resource,dba to paul;

授權成功。

SQL> conn paul/paul
已連線。
SQL>
SQL>
SQL>
SQL> CREATE TABLE test_tab_1 (
  2    id            NUMBER(10)    NOT NULL,
  3    description   VARCHAR2(50)  NOT NULL,
  4    created_date  DATE          NOT NULL
  5  )
  6  COMPRESS FOR ALL OPERATIONS;

表已建立。

SQL> CREATE TABLE test_tab_2 (
  2    id            NUMBER(10)    NOT NULL,
  3    description   VARCHAR2(50)  NOT NULL,
  4    created_date  DATE          NOT NULL
  5  )
  6  PARTITION BY RANGE (created_date) (
  7    PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY
')) COMPRESS,
  8    PARTITION test_tab_q2 VALUES LESS THAN (TO_DATE('01/04/2008', 'DD/MM/YYYY
')) COMPRESS FOR DIRECT_LOAD OPERATIONS,
  9    PARTITION test_tab_q3 VALUES LESS THAN (TO_DATE('01/07/2008', 'DD/MM/YYYY
')) COMPRESS FOR ALL OPERATIONS,
 10    PARTITION test_tab_q4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS
 11  );

表已建立。

SQL> SELECT table_name, compression, compress_for FROM user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
TEST_TAB_2
TEST_TAB_1                     ENABLED  OLTP

SQL> SELECT table_name, partition_name, compression, compress_for FROM user_tab_
partitions;

TABLE_NAME                     PARTITION_NAME                 COMPRESS
------------------------------ ------------------------------ --------
COMPRESS_FOR
------------
TEST_TAB_2                     TEST_TAB_Q1                    ENABLED
BASIC

TEST_TAB_2                     TEST_TAB_Q2                    ENABLED
BASIC

TEST_TAB_2                     TEST_TAB_Q3                    ENABLED
OLTP

TABLE_NAME                     PARTITION_NAME                 COMPRESS
------------------------------ ------------------------------ --------
COMPRESS_FOR
------------
TEST_TAB_2                     TEST_TAB_Q4                    DISABLED

 

SQL>
SQL> SELECT table_name, partition_name, compression, compress_for FROM user_tab_
partitions;

TABLE_NAME                     PARTITION_NAME                 COMPRESS
------------------------------ ------------------------------ --------
COMPRESS_FOR
------------
TEST_TAB_2                     TEST_TAB_Q1                    ENABLED
BASIC

TEST_TAB_2                     TEST_TAB_Q2                    ENABLED
BASIC

TEST_TAB_2                     TEST_TAB_Q3                    ENABLED
OLTP

TABLE_NAME                     PARTITION_NAME                 COMPRESS
------------------------------ ------------------------------ --------
COMPRESS_FOR
------------
TEST_TAB_2                     TEST_TAB_Q4                    DISABLED