[
https://issues.apache.org/jira/browse/CALCITE-5756?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
winds updated CALCITE-5756:
---------------------------
Description:
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.
was:
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.
1. Expand ProjectJoinRemoveRule to support inner join remove by the
foreign-unique constraint in the catalog.
2. Should use
org.apache.calcite.rel.metadata.RelMetadataQuery#getTableReferences acquire
Set<RelTableRef>.
3. Get constraints by
org.apache.calcite.plan.RelOptTable#getReferentialConstraints.
4. Build the graph by constraints and then remove the redundant inner join
according to graph inwardEdges,outwardEdges.
Please help me to check the improvement whether is useful or not.
And i would like to add this improvement to Calcite.
> Expand ProjectJoinRemoveRule to support inner join remove by the
> foreign-unique constraint in the catalog
> ---------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5756
> URL: https://issues.apache.org/jira/browse/CALCITE-5756
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: winds
> Assignee: winds
> Priority: Minor
> 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)
