This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
admin:indexing:advanced:subqueries [2012/02/15 21:21] doc created |
admin:indexing:advanced:subqueries [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 12: | Line 12: | ||
| **[[admin:indexing:advanced:subqueries|Subqueries]]** | | **[[admin:indexing:advanced:subqueries|Subqueries]]** | | ||
| [[admin:indexing:advanced:distinct|Distinct]] | | [[admin:indexing:advanced:distinct|Distinct]] | | ||
| + | [[admin:indexing:advanced:aggregations|Aggregations]] | | ||
| [[admin:indexing:advanced:ordering|Ordering]] | [[admin:indexing:advanced:ordering|Ordering]] | ||
| ---- | ---- | ||
| - | ==== Overview ==== | + | ==== Subqueries ==== |
| - | A basic indexing strategy accommodates most queries, but there are advanced techniques that optimize more unusual situations. Queries with particularly complex criteria or complex table relationships can be optimized using these techniques. Omnidex also has features designed specifically to optimize distinct counts and other complex aggregations. | + | === Subqueries in the WHERE clause === |
| - | Before embarking on these techniques, it is important to understand [[admin:optimization:plans:home|Omnidex Query Plans]]. These plans describe the steps that are involved in processing a query, including the use of indexes. These query plans are essential to insuring the best performance for queries. | + | 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. |
| - | == Criteria == | + | 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. |
| - | * Composite indexes | + | |
| - | * Expression-based Indexes | + | |
| - | * Rollup tables | + | |
| - | * Categorizing data | + | |
| - | == Joins == | ||
| - | * Prejoined indexes | ||
| - | * Denormalization | ||
| - | * Multi-level hierarchies | ||
| - | * Record-complex indexing | ||
| - | == Subqueries == | + | === Correlated Subqueries === |
| - | * Structure of subqueries | + | |
| - | * Joins instead of subqueries in FROM | + | |
| - | * | + | |
| - | == Distinct operations == | + | Omnidex does not support correlated subqueries. Correlated subqueries are subqueries that reference columns from outside of the query, usually as criteria within the subquery. |
| - | * Composite indexes | + | |
| - | * Grids: Distinct constraints | + | |
| - | == Group By == | + | 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. |
| - | * Composite indexes | + | |
| - | * Rollup tables | + | === Subqueries in the FROM clause === |
| - | * Grids: Distinct constraints | + | |
| - | + | 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. | |
| - | == Order By == | + | |
| - | * Composite indexes | + | 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. |
| - | * | + | |