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