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
