oracle中的压缩,Oracle：表空间的数据压缩（Compress）

在对大型数据处理时，可能会需要压缩数据。

压缩数据可以减少对磁盘空间、内存的占用，还可以减少I/O的操作。同时，由于查询的数据块更少，所以查询压缩数据的操作也会更为快速。

但是处理压缩数据需要消耗更多的CPU资源：读取数据需要解压操作，写入数据需要压缩操作。

在创建表空间的时候可以启用数据压缩。

在表空间级别启用数据压缩的意义在于：对于启用了数据压缩的表空间，该表空间中的所有数据表都会具有(或：继承)压缩的特性。

通过“row compress advanced”的“create database …”SQL语句的选项启用数据压缩的特性。

具体如下：

Oracle PL/SQL

SQL> col file_name for a70

SQL> select file_name from dba_data_files;

FILE_NAME

----------------------------------------------------------------------

/ora_data/allah/system01.dbf

/ora_data/allah/sysaux01.dbf

/ora_data/allah/undotbs01.dbf

/ora_data/allah/users01.dbf

/ora_data/ALLAH/datafile/o1_mf_allah_b5jmqm12_.dbf

SQL>

SQL> select tablespace_name from dba_tablespaces order by 1;

TABLESPACE_NAME

------------------------------

ALLAH

SYSAUX

SYSTEM

TEMP

UNDOTBS1

USERS

6 rows selected.

SQL>

SQL> create tablespace adamhuan default row store compress advanced;

Tablespace created.

SQL> select file_name from dba_data_files;

FILE_NAME

----------------------------------------------------------------------

/ora_data/allah/system01.dbf

/ora_data/allah/sysaux01.dbf

/ora_data/allah/undotbs01.dbf

/ora_data/allah/users01.dbf

/ora_data/ALLAH/datafile/o1_mf_allah_b5jmqm12_.dbf

/ora_data/ALLAH/datafile/o1_mf_adamhuan_b5jqs1c4_.dbf

6 rows selected.

SQL> select tablespace_name from dba_tablespaces order by 1;

TABLESPACE_NAME

------------------------------

ADAMHUAN

ALLAH

SYSAUX

SYSTEM

TEMP

UNDOTBS1

USERS

7 rows selected.

SQL>

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

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

SQL>colfile_namefora70

SQL>selectfile_namefromdba_data_files;

FILE_NAME

----------------------------------------------------------------------

/ora_data/allah/system01.dbf

/ora_data/allah/sysaux01.dbf

/ora_data/allah/undotbs01.dbf

/ora_data/allah/users01.dbf

/ora_data/ALLAH/datafile/o1_mf_allah_b5jmqm12_.dbf

SQL>

SQL>selecttablespace_namefromdba_tablespacesorderby1;

TABLESPACE_NAME

------------------------------

ALLAH

SYSAUX

SYSTEM

TEMP

UNDOTBS1

USERS

6rowsselected.

SQL>

SQL>createtablespaceadamhuandefaultrowstorecompressadvanced;

Tablespacecreated.

SQL>selectfile_namefromdba_data_files;

FILE_NAME

----------------------------------------------------------------------

/ora_data/allah/system01.dbf

/ora_data/allah/sysaux01.dbf

/ora_data/allah/undotbs01.dbf

/ora_data/allah/users01.dbf

/ora_data/ALLAH/datafile/o1_mf_allah_b5jmqm12_.dbf

/ora_data/ALLAH/datafile/o1_mf_adamhuan_b5jqs1c4_.dbf

6rowsselected.

SQL>selecttablespace_namefromdba_tablespacesorderby1;

TABLESPACE_NAME

------------------------------

ADAMHUAN

ALLAH

SYSAUX

SYSTEM

TEMP

UNDOTBS1

USERS

7rowsselected.

SQL>

不同的数据库版本中，启用数据压缩的SQL代码是不一样的：

12c：

row store compress advanced

11g：

compress for oltp

查看表空间是否具有压缩性：

Oracle PL/SQL

SQL> select tablespace_name,def_tab_compression,compress_for from dba_tablespaces;

TABLESPACE_NAME DEF_TAB_ COMPRESS_FOR

------------------------------ -------- ------------------------------

SYSTEM DISABLED

SYSAUX DISABLED

UNDOTBS1 DISABLED

TEMP DISABLED

USERS DISABLED

ALLAH DISABLED

ADAMHUAN ENABLED ADVANCED

7 rows selected.

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SQL>selecttablespace_name,def_tab_compression,compress_forfromdba_tablespaces;

TABLESPACE_NAMEDEF_TAB_COMPRESS_FOR

------------------------------ -------- ------------------------------

SYSTEMDISABLED

SYSAUXDISABLED

UNDOTBS1DISABLED

TEMPDISABLED

USERSDISABLED

ALLAHDISABLED

ADAMHUANENABLEDADVANCED

7rowsselected.

SQL>

其中“compress_for”列的值表明的是压缩级别，它可能为两种值：ADVANCED或者BASIC。

更改表空间的压缩级别：

Oracle PL/SQL

SQL> alter tablespace adamhuan default compress basic;

Tablespace altered.

SQL> select tablespace_name,def_tab_compression,compress_for from dba_tablespaces;

TABLESPACE_NAME DEF_TAB_ COMPRESS_FOR

------------------------------ -------- ------------------------------

SYSTEM DISABLED

SYSAUX DISABLED

UNDOTBS1 DISABLED

TEMP DISABLED

USERS DISABLED

ALLAH DISABLED

ADAMHUAN ENABLED BASIC

7 rows selected.

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

SQL>altertablespaceadamhuandefaultcompressbasic;

Tablespacealtered.

SQL>selecttablespace_name,def_tab_compression,compress_forfromdba_tablespaces;

TABLESPACE_NAMEDEF_TAB_COMPRESS_FOR

------------------------------ -------- ------------------------------

SYSTEMDISABLED

SYSAUXDISABLED

UNDOTBS1DISABLED

TEMPDISABLED

USERSDISABLED

ALLAHDISABLED

ADAMHUANENABLEDBASIC

7rowsselected.

SQL>

禁用表空间的压缩特性：

Oracle PL/SQL

SQL> alter tablespace adamhuan default nocompress;

Tablespace altered.

SQL> select tablespace_name,def_tab_compression,compress_for from dba_tablespaces;

TABLESPACE_NAME DEF_TAB_ COMPRESS_FOR

------------------------------ -------- ------------------------------

SYSTEM DISABLED

SYSAUX DISABLED

UNDOTBS1 DISABLED

TEMP DISABLED

USERS DISABLED

ALLAH DISABLED

ADAMHUAN DISABLED

7 rows selected.

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

SQL>altertablespaceadamhuandefaultnocompress;

Tablespacealtered.

SQL>selecttablespace_name,def_tab_compression,compress_forfromdba_tablespaces;

TABLESPACE_NAMEDEF_TAB_COMPRESS_FOR

------------------------------ -------- ------------------------------

SYSTEMDISABLED

SYSAUXDISABLED

UNDOTBS1DISABLED

TEMPDISABLED

USERSDISABLED

ALLAHDISABLED

ADAMHUANDISABLED

7rowsselected.

SQL>

关于许可：

压缩功能仅在以下两种情况下可选：

1. Oracle Database Enterprise Edition，Oracle数据库企业版

2. Option：Advanced Compression Option，高级压缩选件

——————————————————

End。

Post Views:

379

感谢支持

赏