表的压缩

压缩表

  缺点：在dml操作加载数据时消耗cpu

 优点：，节省空间，加载数据时减少io

表的压缩主要用在 决策系统，在线事物处理，归档系统，你可以指定压缩表空间，压缩表，压缩分区

压缩表允许下列操作

• Single-row or array inserts and updates

• The following direct-path INSERT methods:

  • Direct path SQL*Loader

  • CREATE TABLE AS SELECT statements

  • Parallel INSERT statements

  • INSERT statements with an APPEND or APPEND_VALUES hint

压缩的方法：

Table 20-1 Table Compression Methods

Table Compression Method	Compression Level	CPU Overhead	Applications	Notes
Basic compression	High	Minimal	DSS	None.
OLTP compression	High	Minimal	OLTP, DSS	None.
Warehouse compression (Hybrid Columnar Compression)	Higher	Higher	DSS	The compression level and CPU overhead depend on compression level specified (LOW or HIGH).
Archive compression (Hybrid Columnar Compression)	Highest	Highest	Archiving	The compression level and CPU overhead depend on compression level specified (LOW or HIGH).

压缩特点：

Table 20-2 Table Compression Characteristics

Table Compression Method	CREATE/ALTER TABLE Syntax	Direct-Path INSERT	Notes
Basic compression	COMPRESS [BASIC]	Rows are compressed with basic compression.	COMPRESS and COMPRESS BASIC are equivalent. Rows inserted without using direct-path insert and updated rows are uncompressed.
OLTP compression	COMPRESS FOR OLTP	Rows are compressed with OLTP compression.	Rows inserted without using direct-path insert and updated rows are compressed using OLTP compression.
Warehouse compression (Hybrid Columnar Compression)	COMPRESS FOR QUERY [LOW|HIGH]	Rows are compressed with warehouse compression.	This compression method can result in high CPU overhead. Updated rows and rows inserted without using direct-path insert are stored in row format instead of column format, and thus have a lower compression level.
Archive compression (Hybrid Columnar Compression)	COMPRESS FOR ARCHIVE [LOW|HIGH]	Rows are compressed with archive compression.	This compression method can result in high CPU overhead. Updated rows and rows inserted without using direct-path insert are stored in row format instead of column format, and thus have a lower compression level.

 

创建basic compression 压缩表：

一般我们create table t compress 都是 basic compression

          
          

           
            create table t compress tablespace test  as select * From emp where 0=1;
          
          
           
           

            
             create table t compress  basic tablespace test  as select * From emp where 0=1;
           
           

          
          


           
           

            
            oltp compression 压缩表：
           
           
           
           

            
            

             
             

              
              

               
               
 
                
CREATE TABLE t  COMPRESS FOR OLTP tablespace test  as select * From emp where 0=1;
 
                Warehouse compression：（ORA-64307: 只有位于 Exadata 存储上的表空间才支持混合柱状压缩） 
               
              
              
             
             
             
             

              
              

               
               
 
                
 
                 
 
                  
 
                   
CREATE TABLE t COMPRESS FOR QUERY  tablespace test  as select * From emp where 0=1;
 
                   Archive compression：（ORA-64307: 只有位于 Exadata 存储上的表空间才支持混合柱状压缩） 
                  
 
                  
 
                   
 
                    
 
                     
 
                      
 
                       
 
                        
CREATE TABLE t  COMPRESS FOR ARCHIVE tablespace test  as select * From emp where 0=1;
 
                       
 
                      
 
                     
 
                    
 
                   
 
                  
 
                 
 
                
 
               
              
              
             
             
            
            
           
           
          
          
修改表为oltp 压缩方式：

SQL> alter table t2 move compress for oltp;

修改表为basic 压缩方式：
SQL> alter table t3 move compress ;

增加列：

oltp：可以增加一列指定默认值并且为not null的的列，如果指定默认值，但是可为空是不支持oltp压缩方式的

If a default value is specified for an added column, then the column must be NOT NULL. Added nullable columns with default values 
are not supported.

basic：你不能给增加的列指定默认值

删除列：
oltp：oltp 压缩方式可以支持列的删除列，但是为了避免长时间的解压缩和再压缩 可以设置 列的unused
DROP COLUMN is supported, but internally the database sets the column UNUSED to avoid long-running decompression and 
           
           

            
            

             
             

              
              

               
               

                
                
recompression operations.
               
               
              
              
             
             
            
            
           
           
basic：不支持删除列  Dropping a column is not supported.