Case join decompositions

Patent metadata

Field	Value
Publication number	US-10997174-B2
Application number	US-201514947927-A
Country	US
Kind code	B2
Filing date	Nov 20, 2015
Priority date	Nov 20, 2015
Publication date	May 4, 2021
Grant date	May 4, 2021

Abstract

Methods and apparatus, including computer program products, are provided for a case join. In one aspect, there is provided method, which may include receiving a query for a predefined view that is configured to inhibit modification; detecting whether the predefined view includes a database table extended to include an additional column; and generating, in response to the query, a view based on a case join, when the predefined view includes the database table extended to include the additional column. Related apparatus, systems, methods, and articles are also described.

First claim

What is claimed:

1. A method comprising:
receiving, at a processor, a database query for a predefined view, the predefined view configured to query on a predefined set of data tables at a database, the predefined view configured to inhibit modification of the predefined view by an end-user at a user interface providing the received database query;
detecting, at the processor and in response to the database query, that the predefined view requires a database table extended in the database to include an additional column not defined by the predefined view, wherein the predefined view is defined to inhibit taking into account the additional column;
in response to the detecting, generating, at the processor and based on a case join, a view, the case join including a branch condition, as a condition predicate, for performing a join of the database table, without the additional column inhibited by the predefined view, and another database table including the additional column, the branch condition indicating the database table has been modified to include an additional column; and
providing, by the processor, the generated view for display.

2. The method of claim 1 , wherein the predefined view comprises a result set generated by one or more predefined subqueries on the predefined set of data tables at the database.

3. The method of claim 1 , wherein the database query is received at the processor comprising a query optimizer.

4. The method of claim 3 , wherein the query optimizer performs the case join to provide the predefined view including the additional column.

5. The method of claim 1 , wherein the join comprises a right outer join or a left inner join.

6. The method of claim 3 , further comprising:
in response to the detecting, decomposing, by the query optimizer, the case join when the case join comprises a self key join in which tables are being joined with themselves.

7. The method of claim 6 , wherein the query optimizer decomposes the case join by at least adding, at the view, the added column, without performing a join.

8. A system comprising:
at least one processor and at least one memory including code which when executed causes operations comprising:
receiving, at a processor, a database query for a predefined view, the predefined view configured to query on a predefined set of data tables at a database, the predefined view configured to inhibit modification of the predefined view by an end-user at a user interface providing the received database query;
detecting, at the processor and in response to the database query, that the predefined view requires a database table extended in the database to include an additional column not defined by the predefined view, wherein the predefined view is defined to inhibit taking into account the additional column;
in response to the detecting, generating, at the processor and based on a case join, a view, the case join including a branch condition, as a condition predicate, for performing a join of the database table, without the additional column inhibited by the predefined view, and another database table including the additional column, the branch condition indicating the database table has been modified to include an additional column; and
providing, by the processor, the generated view for display.

9. The system of claim 8 , wherein the predefined view comprises a result set generated by one or more predefined subqueries on the predefined set of data tables at the database.

10. The system of claim 8 , wherein the database query is received at a query optimizer.

11. The system of claim 10 , wherein the query optimizer performs the case join to provide the predefined view including the additional column.

12. The system of claim 8 , wherein the join comprises a right outer join or a left inner join.

13. The system of claim 10 , further comprising:
in response to the detecting, decomposing, by the query optimizer, the case join when the case join comprises a self key join in which tables are being joined with themselves.

14. The system of claim 13 , wherein the query optimizer decomposes the case join by at least adding, at the view, the added column, without performing a join.

15. A non-transitory computer-readable storage medium including program code which when executed causes operations comprising:
at least one processor and at least one memory including code which when executed causes operations comprising:
receiving, at a processor, a database query for a predefined view, the predefined view configured to query on a predefined set of data tables at a database, the predefined view configured to inhibit modification of the predefined view by an end-user at a user interface providing the received database query;
detecting, at the processor and in response to the database query, that the predefined view requires a database table extended in the database to include an additional column not defined by the predefined view, wherein the predefined view is defined to inhibit taking into account the additional column;
in response to the detecting, generating, at the processor and based on a case join, a view, the case join including a branch condition, as a condition predicate, for performing a join of the database table, without the additional column inhibited by the predefined view, and another database table including the additional column, the branch condition indicating the database table has been modified to include an additional column; and
providing, by the processor, the generated view for display.

16. The non-transitory computer-readable storage medium of claim 15 , wherein the predefined view comprises a result set generated by one or more predefined subqueries on the predefined set of data tables at the database.

17. The non-transitory computer-readable storage medium of claim 15 , wherein the database query is received at a query optimizer.

18. The non-transitory computer-readable storage medium of claim 15 , wherein the inhibition prevents adding the additional column directly to the database table.

19. The method of claim 1 , wherein the predefined view requires another database table extended in the database to include an another column not defined by the predefined view, and wherein the case join includes another branch condition indicating the other database table has been modified to include an another column.

Assignees

• Sap Se

Inventors

• Song Sang Il
• Woo Junhee
• Lee Taehyung
• Wi Sung Heun
• Kim Ki Hong
• Yoon Yongsik

Classifications

• G06F16/24544 Primary

  Join order optimisation · CPC title