How to force the 'Query optimizer' not to perform a rewrite of 'IN' subqueries into joins when pushed to the database in TIBCO Data Virtualization?

How to force the 'Query optimizer' not to perform a rewrite of 'IN' subqueries into joins when pushed to the database in TIBCO Data Virtualization?

book

Article ID: KB0072023

calendar_today

Updated On:

Products

TIBCO Data Virtualization

Description

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"))   

 

User-added image


 

This article shows how to force the Query optimizer not to perform a rewrite of IN subqueries into Joins when pushed to the database.

Issue/Introduction

How to force the 'Query optimizer' not to perform a rewrite of 'IN' subqueries into joins when pushed to the database in TIBCO Data Virtualization?

Resolution

Append {option REWRITE_IN_SUBQUERIES_AS_JOINS="false",case_sensitive,ignore_trailing_spaces="false"} parameters in the query to help prevent the Query Engine from re-writing IN query with SELECT's which results in treating the query as it is written. 

Here is an example query:

select {option REWRITE_IN_SUBQUERIES_AS_JOINS="false",case_sensitive,ignore_trailing_spaces="false"}
*
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'))





User-added image