You can translate the question and the replies:

Query Simplification

Hello, I have a scenario where I need to "Simply a Query" based on selection. For example I have two base views, first one named "bv_first_view" (with field names - id, name, age, address) , second base view named "bv_second_view" (with field names - id, ssn). Then I have created a derived view with all fields (dv_first_second) by joining on a "ID" field. In my scenario I would like to restrict query submitted to DB2 to first base view when ID and NAME is selected as shown "SELECT t0.ID, t0.NAME FROM bv_first_view" but where as Denodo execution trace - SQL Sentence is showing "SELECT t0.ID, t0.NAME FROM bv_first_view t0 JOIN bv_second_view t1 ON ((t0.ID = t1.ID) )". Is there any option in Denodo that will produce first SQL sentence when I select fields from derived view.
user
14-10-2019 16:44:08 -0400
code

4 Answers

Hi, If you are creating a join view, the query submitted to DB2 will have to contain details about both the tables that are joined, so that only relevant data can be pulled from both tables that are joined. For example: if table 1 has data for ids 1,2 and 3 and table 2 has data corresponding to ids 1, 2 and 4, then when you join the two tables based on id and using an INNER JOIN, only the data corresponding to ids 1 and 2 are populated when you execute the derived view. It is, however, possible to select only the two required fields in the output of the derived view by editing the view and deleting fields from the output tabs. This would not delete the mention of which tables are joined and this behavior adheres to the concept of JOINS as mentioned above. Hope this helps.
Denodo Team
15-10-2019 09:06:11 -0400
code
Hi, Thanks you for explanation. I looked further, It seems "Brach Pruning" can resolve the issue for me. When I enabled "Automatic Simplification of Queries" and run a "SELECT ID, NAME from dv_first_second" I noticed that query optimizer used "Brach Pruning" and used "SELECT t0.ID, t0.NAME FROM bv_first_view" , it did not reference "ID" from "bv_second_view". But I am having trouble understanding what is "Branch Pruning" and how to make it work all the times and restrictions. Thanks for furtuer details on this.
user
15-10-2019 13:18:12 -0400
Sorry for the typo, I mean to say "Branch Pruning"
user
15-10-2019 15:34:49 -0400
Hi, I tried to reproduce the behavior with different settings for join type along with ‘Automatic simplification of queries’ turned ON. For your case, you would have to set the type of the join as ‘Left Outer Join’ since you wish to query only the first view. Setting the type as any other join will result in the query referring to both the views, i.e. branch pruning will not be applied. You can refer to [Automatic Simplification of Queries](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/optimizing_queries/automatic_simplification_of_queries/automatic_simplification_of_queries) to understand how this setting changes the working of joins and uses branch pruning. You can also read through [Branch Pruning]( https://community.denodo.com/docs/html/browse/7.0/vdp/administration/optimizing_queries/automatic_simplification_of_queries/removing_redundant_branches_of_queries_partitioned_unions#removing-redundant-branches-of-queries-partitioned-unions) for understanding the working of this type of optimization. Hope this helps!
Denodo Team
22-10-2019 09:05:11 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here