[ 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)