[
https://issues.apache.org/jira/browse/PHOENIX-5095?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16739907#comment-16739907
]
Vincent Poon commented on PHOENIX-5095:
---------------------------------------
Hm, in my example above, select on the view returns columns from Singers. To
avoid that, we need to create a base table separate from both, and create the
parent as a view as well.
CREATE TABLE IF NOT EXISTS SingersBase (SingerId BIGINT NOT NULL,Delimiter
CHAR(10) NOT NULL,CONSTRAINT PK PRIMARY KEY(SingerId,Delimiter));
CREATE VIEW Singers (FirstName VARCHAR, LastName VARCHAR) AS SELECT * from
SingersBase where Delimiter = 'Singers';
> 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
> Priority: Major
>
> 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)
