dynamobi/dynamobi — Issue #137: [LDB-185] Investigate why usage of semijoins on large fact tables can be more expensive than directly filtering the table
Author: dynamobi-build
Created: 2011-12-30 02:16:22+00:00
State: open
Labels: LDB, Optimizer

[reporter="zfong", created="Wed, 22 Oct 2008 16:01:35 -0500 (GMT-05:00)"]
This was discovered while doing testing a large MERGE statement where the target table, X, has > 5 million rows and the source is a view that also selects from X as well as joining with other dimension tables. The source view also has a filter on X.

The number of rows returned after applying the filter on X is > 180K. So, the filter is selective enough so that a semijoin is being used to filter the target table. However, if you time the underlying join statement that's executed by the MERGE, the query takes about 40 seconds.

If you modify the MERGE statement adding a redundant filter on the target table, a semijoin is no longer being used because the source doesn't filter the target any further. That modified join takes about half the time -- 19 seconds.

Moreover, if you rewrite the filter on the source view so a semijoin cannot be used, the underlying join runs in about the same time as the case when the semijoin is used. So, the semijoin is essentially providing no benefit.