Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle table compression Technology Introduction     - linux smartd [FAILED] appears at startup (Linux)

- Tune in high resolution to 1280x800 in Fedora 14 (Linux)

- DNF Command Tutorial (Linux)

- Three kinds of binary tree traversal recursive and iterative solution (Programming)

- Linux System Tutorial: How to browse the Linux command line, weather forecast (Linux)

- Oracle TDE transparent data encryption (Database)

- Oracle database with test data insertion speed (Database)

- BCP importing and exporting large amounts of data Practice (Database)

- To restore the last time applications running when Ubuntu user log in again (Linux)

- Installation GitLab appears ruby_block supervise_redis_sleep action run (Linux)

- MongoDB 2.6 deployment replica set + partitions (Database)

- MyCAT separate read and write to achieve MySQL Practice (Database)

- The Java utility, JavaMail (Programming)

- Storm basic framework for analysis (Programming)

- Linux Network Programming - raw socket can do (Programming)

- Storm how to ensure that at least once semantics (Programming)

- Linux directory permissions to read and execute permissions difference (Linux)

- Ruby and Python simple comparison (Programming)

- Ubuntu Server (Ubuntu 14.04 LTS 64-bit) installation libgdiplus 2.10.9 error solution (Linux)

- Talking about the implementation and the JVM garbage collection mechanism (Programming)

 
         
  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:      
 
- Linux operation and maintenance engineers face questions Intermediate (Linux)
- Migu online music player for Linux (Linux)
- Disable unnecessary services under Linux (Linux)
- linux raid levels and concepts introduced (Linux)
- When RHEL7 use fdisk partition, all partitions can not be used (Linux)
- Mistakenly deleted redo log file group being given the lead to start the database ORA-03113 (Database)
- How to recover deleted files in Linux systems (Linux)
- Struts2 : combobox label use (Programming)
- Mind mapping software installed in CentOS 7 in XMind (Linux)
- Memcached distributed caching (Server)
- Use Pylint regulate your Python code (Programming)
- Ten minutes to find out the character set and character encoding (Linux)
- Based shell: using read, the command-line script to achieve mass participation and input two integer calculation (Programming)
- Thinking in Java study notes - initialization and cleanup (Programming)
- Grub2 Boots the openSUSE installation image (Linux)
- Linux in order to make NMAP hide and seek with the firewall (Linux)
- Linux System Getting Started Learning: On Linux how to convert text files to PDF (Linux)
- Detailed use Zabbix monitoring Nginx (Server)
- GitLab Guide installation under Ubuntu 14.04 (Server)
- Unix system security configuration (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.