| |
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. |
|
|
|