[
https://issues.apache.org/jira/browse/PHOENIX-1179?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14257726#comment-14257726
]
alex kamil edited comment on PHOENIX-1179 at 12/24/14 12:45 AM:
----------------------------------------------------------------
getting an error when trying to execute these queries with phoenix 3.3 on table
with 23M rows:
query 1: SELECT A.ROWKEY,A.VS, A.COL1, A.COL2, A.COL3,A.COL4 FROM BT.MYTABLE AS
A JOIN (SELECT B.ROWKEY, B.VS FROM BT.MYTABLE AS B WHERE ((COL3 =
'COMPLETED')) ) B ON A.ROWKEY=B.ROWKEY AND A.VS=B.VS WHERE COL1 = 'REFERENCES'
AND COL2 = 'CAPTION' ORDER BY A.ROWKEY, A.VS DESC, A.COL1, A.COL2;
query 2: SELECT C.ROWKEY AS RK, C.VS AS VS
FROM BT."TABLE_WITH_23OK_ROWS" AS C JOIN (SELECT DISTINCT B.ROWKEY, B.VS FROM
BT."TABLE_WITH_23M_ROWS" AS B LIMIT 1000000) B ON (C.ROWKEY=B.ROWKEY AND
C.VS=B.VS);
Error: Encountered exception in sub plan [0] execution. (state=,code=0)
Caused by: java.sql.SQLException: Encountered exception in sub plan [0]
execution.
at
org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:159)
at
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:222)
at
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:213)
at
org.apache.phoenix.util.PhoenixContextExecutor.call(PhoenixContextExecutor.java:54)
Caused by: org.apache.phoenix.join.MaxServerCacheSizeExceededException: Size of
hash cache (104857690 bytes) exceeds the maximum allowed size (104857600 by
tes)
at
org.apache.phoenix.join.HashCacheClient.serialize(HashCacheClient.java:104)
at
org.apache.phoenix.join.HashCacheClient.addHashCache(HashCacheClient.java:78)
at
org.apache.phoenix.execute.HashJoinPlan$HashSubPlan.execute(HashJoinPlan.java:372)
at
org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:138)
was (Author: alexdl):
getting an error when trying to execute these queries with phoenix 3.3 on table
with 23M rows:
SELECT A.ROWKEY,A.VS, A.COL1, A.COL2, A.COL3,A.COL4 FROM BT.MYTABLE AS A JOIN
(SELECT B.ROWKEY, B.VS FROM BT.MYTABLE AS B WHERE ((COL3 = 'COMPLETED')) ) B ON
A.ROWKEY=B.ROWKEY AND A.VS=B.VS WHERE COL1 = 'REFERENCES' AND COL2 = 'CAPTION'
ORDER BY A.ROWKEY, A.VS DESC, A.COL1, A.COL2;
SELECT C.ROWKEY AS RK, C.VS AS VS
FROM BT."TABLE_WITH_23OK_ROWS" AS C JOIN (SELECT DISTINCT B.ROWKEY, B.VS FROM
BT."TABLE_WITH_23M_ROWS" AS B LIMIT 1000000) B ON (C.ROWKEY=B.ROWKEY AND
C.VS=B.VS);
Error: Encountered exception in sub plan [0] execution. (state=,code=0)
Caused by: java.sql.SQLException: Encountered exception in sub plan [0]
execution.
at
org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:159)
at
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:222)
at
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:213)
at
org.apache.phoenix.util.PhoenixContextExecutor.call(PhoenixContextExecutor.java:54)
Caused by: org.apache.phoenix.join.MaxServerCacheSizeExceededException: Size of
hash cache (104857690 bytes) exceeds the maximum allowed size (104857600 by
tes)
at
org.apache.phoenix.join.HashCacheClient.serialize(HashCacheClient.java:104)
at
org.apache.phoenix.join.HashCacheClient.addHashCache(HashCacheClient.java:78)
at
org.apache.phoenix.execute.HashJoinPlan$HashSubPlan.execute(HashJoinPlan.java:372)
at
org.apache.phoenix.execute.HashJoinPlan$1.call(HashJoinPlan.java:138)
> Support many-to-many joins
> --------------------------
>
> Key: PHOENIX-1179
> URL: https://issues.apache.org/jira/browse/PHOENIX-1179
> Project: Phoenix
> Issue Type: Sub-task
> Reporter: James Taylor
> Assignee: Maryann Xue
> Fix For: 4.3, 3.3
>
> Attachments: 1179.patch
>
>
> Enhance our join capabilities to support many-to-many joins where the size of
> both sides of the join are too big to fit into memory (and thus cannot use
> our hash join mechanism). One technique would be to order both sides of the
> join by their join key and merge sort the results on the client.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
