US 12,111,828 B2Granted

Method and system to implement error handling with fine-grained SQL hints

AssigneeOracle International Corporation

InventorsKrishna Kantikiran Pasupuleti, Mohamed Ziauddin, Hong Su, Sunil P. Chakkappen

Life of this patent

Estimating the term…

Loading patent…

Abstract

Disclosed is an approach for applying fine-grained hints to obtain optimal control over error handling during query compilation. Using fine-grained hints causes less disruption to the query plans generated by the optimizer as they constrain specific features while retaining scope for the rest of the optimizer functionality. This approach provides a query plan of better quality that avoid errors, particularly in comparison to coarse-grained approaches that restrict processing to earlier database versions or which disables cost-based processing.

Claims30

Dependent claims

A method, comprising:

performing a compilation on a database query at a database server to transform at least a first query block and a second query block in the database query;
and
in response to the compilation on the database query,
detecting an error that occurred during the compilation of the database query;
upon detection of the error, determining a transformation which, when applied to the second query block during the compilation, causes the database server to produce the error during the compilation;
determining an identifier for the second query block that causes the error during the compilation;
generating a negative hint that is specific to a specific combination of the second query block and the transformation by including into the negative hint at least the identifier and information pertaining to the transformation that produces the error when applied to the second query block having the identifier during the compilation;
and
avoiding subsequent occurrence of the error during a subsequent compilation on the second query block at least by using the negative hint to prevent the transformation from being applied to one or more query blocks comprising the identifier during the subsequent compilation, wherein the subsequent compilation transforms the first query block.

6 more elements

A system, comprising:

a processor;
a memory for holding programmable code;
and
wherein the programmable code includes instructions which, when executed by the processor, causes the processor to perform a set of acts, the set of acts comprising:
performing a compilation on a database query at a database server to transform at least a first query block and a second query block in the database query;
and
in response to the compilation on the database query,
detecting an error that occurred during the compilation of the database query;
upon detection of the error, determining a transformation which, when applied to the second query block during the compilation, causes the database server to produce the error during the compilation;
determining an identifier for the second query block that causes the error during the compilation;
generating a negative hint that is specific to a specific combination of the second query block and the transformation by including into the negative hint at least the identifier and information pertaining to the transformation that produces the error when applied to the second query block having the identifier during the compilation;
and
avoiding subsequent occurrence of the error during a subsequent compilation on the second query block at least by using the negative hint to prevent the transformation from being applied to one or more query blocks comprising the identifier during the subsequent compilation, wherein the subsequent compilation transforms the first query block.

10 more elements

A computer program product embodied on a non-transitory computer readable medium, the non-transitory computer readable medium having stored thereon a sequence of instructions which, when executed by a processor, causes the processor to perform a set of acts, the set of acts comprising:

performing a compilation on a database query at a database server to transform at least a first query block and a second query block in the database query;
and
in response to the compilation on the database query,
detecting an error that occurred during the compilation of the database query;
upon detection of the error, determining a transformation which, when applied to the second query block during the compilation, causes the database server to produce the error during the compilation;
determining an identifier for the second query block that causes the error during the compilation;
generating a negative hint that is specific to a specific combination of the second query block and the transformation by including into the negative hint at least the identifier and information pertaining to the transformation that produces the error when applied to the second query block having the identifier during the compilation;
and
avoiding subsequent occurrence of the error during a subsequent compilation on the second query block at least by using the negative hint to prevent the transformation from being applied to one or more query blocks comprising the identifier during the subsequent compilation, wherein the subsequent compilation transforms the first query block.

6 more elements

A method, comprising:

performing a compilation on a database query at a database server to transform at least a first query block and a second query block in the database query;
and
in response to the compilation on the database query,
detecting an error that occurred during the compilation of the database query;
upon detection of the error, determining a transformation as a possible cause for the error that occurred during the compilation of the second query block in the database query, wherein the error corresponds to the compilation that applies the transformation to the second query block during the compilation;
determining an identifier for the second query block that causes the error during the compilation;
generating a hint that is specific to a specific combination of the second block and the transformation by including into the hint at least the identifier and information pertaining to the transformation that produces the error when applied to the second query block having the identifier during the compilation;
and
avoiding subsequent occurrence of the error during a subsequent compilation on the second query block in the database query, wherein the subsequent compilation successfully transforms at least the first query block but uses the hint to affect application of the transformation to one or more query blocks comprising the identifier during the subsequent compilation that occurs after the compilation.

6 more elements

A system, comprising:

a processor;
a memory for holding programmable code;
and
wherein the programmable code includes instructions for:
performing a compilation on a database query at a database server to transform at least a first query block and a second query block in the database query;
and
in response to the compilation on the database query,
detecting an error that occurred during compilation of the database query;
upon detection of the error, determining a transformation as a possible cause for the error that occurred during the compilation of the second query block in the database query, wherein the error corresponds to the compilation that applies the transformation to the second query block during the compilation;
determining an identifier for the second query block that causes the error during the compilation;
generating a hint that is specific to a specific combination of the second query block and the transformation by including into the hint at least the identifier and information pertaining the transformation that produces the error when applied to the second query block having the identifier during the compilation;
and
avoiding subsequent occurrence of the error during a subsequent compilation on the second query block in the database query, wherein the subsequent compilation successfully transforms at least the first query block but uses the hint to affect application of the transformation to one or more query blocks during the subsequent compilation that occurs after the compilation.

10 more elements

performing a compilation on a database query at a database server to transform at least a first query block and a second query block in the database query;
and
in response to the compilation on the database query,
detecting an error that occurred during compilation of the database query;
upon detection of the error, determining a transformation as a possible cause for the error that occurred during the compilation of the second query block in the database query, wherein the error corresponds to the compilation that applies the transformation to the second query block during the compilation;
determining an identifier for the second query block that causes the error during the compilation;
generating a hint that is specific to a specific combination of the second block and the transformation by including into the hint at least the identifier and information pertaining to the transformation that produces the error when applied to the second query block having the identifier during the compilation;
and
avoiding subsequent occurrence of the error during a subsequent compilation on the second query block in the database query, wherein the subsequent compilation successfully transforms at least the first query block but uses the hint to affect application of the transformation to one or more query block comprising the identifier during the subsequent compilation that occurs after the compilation.

6 more elements

Dependencies are inferred from the claim text.