The result of a query can be cached in Exasol to the effect that repeated identical queries complete in no time. This feature has been introduced in version 5 and is enabled by default.

SQL_EXA> select session_value,system_value  from exa_parameters where parameter_name='QUERY_CACHE';
EXA: select session_value,system_value  from exa_parameters where parameter...

SESSION_VALUE        SYSTEM_VALUE
-------------------- --------------------
ON                   ON

1 row in resultset.

The Query Cache can be (de-)activated on the session level as well as on the system level.

SQL_EXA> alter session set query_cache='off';
EXA: alter session set query_cache='off';

Rows affected: 0
SQL_EXA> select object_name,mem_object_size/1024/1024 as mb from exa_user_object_sizes where object_name='T';
EXA: select object_name,mem_object_size/1024/1024 as mb from exa_user_objec...

OBJECT_NAME          MB
-------------------- ----------------------------------
T                             1537.49641990661621093750

1 row in resultset.

SQL_EXA> select count(*) from t;
EXA: select count(*) from t;

COUNT(*)
---------------------
            320000000

1 row in resultset.
SQL_EXA> set timing on;
SQL_EXA> select count(*) from t where numcol2=42;
EXA: select count(*) from t where numcol2=42;

COUNT(*)
---------------------
               888896

1 row in resultset.


Timing element: 4
Elapsed: 00:00:03.022

SQL_EXA> select count(*) from t where numcol2=42;
EXA: select count(*) from t where numcol2=42;

COUNT(*)
---------------------
               888896

1 row in resultset.


Timing element: 5
Elapsed: 00:00:02.620

SQL_EXA> select count(*) from t where numcol2=42;
EXA: select count(*) from t where numcol2=42;

COUNT(*)
---------------------
               888896

1 row in resultset.


Timing element: 6
Elapsed: 00:00:02.724

Without using the Query Cache the repeated query takes roughly 3 seconds.

SQL_EXA> alter session set query_cache='on';
EXA: alter session set query_cache='on';

Rows affected: 0


Timing element: 7
Elapsed: 00:00:00.008

SQL_EXA> select count(*) from t where numcol2=42;
EXA: select count(*) from t where numcol2=42;

COUNT(*)
---------------------
               888896

1 row in resultset.


Timing element: 8
Elapsed: 00:00:00.009

Lightning fast! If statement profiling is enabled, QUERY CACHE RESULT shows as PART_NAME in tables like EXA_USER_PROFILE_LAST_DAY.
Also EXECUTION_MODE from EXA_SQL_LAST_DAY shows the usage of the Query Cache:

SQL_EXA> set timing off;
SQL_EXA> select current_statement;
EXA: select current_statement;

CURRENT_STATEMENT
---------------------
                   35

1 row in resultset.

SQL_EXA> show autocommit;
AUTOCOMMIT = "ON"
SQL_EXA> select command_name,duration,row_count,execution_mode from exa_sql_last_day where stmt_id=33 and session_id=current_session;
EXA: select command_name,duration,row_count,execution_mode from exa_sql_las...

COMMAND_NAME                             DURATION    ROW_COUNT             EXECUTION_MODE
---------------------------------------- ----------- --------------------- --------------------
SELECT                                         0.005                     1 CACHED

1 row in resultset.

If DML changes the table, the result in the Query Cache is invalidated automatically:

SQL_EXA> update t set numcol2=1 where rowid in (select rowid from t limit 1);
EXA: update t set numcol2=1 where rowid in (select rowid from t limit 1);
Rows affected: 1

SQL_EXA> set timing on;
SQL_EXA> select count(*) from t where numcol2=42;
EXA: select count(*) from t where numcol2=42;

COUNT(*)
---------------------
888896

1 row in resultset.

Timing element: 10
Elapsed: 00:00:02.870

SQL_EXA> set timing off;
SQL_EXA> select current_statement;
EXA: select current_statement;

CURRENT_STATEMENT
---------------------
51

1 row in resultset.

SQL_EXA> select command_name,duration,row_count,execution_mode from exa_sql_last_day where stmt_id=49 and session_id=current_session;
EXA: select command_name,duration,row_count,execution_mode from exa_sql_las...

COMMAND_NAME DURATION ROW_COUNT EXECUTION_MODE
---------------------------------------- ----------- --------------------- --------------------

0 rows in resultset.

There’s a 1 Minute interval for syncing the statistic tables. It can be triggered manually, though:

SQL_EXA> flush statistics;
EXA: flush statistics;

Rows affected: 0

SQL_EXA> commit;
EXA: commit;

Rows affected: 0

SQL_EXA> select command_name,duration,row_count,execution_mode from exa_sql_last_day where stmt_id=49 and session_id=current_session;
EXA: select command_name,duration,row_count,execution_mode from exa_sql_las...

COMMAND_NAME                             DURATION    ROW_COUNT             EXECUTION_MODE
---------------------------------------- ----------- --------------------- --------------------
SELECT                                         2.862                     1 EXECUTE

1 row in resultset.

Runtime and EXECUTION_MODE EXECUTE confirms that the Query Cache was invalidated by the UPDATE above. Now it’s automatically refreshed:

SQL_EXA> set timing on;
SQL_EXA> select count(*) from t where numcol2=42;
EXA: select count(*) from t where numcol2=42;

COUNT(*)
---------------------
               888896

1 row in resultset.


Timing element: 11
Elapsed: 00:00:00.010

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.