[jira] [Created] (CALCITE-772) Rewrite scan-filter as scan-filter-join on sorted materialization

["Julian Hyde (JIRA)" <ji...@apache.org>]

Julian Hyde created CALCITE-772:
-----------------------------------

             Summary: Rewrite scan-filter as scan-filter-join on sorted materialization
                 Key: CALCITE-772
                 URL: https://issues.apache.org/jira/browse/CALCITE-772
             Project: Calcite
          Issue Type: Bug
            Reporter: Julian Hyde
            Assignee: Julian Hyde


If there is a sort-project materialization (think of a secondary index) then Calcite should rewrite a scan-filter query on the base table to a scan-filter on the materialization followed by a join to the base table.

This may not sound efficient, but if the join is implemented by a key scan then it is efficient. This is what Oracle calls "TABLE ACCESS BY INDEX ROWID". It may not even look like a join - it may look like a table scan that reads a sequence of row keys from an underlying relational expression.

For example, given the following schema (in pseudo-DDL):

{code}
CREATE TABLE Emp (empid, deptno, gender)
 PRIMARY KEY (empid)
 SORTED ON (empid);
CREATE MATERIALIZATION I_Emp_Deptno
  PRIMARY KEY (deptno, empno)
  SORTED ON (deptno, empno) AS
  SELECT deptno, empid FROM Emp ORDER BY deptno;
{code}

Calcite should rewrite the query

{code}
SELECT * FROM Emp WHERE deptno IN (10, 20)
{code}

to

{noformat}
Join(inner, left.empno = right.empno)
  Filter(deptno IN (10, 20))
    TableScan(table=[I_Emp_Deptno])
  TableScan(table=[Emp])
{noformat}




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)