Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle table compression Technology Introduction     - How to configure MariaDB replication in CentOS Linux (Database)

- Solve the compatibility problem between Linux and Java at the source in bold font (Linux)

- OpenGL Superb Learning Notes - GLSL language foundation (Programming)

- Oracle GoldenGate encryption (Database)

- How to use the on-screen keyboard in Linux (Linux)

- Linux NFS service fixed ports and firewall configuration (Linux)

- Android start automatically and add and delete a desktop shortcut (Programming)

- Linux environment installation of rvm and ruby (Linux)

- Ubuntu is expected to allow you to install the latest Nvidia Linux drivers easier (Linux)

- How to recover deleted files in Linux systems (Linux)

- RHEL5 / 6 Installation Notes (Linux)

- RabbitMQ user roles and access control (Linux)

- Ubuntu 12.04 LTS installation configuration JDK1.6.0_45 (Linux)

- Mount NFS network file system (Linux)

- Binary tree traversal recursive and non-recursive (cyclic) traversal achieve (Programming)

- System-level alias vs Oracle ADR functionality (Database)

- CentOS and RHEL to install IPython 0.11 (Linux)

- Java object initialization (Programming)

- Tsung first test installation (Linux)

- Alternative methods of intrusion bundled executable file new thinking (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:      
 
- Ubuntu 14.10 users to install Audio Recorder 1.5.7 (Linux)
- Java how to achieve bubble sort the problem Arraylist (Programming)
- C ++ pointer of the (error-prone model) (Programming)
- Python Basics Tutorial - lambda keyword (Programming)
- Troubleshooting Linux virtual machine device id and the letter of inconsistencies (Linux)
- map and hash_map STL containers (Programming)
- Oracle 11g tracking and monitoring system-level triggers to drop misuse (Database)
- Oracle row and column switch to turn columns (Database)
- Ubuntu 14.04 compile, install, configure, the latest development version GoldenDict (Linux)
- OpenGL Superb Learning Notes - Depth Texture and Shadows (Programming)
- Android determine the device network connection status, and determine the connection (Programming)
- Tmux Getting Start (Linux)
- Oracle 12c of the auto-increment Identity Columns (Database)
- Oracle view object space usage show_space (Database)
- C ++ copy constructor (Programming)
- About Samba certification process and permissions (Linux)
- KVM virtualization of nested virtualization (Linux)
- Deployment Kubernetes manage Docker example cluster on Ubuntu (Server)
- Oracle and MySQL difference between the jdbc (Database)
- Java transient keyword (Programming)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.