asterixdb-notifications mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael J. Carey (Jira)" <j...@apache.org>
Subject [jira] [Updated] (ASTERIXDB-2913) Need "break up to make up" rewrite rule for unnested data
Date Mon, 14 Jun 2021 04:13:00 GMT

     [ https://issues.apache.org/jira/browse/ASTERIXDB-2913?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Michael J. Carey updated ASTERIXDB-2913:
----------------------------------------
    Description: 
Consider the following query using the Don Chamberlin book data:

 _USE DonCData;_ 
 _WITH flat_orders AS (SELECT orderno, custid, order_date, ship_date FROM orders),_
            _flat_items AS (SELECT o.orderno, i.* FROM orders o, o.items i)_
 _SELECT fo.*, fi.* FROM flat_orders fo, flat_items fi_
 _WHERE fo.orderno = fi.orderno;_

Right now this query leads to two scans of orders - one for flat_orders and one for flat_items
- followed by a join of the two.

It seems like it should be possible to rewrite (under the hood) this query as simply:

_USE DonCData;_ 
 _SELECT o.orderno, o.custid, o.order_date, o.ship_date, i.*_
 _FROM orders o, o.items i;_

This capability could prove super-helpful in our future BI story.  (Picture two flat views
being exported for JDBC - it would be nice to be able to piece them back together for efficient
querying under the hood.)

  was:
Consider the following query using the Don Chamberlin book data:

 

_USE DonCData;_ 
_WITH flat_orders AS (SELECT orderno, custid, order_date, ship_date FROM orders),_
           _flat_items AS (SELECT o.orderno, i.* FROM orders o, o.items i)_
_SELECT fo.*, fi.* FROM flat_orders fo, flat_items fi_
_WHERE fo.orderno = fi.orderno;_

Right now this query leads to two scans of orders - one for flat_orders and one for flat_items
- followed by a join of the two.

It seems like it should be possible to rewrite (under the hood) this query as simply:

_USE DonCData;_ 
_SELECT o.orderno, o.custid, o.order_date, o.ship_date, i.*_
_FROM orders o, o.items i;_

This capability could prove super-helpful in our future BI story.  (Picture two flat views
being exported for JDBC - it would be nice to be able to piece them back together for efficient
querying under the hood.)


> Need "break up to make up" rewrite rule for unnested data
> ---------------------------------------------------------
>
>                 Key: ASTERIXDB-2913
>                 URL: https://issues.apache.org/jira/browse/ASTERIXDB-2913
>             Project: Apache AsterixDB
>          Issue Type: Improvement
>            Reporter: Michael J. Carey
>            Assignee: Dmitry Lychagin
>            Priority: Major
>
> Consider the following query using the Don Chamberlin book data:
>  _USE DonCData;_ 
>  _WITH flat_orders AS (SELECT orderno, custid, order_date, ship_date FROM orders),_
>             _flat_items AS (SELECT o.orderno, i.* FROM orders o, o.items i)_
>  _SELECT fo.*, fi.* FROM flat_orders fo, flat_items fi_
>  _WHERE fo.orderno = fi.orderno;_
> Right now this query leads to two scans of orders - one for flat_orders and one for flat_items
- followed by a join of the two.
> It seems like it should be possible to rewrite (under the hood) this query as simply:
> _USE DonCData;_ 
>  _SELECT o.orderno, o.custid, o.order_date, o.ship_date, i.*_
>  _FROM orders o, o.items i;_
> This capability could prove super-helpful in our future BI story.  (Picture two flat
views being exported for JDBC - it would be nice to be able to piece them back together for
efficient querying under the hood.)



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Mime
View raw message