# Basic compression
# With basic compression, the Oracle Database server compresses data at the time of performing bulk load using
# operations such as direct loads or CREATE TABLE AS SELECT
# Is recommended for bulk loading data warehouses
show user;
show con_name;
/*
USER is "PDB10_ADMIN"
CON_NAME
------------------------------
PDB10
*/
# Case 1 - no compression
create table test_01
as
select * from dba_objects a where rownum <= 10000;
/*
Table TEST_01 created.
*/
# DDL 출력시 화면에 내용이 다 출력되지 않을때 사용하는 명령어
set long 100000
SELECT DBMS_METADATA.GET_DDL('TABLE','TEST_01') FROM dual;
/*
DBMS_METADATA.GET_DDL('TABLE','TEST_01')
--------------------------------------------------------------------------------
CREATE TABLE "PDB10_ADMIN"."TEST_01"
(
"OWNER" VARCHAR2(128),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(128),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(23),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(128),
"SHARING" VARCHAR2(18),
"EDITIONABLE" VARCHAR2(1),
"ORACLE_MAINTAINED" VARCHAR2(1),
"APPLICATION" VARCHAR2(1),
"DEFAULT_COLLATION" VARCHAR2(100),
"DUPLICATED" VARCHAR2(1),
"SHARDED" VARCHAR2(1),
"CREATED_APPID" NUMBER,
"CREATED_VSNID" NUMBER,
"MODIFIED_APPID" NUMBER,
"MODIFIED_VSNID" NUMBER
)
SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
*/
ANALYZE TABLE TEST_01 COMPUTE statistics
/*
Table TEST_01 analyzed.
*/
/*
What is the meaning of this?
Actually, this statement will collect the statistics.
Information about the table test_01.
So when you make this, Oracle will update the dictionary tables in order to give
more information about this table.
For example, it will calculate the number of rows in the table.
It will calculate the number of blocks and many information.
*/
select blocks, pct_free , compression, compress_for
from user_tables
where table_name = 'TEST_01';
/*
BLOCKS PCT_FREE COMPRESS COMPRESS_FOR
---------- ---------- -------- ------------------------------
186 10 DISABLED
*/
# Case 2 - with basic compression enabled
# Case 1 과 동일한 데이터량이지만 블럭값이 크게 차이난다.
create table test_02 compress basic
as
select * from dba_objects where rownum <= 10000;
/*
Table TEST_02 created.
*/
ANALYZE TABLE TEST_02 COMPUTE statistics
/*
Table TEST_02 analyzed.
*/
select table_name, blocks, pct_free , compression, compress_for
from user_tables
where table_name in ('TEST_01','TEST_02');
/*
TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR
--------------- ---------- ---------- -------- ------------------------------
TEST_01 186 10 DISABLED
TEST_02 43 0 ENABLED BASIC
*/
# Case 3 - Normal insert into empty table defined as compress basic (no effects )
create table test_03 compress basic
as
select * from dba_objects where 1=2
/*
Table TEST_03 created.
*/
select * from test_03
/*
no rows selected
*/
insert into test_03
select * from dba_objects where rownum <= 10000;
commit;
/*
10,000 rows inserted.
Commit complete.
*/
select table_name, blocks, pct_free , compression, compress_for
from user_tables
where table_name like 'TEST___%';
/*
TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR
--------------- ---------- ---------- -------- ------------------------------
TEST_01 186 10 DISABLED
TEST_02 43 0 ENABLED BASIC
TEST_03 0 0 ENABLED BASIC
*/
ANALYZE TABLE TEST_03 COMPUTE statistics
/*
Table TEST_03 analyzed.
*/
# statistics 을 실행해서 블럭값이 refresh 한 경우, insert 처리하면 블럭값이 case 1 보다 커진다
select table_name, blocks, pct_free , compression, compress_for
from user_tables
where table_name like 'TEST___%';
/*
TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR
--------------- ---------- ---------- -------- ------------------------------
TEST_01 186 10 DISABLED
TEST_02 43 0 ENABLED BASIC
TEST_03 244 0 ENABLED BASIC
*/
# Case 4 - Direct path insert(APPEND hint) into empty table defined as compressed
create table TEST_04 compress basic
as
select * from dba_objects where rownum = 0;
/*
Table TEST_04 created.
*/
select * from TEST_04
/*
no rows selected
*/
insert /*+ append */ into TEST_04 select * from dba_objects where rownum <= 10000
/*
The APPEND hint tells the optimizer to perform a direct-path insert, which improves the
performance of INSERT .. SELECT operations for a number of reasons:
Data is appended to the end of the table, rather than attempting to use existing free space within the table.
Data is written directly to the data files, by-passing the buffer cache.
*/
/*
10,000 rows inserted.
*/
ANALYZE TABLE TEST_04 COMPUTE statistics
/*
Table TEST_04 analyzed.
*/
# APPEND hint 를 사용한 경우, 압축이 동시에 이루어진다.
select table_name, blocks, pct_free , compression, compress_for
from user_tables
where table_name like 'TEST___%';
/*
TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR
--------------- ---------- ---------- -------- ------------------------------
TEST_01 186 10 DISABLED
TEST_02 43 0 ENABLED BASIC
TEST_03 244 0 ENABLED BASIC
TEST_04 43 0 ENABLED BASIC
*/
# Case 5 - without compression, then change to compressed
# compress >> move >> ANALYZE .. COMPUTE statistics
create table test_05
as
select * from dba_objects where rownum <= 10000;
/*
Table TEST_05 created.
*/
select table_name, blocks, pct_free , compression, compress_for
from user_tables
where table_name like 'TEST___%';
/*
TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR
--------------- ---------- ---------- -------- ------------------------------
TEST_01 186 10 DISABLED
TEST_02 43 0 ENABLED BASIC
TEST_03 244 0 ENABLED BASIC
TEST_04 43 0 ENABLED BASIC
TEST_05 186 10 DISABLED
*/
alter table test_05 compress basic;
/*
Table TEST_05 altered.
*/
select table_name, blocks, pct_free , compression, compress_for
from user_tables
where table_name like 'TEST___%';
/*
TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR
--------------- ---------- ---------- -------- ------------------------------
TEST_01 186 10 DISABLED
TEST_02 43 0 ENABLED BASIC
TEST_03 244 0 ENABLED BASIC
TEST_04 43 0 ENABLED BASIC
TEST_05 186 10 ENABLED BASIC
*/
/*
move/'COMPUTE statistics' - In this case, Oracle will look to the old data and Oracle will try to compress it.
*/
alter table test_05 move
/*
Table TEST_05 altered.
*/
ANALYZE TABLE TEST_05 COMPUTE statistics
/*
Table TEST_05 analyzed.
*/
select table_name, blocks, pct_free , compression, compress_for
from user_tables
where table_name like 'TEST___%';
/*
TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR
--------------- ---------- ---------- -------- ------------------------------
TEST_01 186 10 DISABLED
TEST_02 43 0 ENABLED BASIC
TEST_03 244 0 ENABLED BASIC
TEST_04 43 0 ENABLED BASIC
TEST_05 43 0 ENABLED BASIC
*/
