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. Automatic Indexes in #Exasol | Uwe Hesse

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..