US 12,182,124 B1Granted

Query optimizer-readable objects facilitating data source switching

AssigneeSap SE

InventorsHeiko Gerwens, Johannes Merx

Life of this patent

Estimating the term…

Loading patent…

Abstract

Techniques and solutions are provided for improving query performance of queries that can dynamically switch between accessing different data sources for a particular operation. The disclosure provides an object type, which can be referred to as a configuration object, that specifies which of multiple data sources should be used in query execution at a particular point in time. Values that specify a data source can be included as data in an instance of the object type, such as values in a relational database table that implements the configuration object. A data source to be used with a query can be changed dynamically by updating contents of the table. During query optimization, a query optimizer can recognize that the configuration object is of a particular type that causes the query optimizer to access contents of the configuration object. The contents can be used to prune portions of a query plan.

Claims20

Dependent claims

A computing system comprising:

at least one memory;
one or more hardware processor units coupled to the at least one memory;
and
one or more computer readable storage media storing computer-executable instructions that, when executed, cause the computing system to perform operations comprising:
receiving a query that recites operations using a first object, a second object, and a switch object;
processing the query with a query optimizer to generate a query plan, the processing the query with the query optimizer comprising:
determining that the query comprises a first join operation and a second join operation, wherein the first join operation references the switch object and the first object, and the second join operation references the switch object and the second object;
based on determining that the switch object is of a switch object type, retrieving values stored in the switch object for a first attribute type, wherein the values stored in the switch object are dynamically modifiable to affect whether the first join operation or the second join operation is used in preparing the query plan;
determining that the first object comprises values for a second attribute type that match at least one value of the first attribute type retrieved from the switch object, wherein the first attribute type and the second attribute type correspond to a condition of the first join operation;
determining that the second object does not comprise values for a third attribute type that match at least one value of the first attribute type for the switch object, wherein the first attribute type and the third attribute type correspond to a condition of the second join operation;
and
pruning the second join operation from a search space considered by the query optimizer based on determining that the second object does not comprise values for the third attribute type that match at least one value of the first attribute type for the switch object.

9 more elements

A method, implemented in a computing system comprising at least one hardware processor and at least one memory coupled to the at least one hardware processor, the method comprising:

receiving a database operation to insert one or more values for a first attribute type into a switch object;
receiving a query that recites operations using a first object, a second object, and the switch object;
processing the query with a query optimizer, the processing the query with the query optimizer comprising:
determining that the query comprises a first join operation and a second join operation, wherein the first join operation references the switch object and a first object, and the second join operation references the switch object and a second object;
retrieving values stored in the switch object for a first attribute type;
determining that the first object comprises values for a second attribute type that match at least one value of the first attribute type for the switch object, wherein the first attribute type and the second attribute type correspond to a condition of the first join operation;
determining that the second object does not comprise values for a third attribute type that match at least one value of the first attribute type for the switch object, wherein the first attribute type and the third attribute type correspond to a condition of the second join operation;
and
pruning the second join operation from a search space considered by the query optimizer.

6 more elements

One or more non-transitory computer-readable storage media comprising:

computer-executable instructions that, when executed by a computing system comprising at least one hardware processor and at least one memory coupled to the at least one hardware processor, cause the computing system to receive a query that recites operations using a switch object;
computer-executable instructions that, when executed by the computing system, cause the computing system to process the query with a query optimizer, the processing the query with the query optimizer comprising:
computer-executable instructions that, when executed by the computing system, cause the computing system to determine that the query comprises a first join operation and a second join operation, wherein the first join operation references a switch object and a first object, and the second join operation references the switch object and a second object;
computer-executable instructions that, when executed by the computing system, cause the computing system to, retrieve values stored in the switch object for a first attribute type;
computer-executable instructions that, when executed by the computing system, cause the computing system to determine that the first object comprises values for a second attribute type that match at least one value of the first attribute type for the switch object, wherein the first attribute type and the second attribute type correspond to a condition of the first join operation;
computer-executable instructions that, when executed by the computing system, cause the computing system to determine that the second object does not comprise values for a third attribute type that match at least one value of the first attribute type for the switch object, wherein the first attribute type and the third attribute type correspond to a condition of the second join operation;
and
computer-executable instructions that, when executed by the computing system, cause the computing system to prune the second join operation from a search space considered by the query optimizer.

5 more elements

Dependencies are inferred from the claim text.