Configuring Your Data Source for the Semijoin Optimization
The settings on the Advanced tab for a data source help the query engine determine when to use semijoin optimization.
Consider this example:
SELECT * FROM DS1.R INNER {OPTION SEMIJOIN} JOIN DS2.S ON R.r1 = S.s1 AND R.r2=S.s2.
Ideally the TDV query engine generates a predicate in the WHERE clause against DS2 that looks like this:
(s1, s2) IN ((2,3),(1,5), …)
This is called the row constructor form of IN clause.
If the targeted data source (DS2 in this example) does not support this type of SQL, the TDV query engine has two options:
• Use the OR syntax form and generate a query predicate that looks like the following:
(s1=2 and s2=3) or (s1=1 and s2 = 5) or …)
• Use multiple scalar IN clauses syntax to generate a query predicate that looks like:
s1 IN (2,1, …) and s2 IN(3, 5,…)
The OR syntax is semantically identical to the row-constructor syntax.
The multiple scalar IN clauses syntax is not as selective, and it can result in more rows being brought into the TDV Server for further local processing. The longer the SQL string, the more likely an execution plan has to run a partitioned semijoin. Run multiple statements to retrieve all the data.
To configure your data source for the semijoin optimization
1. Open the data source.
2. Open the Configuration tab.
3. Select the Advanced tab.
4. Scroll down to set the following parameters.
Field | Description |
Max Source Side Cardinality Estimate | Change the number of LHS rows that triggers an automatic semijoin. If the left side cardinality is less than this value, and the product of this value and the Min Ratio of Target Cardinality to Source Cardinality (next row of this table) is less than the estimated RHS cardinality, the TDV query engine attempts to rewrite the query execution plan to use the semijoin optimization. |
Min Ratio of Target Cardinality to Source Cardinality | Sets a minimum ratio of RHS to LHS to trigger use of semijoin optimization. |
5. Save your settings.