[jira] [Comment Edited] (CALCITE-5756) Expand ProjectJoinRemoveRule to support inner join removal by using the foreign-unique constraints

["winds (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17741583#comment-17741583 ] 

winds edited comment on CALCITE-5756 at 7/10/23 12:15 PM:
----------------------------------------------------------

Based on the previous discussion, the `RelOptForeignKey` component has been added to represent the composite or single foreign key and the unique key that foreign key references.


was (Author: JIRAUSER292370):
Based on the previous discussion, the `RelOptForeignKey` component has been added to represent the composite foreign keys and the unique constraint of foreign key references.

> 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: winds
>            Assignee: winds
>            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)