Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle table compression Technology Introduction     - Check with Hello World Docker installation (Server)

- ORA-00600 error solve one case (Database)

- MySQL enabled SSD storage (Database)

- Use scripts easily install the latest Linux kernel in Ubuntu (Linux)

- Relationship between Linux permissions with the command (Linux)

- CentOS / Linux SELinux Close (Linux)

- On the design of Oracle database backup (Database)

- MySQL error: ERROR 1175: You are using safe update mode solution (Database)

- How x2g0 install Remote Desktop on Linux VPS (Server)

- Common Linux system performance monitoring command (Linux)

- PostgreSQL 9.3.2 Json type of use (Database)

- Distributed File System using MogileFS (Linux)

- MySQL Data Types (Database)

- 25 Git Advanced Skills (Linux)

- Linux Security Setup Guide (Linux)

- The Rabbitmq installation under CentOS 6.4 (Linux)

- Java filter (Programming)

- ARM constant expression (Programming)

- MySQL 5.6 use GTIDs build the master database (Database)

- wget command examples (Linux)

 
         
  Oracle table compression Technology Introduction
     
  Add Date : 2017-08-31      
         
       
         
  Oracle table compression (Table Compression) Introduction

1, the official documentation saying:
As your database grows in size, consider using table compression. Compression saves disk space, reduces memory use in the database buffer cache, and can significantly speed query execution during reads. Compression has a cost in CPU overhead for data loading and DML. However, this cost might be offset by reduced I / O requirements

With a growing database, consider using table compression. Compression can save disk space, the database buffer cache to reduce memory usage, and can speed up queries.
Compression for data loading and DML operations have a certain CPU consumption. However, these may consume I / O to reduce the offset.

Table compression is completely transparent to applications. It is useful in decision support systems (DSS), online transaction processing (OLTP) systems, and archival systems.

Table compression for applications completely transparent. For DSS systems, online transaction processing and archiving systems are very useful.

You can specify compression for a tablespace, a table, or a partition. If specified at the tablespace level, then all tables created in that tablespace are compressed by default.

You can table space, partition table or a specified compression. If you specify the basic table space, the table space after all the tables create a default compression enabled.

Compression can occur while data is being inserted, updated, or bulk loaded into a table Operations that permit compression include.:
Compressed data can then insert, update or bulk load occurs when the table. Compression table allows the following operations:
Single-row or array inserts and updates a line or lines to insert and update

The following direct-path INSERT methods: direct path insert method:

Direct path SQL * Loader
1) CREATE TABLE AS SELECT statements
2) Parallel INSERT statements
3) INSERT statements with an APPEND or APPEND_VALUES hint

Up to now, Oracle Database There are four kinds of table compression technology:
1) Basic compression
2) OLTP compression
3) Warehouse compression (Hybrid Columnar Compression)
4) Archive compression (Hybrid Columnar Compression)

Here I introduces the basic compression:

2, the basic compression features:
1) using basic compression only when data is inserted or updated records direct path (direct-path insert and updated) compression will occur.
And support for wired data types and SQL operations.

3. How to enable basic compression?
1) compress conditions specified by the create table statement.
2) through the alter table .. compress; to an existing table to enable compression;
3) through the alter table .. nocompress; disable table compression

4, the basic compression on some examples
4.1 to create a compressed table

CREATE TABLE emp_comp compress
AS
SELECT * FROM emp
WHERE 1 = 2; 1
4.2 data dictionary view compressed state table

SCOTT @ orcl> SELECT table_name, compression, compress_for
  2 FROM user_tables
  3 WHERE table_name = 'EMP_COMP';

TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP ENABLED BASIC1
4.3 insert data through a non-direct path

SCOTT @ orcl> INSERT INTO emp_comp
  2 SELECT * FROM emp;

16 lines have been created.

SCOTT @ orcl> commit;

- Display occupancy
SYS @ orcl> exec show_space ( 'EMP_COMP', 'SCOTT');
Unformatted Blocks .................... 0
FS1 Blocks (0-25) .................... 0
FS2 Blocks (25-50) .................... 0
FS3 Blocks (50-75) .................... 0
FS4 Blocks (75-100) .................... 5
Full Blocks .................... 0
Total Blocks ........................... 8
Total Bytes ........................... 65,536
Total MBytes ........................... 0
Unused Blocks ........................... 0
Unused Bytes ........................... 0
Last Used Ext FileId .................... 4
Last Used Ext BlockId ................... 14,304
Last Used Block ......................... 8

- Look emp occupancy
SYS @ orcl> exec show_space ( 'EMP', 'SCOTT');
Unformatted Blocks .................... 0
FS1 Blocks (0-25) .................... 0
FS2 Blocks (25-50) .................... 0
FS3 Blocks (50-75) .................... 0
FS4 Blocks (75-100) .................... 5
Full Blocks .................... 0
Total Blocks ........................... 8
Total Bytes ........................... 65,536
Total MBytes ........................... 0
Unused Blocks ........................... 0
Unused Bytes ........................... 0
Last Used Ext FileId .................... 4
Last Used Ext BlockId ................... 144
Last Used Block ......................... 8

- Occupancy compared with the original EMP table, emp_comp table not compressed. Note: For show_space process usage

4.4 Inserting data via direct path

drop table emp_comp purge;

CREATE TABLE emp_comp compress
AS
SELECT * FROM emp
WHERE 1 = 2;


insert / * + append * / into emp_comp
select *
from emp;

- Display occupancy
SYS @ orcl> exec show_space ( 'EMP_COMP', 'SCOTT');
Unformatted Blocks .................... 0
FS1 Blocks (0-25) .................... 0
FS2 Blocks (25-50) .................... 0
FS3 Blocks (50-75) .................... 0
FS4 Blocks (75-100) .................... 0
Full Blocks .................... 1
Total Blocks ........................... 8
Total Bytes ........................... 65,536
Total MBytes ........................... 0
Unused Blocks ........................... 4
Unused Bytes ........................... 32,768
Last Used Ext FileId .................... 4
Last Used Ext BlockId ................... 14,304
Last Used Block ......................... 4

- Obviously less occupy four blocks
4.5 disable table compression

SCOTT @ orcl> alter table emp_comp NOCOMPRESS;

Table has been changed.

SCOTT @ orcl> SELECT table_name, compression, compress_for
  2 FROM user_tables
  3 WHERE table_name = 'EMP_COMP'
  4;

TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP DISABLED4.6 Enable Table Compression

SCOTT @ orcl> alter table emp_comp COMPRESS;

Table has been changed.

SCOTT @ orcl> SELECT table_name, compression, compress_for
  2 FROM user_tables
  3 WHERE table_name = 'EMP_COMP';

TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP ENABLED BASIC5, look at the table and finally compressed using several limitations:
1) For basic compression, you can not add a column with a default value in the compressed table:

SCOTT @ orcl> alter table emp_comp add remark varchar2 (200) default 'null';
alter table emp_comp add remark varchar2 (200) default 'null'
                        *
Line 1 Error:
ORA-39726: no support for compression on tables Add / Remove Columns operation 2) Can not delete row compression on the table:

SCOTT @ orcl> alter table emp_comp drop column ename;
alter table emp_comp drop column ename
                                *
Line 1 Error:
ORA-39726: no support for compression on tables Add / Remove Columns operation 3) table compression does not support online segment shrink (Online segment shrink)
4) does not support SecureFiles large objects
5) The default setting compression table creation PCT_FREE 0; unless you manually specify.
     
         
       
         
  More:      
 
- Python Socket Network Programming (Programming)
- Linux System Getting Started Learning: The Linux anacron command (Linux)
- Sniffer install applications in Linux supplement (Linux)
- Oracle Data File Management (Database)
- MyCAT separate read and write to achieve MySQL Practice (Database)
- Linux script to copy the folder to all folders with the same name (Linux)
- Python uses multi-process pool (Programming)
- LVM Disk Manager Application (Linux)
- DBCA Error: ORA-19809: limit exceeded for recovery files process (Database)
- Nginx server security configuration (Server)
- How Linux system password security guarantee (Linux)
- MySQL 5.6.12 binary log path switching binlog (Database)
- Using 30 seconds to write a detailed analysis of garbage removal system (Linux)
- shell script: a key optimization CentOS system (Linux)
- Adjustment expand VMDK format VirtualBox disk space (Linux)
- RedHat command line and graphical interface switching (Linux)
- How to compile and install wxWidgets in Ubuntu / Debian / Linux Mint (Linux)
- Nginx + uWSGI + Django + Python Application Architecture Deployment (Server)
- Ubuntu deployed under regular tasks with crontab (Linux)
- Virtual Judge environment to build and configure under Ubuntu (Server)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.