Datometry Documentation

You can specify whether or not Hyper-Q collects statistics on temporary tables created during nested case emulation. This allows you to determine when statistics are collected, particularly if they are not needed and may impact performance.

The stats_on_temp_tables parameter applies to temporary tables created by Hyper-Q as part of an emulation (as opposed to temporary tables created explicitly by the client). Currently, the statistics collection is enabled only for the un-nesting of complex case expressions, so enabling stats_on_temp_tables only affects queries that require this type of emulation. In the trace log these queries are identified with the event code DLE0049--Eliminate Nested Case Expressions.

To learn about the trace log, see Hyper-Q Trace Log File.

If the emulation creates a large table, and that table is used in joins, it may be beneficial to enable statistics collection to allow the query optimizer to choose a better query plan. If the table is small, collecting statistics on it may create more overhead than it improves.

The stats_on_temp_tables parameter can be specified as either:

The syntax for stats_on_temp_tables is:

stats_on_temp_tables = true | false

where:

You can specify stats_on_temp_tables as a global setting in the Hyper-Q configuration file (dtm.ini), or as part of a query using @dtm_.

SELECT * FROM (SELECT CASE WHEN i=1 then (CASE WHEN j =2 then CASE WHEN k=3
--@dtm_stats_on_temp_tables=true
then 1 else 0 end else 0 end) else 0 end as x, a, b, c from test1, test2)
 t
where t.a=1 and t.b=2;

"gateway".stats_on_temp_tables = true