[ https://issues.apache.org/jira/browse/CALCITE-5894?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17753862#comment-17753862 ]
JingDas edited comment on CALCITE-5894 at 8/14/23 3:09 PM: ----------------------------------------------------------- [~jhyde] I re-read this part of the paper again. I aggre with you, after “order homogenization”, interesting order can be push down. If the interesting order is above join, after push down,this give a chance to use ` EnumerableMergeJoin` instead of `EnumerableHashJoin` in the optimize because one input of join is sorted. In another scene after “order homogenization” sort maybe removed, which can not happen without “order homogenization”. I will log a new Jira case for “order homogenization” which is [CALCITE-5928|https://issues.apache.org/jira/browse/CALCITE-5928] was (Author: JIRAUSER292370): [~jhyde] I re-read this part of the paper again. I aggre with you, after “order homogenization”, interesting order can be push down. If the interesting order is above join, after push down,this give a chance to use ` EnumerableMergeJoin` instead of `EnumerableHashJoin` in the optimize because one input of join is sorted. In another scene after “order homogenization” sort maybe removed, which can not happen without “order homogenization”. I will log a new Jira case for “order homogenization”. > 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)