phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Maryann Xue <maryann....@gmail.com>
Subject Re: Using Hints in Phoenix
Date Tue, 10 Mar 2015 20:54:28 GMT
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.johnson@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.xue@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.johnson@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
>
>
>
>
>

Mime
View raw message