Function semantic based partition-wise SQL execution and partition pruning

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.

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.

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.