SQL Query Order of Operations and Delegation
Order of operations for SQL queries refers to how Spectrum Spatial (or any query processor that generates an execution plan) resolves the individual clauses in the query.
Delegation is a set of rules that explain what parts of the query can be handed off to the data source provider so Spectrum Spatial does not have to do all the processing. If the data source provider supports the operation, the query may be delegated, provided it conforms to the rules of delegation.
By understanding the order of operations and how the order of operations in a query affect delegation to a data source provider, you can create powerful queries without sacrificing performance.
select city, avg(hhinc) FROM table WHERE state='ny' GROUP BY city ORDER BY city
The following shows the order of operations that occurs when the query is evaluated.
[FROM table] [WHERE state='ny'] [SELECT city, avg(hhinc) GROUP BY city] [ORDER BY city]
Notice that the filtering of state='ny' precedes carrying out the selection of the rows. This order is important when delegating the work to the data source provider.
For example, if the data source provider does not support the WHERE clause operator '=', the SELECT clause cannot be delegated either according to Spectrum Spatial's rules. In this case, Spectrum Spatial will need to do the work.
Most of the benefit of delegation comes from a well-constructed WHERE clause. If the WHERE clause cannot be delegated, then the rest of the query cannot be delegated. For more on the WHERE clause rules see Delegation Rules: WHERE Clause.