How to force a table to go first in join?

James_3

Hello All,

I have been trying to performance tune a query but it doesn't want to use the ENDDATETIME within the projection for the RTMS_TIMER table.  Is there a way to force the LON_TZ_PAYLOAD_STATUS table to go first in the join and force the use of the ENDDATETIME column in the projection?

Query:
 SELECT Z.Client,
        r.Domain,
        Z.TRANSACTION_DATE AS Enddatetime,
        COUNT(DISTINCT r.User_Name) AS User_Cnt
   FROM OLYPRD.LON_TZ_PAYLOAD_STATUS Z join
                           Olyprd.Rtms_Timer r
                           ON (Z.CLIENT = R.CLIENT AND r.ENDDATETIME >= z.UTC_START_DT_TM AND r.ENDDATETIME < z.UTC_END_DT_TM)
                  WHERE z.client = 'AHAU_TX'
                    AND Z.TRANSACTION_DATE = DATE '2014-04-20'
                    AND R.DOMAIN = 'P3291'
                    AND r.Elapsedtime >= 0
                    AND r.Timername IS NOT NULL
                    AND r.Enterprise IS NOT NULL
  GROUP BY Z.Client,
           r.Domain,
           Z.TRANSACTION_DATE;

Explain:
Access Path:
+-GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 475K, Rows: 5] (PATH ID: 1)
|  Aggregates: count(DISTINCT r.USER_NAME)
|  Group By: Z.CLIENT, r.DOMAIN, Z.TRANSACTION_DATE
|  Execute on: All Nodes
| +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 473K, Rows: 84K] (PATH ID: 2)
| |      Group By: Z.CLIENT, r.DOMAIN, Z.TRANSACTION_DATE, r.USER_NAME
| |      Execute on: All Nodes
| | +---> JOIN MERGEJOIN(inputs presorted) [Cost: 471K, Rows: 84K] (PATH ID: 3)
| | |      Join Cond: (Z.CLIENT = r.CLIENT)
| | |      Join Filter: (r.ENDDATETIME >= Z.UTC_START_DT_TM) AND (r.ENDDATETIME < Z.UTC_END_DT_TM)
| | |      Materialize at Output: r.DOMAIN, r.USER_NAME
| | |      Execute on: All Nodes
| | | +-- Outer -> STORAGE ACCESS for r [Cost: 426K, Rows: 12M] (PATH ID: 4)
| | | |      Projection: OLYPRD.RTMS_TIMER_PROJ_1_1
| | | |      Materialize: r.CLIENT, r.ENDDATETIME
| | | |      Filter: (r.CLIENT = 'AHAU_TX')
| | | |      Filter: (r.ENTERPRISE IS NOT NULL)
| | | |      Filter: (r.DOMAIN = 'P3291')
| | | |      Filter: (r.TIMERNAME IS NOT NULL)
| | | |      Filter: (r.ELAPSEDTIME >= 0)
| | | |      Execute on: All Nodes
| | | |      Runtime Filter: (SIP1(MergeJoin): r.CLIENT)
| | | +-- Inner -> STORAGE ACCESS for Z [Cost: 145, Rows: 2] (PUSHED GROUPING) (PATH ID: 5)
| | | |      Projection: OLYPRD.LON_TZ_PAYLOAD_STATUS_node0015
| | | |      Materialize: Z.TRANSACTION_DATE, Z.CLIENT, Z.UTC_START_DT_TM, Z.UTC_END_DT_TM
| | | |      Filter: (Z.CLIENT = 'AHAU_TX')
| | | |      Filter: (Z.TRANSACTION_DATE = '2014-04-20'::date)
| | | |      Execute on: All Nodes