[jira] [Updated] (CALCITE-6240) Remove ROW_NUMBER if OVER PARTITION BY unique key
[ https://issues.apache.org/jira/browse/CALCITE-6240?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jiajun Xie updated CALCITE-6240: Priority: Minor (was: Major) > Remove ROW_NUMBER if OVER PARTITION BY unique key > - > > Key: CALCITE-6240 > URL: https://issues.apache.org/jira/browse/CALCITE-6240 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Jiajun Xie >Assignee: Jiajun Xie >Priority: Minor > > The SQL Only have ROW_NUMBER function. > {code:java} > SELECT empno, ename FROM ( > SELECT ROW_NUMBER() OVER (PARTITION BY empno) rn, empno, ename > FROM emp > ) WHERE rn <= 5{code} > to > {code:java} > SELECT empno, ename FROM emp{code} > > If we know every partition count is less than filter count, we can optimize > {code:java} > SELECT deptno, ename FROM ( > SELECT ROW_NUMBER() OVER (PARTITION BY deptno) rn, deptno, ename > FROM emp > ) WHERE rn <= N > -- Statistic let we know every department count is less than N{code} > to > {code:java} > SELECT empno, ename FROM emp {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6240) Remove ROW_NUMBER if OVER PARTITION BY unique key
[ https://issues.apache.org/jira/browse/CALCITE-6240?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jiajun Xie updated CALCITE-6240: Summary: Remove ROW_NUMBER if OVER PARTITION BY unique key (was: Removed ROW_NUMBER if OVER PARTITION BY unique key) > Remove ROW_NUMBER if OVER PARTITION BY unique key > - > > Key: CALCITE-6240 > URL: https://issues.apache.org/jira/browse/CALCITE-6240 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Jiajun Xie >Assignee: Jiajun Xie >Priority: Major > > The SQL Only have ROW_NUMBER function. > {code:java} > SELECT empno, ename FROM ( > SELECT ROW_NUMBER() OVER (PARTITION BY empno) rn, empno, ename > FROM emp > ) WHERE rn <= 5{code} > to > {code:java} > SELECT empno, ename FROM emp{code} > > If we know every partition count is less than filter count, we can optimize > {code:java} > SELECT deptno, ename FROM ( > SELECT ROW_NUMBER() OVER (PARTITION BY deptno) rn, deptno, ename > FROM emp > ) WHERE rn <= N > -- Statistic let we know every department count is less than N{code} > to > {code:java} > SELECT empno, ename FROM emp {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)