How can I tell if my view/subquery is getting merged with the outer query?
In SingleStore, views and subqueries are the same.
The default plan is to merge subqueries into the parent/outer query without materializing the subquery.
The above transformation can be overwritten with the hint "with(no_merge_this_select=true)", which results in the subquery being materialized and projected as a table. Note - the hint should be placed in the subquery itself.
This hint is useful to:
- Force a particular table join order.
- Prevent a table in the outer query from being joined directly to a table in the subquery.
- Break up counts of tables being joined in the query tree to keep below the limits listed below, so the cost-based optimizer is still used, not the logical optimizer.
- v7.1- distributed_optimizer_max_join_size
- v7.3+ distributed_optimizer_unrestricted_search_threshold
- Apply another hint only within the scope of the subquery; e.g., a particular type of join operator or join order.