column segment_name format a30
set timing off

select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


select t2.block_size, t2.logging, t2.extent_management, t2.segment_space_management, t2.def_tab_compression
from       dba_users       t1
inner join dba_tablespaces t2 on t1.default_tablespace = t2.tablespace_name
where t1.username = user;

BLOCK_SIZE LOGGING   EXTENT_MAN SEGMEN DEF_TAB_
---------- --------- ---------- ------ --------
     16384 LOGGING   LOCAL      AUTO   DISABLED


create table test_without_compression(f varchar2(100 char));

Таблица создана.


create table test_with_compression   (f varchar2(100 char)) compress for oltp;

Таблица создана.


set timing on

insert into test_without_compression(f)
select decode(mod(rownum, 5)
                    ,0, rpad('a', 100, 'a')
                    ,1, rpad('b', 100, 'b')
                    ,2, rpad('c', 100, 'c')
                    ,3, rpad('d', 100, 'd')
                    ,4, rpad('e', 100, 'e')
             ) f
from dual
connect by level <= 1e5;

100000 строк создано.

Затрач.время: 00:00:00.38

set autotrace on explain

insert into test_with_compression(f)
select decode(mod(rownum, 5)
                    ,0, rpad('a', 100, 'a')
                    ,1, rpad('b', 100, 'b')
                    ,2, rpad('c', 100, 'c')
                    ,3, rpad('d', 100, 'd')
                    ,4, rpad('e', 100, 'e')
             ) f
from dual
connect by level <= 1e5;

100000 строк создано.

Затрач.время: 00:00:01.31

План выполнения
----------------------------------------------------------
Plan hash value: 1731520519

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |                       |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL       | TEST_WITH_COMPRESSION |       |            |          |
|   2 |   COUNT                        |                       |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|                       |       |            |          |
|   4 |     FAST DUAL                  |                       |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(LEVEL<=1e5)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


set autotrace off
set timing off

select segment_name, segment_type, bytes, bytes / power(1024, 2) mb
from user_segments
where segment_name like 'TEST_WITH%COMPRESSION';

SEGMENT_NAME                   SEGMENT_TYPE            BYTES         MB
------------------------------ ------------------ ---------- ----------
TEST_WITHOUT_COMPRESSION       TABLE                12582912         12
TEST_WITH_COMPRESSION          TABLE                 2097152          2


drop table test_without_compression purge;

Таблица удалена.

drop table test_with_compression purge;

Таблица удалена.

column segment_name format a30
set timing off

select * from v$version;

select t2.block_size, t2.logging, t2.extent_management, t2.segment_space_management, t2.def_tab_compression
from       dba_users       t1
inner join dba_tablespaces t2 on t1.default_tablespace = t2.tablespace_name
where t1.username = user;

create table test_without_compression(f varchar2(100 char));

create table test_with_compression   (f varchar2(100 char)) compress for oltp;

set timing on

insert into test_without_compression(f)
select decode(mod(rownum, 5) 
                    ,0, rpad('a', 100, 'a')
                    ,1, rpad('b', 100, 'b')
                    ,2, rpad('c', 100, 'c')
                    ,3, rpad('d', 100, 'd')
                    ,4, rpad('e', 100, 'e') 
             ) f       
from dual
connect by level <= 1e5;

set autotrace on explain

insert into test_with_compression(f)
select decode(mod(rownum, 5) 
                    ,0, rpad('a', 100, 'a')
                    ,1, rpad('b', 100, 'b')
                    ,2, rpad('c', 100, 'c')
                    ,3, rpad('d', 100, 'd')
                    ,4, rpad('e', 100, 'e') 
             ) f       
from dual
connect by level <= 1e5;

set autotrace off
set timing off

select segment_name, segment_type, bytes, bytes / power(1024, 2) mb
from user_segments
where segment_name like 'TEST_WITH%COMPRESSION';

drop table test_without_compression purge;
drop table test_with_compression purge;