Optimization of nested SQL queries revisited
SIGMOD '87 Proceedings of the 1987 ACM SIGMOD international conference on Management of data
Optimization and dataflow algorithms for nested tree queries
VLDB '89 Proceedings of the 15th international conference on Very large data bases
SIGMOD '94 Proceedings of the 1994 ACM SIGMOD international conference on Management of data
On optimizing an SQL-like nested query
ACM Transactions on Database Systems (TODS)
Orthogonal optimization of subqueries and aggregation
SIGMOD '01 Proceedings of the 2001 ACM SIGMOD international conference on Management of data
Access path selection in a relational database management system
SIGMOD '79 Proceedings of the 1979 ACM SIGMOD international conference on Management of data
ICDE '96 Proceedings of the Twelfth International Conference on Data Engineering
VLDB '87 Proceedings of the 13th International Conference on Very Large Data Bases
Efficiently Publishing Relational Data as XML Documents
VLDB '00 Proceedings of the 26th International Conference on Very Large Data Bases
Optimization of Queries with User-defined Predicates
VLDB '96 Proceedings of the 22th International Conference on Very Large Data Bases
The Volcano Optimizer Generator: Extensibility and Efficient Search
Proceedings of the Ninth International Conference on Data Engineering
ACM SIGMOD Record
AniPQO: almost non-intrusive parametric query optimization for nonlinear cost functions
VLDB '03 Proceedings of the 29th international conference on Very large data bases - Volume 29
Strategies for query unnesting in XML databases
ACM Transactions on Database Systems (TODS)
Efficient auditing for complex SQL queries
Proceedings of the 2011 ACM SIGMOD International Conference on Management of data
Fundamentals of order dependencies
Proceedings of the VLDB Endowment
Expressiveness and complexity of order dependencies
Proceedings of the VLDB Endowment
Hi-index | 0.00 |
Nested iteration is an important technique for query evaluation. It is the default way of executing nested subqueries in SQL. Although decorrelation often results in cheaper non-nested plans, decorrelation is not always applicable for nested subqueries. Nested iteration, if implemented properly, can also win over decorrelation for several classes of queries. Decorrelation is also hard to apply to nested iteration in user-defined SQL procedures and functions. Recent research has proposed evaluation techniques to speed up execution of nested iteration, but does not address the optimization issue. In this paper, we address the issue of exploiting the ordering of nested iteration/procedure calls to speed up nested iteration. We propose state retention of operators as an important technique to exploit the sort order of parameters/correlation variables. We then show how to efficiently extend an optimizer to take parameter sort orders into consideration. We implemented our evaluation techniques on PostgreSQL, and present performance results that demonstrate significant benefits.