Transforming User-Defined Table Functions to Derived Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems face inefficiencies when processing user-defined table functions (UDTFs) due to the need to access external code libraries, which increases resource usage and impacts query processing performance.
Innovation Solution
Transforming UDTFs into derived tables within the database query, allowing for inlining or lateral correlation, thereby avoiding external code library calls and enabling optimization of the query and derived table as a whole.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If UDTF is used in database query, then query functionality is extended, but resource usage increases and query processing performance deteriorates
Solution Approach 1:
The patent merges the UDTF logic with the main query by inlining the SQL statements from the UDTF directly into the query plan. This integration eliminates the need for separate function execution, allowing the database optimizer to treat the entire query as a unified unit and apply optimizations across the complete query logic, thereby improving performance while preserving functionality.
Solution Approach 2:
The patent extracts the SQL statements from the external UDTF code library and incorporates them directly into the database query. By taking out the function logic from its external container and embedding it within the query itself, the system eliminates overhead associated with external code execution while maintaining the extended query capabilities.
2Adaptability or versatility
If UDTF is used in database query, then query functionality is extended, but resource usage increases
Solution Approach 1:
The patent combines the UDTF execution with the main query execution by inlining the function logic. This merging eliminates redundant resource consumption associated with separate function invocation, such as memory allocation for external code execution and repeated parsing of function definitions, thereby reducing overall resource usage while preserving query functionality.
Solution Approach 2:
The patent extracts the SQL statements from the external UDTF code library and embeds them directly in the query. This extraction eliminates the need to load and execute external code, reducing memory usage and CPU cycles spent on function invocation overhead, thus lowering resource consumption while maintaining extended query capabilities.
3Productivity
If UDTF is inlined as derived table, then external code library calls are avoided, but query complexity increases
Solution Approach 1:
The patent introduces a derived table as an intermediary structure to represent the inlined UDTF logic. This derived table acts as a mediator that encapsulates the extracted SQL statements, allowing the query optimizer to work with a structured representation of the query logic while simplifying the overall query plan generation and execution.
Data Source
AI summary
Disclosed aspects relate to transforming a user-defined table function (UDTF) to a derived table in a database management system (DBMS). It may be detected by the DBMS that a query indicates the UDTF. A derived table may be constructed from the UDTF. The derived table may be derived from a set of statements of the UDTF. The derived table may be introduced to the query to fulfill the UDTF. A query plan may be generated for the query by the DBMS. The query plan may be generated in response to introducing the derived table in the query.