Search My Oracle Blog

Custom Search

09 May, 2016

Compression -- 7 : Updating after BASIC Compression

In the first blog post on compression, I had shown BASIC Compression and also the effect of executing an UPDATE on a table with BASIC compression.

To expand on the them of UPDATEs of BASIC compression blocks ....

SQL> select count(*) from source_data;

  COUNT(*)
----------
    367156

SQL> create table target_comp row store compress basic as select * from source_data where 1=2;

Table created.

SQL> select pct_free from user_tables where table_name = 'TARGET_COMP';

  PCT_FREE
----------
         0

SQL> insert /*+ APPEND */ into target_comp
  2  select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_comp
  2  select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_comp
  2  select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> analyze table target_comp compute statistics;

Table analyzed.

SQL> select chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

 CHAIN_CNT     BLOCKS
---------- ----------
         0       4452

SQL> 
SQL> exec dbms_stats.gather_table_stats('','TARGET_COMP');

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

  NUM_ROWS  CHAIN_CNT     BLOCKS
---------- ---------- ----------
   1101468          0       4452

SQL> 


So we have a table with 1.1million rows and no Row Chaining.

What happens if we update about 20% of the rows ?

SQL> begin
  2  for rec in (select rowid from target_comp where rownum < 220001)
  3  loop
  4    update target_comp set owner=owner where rowid=rec.rowid;
  5    commit;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> analyze table target_comp compute statistics;

Table analyzed.

SQL> select chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

 CHAIN_CNT     BLOCKS
---------- ----------
    202189       7882

SQL> exec dbms_stats.gather_table_stats('','TARGET_COMP');

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt, blocks from user_tables where table_name = 'TARGET_COMP';

  NUM_ROWS  CHAIN_CNT     BLOCKS
---------- ---------- ----------
   1101468     202189       7882

SQL> 


I have updated 220,000 rows without actually increasing the notional length of each row (I set OWNER=OWNER).  Yet, The CHAIN_CNT is now 202K and the table's HighWaterMark has expanded from 4,452 blocks to 7,882 blocks.  A significant increase !
(YMMV may vary in your tests !)

It was Jonathan Lewis who suggested getting the Chain Count (or LIST CHAINED ROWS) to understand the impact of UPDATEs on a table with BASIC compression.
.
.
.


No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016