3/24/2021 0 Comments Firebird Recursive Query
The use case for CTE is similar to VIEW but it is more handy than VIEW.To use a recursive query, you need to add the RECURSIVE keyword.
![]() Add IntermediateTable to ResultTable and replace IntermediateTable to WorkTable. In the above query first, the non-recursive term is evaluated. Then the recursive term is evaluated and the result is added to the previous result set over and over until theres no more data to process. Finally the last SELECT is executed and data is extracted from the result set. Set operations other than UNION ALL between a non recursive term and a recursive term are not permitted. WithClause.subquery.subquery.larg represents SELECT FROM department WHERE name A and WithClause.subquery.subquery.rarg represents SELECT d. ![]() To process CTEs, new members selfreference and nonrecursivequery are added to RangeTblEntry. To find such dependency information, a topological sort is performed. Oracle does not support WITH RECURSIVE, but has its own CONNECT BY syntax. A name space precursivenamespace, whose structure type is RangeRecursive, is added to in ParseState structure. Recursion plan is the top level plan for execution of WITH RECURSIVE query. In the example below, Recursion plan represents the execution plan for SELECT FROM subdepartment part. Recursion plan always has Append subplan which represents the execution plan for UNION ALL for non recursive part and recursive part. Plan for non recursive part is nothing special and is an ordinary plan generated according to the non recursive query part. The working table (WT) is created and its pointer is stored in RecursionState.workingtable. The intermediate table (IT) is created and its pointer is stored in RecursionState.intermediatetuplestorestate. First it execute a plan for non recursive term and the result is stored in WT. Then execute a plan for recursive term, which is a subplan of the recursion plan. If no row is returned, the recursion is ended and ExecRecursion() returns NULL. ![]() To solve the problem, hasrecursivescan is added to PlanState structure, which is true if upper plan has RecursiveScan as a subplan and hash is recreated.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |