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)
