[
https://issues.apache.org/jira/browse/CALCITE-5756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17748462#comment-17748462
]
Alessandro Solimando commented on CALCITE-5756:
-----------------------------------------------
I agree with you, [~jingda], it's good to split the contribution into two, the
current scope is too big now.
I suggest to open a separate ticket for the RelMetadataQuery#getForeignKeys
contribution, mark this ticket as "depending on" or "blocked by" (or "relates
to", at the very least), and resume this ticket once the other contribution has
been merged.
WDYT?
> Expand ProjectJoinRemoveRule to support inner join removal by using the
> foreign-unique constraints
> --------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5756
> URL: https://issues.apache.org/jira/browse/CALCITE-5756
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: JingDas
> Assignee: JingDas
> Priority: Major
> Labels: pull-request-available
>
> Join elimination is a useful optmize improvement.
> Consider a query that joins the two tables but does not make use of the Dept
> columns:
> {code:java}
> SELECT Emp.name, Emp.salary
> FROM Emp, Dept
> WHERE Emp.deptno = Dept.dno {code}
> Assuming Emp.deptno is the foreign-key and is non-null, Dept.dno is the
> unique-key. The sql above can be rewritten as following. remove the Dept
> table without affecting the resultset.
> {code:java}
> SELECT Emp.name, Emp.salary
> FROM Emp {code}
> Without redundant join elimination, this query execution may perform poorly.
> The optimize improvement is also available in SQL Server, Oracle and
> Snowflake and so on.
> In Calcite, i think that is also useful. The infrastructure that join
> elimination depend on is already available.
> The main steps are as follows:
> 1. Analyse the column used by project, and then split them to left and right
> side.
> 2. Acccording to the project info above and outer join type, bail out in some
> scene.
> 3. Get join info such as join keys.
> 4. For inner join check foreign and unique keys, these may use
> RelMetadataQuery#getForeignKeys(newly add, similar to
> RelMetadataQuery#getUniqueKeys),
> RelOptTable#getReferentialConstraints.
> 5. Check removing side join keys are areColumnsUnique both for outer join and
> inner join.
> 6. If all done, calculate the fianl project and transform.
> Please help me to check the improvement whether is useful or not.
> And i would like to add this improvement to Calcite.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
