Two-valued logic primitives for SQL query processing
Saved in:
| Title: | Two-valued logic primitives for SQL query processing |
|---|---|
| Patent Number: | 11269,874 |
| Publication Date: | March 08, 2022 |
| Appl. No: | 16/823632 |
| Application Filed: | March 19, 2020 |
| Abstract: | The present invention relates to data manipulation language (DML) acceleration. Herein are database techniques to use value range analysis and range-limited execution operators when a value is excluded. In an embodiment, a computer receives a data access request that specifies an expression that includes operator(s), including a particular operator that has argument(s) that has only three possible values. Before detecting the actual value of a particular argument, the computer detects that a particular value of the three possible values is excluded for the particular argument. Responsively, an implementation of the particular operator that never accepts the particular value for the particular argument is selected. Applying the expression to fulfil the data access request entails invoking the selected implementation of the particular operator. |
| Inventors: | Oracle International Corporation (Redwood Shores, CA, US) |
| Assignees: | Oracle International Corporation (Redwood Shores, CA, US) |
| Claim: | 1. A method comprising: receiving a data access request that specifies an expression that includes one or more operators that include a particular operator that has a plurality of arguments, wherein each argument of the plurality of arguments has only three possible values; detecting, before detecting actual values of the plurality of arguments, that a particular value of the three possible values is excluded for the plurality of arguments; selecting, based on said detecting that the particular value is excluded, an implementation of the particular operator that never accepts the particular value for the plurality of arguments; fulfilling, by a database management system (DBMS), the data access request, including applying the expression and invoking said implementation of the particular operator. |
| Claim: | 2. The method of claim 1 wherein said detecting that the particular value is excluded comprises at least one selected from the group consisting of: detecting, before invoking a second operator of the one or more operators, that the second operator will not produce the particular value, and traversing the one or more operators as a tree that does not contain the particular operator as a leaf. |
| Claim: | 3. The method of claim 1 wherein: said detecting that the particular value is excluded comprises traversing the one or more operators as a tree; said traversing the the one or more operators as the tree comprises at least one selected from the group consisting of: a post order traversal and a depth first traversal. |
| Claim: | 4. The method of claim 1 wherein said detecting that the particular value is excluded comprises: associating a respective set of projected fields with each operator of the one or more operators; detecting that at least one projected field of the set of projected fields of at least one operator of the one or more operators excludes the particular value. |
| Claim: | 5. The method of claim 1 wherein at least one selected from the group consisting of: the particular value is null, and the particular operator produces a Boolean. |
| Claim: | 6. The method of claim 1 wherein: each record of a plurality of records has a same field; said detecting that the particular value is excluded comprises detecting at least one selected from the group consisting of: said field is a candidate key or a sequence, and a data schema indicates that said field excludes duplicate values. |
| Claim: | 7. The method of claim 6 wherein said detecting that the field excludes the particular value comprises: setting, before said receiving the data access request, a flag to indicate that a particular scan does not find the particular value, wherein said particular scan is one selected from the group consisting of: a scan of the plurality of records, and a scan by a single instruction multiple data (SIMD); detecting that the flag indicates that the particular scan does not find the particular value. |
| Claim: | 8. The method of claim 7 further comprising toggling said flag that indicates that the particular scan does not find the particular value when one selected from the group consisting of: said field of a record of the plurality of records is reassigned to the particular value, and said field of a new record that is added to the plurality of records has the particular value. |
| Claim: | 9. The method of claim 1 wherein separate network elements or separate computers respectively perform: in said DBMS, said detecting that the particular value is excluded, and not in the DBMS, said invoking said implementation of the particular operator. |
| Claim: | 10. The method of claim 1 wherein said invoking said implementation of the particular operator comprises executing at most one branch instruction of an instruction set architecture (ISA). |
| Claim: | 11. A method comprising: receiving a data access request that specifies an expression that includes one or more operators that include a particular operator that has at least one argument that has only three possible values; detecting, before detecting an actual value of a particular argument of the at least one argument, that a particular value of the three possible values is excluded for the particular argument, wherein said detecting that the particular value is excluded comprises detecting one selected from the group consisting of: the particular value is not contained in an encoding dictionary or an index, the particular value is not found by a scan of a plurality of records or by a single instruction multiple data (SIMD), and no bits of a bit string have a particular bit value selecting, based on said detecting that the particular value is excluded, an implementation of the particular operator that never accepts the particular value for the particular argument; applying the expression, including invoking said implementation of the particular operator, to fulfil the data access request. |
| Claim: | 12. One or more non-transitory computer-readable media storing instructions that, when executed by one or more processors, cause: receiving a data access request that specifies an expression that includes one or more operators that include a particular operator that has a plurality of arguments, wherein each argument of the plurality of arguments has only three possible values; detecting, before detecting actual values of the plurality of arguments, that a particular value of the three possible values is excluded for the plurality of arguments; selecting, based on said detecting that the particular value is excluded, an implementation of the particular operator that never accepts the particular value for the plurality of arguments; applying the expression, including invoking said implementation of the particular operator, to fulfil the data access request. |
| Claim: | 13. The one or more non-transitory computer-readable media of claim 12 said detecting that the particular value is excluded comprises at least one selected from the group consisting of: detecting, before invoking a second operator of the one or more operators, that the second operator will not produce the particular value, and traversing the one or more operators as a tree that does not contain the particular operator as a leaf. |
| Claim: | 14. The one or more non-transitory computer-readable media of claim 12 wherein said detecting that the particular value is excluded comprises: associating a respective set of projected fields with each operator of the one or more operators; detecting that at least one projected field of the set of projected fields of at least one operator of the one or more operators excludes the particular value. |
| Claim: | 15. The one or more non-transitory computer-readable media of claim 12 wherein at least one selected from the group consisting of: the particular value is null, and the particular operator produces a Boolean. |
| Claim: | 16. The one or more non-transitory computer-readable media of claim 12 wherein: each record of a plurality of records has a same field; said detecting that the particular value is excluded comprises detecting at least one selected from the group consisting of: said field is a candidate key or a sequence, and a data schema indicates that said field excludes duplicate values. |
| Claim: | 17. The one or more non-transitory computer-readable media of claim 16 wherein said detecting that the field excludes the particular value comprises: setting, before said receiving the data access request, a flag to indicate that a particular scan does not find the particular value, wherein said particular scan is one selected from the group consisting of: a scan of the plurality of records, and a scan by a single instruction multiple data (SIMD); detecting that the flag indicates that the particular scan does not find the particular value. |
| Claim: | 18. The one or more non-transitory computer-readable media of claim 17 wherein the instructions further cause toggling said flag that indicates that the particular scan does not find the particular value when one selected from the group consisting of: said field of a record of the plurality of records is reassigned to the particular value, and said field of a new record that is added to the plurality of records has the particular value. |
| Claim: | 19. The one or more non-transitory computer-readable media of claim 12 wherein separate network elements or separate computing devices respectively perform: said detecting that the particular value is excluded, and said invoking said implementation of the particular operator. |
| Claim: | 20. One or more non-transitory computer-readable media storing instructions that, when executed by one or more processors, cause: receiving a data access request that specifies an expression that includes one or more operators that include a particular operator that has at least one argument that has only three possible values; detecting, before detecting an actual value of a particular argument of the at least one argument, that a particular value of the three possible values is excluded for the particular argument, wherein said detecting that the particular value is excluded comprises detecting one selected from the group consisting of: the particular value is not contained in an encoding dictionary or an index, the particular value is not found by a scan of a plurality of records or by a single instruction multiple data (SIMD), and no bits of a bit string have a particular bit value said field is a candidate key or a sequence, or selecting, based on said detecting that the particular value is excluded, an implementation of the particular operator that never accepts the particular value for the particular argument; applying the expression, including invoking said implementation of the particular operator, to fulfil the data access request. |
| Patent References Cited: | 5367675 November 1994 Cheng 5590324 December 1996 Leung 5905982 May 1999 Carey 6996557 February 2006 Leung 7739219 June 2010 Liu 2006/0053131 March 2006 Meijer 2007/0050380 March 2007 Meijer 2008/0154841 June 2008 Reichart 2011/0078134 March 2011 Bendel 2018/0075105 March 2018 Chavan et al. 2019/0079974 March 2019 Goergens |
| Other References: | Zhou et al., “Implementing Database Operations Using SIMD Instructions”, ACM SIGMOD '2002 Jun. 4-6, 2002 Madison, Wisconsin, USA, 12 pages. cited by applicant Moerkotte, Guido, “Buildings Query Compilers”, dated Mar. 5, 2019, Chapter 5, 5 pages. cited by applicant Modern SQL, “The Three-Valued Logic of SQL”, dated Jan. 19, 2020, 12 pages. cited by applicant Eich et al., “Faster Plan Generation through Consideration of Functional Dependencies and Keys”, Proceedings of the VLDB Endowment, vol. 9, No. 10, dated 2016, 12 pages. cited by applicant Moerkotte,Guido, “Building Query Compilers”, dated Mar. 5, 2019, Chapter 5 Only, 714 pages. cited by applicant Graete, Goetz, “Query Evaluation Techniques tor Large Databases”, ACM Computing Surveys 25(2), only sections 10.2-10.3 and 12.4 dated Nov. 10, 1993, 168 pages. cited by applicant |
| Primary Examiner: | Richardson, James E |
| Attorney, Agent or Firm: | Hickman Becker Bingham Ledesma LLP Miller, Brian N. |
| Accession Number: | edspgr.11269874 |
| Database: | USPTO Patent Grants |
| Abstract: | The present invention relates to data manipulation language (DML) acceleration. Herein are database techniques to use value range analysis and range-limited execution operators when a value is excluded. In an embodiment, a computer receives a data access request that specifies an expression that includes operator(s), including a particular operator that has argument(s) that has only three possible values. Before detecting the actual value of a particular argument, the computer detects that a particular value of the three possible values is excluded for the particular argument. Responsively, an implementation of the particular operator that never accepts the particular value for the particular argument is selected. Applying the expression to fulfil the data access request entails invoking the selected implementation of the particular operator. |
|---|