Abstract: Methods for page split detection and affinity in query processing pushdowns are performed by systems and devices. Page servers perform pushdown operations based on specific, and specifically formatted or generated, information, instructions, and data provided thereto from a compute node. Page servers also determine that page splits have occurred during reading of data pages maintained by page servers during pushdown operations, and also during fulfillment of compute node data requests. To detect a data page has split, page servers utilize information from a compute node of an expected next data page which is compared to a next data page in the page server page index. A mismatch in the comparison by page servers indicates data page was split. Compute nodes and page servers store and maintain off-row data generated during data operations via page affinity considerations where the off-row data is stored at the same page server as the data.

Abstract: Distributed database systems including compute nodes and page servers are described herein that enable compute nodes to pushdown certain query processing compute tasks to the page servers to take advantage of otherwise idle compute resources at the page servers, and to reduce the quantity of data that moves between compute nodes and page servers. A distributed database system includes a page server and a compute node, wherein the page server is configured to maintain multiple versions of stored data objects. The compute node is configured to receive a query and generate a transaction context (TC) and modified table schemas (MTS) scoped to the query, and pushdown the query, TC and MTS to the page server that is configured to determine which data objects at the page server satisfy the query, and for each such object, which version of the object should be returned based on the TC.

Abstract: Distributed database systems including compute nodes and page servers are described herein that enable compute nodes to pushdown certain query processing compute tasks to the page servers to take advantage of otherwise idle compute resources at the page servers, and to reduce the quantity of data that moves between compute nodes and page servers. A distributed database system includes a page server and a compute node, wherein the page server is configured to maintain multiple versions of stored data objects. The compute node is configured to receive a query and generate a transaction context (TC) and modified table schemas (MTS) scoped to the query, and pushdown the query, TC and MTS to the page server that is configured to determine which data objects at the page server satisfy the query, and for each such object, which version of the object should be returned based on the TC.

Abstract: Methods for operation fragmentation with metadata serialization in query processing pushdowns are performed by systems and devices. A compute node receives a query directed to database data, and generates query text fragments. Portions of metadata of the database are read from different page servers, and are serialized by the compute node. Page identities of data pages in a page server that stores the data are determined from a page index at the compute node, and the compute node provides the text fragments, the serialized metadata, and the page identities to the page server storing the data. The page server compiles the text fragments based on the serialized metadata to generate an executable query plan for the query. The page server initializes and performs execution of the executable query plan against the data as a pushdown query operation, and a result of pushdown query operation is provided to the compute node.

Abstract: Methods for operation fragmentation with metadata serialization in query processing pushdowns are performed by systems and devices. A compute node receives a query directed to database data, and generates query text fragments. Portions of metadata of the database are read from different page servers, and are serialized by the compute node. Page identities of data pages in a page server that stores the data are determined from a page index at the compute node, and the compute node provides the text fragments, the serialized metadata, and the page identities to the page server storing the data. The page server compiles the text fragments based on the serialized metadata to generate an executable query plan for the query. The page server initializes and performs execution of the executable query plan against the data as a pushdown query operation, and a result of pushdown query operation is provided to the compute node.

Abstract: Methods for page split detection and affinity in query processing pushdowns are performed by systems and devices. Page servers perform pushdown operations based on specific, and specifically formatted or generated, information, instructions, and data provided thereto from a compute node. Page servers also determine that page splits have occurred during reading of data pages maintained by page servers during pushdown operations, and also during fulfillment of compute node data requests. To detect a data page has split, page servers utilize information from a compute node of an expected next data page which is compared to a next data page in the page server page index. A mismatch in the comparison by page servers indicates data page was split. Compute nodes and page servers store and maintain off-row data generated during data operations via page affinity considerations where the off-row data is stored at the same page server as the data.

Abstract: Methods for operation fragmentation with metadata serialization in query processing pushdowns are performed by systems and devices. A compute node receives a query directed to database data, and generates query text fragments. Portions of metadata of the database are read from different page servers, and are serialized by the compute node. Page identities of data pages in a page server that stores the data are determined from a page index at the compute node, and the compute node provides the text fragments, the serialized metadata, and the page identities to the page server storing the data. The page server compiles the text fragments based on the serialized metadata to generate an executable query plan for the query. The page server initializes and performs execution of the executable query plan against the data as a pushdown query operation, and a result of pushdown query operation is provided to the compute node.

Abstract: Distributed database systems including compute nodes and page servers are described herein that enable compute nodes to pushdown certain query processing compute tasks to the page servers to take advantage of otherwise idle compute resources at the page servers, and to reduce the quantity of data that moves between compute nodes and page servers. A distributed database system includes a page server and a compute node, wherein the page server is configured to maintain multiple versions of stored data objects. The compute node is configured to receive a query and generate a transaction context (TC) and modified table schemas (MTS) scoped to the query, and pushdown the query, TC and MTS to the page server that is configured to determine which data objects at the page server satisfy the query, and for each such object, which version of the object should be returned based on the TC.

Abstract: Technologies for forming logical indexes and utilizing such indexes so as to abstract many of the complexities resulting from referencing partitioned database tables. Included are technologies for making use of order-preserving properties of table metadata, for adding a partition equality predicate to an explicit predicate in merge-join processing on partitioned tables, and for selecting execution of a logical skip scan on a partitioned table when a query predicate does not reference a specific partition. Such technologies generally abstract from the query writer and processing systems explicit referencing of table partitions.

Abstract: Technologies for forming logical indexes and utilizing such indexes so as to abstract many of the complexities resulting from referencing partitioned database tables. Included are technologies for making use of order-preserving properties of table metadata, for adding a partition equality predicate to an explicit predicate in merge-join processing on partitioned tables, and for selecting execution of a logical skip scan on a partitioned table when a query predicate does not reference a specific partition. Such technologies generally abstract from the query writer and processing systems explicit referencing of table partitions.

Abstract: Technologies for forming logical indexes and utilizing such indexes so as to abstract many of the complexities resulting from referencing partitioned database tables. Included are technologies for making use of order-preserving properties of table metadata, for adding a partition equality predicate to an explicit predicate in merge-join processing on partitioned tables, and for selecting execution of a logical skip scan on a partitioned table when a query predicate does not reference a specific partition. Such technologies generally abstract from the query writer and processing systems explicit referencing of table partitions.

Abstract: Disclosed are systems and methods for optimization and efficient processing of MERGE statements. MERGE allows performing a mix of inserts, updates, and deletes in a single statement, and introduces new challenges compared to legacy DML statements, where the kind of action to be performed is hard-coded and known at compile time. Such optimizations may include Halloween Protection detection for MERGE statements, optimized prevention of non-deterministic MERGE statements, in-place inserts for MERGE statements scanning the “Read Instance” of the target table, and optimized execution of MERGE statements seeking the “Read Instance” of the target table. Such optimizations may be fundamental in order to ensure proper performance and reliable processing times.

Abstract: A method to achieve acceptable query performance even when a database query optimizer chooses an inefficient query plan due to poor cardinality (row count) estimation includes generating a batch sort query plan which includes a row count operation and a reorder operation. The batch sort, and in particular the reorder operation, is not required to satisfy the query but without these operations the query plan may prove to be inefficient. When the row count operation determines that the query plan is inefficient, the row reordering operation reorders rows for more I/O efficient processing. The results of both the row count operation and the reorder operation are used to produce the desired rowset to satisfy the input query without discarding any retrieved rows.

Abstract: Disclosed are systems and methods for optimization and efficient processing of MERGE statements. MERGE allows performing a mix of inserts, updates, and deletes in a single statement, and introduces new challenges compared to legacy DML statements, where the kind of action to be performed is hard-coded and known at compile time. Such optimizations may include Halloween Protection detection for MERGE statements, optimized prevention of non-deterministic MERGE statements, in-place inserts for MERGE statements scanning the “Read Instance” of the target table, and optimized execution of MERGE statements seeking the “Read Instance” of the target table. Such optimizations may be fundamental in order to ensure proper performance and reliable processing times.

Abstract: The present invention is directed to systems and methods for improved prefetching. The present invention reduces the processing time and associated costs of prefetching by incorporating a number of techniques for efficiently searching a node structure. Such techniques eliminate redundant traversal of nodes. Additionally, such techniques do not require locking the node structure to prevent concurrent updates. Furthermore, such techniques may exploit known key ordering information to optimize a search.

Abstract: Technologies for forming logical indexes and utilizing such indexes so as to abstract many of the complexities resulting from referencing partitioned database tables. Included are technologies for making use of order-preserving properties of table metadata, for adding a partition equality predicate to an explicit predicate in merge-join processing on partitioned tables, and for selecting execution of a logical skip scan on a partitioned table when a query predicate does not reference a specific partition. Such technologies generally abstract from the query writer and processing systems explicit referencing of table partitions.