COOL data overhead

22 Jun 2007

After moving one of the tables to an "enhanced" version with :

create table PDBST005_F0001_IOVS_switch as
(
select  OBJECT_ID,
        CHANNEL_ID,
        IOV_SINCE,
        IOV_UNTIL,
        USER_TAG_ID,
        TO_DATE(SUBSTR(SYS_INSTIME,0,19), 'YYYY-MM-DD_HH24:MI:SS') "SYS_INSTIME",
        TO_DATE(SUBSTR(LASTMOD_DATE,0,19), 'YYYY-MM-DD_HH24:MI:SS') "LASTMOD_DATE",
        ORIGINAL_ID,
        NEW_HEAD_ID,
        S
from    PDBST005_F0001_IOVS
);

And the result in term of space is the following :

SQL> select sum(bytes)/power(2,30)||' GB' "Size" from user_extents where segment_name = 'PDBST005_F0001_IOVS';

Size
-------------------------------------------
1.9375 GB

SQL> select sum(bytes)/power(2,30)||' GB' "Size" from user_extents where segment_name = 'PDBST005_F0001_IOVS_SWITCH';

Size
-------------------------------------------
1.4375 GB

It should not affects indexes because none of them is based on the modified columns.

Here there was a 100 Bytes payload and we saved around 18% of space (taking into account the indexes).