ignite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vladimir Ozerov (JIRA)" <j...@apache.org>
Subject [jira] [Created] (IGNITE-7038) SQL: nested tables support
Date Tue, 28 Nov 2017 07:15:00 GMT
Vladimir Ozerov created IGNITE-7038:
---------------------------------------

             Summary: SQL: nested tables support
                 Key: IGNITE-7038
                 URL: https://issues.apache.org/jira/browse/IGNITE-7038
             Project: Ignite
          Issue Type: Bug
          Components: sql
            Reporter: Vladimir Ozerov


Many commercial databases support a kind of nested tables which is essentially a parent-child
relation with special storage format. With this approach child data can be located efficiently
without joins. 

Syntax example:
{code}
CREATE TYPE address_t AS OBJECT (
   city    VARCHAR2(20),
   street  VARCHAR2(30)
);

CREATE TYPE address_tab IS TABLE OF address_t;

CREATE TABLE customers (
   custid  NUMBER,
   address address_tab )
NESTED TABLE address STORE AS customer_addresses;

INSERT INTO customers VALUES (
    1,
    address_tab(
        address_t('Redwood Shores', '101 First'),
        address_t('Mill Valley', '123 Maple')
    )
);
{code}

Several storage formats should be considered. First, data can be embedded to parent data row
directly or through forward reference to a chain of dedicated blocks (similar to LOB data
types). This is how conventional RDBMS systems work. 

Second, children rows could be stored in the same PK index as parent row. This is how Spanner
works. In this case parent and child rows are different rows, but stored in the same data
structures. This allows for sequential access to both parent and child data in case of joins,
which could be extremely valuable in OLAP cases.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message