Plan-per-tuple optimizing of database queries with user-defined functions

Claim 3

The method of claim 1 , wherein the step of generating a plurality of query plans is performed at a database management system compile time, and the steps of evaluating the plurality of query plans and selecting a query plan are performed at a database management system run time.

Claim 4

The method of claim 1 , wherein the query invokes a predicate user-defined function (UDF).

Claim 5

The method of claim 1 , further comprising the steps of: determining if the query invokes a resource critical operation; performing steps (a)-(c) when the query involves a resource critical operation; and generating a query plan when the query does not invoke a resource critical operation.

Claim 6

The method of claim 1 , further comprising the steps of determining if the query invokes a substantially variant operation; performing steps (a)-(c) when the query invokes a substantially variant operation; and generating a query plan when the query does not invoke a substantially variant operation.

Claim 7

The method of claim 1 , wherein the step of evaluating the plurality of query plans using a measured value for the system resource metric comprises the step of obtaining the measured value for the system resource metric from a global resource object.

Claim 8

The method of claim 1 , wherein the system resource metric is selected from the group comprising: an available processing capacity of a database management system node; an available non-volatile memory for a database management system node; an available volatile memory capacity of a database management system node; and an available communications throughput capacity from a first database management system node to a second database management system node.

Claim 9

The method of claim 1 , wherein the database management system comprises a plurality of database management system nodes and wherein: the step of evaluating the plurality of query plans using a measured value for the system resource metric comprises the step of obtaining the measured value of the system resource metric for each of the database management system nodes; and the step executing the query plan comprises the step of executing the selected query plan at a database management system node selected according to the measured value for the system resource metric at the database management system node.

Claim 14

The apparatus of claim 10 , wherein the plurality of query plans is generated at a database management system compile time, and the plurality of query plans is evaluated and a query plan is selected at a database management system run time.

Claim 15

The apparatus of claim 11 , wherein the query invokes a predicate user-defined function (UDF).

Claim 16

The apparatus of claim 10 , further comprising: means for determining if the query invokes a resource critical operation; means for determining when the query involves a resource critical operation; and means for generating a query plan when the query does not invoke a resource critical operation.

Claim 17

The apparatus of claim 10 , further comprising: means for determining if the query invokes a substantially variant operation; means for determining when the query invokes a substantially variant operation; and generating a query plan when the query does not invoke a substantially variant operation.

Claim 18

The apparatus of claim 10 , wherein the means for evaluating the plurality of query plans using a measured value for the resource metric comprises means for obtaining the measured value for the system resource metric from a global resource object.

Claim 19

The apparatus of claim 10 , wherein the system resource metric is selected from the group comprising: an available processing capacity of a database management system node; an available non-volatile memory for a database management system node; an available volatile memory capacity of a database management system node; and an available communications throughput capacity from a first database management system node to a second database management system node.

Claim 20

The apparatus of claim 10 , wherein the database management system comprises a plurality of database management system nodes and wherein: the means for evaluating the plurality of query plans using a measured value for the system resource metric comprises a means for obtaining the measured value of the system resource metric for each of the database management system nodes; and the means for executing the query plan comprises a means for executing the selected query plan at a database management system node selected according to the measured value for the system resource metric at the database management system node.

Claim 21

A program storage device, readable by computer having a processor and a memory, tangibly embodying one or more programs of instructions executable by the computer to perform the method steps of executing a database query in a database management system, the method steps comprising the steps of: (a) generating a plurality of query plans for the database query, each query plan optimized with respect to at least one system resource metric of the database management system; (b) at run time, evaluating the plurality of query plans using a measured value for the system resource metric; (c) selecting a query plan from the evaluated plurality of query plans based on the measured value; and (d) executing the selected query plan in the database management system; predicting at least one resource requirement for the query; generating a plurality of query plans for the database query, each query plan optimized with respect to at least one system resource metric of the database management system; at run time, evaluating the plurality of query plans using a measured value for the system resource metric; selecting a query plan from the evaluated plurality of query plans based on the measured value; executing the selected query plan in the database management system, and when the predicted resource requirement exceeds a threshold value, generating a query plan when the query resource requirement does not exceed the threshold value.

Claim 22

A program storage device, readable by computer having a processor and a memory, tangibly embodying one or more programs of instructions executable by the computer to perform the method steps of executing a database query in a database management system, the method steps comprising the steps of: (a) generating a plurality of query plans for the database query, each query plan optimized with respect to at least one system resource metric of the database management system; (b) at run time, evaluating the plurality of query plans using a measured value for the system resource metric; (c) selecting a query plan from the evaluated plurality of query plans based on the measured value; and (d) executing the selected query plan in the database management system; predicting at least one resource requirement for the query; generating a plurality of query plans for the database query, each query plan optimized with respect to at least one system resource metric of the database management system; at run time, evaluating the plurality of query plans using a measured value for the system resource metric; selecting a query plan from the evaluated plurality of query plans based on the measured value; executing the selected query plan in the database management system, and when the predicted resource requirement exceeds a threshold value, generating a query plan when the query resource requirement does not exceed the threshold value.

Claim 23

The program storage device of claim 18 , wherein the method step of generating a plurality of query plans is performed at a database management system compile time, and the method steps of evaluating the plurality of query plans and selecting a query plan are performed at a database management system run time.

Claim 24

The program storage device of claim 18 , wherein the query invokes a predicate user-defined function (UDF).

Claim 25

The program storage device of claim 18 , wherein the method steps further comprise the method steps of: determining if the query invokes a resource critical operation; performing steps (a)-(c) when the query involves a resource critical operation; and generating a query plan when the query does not invoke a resource critical operation.

Claim 26

The program storage device of claim 18 , wherein the method steps further comprise the steps of: determining if the query invokes a substantially variant operation; performing steps (a)-(c) when the query invokes a substantially variant operation; and generating a query plan when the query does not invoke a substantially variant operation.

Claim 27

The program storage device of claim 18 , wherein the method step of evaluating the plurality of query plans using a measured value for the system resource metric comprises the method step of obtaining the measured value for the system resource metric from a global resource object.

Claim 28

The program storage device of claim 18 , wherein the system resource metric is selected from the group comprising: an available processing capacity of a database management system node; an available non-volatile memory for a database management system node; an available volatile memory capacity of a database management system node; and an available communications throughput capacity from a first database management system node to a second database management system node.

Claim 29

The program storage device of claim 18 , wherein the database management system comprises a plurality of database management system nodes and wherein: the method step of evaluating the plurality of query plans using a measured value for the resource metric comprises the method step of obtaining the measured value of the system resource metric for each of the database management system nodes; and the method step of executing the query plan comprises the method step of executing the selected query plan at a database management system node selected according to the measured value for the system resource metric at the database management system node.