# US 11,442,933 B2 · Function semantic based partition-wise SQL execution and partition pruning > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 11,442,933 B2 - Document ID: US11442933B2 - Status: Granted - Kind: B2 - Application number: 15/711,302 - Filed: 2017-09-21 - Published: 2019-03-21 - Granted: 2022-09-13 - Earliest claimed: 2017-09-21 - Assignees: Oracle International Corporation - Applicants: Oracle International Corporation - Inventors: Srikanth Bellamkonda; Andrew Witkowski; Manish Pratap Singh; Madhuri Kandepi - Web page: https://patentgrep.com/patent/US11442933B2 ## CPC - G06F 16/2443: {Stored procedures} - G06F 16/24553: {of query operations} - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract An approach for implementing function semantic based partition-wise SQL execution and partition pruning in a data processing system is provided. The system receives a query directed to a range-partitioned table and determines if operation key(s) of the query include(s) function(s) over the table partitioning key(s). If so, the system obtains a set of values corresponding to each partition by evaluating the function(s) on a low bound and/or a high bound table partitioning key value corresponding to the partition. The system may then compare the sets of values corresponding to different partitions and determine whether to aggregate results obtained by executing the query over the partitions based on the comparison. The system may also determine whether to prune any partitions from processing based on a set of correlations between the set of values for each partition and predicate(s) of the query including function(s) over the table partitioning key(s). ## Claims ### Claim 1 A method for processing a request to perform an operation directed to a partitioned table that has been range-partitioned into a plurality of partitions based at least in part upon a table partitioning key, the partitioned table comprising a column corresponding to the table partitioning key, the method comprising: receiving a query directed to the partitioned table, wherein the query corresponds to a format that comprises: a first keyword comprising a SELECT, INSERT, UPDATE, DELETE, or MERGE clause; a first operation key that follows the first keyword; a FROM keyword that follows the first operation key; an identification of the partitioned table that follows the FROM keyword; a second keyword that follows the identification of the partitioned table, wherein the second keyword comprises a GROUP BY or WHERE clause; and a second operation key that comprises a function and follows the second keyword, wherein the function comprises a built-in SQL function or a user-defined function, and the function receives the table partitioning key or a column identification corresponding to the table partitioning key as an input argument; determining whether the query can be executed using partition-wise execution based on at least whether the function, when invoked, executes over the table partitioning key or a column that corresponds to the table partitioning key; and executing, in response to a determination that the query can be executed using partition-wise execution, the query over the partitioned table using the partition-wise execution, wherein the partition-wise execution comprises executing the query on a partition-by-partition basis. ### Claim 2 (depends on 1) The method of claim 1, further comprising analyzing the function over the column that corresponds to the table partitioning key to determine partition boundaries by: obtaining a first set of values corresponding to a first partition of the plurality of partitions by evaluating the function on at least one of a first low bound value or a first high bound value stored in the column corresponding to the table partitioning key for the first partition; obtaining a second set of values corresponding to a second partition of the plurality of partitions by evaluating the function on at least one of a second low bound value or a second high bound value stored in the column corresponding to the table partitioning key for the second partition; and determining whether to aggregate query execution results obtained by executing the query over the partitioned table using the partition-wise execution based at least in part on a comparison of the first set of values corresponding to the first partition with the second set of values corresponding to the second partition. ### Claim 3 (depends on 2) The method of claim 2, wherein it is determined that the query execution results are to be aggregated when any of the first set of values match any of the second set of values. ### Claim 4 (depends on 2) The method of claim 2, wherein it is determined that the query execution results are not to be aggregated when none of the first set of values match any of the second set of values. ### Claim 5 (depends on 1) The method of claim 1, wherein the table partitioning key comprises a date. ### Claim 6 (depends on 1) The method of claim 1, wherein the function executes over a superset of the table partitioning key. ### Claim 7 (depends on 1) The method of claim 1, wherein a bound of a first partition of the plurality of partitions is undefined after providing one or more values in the column that corresponds to the table partitioning key to the function for evaluation. ### Claim 8 (depends on 5) The method of claim 5, wherein at least some of the plurality of partitions do not correspond to an equal time span. ### Claim 9 (depends on 1) The method of claim 1, wherein executing the query over the partitioned table using the partition-wise execution comprises: assigning different partitions of the plurality of partitions to different slave processes that execute the query over any assigned partitions. ### Claim 10 (depends on 1) The method of claim 1, wherein the first operation key and the second operation key comprise matching functions. ### Claim 11 (depends on 1) The method of claim 1, wherein the function extracts and returns a portion of a specified field in the column corresponding to the table partitioning key. ### Claim 12 (depends on 1) The method of claim 1, further comprising: determining a reduced number of partitions from the plurality of partitions that satisfy the query at least by evaluating a predicate in the query directed towards the partitioned table. ### Claim 13 A non-transitory computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, causes a set of acts for processing a request to perform an operation directed to a partitioned table that has been range-partitioned into a plurality of partitions based at least in part upon a table partitioning key, the partitioned table comprising a column corresponding to the table partitioning key, the set of acts comprising: receiving a query directed to the partitioned table, wherein the query corresponds to a format that comprises: a first keyword comprising a SELECT, INSERT, UPDATE, DELETE, or MERGE clause; a first operation key that follows the first keyword; a FROM keyword that follows the first operation key; an identification of the partitioned table that follows the FROM keyword; a second keyword that follows the identification of the partitioned table, wherein the second keyword comprises a GROUP BY or WHERE clause; and a second operation key that comprises a function and follows the second keyword, wherein the function comprises a built-in SQL function or a user-defined function, and the function receives the table partitioning key or a column identification corresponding to the table partitioning key as an input argument; determining whether the query can be executed using partition-wise execution based on at least whether the function, when invoked, executes over the table partitioning key or a column that corresponds to the table partitioning key; and executing, in response to a determination that the query can be executed using partition-wise execution, the query over the partitioned table using the partition-wise execution, wherein the partition-wise execution comprises executing the query on a partition-by-partition basis. ### Claim 14 (depends on 13) The non-transitory computer readable medium of claim 13, wherein the set of acts further comprising analyzing the function over the column that corresponds to the table partitioning key to determine partition boundaries by: obtaining a first set of values corresponding to a first partition of the plurality of partitions by evaluating the function on at least one of a first low bound value or a first high bound value stored in the column corresponding to the table partitioning key for the first partition; obtaining a second set of values corresponding to a second partition of the plurality of partitions by evaluating the function on at least one of a second low bound value or a second high bound value stored in the column corresponding to the table partitioning key for the second partition; and determining whether to aggregate query execution results obtained by executing the query over the partitioned table using the partition-wise execution based at least in part on a comparison of the first set of values corresponding to the first partition with the second set of values corresponding to the second partition. ### Claim 15 (depends on 14) The non-transitory computer readable medium of claim 14, wherein it is determined that the query execution results are to be aggregated when any of the first set of values match any of the second set of values. ### Claim 16 (depends on 14) The non-transitory computer readable medium of claim 14, wherein it is determined that the query execution results are not to be aggregated when none of the first set of values match any of the second set of values. ### Claim 17 (depends on 13) The non-transitory computer readable medium of claim 13, wherein the table partitioning key comprises a date. ### Claim 18 (depends on 13) The non-transitory computer readable medium of claim 13, wherein the function executes over a superset of the table partitioning key. ### Claim 19 (depends on 13) The non-transitory computer readable medium of claim 13, wherein a bound of a first partition of the plurality of partitions is undefined after providing one or more values in the column that corresponds to the table partitioning key to the function for evaluation. ### Claim 20 (depends on 17) The non-transitory computer readable medium of claim 17, wherein at least some of the plurality of partitions do not correspond to an equal time span. ### Claim 21 (depends on 13) The non-transitory computer readable medium of claim 13, wherein executing the query over the partitioned table using the partition-wise execution comprises: assigning different partitions of the plurality of partitions to different slave processes that execute the query over any assigned partitions. ### Claim 22 (depends on 13) The non-transitory computer readable medium of claim 13, wherein the first operation key and the second operation key comprise matching functions. ### Claim 23 (depends on 13) The non-transitory computer readable medium of claim 13, wherein the function extracts and returns a portion of a specified field in the column corresponding to the table partitioning key. ### Claim 24 (depends on 13) The non-transitory computer readable medium of claim 13, wherein the set of acts further comprise: determining a reduced number of partitions from the plurality of partitions that satisfy the query at least by evaluating a predicate in the query directed towards the partitioned table. ### Claim 25 A computer system for processing a request to perform an operation directed to a ranged-partitioned table that has been partitioned into a plurality of partitions based at least in part upon a table partitioning key, the partitioned table comprising a column corresponding to the table partitioning key, comprising: a memory for storing instructions; and a processor that executes the instructions which to cause a set of acts, the set of acts comprising: receiving a query directed to the partitioned table, wherein the query corresponds to a format that comprises: a first keyword comprising a SELECT, INSERT, UPDATE, DELETE, or MERGE clause; a first operation key that follows the first keyword; a FROM keyword that follows the first operation key; an identification of the partitioned table that follows the FROM keyword; a second keyword that follows the identification of the partitioned table, wherein the second keyword comprises a GROUP BY or WHERE clause; and a second operation key that comprises a function and follows the second keyword, wherein the function comprises a built-in SQL function or a user-defined function, and the function receives the table partitioning key or a column identification corresponding to the table partitioning key as an input argument; determining whether the query can be executed using partition-wise execution based on at least whether the function, when invoked, executes over the table partitioning key or a column that corresponds to the table partitioning key; and executing, in response to a determination that the query can be executed using partition-wise execution, the query over the partitioned table using the partition-wise execution, wherein the partition-wise execution comprises executing the query on a partition-by-partition basis. ### Claim 26 (depends on 25) The computer system of claim 25, wherein the set of acts further comprising analyzing the function over the column that corresponds to the table partitioning key to determine partition boundaries by: obtaining a first set of values corresponding to a first partition of the plurality of partitions by evaluating the function on at least one of a first low bound value or a first high bound value stored in the column corresponding to the table partitioning key for the first partition; obtaining a second set of values corresponding to a second partition of the plurality of partitions by evaluating the function on at least one of a second low bound value or a second high bound value stored in the column corresponding to the table partitioning key for the second partition; and determining whether to aggregate query execution results obtained by executing the query over the partitioned table using the partition-wise execution based at least in part on a comparison of the first set of values corresponding to the first partition with the second set of values corresponding to the second partition. ### Claim 27 (depends on 26) The computer system of claim 26, wherein it is determined that the query execution results are to be aggregated when any of the first set of values match any of the second set of values. ### Claim 28 (depends on 26) The computer system of claim 26, wherein it is determined that the query execution results are not to be aggregated when none of the first set of values match any of the second set of values. ### Claim 29 (depends on 25) The computer system of claim 25, wherein the table partitioning key comprises a date. ### Claim 30 (depends on 25) The computer system of claim 25, wherein the function executes over a superset of the table partitioning key. ### Claim 31 (depends on 29) The computer system of claim 29, wherein at least some of the plurality of partitions do not correspond to an equal time span. ### Claim 32 (depends on 25) The computer system of claim 25, wherein executing the query over the partitioned table using the partition-wise execution comprises: assigning different partitions of the plurality of partitions to different slave processes that execute the query over any assigned partitions. ### Claim 33 (depends on 25) The computer system of claim 25, wherein the first operation key and the second operation key comprise matching functions. ### Claim 34 (depends on 25) The computer system of claim 25, wherein the function extracts and returns a portion of a specified field in the column corresponding to the table partitioning key. ### Claim 35 (depends on 25) The computer system of claim 25, wherein the set of acts further comprise: determining a reduced number of partitions from the plurality of partitions that satisfy the query at least by evaluating a predicate in the query directed towards the partitioned table. ### Claim 36 A non-transitory computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, causes the processor to execute a set of acts for processing a request to perform an operation directed to a partitioned table that has been range-partitioned into a plurality of partitions based at least in part upon a table partitioning key, the set of acts comprising: receiving a query directed to the partitioned table, wherein the partitioned table comprises the plurality of partitions and a column corresponding to the table partitioning key, the query corresponds to a format that comprises: a first keyword comprising a SELECT, INSERT, UPDATE, DELETE, or MERGE clause; a first operation key that follows the first keyword; a FROM keyword that follows the first operation key; an identification of the partitioned table that follows the FROM keyword; a GROUP BY or WHERE clause that follows the identification of the partitioned table; and a predicate which follows the GROUP BY or WHERE clause and includes a second operation key which comprises a function, wherein the function comprises a built-in SQL function or a user-defined function, and the function receives the table partitioning key or a column corresponding to the table partitioning key as an input argument; determining whether the query can be executed using partition-wise execution based on at least whether the function, when invoked, executes over the table partitioning key or a column that corresponds to the table partitioning key; and executing, in response to a determination that the query can be executed using partition-wise execution wherein the partition-wise execution comprises executing the query on a partition-by-partition basis, the query over the partitioned table at least by: evaluating the function in the predicate over one or more values in the column that corresponds to the table partitioning key; and pruning at least one partition from the plurality of partitions for the query based at least in part upon a result of evaluating the function over the one or more values in the column for the table partitioning key. ### Claim 37 (depends on 36) The non-transitory computer readable medium of claim 36, wherein the set of acts further comprising analyzing the function over the column that corresponds to the table partitioning key to determine partition boundaries by: obtaining a first set of values corresponding to a first partition of the plurality of partitions by evaluating the function on at least one of a first low bound value or a first high bound value stored in the column corresponding to the table partitioning key for the first partition; obtaining a second set of values corresponding to a second partition of the plurality of partitions by evaluating the function on at least one of a second low bound value or a second high bound value stored in the column corresponding to the table partitioning key for the second partition; and determining whether to aggregate query execution results obtained by executing the query over the partitioned table using the partition-wise execution based at least in part on a comparison of the first set of values corresponding to the first partition with the second set of values corresponding to the second partition. ### Claim 38 (depends on 37) The non-transitory computer readable medium of claim 37, wherein it is determined that the query execution results are to be aggregated when any of the first set of values match any of the second set of values. ### Claim 39 (depends on 37) The non-transitory computer readable medium of claim 37, wherein it is determined that the query execution results are not to be aggregated when none of the first set of values match any of the second set of values. ### Claim 40 (depends on 36) The non-transitory computer readable medium of claim 36, wherein a bound of a first partition of the plurality of partitions is undefined after providing one or more values in the column that corresponds to the table partitioning key to the function for evaluation. ### Claim 41 (depends on 36) The non-transitory computer readable medium of claim 36, wherein the function executes over a superset of the table partitioning key. ### Claim 42 (depends on 36) The non-transitory computer readable medium of claim 36, wherein the table partitioning key comprises a date. ### Claim 43 (depends on 36) The non-transitory computer readable medium of claim 36, wherein executing the query over the partitioned table using the partition-wise execution comprises: assigning different partitions of the plurality of partitions to different slave processes that execute the query over any assigned partitions. ### Claim 44 (depends on 36) The non-transitory computer readable medium of claim 36, wherein the first operation key and the second operation key comprise matching functions. ### Claim 45 (depends on 36) The non-transitory computer readable medium of claim 36, wherein the function extracts and returns a portion of a specified field in the column corresponding to the table partitioning key. ### Claim 46 (depends on 42) The non-transitory computer readable medium of claim 42, wherein at least some of the plurality of partitions do not correspond to an equal time span.