Nested queries

[Liam Fisk <li...@xtra.co.nz>]

Hi,
I'm wrapping my head around Calcite at the moment - was wondering if somebody could clarify something for me.
I've got serialised javabeans in a cassandra DB - can I use the pushdown feature to query the cassandra indices first, find a subset of relevant java beans, deserialise them, and then query the java bean fields?
Cheers,
Liam

Re: Nested queries

[Julian Hyde <ju...@hydromatic.net>]

This is a classic pattern in database — indexed lookup. Transform

  Filter
     |
  Scan

into

 StreamScan(table)
    |
  Scan(index)   

StreamScan is unlike regular table scans in that it has an input. The input is a list of key values. (There would be variants where the inputs are ranges.) Calcite doesn't have a stream scan built in. You’d have to build one. 

You can represent it logically, and more generally, as a nested-loops join:

             Correlate
             /          \
  Scan(index)   Filter
                             \
                          Scan(table)
    
Correlate reads a row from the index scan (i.e. the next key value that matches your criteria), sets the variable, then restarts the table scan. This doesn’t perform well as it stands (start a full-table scan for every row from the left!!!), but making it into a kind of join allows you to choose indexes at the same time as you are choosing join order, then you can transform it to something more efficient, such as the StreamScan.

Julian


On Nov 21, 2014, at 7:45 PM, Liam Fisk <li...@xtra.co.nz> wrote:

> Hi,
> I'm wrapping my head around Calcite at the moment - was wondering if somebody could clarify something for me.
> I've got serialised javabeans in a cassandra DB - can I use the pushdown feature to query the cassandra indices first, find a subset of relevant java beans, deserialise them, and then query the java bean fields?
> Cheers,
> Liam
>