Overview | Criteria | Joins | Subqueries | Distinct | Aggregations | Ordering
Omnidex has substantial support for subqueries in the WHERE clause. The the select item from the subquery should be indexed with Omnidex, as should the reference column in the outer query. Omnidex will attempt to process the subquery and automatically continue processing into the outer query solely through the use of indexing.
Subqueries can be used to improve the optimization of complex statements. Some statements join many tables together, with some tables being used for criteria and other tables being used to return data. Often the processing of tables being used for criteria can be shifted into subqueries, and this can be used to improve the optimization of the query within Omnidex.
Omnidex does not support correlated subqueries. Correlated subqueries are subqueries that reference columns from outside of the query, usually as criteria within the subquery.
Many correlated subqueries can rewritten to avoid the correlation through the use of table joins. This strategy will often allow Omnidex to optimize the query.
Omnidex will often optimize statements that have subqueries in the FROM clause of a SELECT statement. Many times, a query with just one subquery in the FROM clause can be processed without having to subset the subquery. In more complex situations, or if there are multiple subqueries in the FROM clause, Omnidex will create intermediate result sets for each subquery and join them together.
If Omnidex must subset the subqueries into intermediate result sets, then most processing from that point forward does not have the advantage of Omnidex indexing and will be slower. On larger databases, it may be beneficial to restructure the query so that more Omnidex indexing can be used. Typically, this involves limiting the query to a single subquery in the FROM clause, or entirely eliminating subqueries in the FROM clause.
See also: