概要
表領域レベルの圧縮の動作を確認していきます。
マニュアル
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 金 11月 20 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"が金 11月 20 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 金 11月 20 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"が金 11月 20 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 金 11月 20 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"が金 11月 20 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
