Method and system for dynamic join reordering
View Patent ↗A database engine and a system running a database engine utilize a dynamic join reordering feature to change the order of two or more join operations while a query is executing. The database engine starts execution of the query with an initial join order setting but monitors the execution of the query to determine whether the initial join order or some other join order would provide better runtime performance. If another join order would provide better performance, then the database engine can change the join order during query execution and complete the query using the new join order.
1. An apparatus, comprising:
at least one computer processor; and
program code configured upon execution by the at least one processor to monitor a query during runtime, wherein the query involves a plurality of join operations, wherein the plurality of join operations include a first join that includes a first table and a second table and a second join that includes the first table and a third table, and wherein the program code is configured to monitor the query by:
running the query according to a first join order;
generating a first portion of a result set for the query while running the query according to the first join order, including adding to the result set a first record that matches the plurality of join operations in the query;
concurrent with running the query, collecting performance statistics about each of the join operations;
dynamically changing the first join order, during running of the query, to a second join order based on the statistics;
where said dynamically changing the first join order, during running of the query, to a second join order based on the statistics comprises:
determining respective fan-in statistics for the first join and second join; and
dynamically changing the first join order to the second join order if the respective fan-in statistics indicate that the second join is more likely to cause fan-in than the first join; and
generating a second portion of the result set for the query while running the query according to the second join order, including adding to the result set a second record that matches the plurality of join operations in the query such that the result set includes at least the first and second records, the first and second portions of the result set generated for the same execution of the query.
2. The apparatus according to claim 1 , wherein the program code is further configured to collect additional statistics about each of the join operations after the first join order is changed to the second join order.
3. The apparatus according to claim 2 , wherein the program code is further configured to change the second join order to either the first join order or a third join order used on the additional statistics.
4. The apparatus according to claim 1 , wherein the program code is further configured to determine respective fan-out statistics for the first join and the second join, and dynamically change the first join order to the second join order based on a combination of the respective fan-in and fan-out statistics.
5. The apparatus according to claim 1 , wherein the program code is further configured to identify a predetermined, sample size, collect statistics for the predetermined sample size, evaluate the collected statistics, and dynamically change the first join order to the second join order based on the collected statistics.
6. The apparatus according to claim 5 , wherein the program code is further configured to collect additional statistics for substantially all of the query, compare the additional statistics with the collected statistics, and adjust the predetermined sample size, for use by a subsequent query, according to results of the comparison.
7. The apparatus according to claim 1 , wherein the program rode is further configured to run another query after the query and select an initial join order for the other query based on the collected performance statistics.
8. A program product, comprising:
a recordable computer readable storage medium; and
program code stored on the recordable computer readable storage medium and configured upon execution to monitor a query during runtime, wherein the query involves a plurality of join operations, wherein the plurality of join operations include a first join that includes a first table and a second table and a second join that includes the first table and a third table, and wherein the program code is configured to monitor the query by
running the query according to a first join order;
generating a first portion of a result set for the query while running the query according to the first join order, including adding to the result set a first record that matches the plurality of join operations in the query;
concurrent, with running the query, collecting performance statistics about each of the join operations;
dynamically changing the first join order, during running of the query, to a second join order based on the statistics;
where said dynamically changing the first join order, during running of the query, to a second join order based on the statistics comprises:
determining respective fan-in statistics for the first join and second join; and
dynamically changing the first join order to the second join order if the respective fan-in statistics indicate that the second join is more likely to cause fan-in than the first join; and
generating a second portion of the result set for the query while running the query according to the second join order, including adding to the result set a second record that matches the plurality of join operations in the query such that the result set includes at least the first and second records, the first and second portions of the result set generated for the same execution of the query.