[ https://issues.apache.org/jira/browse/CALCITE-5894?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17754212#comment-17754212 ]
Julian Hyde commented on CALCITE-5894: -------------------------------------- I've also added some comments to CALCITE-5913. Let's start with a simple interface, and a simple implementation (FD = key dependency), and iterate. > Add SortRemoveRedundantRule to remove redundant sort fields if they are > functionally dependent on other sort fields > ------------------------------------------------------------------------------------------------------------------- > > Key: CALCITE-5894 > URL: https://issues.apache.org/jira/browse/CALCITE-5894 > Project: Calcite > Issue Type: New Feature > Reporter: JingDas > Assignee: JingDas > Priority: Minor > Labels: pull-request-available > > 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.job, 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, e_agg.job{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)