對錶資料壓縮compress的修改和查詢

raysuen發表於2017-06-13
#1 將錶轉換為basic壓縮表
alter table emp move compress basic;

#2 將錶轉換為OLTP壓縮表
alter table emp move compress for oltp;

#3 將一個分割槽轉換為basic壓縮表
alter table table_name move partitoin partition_name compress basic;

#4 將一個分割槽轉換為oltp壓縮表
alter table table_name move partitoin partition_name compress for oltp;

#5 修改一個分割槽的現有壓縮演算法，修改之後只對新記錄有效
alter table table_name modify partition,partiton_name compress for oltp;

#檢視記錄是壓縮記錄
select decode(
    DBMS_COMPRESSION.GET_COMPRESSION_TYPE(
        OWNNAME => 'SCOTT',
        TABNAME => 'EMP',
        ROW_ID => 'AAAVVhAAEAAAACzAAC'),
    1,'No Compression',
    2,'Basic or OLTP Compression for Query High',
    3,'Hybrid Columnar Compression for Query Low',
    4,'Hybrid Columnar Compression for Archive High',
    5,'Hybrid Columnar Compression for Archive Low',
    'Unknown Compression Type') compression_type
from dual;
SQL> desc dbms_compression
PROCEDURE GET_COMPRESSION_RATIO
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
SCRATCHTBSNAME                 VARCHAR2                IN
OWNNAME                        VARCHAR2                IN
TABNAME                        VARCHAR2                IN
PARTNAME                       VARCHAR2                IN
COMPTYPE                       NUMBER                  IN
BLKCNT_CMP                     BINARY_INTEGER          OUT
BLKCNT_UNCMP                   BINARY_INTEGER          OUT
ROW_CMP                        BINARY_INTEGER          OUT
ROW_UNCMP                      BINARY_INTEGER          OUT
CMP_RATIO                      NUMBER                  OUT
COMPTYPE_STR                   VARCHAR2                OUT
SUBSET_NUMROWS                 NUMBER                  IN     DEFAULT
FUNCTION GET_COMPRESSION_TYPE RETURNS NUMBER
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
TABNAME                        VARCHAR2                IN
ROW_ID                         ROWID                   IN
PROCEDURE INCREMENTAL_COMPRESS
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2(30)            IN
TABNAME                        VARCHAR2(128)           IN
PARTNAME                       VARCHAR2(30)            IN
COLNAME                        VARCHAR2                IN
DUMP_ON                        NUMBER                  IN     DEFAULT
AUTOCOMPRESS_ON                NUMBER                  IN     DEFAULT
WHERE_CLAUSE                   VARCHAR2                IN     DEFAULT

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28572479/viewspace-2140691/，如需轉載，請註明出處，否則將追究法律責任。

相關文章