You can translate the question and the replies:

denodo predicate pushdown uses questions

Here are two base view sourced from two Oracle databases： * view1: admin.order-> source table:oracle1.order * view2:admin.order_extend -> source table:oracle2.order_extend it can normal predicate pushdown if I execute: ``` select v1.oid,v1.wcc_num,v2.ddx from admin.order v1 inner join admin.order_extend v2 on v1.oid = v2.order_id where v1.oid in ('8f321864-adb4-4110-9fed-b244de1da421','b05d2e80-1b29-4f94-817d-fce075cba063') ``` EXECUTION TRACEC INFO: * admin.order [Search conditions] = "oid in ('8f321864-adb4-4110-9fed-b244de1da421', 'b05d2e80-1b29-4f94-817d-fce075cba063') oid is not null" * admin.order_extend [Search conditions] = "order_id in ('8f321864-adb4-4110-9fed-b244de1da421', 'b05d2e80-1b29-4f94-817d-fce075cba063') order_id is not null" if I filter it by order number(v1.wcc_num)： ``` select v1.oid,v1.wcc_num,v2.ddx from admin.order v1 inner join admin.order_extend v2 on v1.oid = v2.order_id where v1.wcc_num in ('198083650','193126339') ``` EXECUTION TRACEC INFO: * admin.order [Search conditions] = "wcc_num in ('198083650', '193126339') oid is not null" * admin.order_extend [Search conditions] = "order_id is not null" I want to pushdown t2(admin.order_extend) from result of v1 (for example:select order_id from admin.order where wcc_num in ('198083650', '193126339') result: '8f321864-adb4-4110-9fed-b244de1da421', 'b05d2e80-1b29-4f94-817d-fce075cba063') **what should I do ?**
predicate pushdown
user
17-07-2023 23:04:39 -0400
code

4 Answers

Hi, I understand that you are filter your order_id of your second table for the values '8f321864-adb4-4110-9fed-b244de1da421', 'b05d2e80-1b29-4f94-817d-fce075cba063', which you receive after you filter v1.wcc_num for the values '198083650', '193126339'. You can do that in one query like that: SELECT ddx FROM admin.order_extend WHERE order_id IN (SELECT oid FROM admin.order WHERE wcc_num IN ('19803650', '193126339')) Let me know if that helps!
Denodo Team
19-07-2023 04:05:48 -0400
code
Hi, Thanks for you answer. it still can't normal predicate pushdown ,it still scan full table: ``` SELECT ddx FROM admin.order_extend WHERE order_id IN (SELECT oid FROM admin.order WHERE wcc_num IN ('19803650', '193126339')) ``` EXECUTION TRACEC INFO[order_extend]: Number of rows:10217942 SQL sentence:SELECT t0.DDX, t0.ORDER_ID FROM XMES.ORDER_EXTEND t0 Search conditions: [None]
user
19-07-2023 04:19:22 -0400
Hi, It is possible to specify the type of JOIN operation you are using. By specifying a [NESTED JOIN](https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/optimizing_queries/optimizing_join_operations/optimizing_join_operations#optimizing-join-operations:~:text=the%20other%20branch.-,Nested%20Join,-The%20Nested%20join) you can enforce the pushdown you are looking for. Example 1: select v1.oid,v1.wcc_num,v2.ddx from admin.order v1 inner NESTED join admin.order_extend v2 on v1.oid = v2.order_id where v1.wcc_num in ('198083650','193126339') Example 2: specifying the[ NESTED JOIN for subqueries](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/queries_select_statement/from_clause/subqueries_in_the_where_clause_of_the_query#:~:text=Nested%20semijoin%3A%20the%20subquery%20is%20executed%20once%20for%20each%20row%20of%20the%20main%20query%E2%80%99s%20results.) (which are internally translated into semijoins): SELECT ddx FROM admin.order_extend WHERE order_id IN (SELECT oid FROM admin.order WHERE wcc_num IN ('19803650', '193126339') CONTEX (SUBQUERYPLAN = NESTED)) The Nested join method first queries the input view of the **left** side of the join to obtain the tuples that verify the join condition. Then, it executes a query to the view of the right side, for each combination of values obtained for the field that takes part in the join. If you are creating the JOINS graphically instead of with VQL statements, you can change the Join Method under **EDIT -> JOIN CONDITIONS -> JOIN METHOD**. There you can choose the NESTED JOIN type. Hope this helps!
Denodo Team
19-07-2023 06:45:15 -0400
code
Thanks ,NESTED JOIN can solve my problem
user
20-07-2023 02:09:56 -0400
You must sign in to add an answer. If you do not have an account, you can register here