Skip to main content

Datometry Documentation

Enable or Disable Statistics Collection on Temp Tables

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.

Note

If the temp table does not participate in joins or other filters, Hyper-Q will detect that and not create any additional statistics whether or not stats_on_temp_tables is enabled or disabled.

The stats_on_temp_tables parameter can be specified as either:

  • A single‑statement request in a query override.

  • A global setting in the Hyper-Q configuration file (dtm.ini).

The syntax for stats_on_temp_tables is:

stats_on_temp_tables = true | false

where:

Parameter

Description

true

Specifies that Hyper-Q collect statistics on temporary tables created during nested case emulation.

false

Disables statistic collection on temporary tables.

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_.

Example 18. Specify Statistics Collection on Temporary Tables as a Hyper-Q Hint

You can specify stats_on_temp_tables as a Hyper-Q hint.

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;


Example 19. Specify Statistics Collection on Temporary Tables as a Global Setting

You can specify stats_on_temp_tables as a global setting in the Hyper-Q configuration file (dtm.ini). When set as a global setting, the option you specify is applied to all sessions.

"gateway".stats_on_temp_tables = true