Any help here...? On 20 Dec 2017 17:58, "Flavio Pompermaier" <pomperma...@okkam.it> wrote:
> Hi to all, > I'm trying to find the best query for my use case but I found that one > version work and the other one does not (unless that I don't apply some > tuning to timeouts etc like explained in [1]). > > The 2 queries extract the same data but, while the first query terminates > the second does not. > *PS*: without the USE_SORT_MERGE_JOIN both queries weren't working > > SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) > FROM PEOPLE ds JOIN MYTABLE l ON ds.PERSON_ID = l.LOCALID > WHERE l.EID IS NULL AND l.DSID = 'PEOPLE' AND l.HAS_CANDIDATES = FALSE; > +----------------------------------------------------------- > ----------------------------------------------------+------- > ----------+----------------+----------------+ > | PLAN > | EST_BYTES_READ | EST_ROWS_READ | > EST_INFO_TS | > +----------------------------------------------------------- > ----------------------------------------------------+------- > ----------+----------------+----------------+ > | SORT-MERGE-JOIN (INNER) TABLES > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 3-WAY FULL > SCAN OVER PEOPLE | 14155777900 <(415)%20577-7900> | > 12077867 | 1513754378759 | > | SERVER FILTER BY FIRST KEY ONLY > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | CLIENT MERGE SORT > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | AND (SKIP MERGE) > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 15-WAY RANGE > SCAN OVER MYTABLE [0] - [2] | 14155777900 <(415)%20577-7900> | > 12077867 | 1513754378759 | > | SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND > HAS_CANDIDATES = false) | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | SERVER SORTED BY [L.LOCALID] > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | CLIENT MERGE SORT > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | CLIENT AGGREGATE INTO SINGLE ROW > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > +----------------------------------------------------------- > ----------------------------------------------------+------- > ----------+----------------+----------------+ > > > SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) > FROM (SELECT LOCALID FROM MYTABLE > WHERE EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES = FALSE) l JOIN > PEOPLE ds ON ds.PERSON_ID = l.LOCALID; > +----------------------------------------------------------- > ---------------------------------------------------+-------- > ---------+----------------+----------------+ > | PLAN > | EST_BYTES_READ | EST_ROWS_READ | > EST_INFO_TS | > +----------------------------------------------------------- > ---------------------------------------------------+-------- > ---------+----------------+----------------+ > | SORT-MERGE-JOIN (INNER) TABLES > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 3-WAY RANGE > SCAN OVER MYTABLE [0] - [2] | 14155777900 <(415)%20577-7900> | > 12077867 | 1513754378759 | > | SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND > HAS_CANDIDATES = false) | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | CLIENT MERGE SORT > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | AND (SKIP MERGE) > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 42-WAY FULL > SCAN OVER PEOPLE | 14155777900 <(415)%20577-7900> | > 12077867 | 1513754378759 | > | SERVER FILTER BY FIRST KEY ONLY > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | SERVER SORTED BY [DS.PERSON_ID] > | 14155777900 <(415)%20577-7900> | > 12077867 | 1513754378759 | > | CLIENT MERGE SORT > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > | CLIENT AGGREGATE INTO SINGLE ROW > | 14155777900 <(415)%20577-7900> > | 12077867 | 1513754378759 | > +----------------------------------------------------------- > ---------------------------------------------------+-------- > ---------+----------------+----------------+ > 10 rows selected (0.041 seconds) > > What do you think? Whould I try to give more resources to HBase/Phoenix or > is the first query the best (and reliable) one? > Any insight about this is highly appreciated.. > > Best, > Flavio >
