[
https://issues.apache.org/jira/browse/CALCITE-724?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14542859#comment-14542859
]
Julian Hyde commented on CALCITE-724:
-------------------------------------
I think it's just a join. It could be implemented as a hash join, merge join,
or a nested loops join. The last one, also known as a Correlate, is preferred.
Here is a sequence of steps that could get us from your query to a plan that
uses the index to drive a narrow scan of the Customers table.
Initial query:
{code}Join(condition: left.customer_id = right.customer_id)
Scan(table: Orders)
Scan(table: Customers){code}
Convert join to correlate:
{code}Correlate(var: v1)
Scan(table: Orders)
Filter(condition: customer_id = v1.customer_id)
Scan(table: Customers){code}
Convert table scan to range scan on index join:
{code}Correlate(var: v1)
Scan(table: Orders)
Join(left.rowid = right.rowid)
Filter(condition: key = v1.customer_id)
TableScan(i_Customers_PK)
TableScan(Customers){code}
Convert table scan on index to range scan (valid on any sorted table, not just
indexes):
{code}Correlate(var: v1)
Scan(table: Orders)
Join(left.rowid = right.rowid)
RangeScan(table: i_Customers_PK, sargs: [v1.customer_id])
Scan(Customers){code}
Convert "join on rowid" into a ScanByRowId:
{code}Correlate(var: v1)
Scan(table: Orders)
ScanByRowId(table: Customers)
RangeScan(table: i_Customers_PK, sargs: [v1.customer_id], projects:
[rowid]){code}
And that's the plan you want (I think).
To be clear, ScanByRowId and RangeScan do not exist currently. However,
RangeScan is related to ProjectableFilterableTable and BindableTableScan.
> Add support for hash join using an index
> ----------------------------------------
>
> Key: CALCITE-724
> URL: https://issues.apache.org/jira/browse/CALCITE-724
> Project: Calcite
> Issue Type: Improvement
> Affects Versions: 1.3.0-incubating
> Reporter: Andy Grove
> Assignee: Julian Hyde
>
> I would like to implement a hash join where the left input is a stream or a
> table scan and for each incoming tuple I would like Calcite to perform an
> index-based lookup against a table to find matching tuples.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
