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 > > > > > > >
