Hi Maryann,
Sending this to the whole user group now I have a bit more investigation.
Managed to do some digging and also figured out how to use sqlline.py, and
got the following results.
With sqlline.py:
*0: jdbc:phoenix:sales1,sales2,sales3> explain SELECT /*+
USE_SORT_MERGE_JOIN */ * FROM testtable1 t1 join testtable2 t2 on t2.rowid
= t1.rowid;*
*+------------------------------------------+*
*| PLAN |*
*+------------------------------------------+*
*| SORT-MERGE-JOIN (INNER) TABLES |*
*| CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER TESTTABLE1 |*
*| AND |*
*| CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER TESTTABLE2 |*
*| SERVER SORTED BY [T2.ROWID] |*
*| CLIENT MERGE SORT |*
*+------------------------------------------+*
*6 rows selected (0.035 seconds)*
So it does seem to work from sqlline!! Then, I decided to try and use it
from inside a Java class:
Class.*forName*("org.apache.phoenix.jdbc.PhoenixDriver");
Connection conn = DriverManager.*getConnection*(
"jdbc:phoenix:sales1,sales2,sales3", "", "");
// Create a Statement class to execute the SQL
statement
Statement stmtLimited = conn.createStatement();
System.*out*.println("Executing statement");
// Execute the SQL statement and get the results in a
*Resultset*
ResultSet rsLimited = stmtLimited.executeQuery("explain
SELECT /*+ USE_SORT_MERGE_JOIN */ * FROM testtable1 t1 join testtable2 t2
on t2.rowid = t1.rowid");
*while*(rsLimited.next()) {
String plan = rsLimited.getString("PLAN");
System.*out*.println(plan);
}
stmtLimited.close();
conn.close();
And I get:
Executing statement
CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER TESTTABLE1
PARALLEL INNER-JOIN TABLE 0
CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER TESTTABLE2
DYNAMIC SERVER FILTER BY ROWID BETWEEN MIN/MAX OF (T2.ROWID)
So it seems that when executed from Java code (Squirrel is also a
Java-based client) it ignores the hint! What do you think? Is there a way
around this?
Thanks!
Matt
*From:* Maryann Xue [mailto:maryann....@gmail.com]
*Sent:* 17 March 2015 16:23
*To:* user@phoenix.apache.org
*Subject:* Re: Using Hints in Phoenix
Hi Matt,
Sorry that I still could not reproduce the issue. Could you try reproducing
it in a unit test?
Thanks,
Maryann
On Fri, Mar 13, 2015 at 6:20 AM, Matthew Johnson <matt.john...@algomi.com>
wrote:
Hi Maryann,
All hbases in my cluster have:
*phoenix-4.3.0-server.jar*
in the lib folder, and my client is using:
*phoenix-4.3.0-client.jar*
I generated the MD5 in case something has gone wrong with the versioning:
*e0ade979e7b444fb8f8f4b7b5578edab*
And I have opened up the jar, and can see the new class
*SortMergeJoinPlan.class*, so presumably I have the right version – is
there anything else I can check?
Cheers,
Matt
*From:* Maryann Xue [mailto:maryann....@gmail.com]
*Sent:* 12 March 2015 23:02
*To:* user@phoenix.apache.org
*Subject:* Re: Using Hints in Phoenix
Hi Matt,
I checked on my side. Sort-merge-join would work for both tables and views.
And I also verified that 4.3 branch does have the corresponding check-in (
https://git1-us-west.apache.org/repos/asf?p=phoenix.git;a=commitdiff;h=ebc7ee42cdb2b05a293f54dc687ca975db9acbc3)
although the check-in message had a little mistake there.
Could you please verify your Phoenix library version again, Matt?
Especially the client.
Thanks,
Maryann
On Thu, Mar 12, 2015 at 6:00 PM, Maryann Xue <maryann....@gmail.com> wrote:
Hi Matt,
Thanks for sharing the query. Using that hint should supposedly force
sort-merge join no matter what. I will go ahead and verify that.
Thanks,
Maryann
On Thu, Mar 12, 2015 at 2:25 PM, Matthew Johnson <matt.john...@algomi.com>
wrote:
Hi Maryann,
My views:
*create view "mytestview1" *
*(*
*"rowid" varchar primary key, *
*"metadata"."bId" varchar, *
*"metadata"."dId" varchar*
*) *
*create view "mytestview2" *
*(*
*"rowid" varchar primary key, *
*"data"."bId" varchar,*
*"data"."details" varchar,*
*"data"."comment" varchar*
*) *
The amount of data:
*SELECT count(*) FROM "mytestview1";*
* -- 78,549*
*SELECT count(*) FROM "mytestview2";*
* -- 2,130,905*
The query:
Without hint:
*EXPLAIN select count(*) *
*from "mytestview1" m1*
*inner join "mytestview2" m2*
*on m1."bId" = m2."bId"*
*CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER mytest1*
* SERVER AGGREGATE INTO SINGLE ROW*
* PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)*
* CLIENT 5-CHUNK PARALLEL 1-WAY FULL SCAN OVER mytest2*
With hint:
*EXPLAIN select /*+ USE_SORT_MERGE_JOIN */ count(*) *
*from "mytestview1" m1*
*inner join "mytestview2" m2*
*on m1."bId" = m2."bId"*
*CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER mytest1*
* SERVER AGGREGATE INTO SINGLE ROW*
* PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)*
* CLIENT 5-CHUNK PARALLEL 1-WAY FULL SCAN OVER mytest2*
I know that when joining that I should ideally do <large table> join <small
table>, but this is on my test environment and in production both tables
are roughly the same size, so I’m trying to force it to use the sort merge
before running the query in prod. My region servers in test don’t have that
much heap space (about 2 gigs) if that makes a difference. Do I need to
force a major compaction, generate statistics, anything like that?
Thanks!
Matt
*From:* Maryann Xue [mailto:maryann....@gmail.com]
*Sent:* 11 March 2015 20:16
*To:* user@phoenix.apache.org
*Subject:* Re: Using Hints in Phoenix
Hi Matt,
Views or tables should not matter in this. Would you mind sharing your
query and DDLs?
Thanks,
Maryann
On Wed, Mar 11, 2015 at 6:06 AM, Matthew Johnson <matt.john...@algomi.com>
wrote:
Hi Maryann,
I am using 4.3.0 (I upgraded as soon as it was released, largely so I could
pick up this feature). I am actually joining views rather than tables –
would this make a difference?
Cheers,
Matt
*From:* Maryann Xue [mailto:maryann....@gmail.com]
*Sent:* 10 March 2015 20:54
*To:* user@phoenix.apache.org
*Subject:* Re: Using Hints in Phoenix
Hi Matt,
Which version of Phoenix are you using? Sort-merge join is only available
in Phoenix 4.3.
Thanks,
Maryann
On Tue, Mar 10, 2015 at 6:11 AM, Matthew Johnson <matt.john...@algomi.com>
wrote:
Hi Maryann,
Thanks for clarifying that for me. I’ve been playing with the hint
*USE_SORT_MERGE_JOIN* to try and solve an issue when joining two very large
tables:
*Error: Encountered exception in sub plan [0] execution.*
*SQLState: null*
*ErrorCode: 0*
Which I believe is related to a lack of memory for building the hash table?
I thought that using *SELECT /*+ USE_SORT_MERGE_JOIN*/* would make the join
much slower but would work on an unlimited data set – did I misunderstand?
The explain plan does not change with or without this hint:
CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER mytable1
SERVER AGGREGATE INTO SINGLE ROW
PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)
CLIENT 15-CHUNK PARALLEL 1-WAY FULL SCAN OVER mytable2
Cheers,
Matt
*From:* Maryann Xue [mailto:maryann....@gmail.com]
*Sent:* 09 March 2015 15:00
*To:* user@phoenix.apache.org
*Subject:* Re: Using Hints in Phoenix
Hi Matt,
So far in Phoenix, hints are only supported as specified right after
keywords SELECT, UPSERT and DELETE. Same for join queries. It is currently
impossible to hint a certain join algorithm for a specific join node in a
multiple join query. However, for subqueries, the inner query can have its
own hints, independent of the outer query, like "SELECT /*+ INDEX(t idx1)*/
col1, col2 FROM t WHERE col3 IN (SELECT /*+ NO_INDEX*/ id FROM r WHERE name
= 'x')".
Thanks,
Maryann
On Mon, Mar 9, 2015 at 7:26 AM, Matthew Johnson <matt.john...@algomi.com>
wrote:
Hi guys,
This is more of a general question than a problem – but I’m just wondering
if someone can clarify for me what the syntax rules are for hints in
Phoenix. Does it matter where in the query they go? Do they always go
something like *SELECT <insert hint> x from y*? Or, if the hint is for a
join (eg Sort Merge) does it go in the join part (*SELECT x from y inner
join <insert hint> z on j = k*)?
Couldn’t seem to find anything specific on this in the docs, and haven’t
worked much with database hints in general so maybe there is a convention
that I am not aware of – apologies if it’s a stupid question!
Cheers,
Matt
