Recursion In Sql
• SQL: 1999 permit recursive view definition.
• Example: Find all employeemanager pairs, where the employee reports to the manager directly or indirectly.
This example view, empl, is called the transitive closure of the manager relation.
The Power Of Recursion
- Recursive views make it possible to write queries, such as transitive closure queries, that cannot be written without recursion or iteration.
- Intuition: Without recursion, a non¬recursive non¬iterative program can perform only a fixed number of joins of manager with itself
- This can give only a fixed number of levels of managers
- Given a program we can construct a database with a greater number of levels of managers on which the program will not work
- Computing transitive closure
- The next slide shows a manager relation
- Each step of the iterative process constructs an extended version of empl from its recursive definition.
- The final result is called the fixed point of the recursive view definition.
- Recursive views are required to be monotonic. That is, if we add tuples to manager the view contains all of the tuples it contained before, plus possibly more.
|
Math and Science
