Oracle Table Compression

Starting with Oracle 8i oracle introduce to compress "index" or "IOT" table.

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c08schem.htm#4602

Oracle 9ir2 Introduce head organized TABLE compression.

Oracle 9ir2 compresses data by eliminating duplicate values in a database block.

We can compress tables and materialized views. For partitioned tables, it is possible to choose to compress some or all partitions.
Compression attribute can be declared for a tablespace, table or partition level.

Compression occurs while data is being bulk inserted or bulk loaded these operation include.

1. Direct Path SQL * loader

2. Create table ... as select statement

3. Parallel INSERT

4. Serial INSERT with an APPEND hint

Data compression works for all data types except LOB.

SQL> create table T1 as select * from all_objects;
Table created.

SQL> --COMPRESSION is not enable for the table

SQL> exec dbms_stats.gather_table_stats('SYSTEM','T1');
PL/SQL procedure successfully completed.

SQL> select round(blocks*8) tablesize from

2 dba_tables where table_name='T1';
TABLESIZE

----------

7944

SQL> --Now enable compression for the table

SQL> alter table T1 move COMPRESS;
Table altered.

SQL> exec dbms_stats.gather_table_stats('SYSTEM','T1');
PL/SQL procedure successfully completed.

SQL> select round(blocks*8) tablesize from

2 dba_tables where table_name='T1';
TABLESIZE

----------

2208

1.We can Enable COMPRESSION at Table Creation time.

CREATE TABLE TABLE_NAME(COLUMN DATATYPE) COMPRESS;

2. We can enable table compress on existing table

ALTER TABLE TABLE_NAME MOVE COMPRESS;

3. For view compression is enable or disabled for the table check USER_TABLES views

select TABLE_NAME,COMPRESSION

from user_tables

where table_name='TABLE_NAME';

4. For DISABLE compression for the table

ALTER TABLE TABLENAME MOVE NOCOMPRESS;

If you want to read more please refer following links

http://www.oracle.com/technology/products/bi/pdf/o9ir2_compression_twp.pdf

http://www.vldb.org/conf/2003/papers/S28P01.pdf

Table Fragmentation

What is Table fragmentation


When rows are not stored contiguously, or if rows are split onto more than one page, performance decreases because these rows require additional page accesses. Table fragmentation is distinct from file fragmentation.

When lots of DML operation apply on tables then tables is fragmented.
because DML is not release free space from table below HWM.

Hint: HWM is indicator for USED BLOCKS in database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted.
Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark in a full table scan.

DDL statement always reset HWM.


How to find table fragmentation

SQL> select count(*) from big1;

1000000 rows selected.

SQL> delete from big1 where rownum <= 300000;

300000 rows deleted.

SQL> commit;

Commit complete.

SQL> update big1 set object_id = 0 where rownum <=350000;

342226 rows updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

Table Size ( with fragmented)

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 72952kb

Actual data in table

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30604.2kb

Note= 72952 – 30604 = 42348 Kb is wasted space in table

The difference between two values is 60% and Pctfree 10% (default) so table is 50% extra space which is wasted because there is no data.

How to reset HWM / remove fragemenation


For that we need to reorganize fragmented table

We have four options to reorganize fragmented tables

1. alter table … move + rebuild indexes
2. export / truncate / import
3. create table as select ( CTAS)
4. dbms_redefinition



Option: 1 “alter table … move + rebuild indexes”

SQL> alter table BIG1 move;

Table altered.

SQL> select status,index_name from user_indexes
2 where table_name = 'BIG1';

STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE BIGIDX

SQL> alter index bigidx rebuild;

Index altered.

SQL> select status,index_name from user_indexes
2 where table_name = 'BIG1';

STATUS INDEX_NAME
-------- ------------------------------
VALID BIGIDX


SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 38224kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 30727.37kb

Option: 2 “Create table as select”

SQL> create table big2 as select * from big1;

Table created.

SQL> drop table big1 purge;

Table dropped.

SQL> rename big2 to big1;

Table renamed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 68986.97kb

SQL> select status from user_indexes
2 where table_name = 'BIG1';

no rows selected

SQL> --Note we need to create all indexes.

Option: 3 “export / truncate / import”

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42535.54kb

SQL> select status from user_indexes where table_name = 'BIG1';

STATUS
--------
VALID

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>exp scott/tiger@Orcl file=c:\big1.dmp tables=big1

Export: Release 10.1.0.5.0 - Production on Sat Jul 28 16:30:44 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table BIG1 468904 rows exported
Export terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:12 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> truncate table big1;

Table truncated.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>imp scott/tiger@Orcl file=c:\big1.dmp ignore=y

Import: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:54 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "BIG1" 468904 rows imported
Import terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:32:21 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42535.54kb

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 51840kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'BIG1';

TABLE_NAME size
------------------------------ ------------------------------------------
BIG1 42542.27kb

SQL> select status from user_indexes where table_name = 'BIG1';

STATUS
--------
VALID

SQL> exec dbms_redefinition.can_redef_table('SCOTT','BIG1',-
> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Option: 4 “dbms_redefinition”

SQL> create table TABLE1 (
2 no number,
3 name varchar2(20) default 'NONE',
4 ddate date default SYSDATE);

Table created.

SQL> alter table table1 add constraint pk_no primary key(no);

Table altered.

SQL> begin
2 for x in 1..100000 loop
3 insert into table1 ( no , name, ddate)
4 values ( x , default, default);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create or replace trigger tri_table1
2 after insert on table1
3 begin
4 null;
5 end;
6 /

Trigger created.

SQL> select count(*) from table1;

COUNT(*)
----------
100000

SQL> delete table1 where rownum <= 50000;

50000 rows deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 2960kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 822.69kb


SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg
SQL> --First check table is condidate for redefinition.
SQL>
SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT',-
> 'TABLE1',-
> sys.dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

SQL> --After verifying that the table can be redefined online, you manually crea
te an empty interim table (in the same schema as the table to be redefined)
SQL>
SQL> create table TABLE2 as select * from table1 WHERE 1 = 2;

Table created.

SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --This procedure keeps the interim table synchronized with the original tab
le.
SQL>
SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> --Create PRIMARY KEY on interim table(TABLE2)
SQL> alter table TABLE2
2 add constraint pk_no1 primary key (no);

Table altered.

SQL> create trigger tri_table2
2 after insert on table2
3 begin
4 null;
5 end;
6 /

Trigger created.

SQL> --Disable foreign key on original table if exists before finish this proces
s.
SQL>
SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT',-
> 'TABLE1',-
> 'TABLE2');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1');

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 1376kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size"
2 from user_tables
3 where table_name = 'TABLE1';

TABLE_NAME size
------------------------------ ------------------------------------------
TABLE1 841.4kb

SQL> select status,constraint_name
2 from user_constraints
3 where table_name = 'TABLE1';

STATUS CONSTRAINT_NAME
-------- ------------------------------
ENABLED PK_NO1

SQL> select status ,trigger_name
2 from user_triggers
3 where table_name = 'TABLE1';

STATUS TRIGGER_NAME
-------- ------------------------------
ENABLED TRI_TABLE2

SQL> drop table TABLE2 PURGE;

Table dropped.

RENAME TABLE

When we rename table how many dependent objects effected

SQL> create table TAJ ( no number, ddate date, name varchar2(20));

Table created.

PRIMARY KEY and FOREIGN KEY
SQL> alter table taj add constraint pk_no primary key(no);

Table altered.

SQL> alter table taj
2 add constraint fk_name foreign key (name)
3 references master(name);

Table altered.

Database Object ( INDEX,VIEW,SYNONYM,PROCEDURE,TRIGGER,FUNCTION)
SQL> create index idx_name on taj(name);

Index created.

SQL> create view vw_taj as select * from taj;

View created.

SQL> create synonym sy_taj for taj;

Synonym created.

SQL> ed
Wrote file afiedt.buf

1 create or replace procedure PRO_TAJ
2 is
3 begin
4 for x in ( select * from taj) loop
5 dbms_output.put_line(X.NO);
6 end loop;
7* end;
SQL> /

Procedure created.


SQL> ed
Wrote file afiedt.buf

1 create or replace trigger tr_taj
2 after insert
3 on TAJ
4 for each row
5 begin
6 insert into TAJ_COPY values ( :new.no,:new.ddate,:new.name);
7* end;
8 /

Trigger created.

SQL> show err
No errors.
SQL> insert into taj values ( 2,sysdate,'TAJ');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from taj_copy;

COUNT(*)
----------
2


SQL> create or replace FUNCTION fun_taj
2 (no_in in number)
3 return varchar2
4 is
5 cname varchar2(20);
6 cursor c1 is
7 select name
8 from taj
9 where no = no_in;
10 begin
11 open c1;
12 fetch c1 into cname;
13 close c1;
14 return cname;
15 end;
16 /

Function created.

PRIVILEGES
SQL> grant SELECT on taj to SCOTT;

Grant succeeded.

TWO WAY TO CHANGE TABLE NAME
1. alter table [oldtablename] rename to [newtablename];
2. rename [oldtablename] to [newtablename];


SQL> rename TAJ to NEW_TAJ;

Table renamed.


SQL> select TABLE_NAME,CONSTRAINT_NAME
2 from user_constraints
3 where table_name in ('TAJ','NEW_TAJ');

TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
NEW_TAJ PK_NO
NEW_TAJ FK_NAME


SQL> select TABLE_NAME, INDEX_NAME
2 from user_indexes
3 where table_name in ('TAJ','NEW_TAJ');

TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
NEW_TAJ PK_NO
NEW_TAJ IDX_NAME


SQL> column object_name format a20
SQL> ed
Wrote file afiedt.buf

1 select OBJECT_NAME,OBJECT_TYPE,STATUS
2 from user_objects
3 where object_type in ('PROCEDURE','FUNCTION','TRIGGER','VIEW','SYNONYM')
4* order by status
SQL> /

OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
VW_TAJ VIEW INVALID
SY_TAJ SYNONYM INVALID
PRO_TAJ PROCEDURE INVALID
FUN_TAJ FUNCTION INVALID
TR_TAJ TRIGGER INVALID


Note: We have to recreate ALL INVALID OBJECTS (MENTION IN ABOVE QUERY)when table name is changed

Table Size

SQL> create table ORACLE as select * from all_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats('SCOTT','ORACLE');

PL/SQL procedure successfully completed.
SQL> column segment_name format a25
SQL> column table_name format a25
SQL> select segment_name,round((bytes/1024),2) ||'kb' "SIZE"
2 from user_segments
3 where segment_name = 'ORACLE';

SEGMENT_NAME SIZE
------------------------- ------------------------------------------
ORACLE 5120kb

SQL> --Table Size
SQL> select table_name, round((blocks*8),2)||'kb' "SIZE"
2 from user_tables
3 where table_name = 'ORACLE';

TABLE_NAME SIZE
------------------------- ------------------------------------------
ORACLE 4504kb

SQL> --Actual Data Size in Table
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "SIZE"
2 from user_tables
3 where table_name = 'ORACLE';

TABLE_NAME SIZE
------------------------- ------------------------------------------
ORACLE 3575.67kb

SQL>

Note :
SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

Table Copy From OneDB to AnotherDB

You can use Three Method.
1.Database link
2.Export or Import
3.Copy Command.

------------------------------------------
1.Database link
sql>conn u1/u1@db2
sql>create table a as select * from all_objects where rownum <= 10; table created. For database link you have "database link" or "create session" privs. sql>conn u1/u1@db1
connected
sql>create database link db2
connect to u1
identified by u1
using 'db2';
database link created
sql>create table t as select * from a@db2;
table created
sql>select count(*) from t;
----------------------------------------------------------
2.Through Export or Import Utilities.
------------------------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table d as select * from all_objects where rownum <= 10; Table created. SQL> host exp a1/a1@db1 tables=d file=d:\oracle\d.dmp log=d:\oracle\d.log
Export: Release 10.1.0.2.0 - Production on Sat Dec 23 18:24:52 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table D 10 rows exported
Export terminated successfully without warnings.
SQL> conn a1/a1@db2
Connected.
SQL> host imp a1/a1@db2 fromuser=a1 touser=a1 file=d:\oracle\d.dmp log=d:\oracle
\d.log ignore=y
Import: Release 10.1.0.2.0 - Production on Sat Dec 23 18:25:58 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "D" 10 rows imported
Import terminated successfully without warnings.
------------------------------------------------------
3.Through Copy Command.
----------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table m as select * from all_objects where rownum <= 10; Table created. SQL> con a1/a1@db2
Connected.
SQL> copy from a1/a1@db1 to a1/a1@db2 -
> create m -
> using select * from m;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table M created.
10 rows selected from a1@db1.
10 rows inserted into M.
10 rows committed into M at a1@db2.