|
副问题[/!--empirenews.page--]
1、官方文档说法：
Oracle支持在表空间（tablespace）、数据表（table）和分区（Partition）级此外压缩，假如配置为表空间级别，那么默认将该表空间中的所有的表都举办压缩。
压缩操纵可以在数据单条插入、数据修改和数据批量导入时产生。
?
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
跟着数据库不绝增添，可以思量行使表压缩。压缩可以节减磁盘空间，镌汰数据库buffer cache内存行使，而且可以加快查询。
压缩对付数据装载和DML操纵有必然的CPU耗损。然而，这些耗损可觉得I/O的镌汰而抵消。
Table compression is completely transparent to applications. It is useful in decision support systems (DSS),online transaction processing (OLTP) systems,and archival systems.
表压缩对付应用措施完全透明。对付DSS体系、在线事宜处理赏罚和归档体系都很有效处。
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.
你可觉得表空间，表可能一个分区指定压缩。假如指定为表空间根基，那么该表空间全部表建设后默认都启用压缩。
Compression can occur while data is being inserted,updated,or bulk loaded into a table. Operations that permit compression include:
压缩可以再数据插入，更新可能批量装载入表中时产生。压缩表应承以下操纵：
Single-row or array inserts and updates 单行或多行插入和更新
The following direct-path INSERT methods: 直接路径插入要领：
Direct path SQL*Loader
1）CREATE TABLE AS SELECT statements
2）Parallel INSERT statements
3）INSERT statements with an APPEND or APPEND_VALUES hint
截至今朝，Oracle数据库共有4种表压缩技能：
1）Basic compression
2）OLTP compression
3）Warehouse compression (Hybrid Columnar Compression)
4）Archive compression (Hybrid Columnar Compression)
这里我首要先容根基压缩：
2、根基压缩特点：
1）行使根基压缩，只有当数据是直接路径插入或更新记录（direct-path insert and updated ）时才会产生压缩。
而且支持有线的数据范例和SQL操纵。
3、怎样启用根基压缩？
1）通过create table语句中指定compress前提。
2）通过alter table .. compress; 来给现有表启用压缩；
3）通过alter table .. nocompress; 来禁用表压缩
4、关于根基压缩的一些例子
4.1 建设压缩表
CREATE TABLE emp_comp compress
AS
SELECT * FROM emp
WHERE 1=2;
1
2
3
4
4.2 通过数据字典查察压缩表状态
[email?protected]> SELECT table_name,compression,compress_for
2 FROM user_tables
3 WHERE table_name=‘EMP_COMP‘;
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP ENABLED BASIC
1
2
3
4
5
6
7
4.3 通过非直接路径插入数据
[email?protected]> INSERT INTO emp_comp
2 SELECT * FROM emp;
已建设16行。
[email?protected]> commit;
--查察表占用
[email?protected]> 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
--看下emp的占用
[email?protected]> exec show_space(‘EMP‘,536
Total MBytes ........................... 0
Unused Blocks........................... 0
Unused Bytes ........................... 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 144
Last Used Block......................... 8
--比拟与原EMP表的占用环境，emp_comp表并未压缩。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
注：关于show_space进程的用法，请参考【http://blog.csdn.net/indexman/article/details/47207987】
4.4 通过直接路径插入数据
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;
--查察表占用
[email?protected]> 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
（编辑：湖南网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
