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.
Pickleball spielen 