GAGA LIFE.

インフラエンジニアブログ

スポンサーリンク

表領域レベルの圧縮

概要

表領域レベルの圧縮の動作を確認していきます。

マニュアル

Oracle® Database 管理者ガイド 19c

  13.2.4 デフォルト圧縮属性を持つ表領域

  表領域の作成時に、表領域に作成されるすべての表と索引またはそれらのパーティションをデフォルトで圧縮するように指定できます。
    - デフォルト圧縮属性を持つ表領域について
      表領域を作成する際に、表領域に作成されるすべての表および索引のデータのデフォルト圧縮を指定できます。
      デフォルト圧縮レベルは、表領域を構成するパーティションにも適用されます。このデータを圧縮するとディスク使用量を削減できます。
    - デフォルト圧縮属性を使用した表領域の作成
      表領域を作成するときに表圧縮のタイプを指定するには、DEFAULTキーワードを使用し、その後に圧縮タイプを含む表圧縮句を指定します。
      DEFAULTキーワードを使用して、その後に索引圧縮句と索引圧縮タイプを指定すると、索引圧縮のタイプを指定することもできます。

実行例

  • 圧縮表領域作成(COMP_TEST)
SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> create tablespace comp_test datafile '+DATA' DEFAULT ROW STORE COMPRESS ADVANCED;

表領域が作成されました。

SQL> select f.file_id,f.tablespace_name,f.file_name,f.bytes,f.blocks,f.autoextensible,t.compress_for
from dba_tablespaces t,dba_data_files f
where t.tablespace_name = f.tablespace_name 
order by f.file_id;
  2    3    4  
   FILE_ID TABLESPACE_NAME      FILE_NAME                                                                                       BYTES     BLOCKS AUTOEXTEN COMPRESS_FOR
---------- -------------------- ------------------------------------------------------------------------------------------ ---------- ---------- --------- --------------------
         9 SYSTEM               +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/system.284.1054420929                  241172480      29440 YES
        10 SYSAUX               +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/sysaux.282.1054420693                 1672478720     204160 YES
        11 UNDOTBS1             +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/undotbs1.283.1054420713                209715200      25600 YES
        12 USERS                +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/users.286.1054421063                    38010880       4640 YES
        13 UNDO_2               +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/undo_2.304.1054459615                  209715200      25600 YES
        14 EXAMPLES             +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/examples.329.1056635519               1073741824     131072 YES
        16 COMP_TEST            +DATA/ORCL/B22F6C4AEEEA2C74E0536538A8C04AE5/DATAFILE/comp_test.413.1056984971               104857600      12800 YES       ADVANCED
  • 比較用非圧縮表作成(EMP2)
SQL> create table emp2 as select * from emp where 0=1;

表が作成されました。

SQL> alter table emp2 modify (empno number(10));

表が変更されました。

SQL> begin
for i in 1..1000000 loop
insert into emp2 (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (i,DBMS_RANDOM.STRING('p',8),null,null,sysdate,null,null,null);
commit;
end loop;
end;
/
  2    3    4    5    6    7    8  
PL/SQLプロシージャが正常に完了しました。

SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
;
  2    3    4    5    6  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
DEPT                 USERS                DISABLED
EMP                  USERS                DISABLED
BONUS                USERS                DISABLED
SALGRADE             USERS                DISABLED
DUMMY                USERS                DISABLED
EMP2                 USERS                DISABLED

SQL> SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
  2    3    4    5    6    7  
SEGMENT_NAME         SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
-------------------- -------------------- -------------------- ----------
EMP                  TABLE                USERS                        64
EMP2                 TABLE                USERS                     33792
  • 圧縮表領域に表作成(EMP_COMP)
SQL> create table emp_comp tablespace comp_test as select * from emp where 0=1;

表が作成されました。

SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP                  USERS                DISABLED

SQL> alter table emp_comp modify (empno number(10));

表が変更されました。

SQL> begin
for i in 1..1000000 loop
insert into emp_comp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (i,DBMS_RANDOM.STRING('p',8),null,null,sysdate,null,null,null);
commit;
end loop;
end;
/
  2    3    4    5    6    7    8  

PL/SQLプロシージャが正常に完了しました。

SQL> SQL> col SEGMENT_NAME for a40
col PARTITION_NAME for a50
SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
SQL> SQL>   2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552

SQL> select SEGMENT_NAME, MB, MB/decode(NOCOMP_SIZE, 0, MB, NOCOMP_SIZE)*100 as RATIO
  from (select SEGMENT_NAME, MB, (lag(MB, 1, 0) over (order by MB DESC)) NOCOMP_SIZE
          from (select SEGMENT_NAME, sum(BYTES)/1024/1024 MB
                  from USER_SEGMENTS
                 where SEGMENT_NAME like 'EMP%'
                 group by SEGMENT_NAME
               )
       );
  2    3    4    5    6    7    8  
SEGMENT_NAME                                     MB      RATIO
---------------------------------------- ---------- ----------
EMP2                                             33        100
EMP_COMP                                         23 69.6969697
EMP                                           .0625  .27173913
  • ダイレクトパスインサート(EMP_DIRECT)
SQL> create table emp_direct tablespace comp_test as select * from emp where 0=1;

表が作成されました。

SQL> alter table emp_direct modify (empno number(10));

表が変更されました。

SQL> begin
for i in 1..1000000 loop
insert /*+ APPEND */ into emp_direct (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (i,DBMS_RANDOM.STRING('p',8),null,null,sysdate,null,null,null);
commit;
end loop;
end;
/
  2    3    4    5    6    7    8  

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP_DIRECT           COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP                  USERS                DISABLED

SQL> SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
  2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552
EMP_DIRECT                               TABLE                COMP_TEST                 23552
  • IMPDP(⇒EMP_IMPDP)
SQL> create table emp_impdp tablespace comp_test as select * from emp where 0=1;

表が作成されました。

SQL> alter table emp_impdp modify (empno number(10));

表が変更されました。

SQL> alter session set container = pdb1;

セッションが変更されました。

SQL> create or replace directory SCOTT_DIR as '/u01/app/oracle/admin/orcl/dpdump';

ディレクトリが作成されました。

SQL> grant read, write on directory SCOTT_DIR to SCOTT;

権限付与が成功しました。

SQL> grant exp_full_database to SCOTT;

権限付与が成功しました。

SQL> grant imp_full_database to SCOTT;

権限付与が成功しました。
$ expdp system/system@pdb1 directory=scott_dir tables=scott.emp2 logfile=emp2.log

Export: Release 19.0.0.0.0 - Production on 金 1120 17:14:48 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
"SYSTEM"."SYS_EXPORT_TABLE_01"を起動しています: system/********@pdb1 directory=scott_dir tables=scott.emp2 logfile=emp2.log 
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
. . "SCOTT"."EMP2"                              26.94 MB 1009999行がエクスポートされました
マスター表"SYSTEM"."SYS_EXPORT_TABLE_01"は正常にロード/アンロードされました
******************************************************************************
SYSTEM.SYS_EXPORT_TABLE_01に設定されたダンプ・ファイルは次のとおりです:
  /u01/app/oracle/admin/orcl/dpdump/expdat.dmp
ジョブ"SYSTEM"."SYS_EXPORT_TABLE_01"が金 1120 17:15:43 2020 elapsed 0 00:00:45で正常に完了しました

$ impdp scott/tiger@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test

Import: Release 19.0.0.0.0 - Production on 金 1120 17:35:39 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
マスター表"SCOTT"."SYS_IMPORT_FULL_01"は正常にロード/アンロードされました
"SCOTT"."SYS_IMPORT_FULL_01"を起動しています: scott/********@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test 
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT"."EMP_IMPDP"                         26.94 MB 1009999行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
ジョブ"SCOTT"."SYS_IMPORT_FULL_01"が金 1120 17:36:22 2020 elapsed 0 00:00:40で正常に完了しました
SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_IMPDP            COMP_TEST            DISABLED
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP_DIRECT           COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP                  USERS                DISABLED

5行が選択されました。

SQL_ID: 7w5qn4fjgpbq8
経過: 00:00:00.01
SQL> col SEGMENT_NAME for a40
col PARTITION_NAME for a50
SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
SQL> SQL>   2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552
EMP_DIRECT                               TABLE                COMP_TEST                 23552
EMP_IMPDP                                TABLE                COMP_TEST                 33792

SQL> drop table emp_impdp purge;

表が削除されました。
  • IMPDP(EMP_IMPDP compression_clause:\"row store compress advanced\"付与)
$ impdp scott/tiger@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test transform=table_compression_clause:\"row store compress advanced\"

Import: Release 19.0.0.0.0 - Production on 金 1120 17:51:23 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
マスター表"SCOTT"."SYS_IMPORT_FULL_01"は正常にロード/アンロードされました
"SCOTT"."SYS_IMPORT_FULL_01"を起動しています: scott/********@pdb1 directory=scott_dir dumpfile=expdat.dmp remap_table=emp2:emp_impdp remap_tablespace=users:comp_test transform=table_compression_clause:"row store compress advanced" 
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT"."EMP_IMPDP"                         26.94 MB 1009999行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
ジョブ"SCOTT"."SYS_IMPORT_FULL_01"が金 1120 17:52:04 2020 elapsed 0 00:00:36で正常に完了しました
SQL> SELECT TABLE_NAME
      ,TABLESPACE_NAME
      ,COMPRESSION
      ,COMPRESS_FOR
FROM   USER_TABLES
WHERE TABLE_NAME LIKE '%EMP%'
;
  2    3    4    5    6    7  
TABLE_NAME           TABLESPACE_NAME      COMPRESSION              COMPRESS_FOR
-------------------- -------------------- ------------------------ --------------------
EMP_COMP             COMP_TEST            ENABLED                  ADVANCED
EMP_DIRECT           COMP_TEST            ENABLED                  ADVANCED
EMP2                 USERS                DISABLED
EMP_IMPDP            COMP_TEST            ENABLED                  ADVANCED
EMP                  USERS                DISABLED

5行が選択されました。

SQL_ID: 7w5qn4fjgpbq8
経過: 00:00:00.01
SQL> col SEGMENT_NAME for a40
col PARTITION_NAME for a50
SELECT SEGMENT_NAME
      ,SEGMENT_TYPE
      ,TABLESPACE_NAME
      ,ROUND(BYTES/1024,2) KBYTES
FROM   USER_SEGMENTS
WHERE SEGMENT_NAME like '%EMP%'
;
SQL> SQL>   2    3    4    5    6    7  
SEGMENT_NAME                             SEGMENT_TYPE         TABLESPACE_NAME          KBYTES
---------------------------------------- -------------------- -------------------- ----------
EMP                                      TABLE                USERS                        64
EMP2                                     TABLE                USERS                     33792
EMP_COMP                                 TABLE                COMP_TEST                 23552
EMP_DIRECT                               TABLE                COMP_TEST                 23552
EMP_IMPDP                                TABLE                COMP_TEST                 23552

スポンサーリンク