Using the Semijoin Option
To define the semijoin in your SQL
1. Gather cardinality statistics on the tables that are part of the semijoin.
The statistics are used for the execution plan.
2. Include the LEFT_CARDINALITY query hint and its value.
The query optimizer uses the hint to choose a better query plan. For example:
SELECT column1 FROM table1 INNER {OPTION LEFT_CARDINALITY=10}
JOIN table2 ON table1.id = table2.id
If the query optimizer knows that LHS cardinality is small enough compared to RHS, it attempts a semijoin.
If LHS cardinality is less than the value of Max Source Side Cardinality Estimate, and the product of LHS cardinality and the value of Min Ratio of Target Cardinality to Source Cardinality is less than the estimated RHS cardinality, the TDV query engine attempts to rewrite the query execution plan to use the semijoin optimization.
3. Define the RIGHT_CARDINALITY query hint and its value.
The optimizer uses the hint to choose a better query plan. Specifically, it is used when checking LHS cardinality against the minimum ratio set in Min Ratio of Target Cardinality to Source Cardinality.
SELECT column1 FROM table1 INNER {OPTION RIGHT_CARDINALITY=10000}
JOIN table2 ON table1.id = table2.id
If the RHS cardinality of a table is not known or not specified, the TDV query analyzer assumes that it is large, and determines whether to use semijoin according to the specified cardinality of the LHS.
4. If statistics have not been collected on the data source, optionally use Studio to specify the minimum, maximum, and expected cardinalities.