Query hint specification

• US 10,592,506 B1
• Filed: 02/13/2013
• Issued: 03/17/2020
• Est. Priority Date: 02/13/2013
• Status: Active Grant

• Current Assignee: Amazon Technologies, Inc. (Amazon.com, Inc.)
• Original Assignee: Amazon Technologies, Inc. (Amazon.com, Inc.)
• Inventors: Thombre, Neil, Fong, Zelaine, Stefani, Stefano, Jakobsson, Hakan, Gupta, Anurag Windlass
• Primary Examiner(s): Adams, Charles D
• Assistant Examiner(s): Baker, Irene
• Application Number:
  US13/766,683
• Time in Patent Office: 2,589 Days
• CPC Class Codes: G06F 16/2453 Query optimisation

Abstract

A query optimizer may receive a query block that includes a hint. The hint may include a unique object identifier to specify an object to which the hint applies. In some instances, the object may correspond to a nested query block. A query optimization plan may be determined based, at least in part, on the hint. The query execution plan may then be executed to query a data store.

Claims

1. A system, comprising:

a memory;

one or more processors coupled to the memory, wherein the memory comprises program instructions executable by the one or more processors to;

receive, by a parser, a query to a database, wherein the query specifies a hint and a query block, wherein the hint is usable to optimize the query, wherein the hint includes a unique object identifier uniquely specifying an object recited within the query block to which the hint applies, wherein the unique object identifier corresponds to a nested query block of the query block, and wherein the unique object identifier is relative to the query block;

determine, by an optimizer, a location of the specified object within the query block to which the hint applies according to an interpretation of the unique object identifier with respect to locations within the query block to locate the specified object in the query block, wherein the unique object identifier is specified from an unambiguous numbering scheme, the unambiguous numbering scheme corresponding to a specific scope of the query block in which objects are numbered sequentially inside the query block in an order of appearance to the parser;

obtain, by the optimizer, a local reference of the unique object identifier by truncating a prefix of the unique object identifier, wherein the truncated prefix is an identifier of the query block;

rewrite, by the optimizer, the query by inserting the hint at a top level of the determined query block location and replacing the unique object identifier in the hint with the local reference of the unique object identifier;

select, by the optimizer, a query execution plan to optimize the rewritten query based, at least in part, on the application of the hint to the specified object; and

execute, by a compute node, the query execution plan to perform the query to the database.

2. The system of claim 1, wherein the unique object identifier uniquely specifies both the nested query block and the object.

3. A method, comprising:

performing, by one or more computers;

receiving a query that specifies a hint and a query block, wherein the hint is usable to optimize the query, wherein the hint includes a unique object identifier uniquely specifying an object recited within the query block to which the hint applies, wherein the unique object identifier corresponds to a nested query block of the query block, and wherein the unique object identifier is relative to the query block;

determining a location of the specified object within the query block to which the hint applies according to an interpretation of the unique object identifier with respect to locations within the query block to locate the specified object in the query block, wherein the unique object identifier is specified from an unambiguous numbering scheme, the unambiguous numbering scheme corresponding to a specific scope of the query block in which objects are numbered sequentially inside the query block in an order of appearance to a parser;

obtain, by the optimizer, a local reference of the unique object identifier by truncating a prefix of the unique object identifier, wherein the truncated prefix is an identifier of the query block;

rewriting the query by inserting the hint at a top level of the determined query block location and replacing the unique object identifier in the hint with the local reference of the unique object identifier;

selecting a query execution plan to optimize the rewritten query based, at least in part, on the application of the hint to the specified object; and

executing the query execution plan to perform the query to a data store.

4. The method of claim 3, wherein the unique object identifier uniquely specifies both the nested query block and the object.

5. The method of claim 3, wherein the unique object identifier is unique from other object identifiers.

6. The method of claim 3, wherein the unambiguous numbering scheme is a Dewey decimal scheme.

7. The method of claim 3, wherein the data store is a columnar database, and wherein the hint is a columnar hint.

8. The method of claim 3, wherein said determining a query execution plan is performed by a leader node of a cluster of a distributed data store and wherein said executing the query execution plan is performed by one or more compute nodes of the cluster.

9. The method of claim 8, further comprising providing the determined query execution plan to the one or more compute nodes.

10. The method of claim 3, wherein the nested query block includes another hint, further comprising:

determining that the hint from the query block and the other hint from the nested query block do not conflict;

applying the other hint to the nested query block; and

modifying the unique object identifier.

11. A non-transitory computer-readable storage medium storing program instructions, wherein the program instructions are computer-executable to implement:

receiving a query that specifies a hint and a query block, wherein the hint is usable to optimize the query, wherein the hint includes a unique object identifier uniquely specifying an object recited within the query block to which the hint applies, wherein the unique object identifier corresponds to a subquery block of the query block, and wherein the unique object identifier is relative to the query block;

determining a location of the specified object within the query block to which the hint applies according to an interpretation of the unique object identifier with respect to locations within the query block to locate the specified object in the query block, wherein the unique object identifier is specified from an unambiguous numbering scheme, the unambiguous numbering scheme corresponding to a specific scope of the query block in which objects are numbered sequentially inside the query block in an order of appearance to a parser;

obtaining a local reference of the unique object identifier by truncating a prefix of the unique object identifier, wherein the truncated prefix is an identifier of the query block;

rewriting the query by inserting the hint at a top level of the determined query block location and replacing the unique object identifier in the hint with the local reference of the unique object identifier;

selecting a query execution plan to optimize the rewritten query based, at least in part, on the application of the hint to the specified object; and

executing the query execution plan to perform the query to a database.

12. The non-transitory computer-readable storage medium of claim 11, wherein rewriting the query further comprises:

modifying the hint resulting in a modified hint, and applying the modified hint to the subquery block.

13. The non-transitory computer-readable storage medium of claim 11, wherein the hint includes the unique object identifier to reference the object to which the hint applies, wherein the unique object identifier specifies both the subquery block and the object.

14. The non-transitory computer-readable storage medium of claim 11, wherein the unambiguous numbering scheme is a Dewey decimal scheme.

15. The non-transitory computer-readable storage medium of claim 11, wherein said using the hint to query a database is performed by a leader node, wherein the program instructions are further computer-executable to implement providing the query execution plan to one or more compute nodes.