[jira] [Created] (IGNITE-7038) SQL: nested tables support

["Vladimir Ozerov (JIRA)" <ji...@apache.org>]

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)