In TIBCO Data Virtualization, when executing the below query with IN condition, which compares the results with another subquery with IN. It is automatically pushed to the database as "INNER JOIN. ". This will add an overhead to the overall query execution time and decrease the performance.
Query used in VIEW :
SELECT
*
FROM
/shared/"Customer Replication"/"SAP HANA"/SFLIGHT/SMENU
WHERE SMENU.CARRID IN (SELECT SMEAL.CARRID FROM /shared/"Customer Replication"/"SAP HANA"/SFLIGHT/SMEAL
WHERE SMEAL.MEALNUMBER IN ('00000004'))
*************************
Query from execution plan pushed to the database:
SELECT "SFLIGHT"."SMENU"."MANDANT","SFLIGHT"."SMENU"."CARRID","SFLIGHT"."SMENU"."MENUNUMBER","SFLIGHT"."SMENU"."STARTER","SFLIGHT"."SMENU"."MAINCOURSE","SFLIGHT"."SMENU"."DESSERT"
FROM (SELECT DISTINCT "SFLIGHT"."SMEAL"."CARRID" FROM "SFLIGHT"."SMEAL"
WHERE RTRIM(UPPER("SFLIGHT"."SMEAL"."MEALNUMBER")) = N'00000004') "tExpr13" INNER JOIN "SFLIGHT"."SMENU" ON RTRIM(UPPER("tExpr13"."CARRID")) = RTRIM(UPPER("SFLIGHT"."SMENU"."CARRID"))
This article shows how to force the Query optimizer not to perform a rewrite of IN subqueries into Joins when pushed to the database.