Vincent Poon created PHOENIX-5095:
-------------------------------------
Summary: Support INTERLEAVE of parent and child tables
Key: PHOENIX-5095
URL: https://issues.apache.org/jira/browse/PHOENIX-5095
Project: Phoenix
Issue Type: Improvement
Affects Versions: 4.15.0
Reporter: Vincent Poon
Spanner has a concept of [interleaved
tables|https://cloud.google.com/spanner/docs/schema-and-data-model#creating-interleaved-tables]
I'd like to brainstorm here how to implement this in Phoenix. In general we
want a design that can have
1) Fast queries against the parent table PK
2) Fast queries against the child table PK
3) Fast joins between the parent and child
It seems we can get pretty close to this with views. Views can have their own
PK which adds to the rowkey of the base table. However, there doesn't seem to
be a delimiter to distinguish PKs of different views on the base table. The
closest I could up with is adding a delimiter to the base table PK, something
like:
CREATE TABLE IF NOT EXISTS Singers (
SingerId BIGINT NOT NULL,
Delimiter CHAR(10) NOT NULL,
FirstName VARCHAR,
CONSTRAINT PK PRIMARY KEY
(
SingerId,
Delimiter
)
);
CREATE VIEW Albums (AlbumId BIGINT PRIMARY KEY, AlbumTitle VARCHAR) AS SELECT *
from Singers where Delimiter = 'Albums';
We also need to make the JOIN on these tables more intelligent, such that a
single scan can join across parent-child. Perhaps by reading metadata created
during INTERLEAVE table creation, so we know we are joining across interleaved
tables.
We could also have a custom split policy to avoid splitting in the middle of an
interleaved table (though this might restrict how large your interleaved child
table can be).
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
