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.

If you want to batch-process over a number of objects in Exasol, scripts that work with the Data Dictionary might do the trick. Let’s say I want to drop all of my tables that start with the letter P:

SQL_EXA> open schema fred;
EXA: open schema fred;

Rows affected: 0

SQL_EXA> create or replace script droptables
          as
          res=query([[select * from exa_user_tables where table_name like 'P%']])
          for i=1, #res
          do
           query([[drop table ::t]],{t=res[i].TABLE_NAME})
          end
        /
EXA: create or replace script droptables...

Rows affected: 0

SQL_EXA> select table_name from exa_user_tables;
EXA: select table_name from exa_user_tables;

TABLE_NAME
--------------------------------------------------
P9_TAB
P8_TAB

2 rows in resultset.

Amazingly enough, DROP TABLE is an operation that can be rolled back in Exasol. So to safeguard, I can disable autocommit here.

SQL_EXA> set autocommit off;
SQL_EXA> execute script droptables;
EXA: execute script droptables;

Rows affected: 0

SQL_EXA> select table_name from exa_user_tables;
EXA: select table_name from exa_user_tables;

TABLE_NAME
--------------------------------------------------

0 rows in resultset.

SQL_EXA> rollback;
EXA: rollback;

Rows affected: 0

SQL_EXA> select table_name from exa_user_tables;
EXA: select table_name from exa_user_tables;

TABLE_NAME
---------------------------------------------------
P9_TAB
P8_TAB

2 rows in resultset.