The OLTP Compression Saves No Space As Expected Using A Row Too Big (Doc ID 1149283.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

If using a row too big on an 8k blocksize database, for example, then the OLTP Compression could not work as expected.

In detail:

1) Test COMPRESS

SQL> create tablespace COMPTEST datafile
'/temp/compk_comptest.dbf' SIZE 100M
extent management local
uniform size 5m
default compress for all operations;
SQL> create user compk identified by compk default tablespace comptest;
SQL> grant dba to compk;
SQL> select TABLESPACE_NAME,COMPRESS_FOR from dba_tablespaces where
TABLESPACE_NAME='COMPTEST';

TABLESPACE_NAME COMPRESS_FOR
------------------------------ ------------
COMPTEST OLTP

SQL> connect compk/compk
SQL> drop table test_table_1;

drop table test_table_1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> DECLARE
v_com_loop PLS_INTEGER := 0;
v_count1 PLS_INTEGER := 0;
v_count2 PLS_INTEGER := 0;
v_loop NUMBER(20) := 10000;
v_cre_table VARCHAR2(1000) := 'CREATE TABLE TEST_TABLE_1 (T1
NUMBER(20), T2 VARCHAR2(4000), T3 VARCHAR(20)) TABLESPACE COMPTEST';
v_ins_data VARCHAR2(2000) := 'INSERT INTO TEST_TABLE_1 VALUES (:1,:2, :3)';
8 v_ran_text VARCHAR2(4000) :=
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
BEGIN
EXECUTE IMMEDIATE v_cre_table;
FOR i IN 1 .. v_loop
LOOP
v_com_loop := v_com_loop + 1.0;
EXECUTE IMMEDIATE v_ins_data USING i, v_ran_text || v_ran_text ||
v_ran_text || v_ran_text, 'PINKY';
IF v_com_loop = 10000.0
THEN
COMMIT;
v_com_loop := 0.0;
END IF;
END LOOP;
END;
/

SQL> select table_name,COMPRESS_FOR,COMPRESSION from user_tables;

TABLE_NAME COMPRESS_FOR COMPRESS
------------------------------ ------------ --------
TEST_TABLE_1 OLTP ENABLED

SQL> select SEGMENT_NAME,TABLESPACE_NAME, BYTES/1024/1024 MB from
USER_SEGMENTS where SEGMENT_NAME like 'TEST_TABLE%';

SEGMENT_NAME TABLESPACE_NAME MB
------------ --------------- --
TEST_TABLE_1 COMPTEST 80



2) Test NO COMPRESS

SQL> create tablespace NOCOMPTEST datafile
'/temp/compk_nocomptest.dbf' SIZE 100M
extent management local
niform size 5m;
SQL> create user compk2 identified by compk2 default tablespace nocomptest;
SQL> grant dba to compk2;
SQL> select TABLESPACE_NAME,COMPRESS_FOR from dba_tablespaces where
TABLESPACE_NAME='NOCOMPTEST';

TABLESPACE_NAME COMPRESS_FOR
------------------------------ ------------
NOCOMPTEST

SQL> connect compk2/compk2
SQL> drop table test_table_2;

drop table test_table_2
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> DECLARE
v_com_loop PLS_INTEGER := 0;
v_count1 PLS_INTEGER := 0;
v_count2 PLS_INTEGER := 0;
v_loop NUMBER(20) := 10000;
v_cre_table VARCHAR2(1000) := 'CREATE TABLE TEST_TABLE_2 (T1
NUMBER(20), T2 VARCHAR2(4000), T3 VARCHAR(20)) TABLESPACE NOCOMPTEST';
v_ins_data VARCHAR2(2000) := 'INSERT INTO TEST_TABLE_2 VALUES (:1,:2, :3)';
v_ran_text VARCHAR2(4000) :=
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
BEGIN
EXECUTE IMMEDIATE v_cre_table;
FOR i IN 1 .. v_loop
LOOP
v_com_loop := v_com_loop + 1.0;
EXECUTE IMMEDIATE v_ins_data USING i, v_ran_text || v_ran_text ||
v_ran_text || v_ran_text, 'PINKY';
IF v_com_loop = 10000.0
THEN
COMMIT;
v_com_loop := 0.0;
END IF;
END LOOP;
END;
/

SQL> select table_name,COMPRESS_FOR,COMPRESSION from user_tables;

TABLE_NAME COMPRESS_FOR COMPRESS
------------------------------ ------------ --------
TEST_TABLE_2 DISABLED

SQL> select SEGMENT_NAME,TABLESPACE_NAME, BYTES/1024/1024 MB from
USER_SEGMENTS where SEGMENT_NAME like 'TEST_TABLE%';

SEGMENT_NAME TABLESPACE_NAME MB
------------ --------------- --
TEST_TABLE_2 NOCOMPTEST 80

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms