[
https://issues.apache.org/jira/browse/CALCITE-5894?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17752234#comment-17752234
]
JingDas commented on CALCITE-5894:
----------------------------------
[~julianhyde] I see your point and aggre with you,
`BuiltInMetadate.FunctionalDependency` is a wonderful, basic and general
ability for this. "Support to get functional dependency metadata in
RelMetadataQuery"
is another Jira case. I'll go back and finish this jira when anthor
BuiltInMetadate.FunctionalDependency Jira is ready. And I think it would be
better to change this Jira case title to "Add SortRemoveRedundantRule to remove
redundant sort fields if they are functionally dependent by other sort fields".
> Add SortRemoveRedundantRule to remove redundant sort fields if sort fields
> contains unique key
> ----------------------------------------------------------------------------------------------
>
> Key: CALCITE-5894
> URL: https://issues.apache.org/jira/browse/CALCITE-5894
> Project: Calcite
> Issue Type: New Feature
> Reporter: JingDas
> Assignee: JingDas
> Priority: Minor
>
> In some scene, Sort fields can be reduct, if sort fields contain unique key
> For example
> {code:java}
> SELECT ename, salary FROM Emp
> order by empno, ename{code}
> where `empno` is a key, `ename` is redundant since `empno` alone is
> sufficient to determine the order of any two records.
> So the SQL can be optimized as following:
> {code:java}
> SELECT name, Emp.salary FROM Emp
> order by empno{code}
> For another example:
> {code:java}
> SELECT e_agg.c, e_agg.ename
> FROM
> (SELECT count(*) as c, ename, job FROM Emp GROUP BY ename, job) AS e_agg
> ORDER BY e_agg.ename, e_agg.c {code}
> Although `e_agg.ename` is not a key but field `ename` is unique and not null,
> it can be optimized as following:
> {code:java}
> SELECT e_agg.c, e_agg.ename
> FROM (SELECT count(*) as c, ename, job FROM Emp GROUP BY ename, job) AS e_agg
> ORDER BY e_agg.ename{code}
> Sorting is an expensive operation, however. Therefore, it is imperative that
> sorting
> is optimized to avoid unnecessary sort field.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
