Automatic Table Reorganization in #Exasol

Exasol Logo

One Exasol key feature is the low maintenance effort it has compared to many other database systems. Automatic Table Reorganization is an example for this approach:

SQL_EXA> open schema adam;
EXA: open schema adam;

Rows affected: 0

SQL_EXA> create or replace table t1 as
         select rownum as id from (select 1 from dual connect by level<=1e6); 
EXA: create or replace table t1 as... Rows affected: 1000000 
SQL_EXA> select table_name,table_row_count,delete_percentage from exa_user_tables;
EXA: select table_name,table_row_count,delete_percentage from exa_user_tabl...

TABLE_NAME  TABLE_ROW_COUNT       DELETE
----------- --------------------- ------
T1                        1000000    0.0

1 row in resultset.

Yes we have CREATE OR REPLACE TABLE in Exasol 🙂 DELETE_PERCENTAGE is of course zero after the table just got created. Notice that TABLE_ROW_COUNT is maintained automatically after each DML – no need to gather stats for that.

SQL_EXA> delete from t1 where rowid in
         (select rowid  from t1 where rownum<=1e5); 
EXA: delete from t1 where rowid in ... Rows affected: 100000 
SQL_EXA> select table_name,table_row_count,delete_percentage from exa_user_tables;
EXA: select table_name,table_row_count,delete_percentage from exa_user_tabl...

TABLE_NAME  TABLE_ROW_COUNT       DELETE
----------- --------------------- ------
T1                         900000   10.0

1 row in resultset.

10% of rows are marked as deleted for this table but not yet removed. Let’s delete another 100k rows and see what happens:

SQL_EXA> select table_name,table_row_count,delete_percentage from exa_user_tables;
EXA: select table_name,table_row_count,delete_percentage from exa_user_tabl...

TABLE_NAME  TABLE_ROW_COUNT       DELETE
----------- --------------------- ------
T1                         800000   20.0

1 row in resultset.

Now 20% of rows are marked as deleted. There is a threshold of 25% deleted rows that triggers an automatic reorganization:

SQL_EXA> delete from t1 where rowid in
         (select rowid  from t1 where rownum<=1e5); 
EXA: delete from t1 where rowid in ... Rows affected: 100000 
SQL_EXA> select table_name,table_row_count,delete_percentage from exa_user_tables;
EXA: select table_name,table_row_count,delete_percentage from exa_user_tabl...

TABLE_NAME  TABLE_ROW_COUNT       DELETE
----------- --------------------- ------
T1                         700000    0.0

1 row in resultset.

DELETE_PERCENTAGE went back to zero because of the automatic reorganization – I didn’t have to do anything about it. By the way, if INSERTS follow the DELETE, DELETE_PERCENTAGE decreases automatically also because space consumed by marked as deleted rows is used.

Reorganization can be done manually if desired, though:

SQL_EXA> delete from t1 where rowid in
         (select rowid  from t1 where rownum<=1e5); 
EXA: delete from t1 where rowid in ... Rows affected: 100000 
SQL_EXA> select table_name,table_row_count,delete_percentage from exa_user_tables;
EXA: select table_name,table_row_count,delete_percentage from exa_user_tabl...

TABLE_NAME  TABLE_ROW_COUNT       DELETE
----------- --------------------- ------
T1                         600000   14.2

1 row in resultset.

SQL_EXA> reorganize table t1 enforce;
EXA: reorganize table t1 enforce;

Rows affected: 0

SQL_EXA> select table_name,table_row_count,delete_percentage from exa_user_tables;
EXA: select table_name,table_row_count,delete_percentage from exa_user_tabl...

TABLE_NAME  TABLE_ROW_COUNT       DELETE
----------- --------------------- ------
T1                         600000    0.0

1 row in resultset.

Although a manual reorganization is possible, it is usually not necessary. Typically, only after the Exasol cluster has been enlarged by adding one or more nodes a reorganization is required.

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: