The table size is increased by 8MB? No compression for UPDATE statement either.Īll this clearly shows that 10g’s Table COMPRESSION would work great for initial BULK LOADS, however subsequent UPDATE’s, DELETE’s and INSERT’s will not result in COMPRESSED blocks. SQL> update test_compressed set object_name = 'XXXXXXXXXXXXXXXXXXXXXXXXXX' where SQL> delete from test_compressed where rownum commit Let’s check other DML statements such as DELETE and UPDATE against the COMPRESSED table. For the same number of rows, the table size only increased by 25MB. Ok, now the size of the COMPRESSED table is 142MB from 117MB. SQL> insert /*+ APPEND */ into TEST_COMPRESSED select * from TEST Now let’s do the same insert with a BULK LOAD (You will see this is not the case with 11g) Wow! From 30MB to 117MB? So, plain INSERT statement does not COMPRESS the data in 10g. SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED' Let’s check the size of the COMPRESSED table. SQL> insert into TEST_COMPRESSED select * from TEST Now let’s do a plain insert into the COMPRESSED table. It is only 30MB, around 30% reduction in size. SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED' Ĭheck out the size of the COMPRESSED table. Now let’s check the size of the COMPRESSED table. SQL> select table_name, compression from dba_tables where table_name like 'TEST SQL> create table TEST_COMPRESSED COMPRESS as select * from test Now create another table called TEST_COMPRESSED with COMPRESS option. SQL> select bytes from dba_segments where segment_name = 'TEST' SQL> select table_name,compression from dba_tables where table_name = 'TEST' With 11g’s Advanced Compression new and updated data are also compressed achieving highest level in storage reduction, while providing performance improvements as compressed blocks result in more data being moved per I/O.įollowing test case was executed in 10.2.0.1 database server.Ī table called TEST was created without COMPRESSION option.
New and updated data were not compressed. While this compression provided some storage reduction, 10g’s table compression only compressed the data during BULK LOAD operations. Oracle provided table level compression feature in 10gR2. This document is meant to show the difference between 10gR2 Table Compression feature and 11gR2’s Advanced Compression.
0 kommentar(er)
