You can translate the question and the replies:

Redirection of Data Read for UNION View

Would appreciate if someone can provide an insight for this problem statement We have a requirement to expose the data to user through Realtime (Hitting ODS for Current Day) and Historical (Hitting Netezza for Day - 1). Both of these dataset are exposed through a UNION to a user. We want to avoid a connection to hit at Netezza if a user query on UNION view with current date filter only. For instance, SELECT * FROM UNION WHERE DATE = CURRENT DATE Optimizer still decide to hit the Netezza regardless if we are passing Current Day in where call. Ideally, it shouldn't hit Netezza as it contains Day -1 whereas ODS which contains current day and should be hit for fetching the data. One of the possible option is to split the data in separate views but that is not an option for us. Please advise if there is workable solution to this problem statement.
user
22-11-2022 02:56:07 -0500
code

3 Answers

Hi, I was able to avoid a connection to hit the Netezza data source when a user queries on the UNION view with the current date filter by creating a **partitioned union** over the views and this allows the creation of a Union view formed by the views containing data from different partitions (different sources or schemas). When executing that Union, the optimizer will only execute the branch that contains the data based on the where conditions defined for that branch. Also, ensure that the ‘[**Automatic simplification of queries**](https://community.denodo.com/docs/html/browse/8.0/vdp/administration/optimizing_queries/automatic_simplification_of_queries/automatic_simplification_of_queries)’ is turned **ON**, which optimizes the queries by applying optimization techniques. You could refer to the [**Removing Redundant Branches of Queries (Partitioned Unions)**](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/optimizing_queries/automatic_simplification_of_queries/removing_redundant_branches_of_queries_partitioned_unions) section of the Virtual DataPort Administration Guide for more information. Hope this helps!
Denodo Team
22-11-2022 06:57:10 -0500
code
Thanks for the sharing the insight on this. It was useful to understand the working by using the link provided. Howoever, I have another question related to the extenation of it; Will that work for a sencario where we have multple layer of UNION of different sources ( RTM UNION HST ) and by UNION them in final view if try to do it , Wil that Work ? SELECTION SRC 1 UNION SRC 2 UNION SRC 2 -------------------- UNION SRC 1 RTM + HST RMT + HST What are the must have condition that needs to be fulfilled in order to make it work ? I have observed these conditions where it get failed 1. SELECTION with WHERE column on FINAL view , should hit the same DATE column which correspond to the filter you have added in RTM and HST related table. LIke if it is sales date then RTM and HST should relate Sales date 2. If above condition is set then if you have used EXTENDED UNION where HST has 12 columns and RTM has 10 column then it will also not work. It must be same 10 - 10 columns. Please add your thoughts based on this and would appreciate if you can provide feedback promptly.
user
08-12-2022 10:54:10 -0500
Hi, I understand that you are having a union view from different sources (RTM and HST) and creating another union in the final view. For your scenario, I was able to achieve the [**Partitioned Unions**](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/optimizing_queries/automatic_simplification_of_queries/removing_redundant_branches_of_queries_partitioned_unions) by creating a selection view over the view_1 from one data source (DS_1) with the condition that the date values are in the current year and creating another selection view over view_2 from another data source (DS_2) with the condition where the date values are from the past year. Finally, I have created a view_3 which union the view_1 and view_2 by using an extended union. When I executed a query involving the data from the past year, the data source (DS_1) is ignored from the execution plan based on the WHERE condition. Please note that the selection views with a **WHERE condition** is necessary. If the WHERE condition was not there, the query would return the data from both the data source. You could also check the [**Execution trace**](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/appendix/execution_trace_information/execution_trace_information) in order to know how where conditions are delegated to the respective datasource. Hope this helps!
Denodo Team
12-12-2022 01:10:51 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here