Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle table compression Technology Introduction     - Java Prototype Pattern (Programming)

- Loop arrays in C language is not easy to find a bug (Programming)

- JavaScript closures and the scope chain (Programming)

- Upgrading from Fedora 20 to 21 (Linux)

- linux server security (Linux)

- Linux Mint 17.2 64 bit installation Docker and management software seagull (Linux)

- Linux remote wake the computer original code [C] (Linux)

- Ubuntu Server 14.04 installation Web server (Linux + Apache + MySQL + PHP) (Server)

- Heartbeat cluster components Overview (Server)

- Several configuration changes illustrate deployment of PHP (Server)

- How to achieve SSH without password login (Server)

- Linux CentOS configuration SVN + SVN hook (Server)

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

- How to configure SNMPv3 on Ubuntu, CentOS and Cisco systems (Linux)

- Linux ps command (Linux)

- How to Start a Linux application running in the background using the terminal mode (Linux)

- Java Virtual Machine class loading mechanism and bytecode execution engine (Programming)

- Examples of Exploration Class File (Programming)

- Linux with Windows Explorer as a security system (Linux)

- MySQL binary packages install for RedHat Linux Enterprise 6.4 (Database)

 
         
  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:      
 
- Awk include binding capacity larger than the specified size of all files directory (Linux)
- VirtualBox CentOS is configured as a local disk mirroring software source (Linux)
- The Sublime Text 3 configuration file (Linux)
- Hardware Firewall Basics (Linux)
- Linux log management tools Logrotate (Linux)
- Quick Install software RAID on Linux (Linux)
- Dialogue UNIX:! $ # @ *% (Linux)
- Taught you how to install Ubuntu Linux (Linux)
- AngularJS notes --- Data Binding (Programming)
- Network security system (Network)
- Use virt-manager to create and manage virtual machines (Linux)
- To install Redis under Linux (Database)
- Command line tool Tmux (Linux)
- Graphic Git (Linux)
- Ubuntu 14.10 used ifconfig commands to manage your network configuration (Linux)
- Automatic and Manual Proxy Settings Switch GNOME Shell Extension Proxy Switcher Recommend (Linux)
- Linux server network penetration testing (Linux)
- To create someone else can not afford to delete the administrator user (Linux)
- Installation configuration CUDA under Ubuntu 14.04 (Linux)
- Packages with Snort intrusion monitoring light (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.