[ https://issues.apache.org/jira/browse/CALCITE-2973?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16821654#comment-16821654 ]
Haisheng Yuan edited comment on CALCITE-2973 at 4/19/19 4:56 AM: ----------------------------------------------------------------- {quote} For the record, here is a non-equi join where you can use a hash join plus a per-row condition, where the 'equi' part of the condition is fairly selective and therefore hash join makes sense. It has to be done as a per-row condition, rather than a filter after the join, because of the left outer. {quote} [~julianhyde] We have different definition of equi join. The Oracle's definition of equi join makes more sense: https://docs.oracle.com/cd/E11882_01/server.112/e41084/queries006.htm#SQLRF52350 Update: My mistake, looks like it still refers to join that has equality condition *only*. was (Author: hyuan): {quote} For the record, here is a non-equi join where you can use a hash join plus a per-row condition, where the 'equi' part of the condition is fairly selective and therefore hash join makes sense. It has to be done as a per-row condition, rather than a filter after the join, because of the left outer. {quote} [~julianhyde] We have different definition of equi join. The Oracle's definition of equi join makes more sense: https://docs.oracle.com/cd/E11882_01/server.112/e41084/queries006.htm#SQLRF52350 > Allow theta joins that have equi conditions to be executed using a hash join > algorithm > -------------------------------------------------------------------------------------- > > Key: CALCITE-2973 > URL: https://issues.apache.org/jira/browse/CALCITE-2973 > Project: Calcite > Issue Type: New Feature > Components: core > Affects Versions: 1.19.0 > Reporter: Lai Zhou > Priority: Minor > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Now the EnumerableMergeJoinRule only supports an inner and equi join. > If users make a theta-join query for a large dataset (such as 10000*10000), > the nested-loop join process will take dozens of time than the sort-merge > join process . > So if we can apply merge-join or hash-join rule for a theta join, it will > improve the performance greatly. -- This message was sent by Atlassian JIRA (v7.6.3#76005)