Compress table

oracle从9i r2开始推出了compress table特性，compress table可以使同样的数据占用更少的空间，非常适用于历史数据的存储。
下文演示了compress table在分区表中的应用。
1.查看分区表中分区的信息
SQL> select s.segment_name,
  2         s.partition_name,
  3         s.bytes / 1024 / 1024,
  4         s.blocks,
  5         p.compression
  6    from dba_segments s, dba_tab_partitions p
  7   where s.segment_name = p.table_name
  8     and s.partition_name = p.partition_name
  9     and s.segment_name = 'bill_orders'
10     and s.partition_name = 'bill_orders_20111122';


SEGMENT_NAME                   PARTITION_NAME                 S.BYTES/1024/1024     BLOCKS COMPRESS
------------------------------ ------------------------------ ----------------- ---------- --------
bill_orders                 bill_orders_20111122                      942     120576 DISABLED


2.查看本地分区索引的信息
SQL> select s.segment_name,s.partition_name,s.bytes/1024/1024,s.blocks
  2    from dba_segments s
  3   where s.segment_name in
  4         ('ind_bill_orders_id', 'ind_bill_orders_end_time')
  5     and s.partition_name = 'bill_orders_20111122';


SEGMENT_NAME                   PARTITION_NAME                 S.BYTES/1024/1024     BLOCKS
------------------------------ ------------------------------ ----------------- ----------
ind_bill_orders_id          bill_orders_20111122                      223      28544
ind_bill_orders_end_time    bill_orders_20111122                      128      16384


SQL> select pi.index_name,pi.partition_name,pi.status,pi.composite
  2    from dba_ind_partitions pi
  3   where pi.index_name in
  4         ('ind_bill_orders_id', 'ind_bill_orders_end_time')
  5     and pi.partition_name = 'bill_orders_20111122';


INDEX_NAME                     PARTITION_NAME                 STATUS   COM
------------------------------ ------------------------------ -------- ---
ind_bill_orders_id          bill_orders_20111122        USABLE   NO
ind_bill_orders_end_time    bill_orders_20111122        USABLE   NO


3.压缩bill_orders_20111122这个分区
SQL> alter table tlbb.bill_orders move partition bill_orders_20111122 compress;


Table altered.
4.根据以下信息，可以看到bill_orders_20111122这个分区大小减少到640m，BLOCK数据从120576减少到81920
SQL> select s.segment_name,
  2         s.partition_name,
  3         s.bytes / 1024 / 1024,
  4         s.blocks,
  5         p.compression
  6    from dba_segments s, dba_tab_partitions p
  7   where s.segment_name = p.table_name
  8     and s.partition_name = p.partition_name
  9     and s.segment_name = 'bill_orders'
10     and s.partition_name = 'bill_orders_20111122';


SEGMENT_NAME                   PARTITION_NAME                 S.BYTES/1024/1024     BLOCKS COMPRESS
------------------------------ ------------------------------ ----------------- ---------- --------
bill_orders                 bill_orders_20111122                      640      81920 ENABLED
SQL> select pi.index_name,pi.partition_name,pi.status,pi.composite
  2    from dba_ind_partitions pi
  3   where pi.index_name in
  4         ('ind_bill_orders_id', 'ind_bill_orders_end_time')
  5     and pi.partition_name = 'bill_orders_20111122';


INDEX_NAME                     PARTITION_NAME                 STATUS   COM
------------------------------ ------------------------------ -------- ---
ind_bill_orders_id          bill_orders_20111122        UNUSABLE NO
ind_bill_orders_end_time    bill_orders_20111122        UNUSABLE NO
压缩完后，可以看到以上索引状态为UNUSABLE需要重新rebuild索引


5.rebuild索引
SQL> alter index tlbb.ind_bill_orders_end_time rebuild partition bill_orders_20111122;


Index altered.


SQL> alter index tlbb.ind_bill_orders_id rebuild partition bill_orders_20111122;


Index altered.


SQL> select pi.index_name,pi.partition_name,pi.status,pi.composite
  2    from dba_ind_partitions pi
  3   where pi.index_name in
  4         ('ind_bill_orders_id', 'ind_bill_orders_end_time')
  5     and pi.partition_name = 'bill_orders_20111122';


INDEX_NAME                     PARTITION_NAME                 STATUS   COM
------------------------------ ------------------------------ -------- ---
ind_bill_orders_id          bill_orders_20111122        USABLE   NO
ind_bill_orders_end_time    bill_orders_20111122        USABLE   NO




SQL> l
  1  select sess.sid,
  2         sess.serial#,
  3         lo.oracle_username,
  4         lo.os_user_name,
  5         ao.object_name,
  6         lo.locked_mode
  7    from v$locked_object lo, dba_objects ao, v$session sess
  8   where ao.object_id = lo.object_id
  9*    and lo.session_id = sess.sid
SQL> /


       SID    SERIAL# ORACLE_USERNAME      OS_USER_NAME                   OBJECT_NAME                    LOCKED_MODE
---------- ---------- -------------------- ------------------------------ ------------------------------ -----------
      4794      11015 SYS                  oracle                         bill_orders                           6
      4794      11015 SYS                  oracle                         bill_orders                           3
     
     
     
以下是之前写的一个批量压缩的脚本，可以修改后使用 
     
declare
  cursor cur_part is
    select s.segment_name,
           s.owner,
           s.partition_name,
           s.bytes / 1024 / 1024,
           s.blocks,
           p.compression
      from dba_segments s, dba_tab_partitions p
     where s.segment_name = p.table_name
       and s.partition_name = p.partition_name
       and s.segment_name = 'TLBB_SERVER_LOG'
       and p.compression = 'DISABLED'
       and s.partition_name like 'SERVER_LOG_201111%';
  cursor cur_inds is
    select i.index_name, i.owner
      from dba_indexes i
     where i.owner = 'TLBB'
       and i.table_name = 'TLBB_SERVER_LOG';
begin
  for part in cur_part loop
    execute immediate 'alter table ' || part.owner || '.' ||
                      part.segment_name || ' move partition ' ||
                      part.partition_name || ' compress';
    dbms_output.put_line('alter table ' || part.owner || '.' ||
                         part.segment_name || ' move partition ' ||
                         part.partition_name || ' compress;');
    for ind in cur_inds loop
      execute immediate 'alter index ' || ind.owner || '.' ||
                        ind.index_name || ' rebuild partition ' ||
                        part.partition_name;
      dbms_output.put_line('alter index ' || ind.owner || '.' ||
                           ind.index_name || ' rebuild partition ' ||
                           part.partition_name || ';');
    end loop;
  end loop;
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26390465/viewspace-1771463/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26390465/viewspace-1771463/