Estimating rows returned by recursive queries using fanout

摘要

In an embodiment, a recursive query is received that comprises a first select statement with a seed select statement and a second select statement with a recursive reference, wherein the recursive query further identifies at least two columns in at least one table, wherein the at least two columns have parent-child relationships represented by nodes in a graph, wherein the graph represents the organization of values in rows in the at least one table. A target recursion depth is calculated for the graph based on a fanout of the graph. In an embodiment, the target recursion depth is calculated by summing the fanout at each recursion depth of the graph multiplied by a number of nodes at each recursion depth of the graph. An estimated number of rows that the recursive query will return is estimated based on the target recursion depth.

主权项

1. A non-transitory computer-readable storage medium encoded with instructions, wherein the instructions when executed comprise:
receiving a recursive query that comprises a first select statement with a seed select statement and a second select statement with a recursive reference, wherein the recursive query further identifies at least two columns in at least one table, wherein the at least two columns have parent-child relationships represented by nodes in a graph, wherein the graph represents an organization of values in rows in the at least one table; calculating a target recursion depth for the graph based on a fanout of the graph, wherein the calculating the target recursion depth further comprises summing the fanout at each recursion depth of the graph multiplied by a number of nodes at the each recursion depth of the graph; estimating an estimated number of rows that the recursive query will return to a result set based on the target recursion depth; and selecting a selected query plan from among a plurality of query plans based the estimated number of rows.