Table Parameterized Functions for SQL Parallel Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database servers face challenges in providing the expressive power of the MapReduce paradigm for data resident in databases, as existing technologies are limited in enabling parallel processing and complex analytical operations within the SQL framework.
Innovation Solution
The implementation of Table Parameterized Functions (TPFs) in a database, which allow for the definition and execution of functions that accept table-valued parameters, enabling parallel processing and extending the capabilities of user-defined functions to handle arbitrary rowsets with user-specified partitioning and ordering.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If standard ANSI SQL functions are used, then functions can return values and be used in expressions, but functions cannot be used directly in FROM clause and cannot process table-valued parameters
Solution Approach 1:
The patent segments function capabilities into two distinct types: scalar functions (returning single values) and table-valued functions (returning sets of rows). This segmentation allows table-valued functions to be used directly in FROM clauses while maintaining compatibility with existing scalar function usage patterns, resolving the contradiction between enhanced versatility and system complexity.
Solution Approach 2:
The patent creates a universal function interface that accommodates both scalar and table-valued functions through a unified syntax. The FROM clause can universally accept either scalar functions (wrapped in subqueries) or table-valued functions directly, making the system more versatile without requiring separate handling mechanisms for each function type.
2Productivity
If MapReduce paradigm functionality is provided for database data, then parallel processing and complex analytical operations become possible, but existing SQL framework limitations prevent full implementation
Solution Approach 1:
The patent introduces table-valued functions as an intermediary layer between standard SQL and MapReduce operations. These functions serve as mediators that can implement complex analytical logic (including parallel processing) while maintaining a SQL-like interface, thus enabling MapReduce capabilities without fundamentally rewriting the SQL framework.
Solution Approach 2:
The patent changes the parameter signature of functions from scalar to table-valued, enabling functions to accept and process table parameters. This parameter change allows functions to operate on entire tables or row sets, facilitating parallel processing and complex analytical operations while staying within the SQL paradigm.
3Adaptability or versatility
If user-defined functions accept only scalar parameters, then function definition is simple, but functions cannot process table-valued parameters or arbitrary rowsets
Solution Approach 1:
The patent implements dynamic function parameter types where functions can be defined to accept either scalar or table-valued parameters. The function signature becomes flexible, allowing the same function to handle different parameter types depending on the specific function implementation, thus enhancing adaptability while keeping definition relatively simple through inheritance and polymorphism.
Data Source
AI summary
Systems, methods and computer program product embodiments for providing table parameterized function (TPF) functionality in a database are disclosed herein. An embodiment includes defining a TPF in a database, wherein the definition of the TPF defines one or more parameters of the TPF, at least one of the parameters being a table parameter. A query is received that operates to call the TPF, wherein the query defines one or more input parameters, the input parameters comprising at least one table input parameter to be used as input for calling the TPF. The query is processed to produce a result, and the result of the query is returned.