oracle创建cmp表,FAQ：Oracle数据库中出现的 CMP$ 表是什么？

MOS以下文档具有说明:

“MOS Note Is Table SCHEMA.CMP4$222224 Or Similar Related To Compression Advisor? (Doc ID 1606356.1)

引用一个测试用例：

SQL>

alter session set tracefile_identifier = 'CompTest1110201815h51';

alter session set events '10046 trace name context forever, level 12';

set serveroutput on

DECLARE

l_blkcnt_cmp PLS_INTEGER;

l_blkcnt_uncmp PLS_INTEGER;

l_row_cmp PLS_INTEGER;

l_row_uncmp PLS_INTEGER;

l_cmp_ratio NUMBER;

l_comptype_str VARCHAR2(32767);

BEGIN

DBMS_COMPRESSION.get_compression_ratio (

scratchtbsname => 'USERS',

ownname => 'TEST_LAF',

objname => 'FOO',

subobjname => NULL,

comptype => DBMS_COMPRESSION.comp_advanced,

blkcnt_cmp => l_blkcnt_cmp,

blkcnt_uncmp => l_blkcnt_uncmp,

row_cmp => l_row_cmp,

row_uncmp => l_row_uncmp,

cmp_ratio => l_cmp_ratio,

comptype_str => l_comptype_str,

subset_numrows => DBMS_COMPRESSION.comp_ratio_allrows,

objtype => DBMS_COMPRESSION.objtype_table

);

DBMS_OUTPUT.put_line('Number of blocks used (compressed) : ' || l_blkcnt_cmp);

DBMS_OUTPUT.put_line('Number of blocks used (uncompressed) : ' || l_blkcnt_uncmp);

DBMS_OUTPUT.put_line('Number of rows in a block (compressed) : ' || l_row_cmp);

DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' || l_row_uncmp);

DBMS_OUTPUT.put_line('Compression ratio : ' || l_cmp_ratio);

DBMS_OUTPUT.put_line('Compression type : ' || l_comptype_str);

END;

/

Number of blocks used (compressed) : 1325

Number of blocks used (uncompressed) : 1753

Number of rows in a block (compressed) : 74

Number of rows in a block (uncompressed) : 55

Compression ratio : 1.3

Compression type : "Compress Advanced"

PL/SQL procedure successfully completed.

通过跟踪文件可以看到内部过程：

grep "CMP*" DBI_ora_20529_CompTest1110201823h19.trc

drop table "TEST_LAF".CMP1$23590 purge

drop table "TEST_LAF".CMP2$23590 purge

drop table "TEST_LAF".CMP3$23590 purge

drop table "TEST_LAF".CMP4$23590 purge

create table "TEST_LAF".CMP3$23590 tablespace "USERS" nologging as select /*+ DYNAMIC_SAMPLING(0) FULL("TEST_LAF"."FOO") */ * from "TEST_LAF"."FOO" sample block( 99) mytab

create table "TEST_LAF".CMP4$23590 organization heap tablespace "USERS" compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from "TEST_LAF".CMP3$23590 mytab

drop table "TEST_LAF".CMP1$23590 purge

drop table "TEST_LAF".CMP2$23590 purge

drop table "TEST_LAF".CMP3$23590 purge

drop table "TEST_LAF".CMP4$23590 purge

墨天轮原文链接：https://www.modb.pro/db/22588(复制链接到浏览器即可访问)