In some cases we can avoid copying sub-query result to the temporary table. We can even use sub-query cursor “as is” – so it could be used as a result of the whole query. In other words there is almost zero overhead against a simple query.
For now such optimization can be applied if a query:
contains only “*” item in the select list clause
has no “where” clause
has no “group by” clause
has no “having” clause
has no “distinct” clause
is not a part of some join operation
has no “order by” clause
has no binding values
sub-query is not a “union/except/intersect” result
SELECT * FROM (SELECT * FROM t1 WHERE f1 > 5);
SELECT * FROM (SELECT t1.f1, t2 f1 FROM t1 JOIN t2 ON l1 WHERE t1.f1 > 5 AND t2.f1 <3);
Most Valentina SQL commands of kind CREATE SOMETHING have clause ‘OR REPLACE’. You should understand that this clause forces DROP of existed object before new will be created. This means that all its sub-objects also will be deleted. To avoid this use ALTER command.