Help tuning SQL ("Explain" included)

Database

Help tuning SQL ("Explain" included)

I could really use some help in finding any way or ways to increase the speed of this query.

select

p.lp_reg_nbr,
p.lp_rar_reg_area_cd

from ody.VHCL_MVS m
inner join ody.VHCL_PLT_HIST p on p.vhc_vhcl_unit_nbr=m.vhcl_vhcl_unit_nbr

where ((rdy_rnt_agr_nbr = '431685748' and erac_rnt_agr_nbr IS NULL)
and (eff_dt <= '2015/02/20 09:35:38' and exp_dt >= '2015/02/20 09:35:38')
and (reg_typ <> 'TP'))

QUALIFY ROW_NUMBER() OVER(PARTITION BY m.rdy_rnt_agr_nbr
ORDER BY p.eff_dt desc) =1

Here is the "Explain" information

Explain select

p.lp_reg_nbr,

p.lp_rar_reg_area_cd

from ody.VHCL_MVS m

inner join ody.VHCL_PLT_HIST p on p.vhc_vhcl_unit_nbr=m.vhcl_vhcl_unit_nbr

where ((rdy_rnt_agr_nbr = '431685748' and erac_rnt_agr_nbr IS NULL) 

and (eff_dt <= '2015/02/20 09:35:38' and exp_dt >= '2015/02/20 09:35:38')

and (reg_typ <> 'TP'))

QUALIFY ROW_NUMBER() OVER(PARTITION BY  m.rdy_rnt_agr_nbr

ORDER BY  p.eff_dt desc) =1

  1) First, we lock ODY_TB.VHCL_MVS in view ody.VHCL_MVS for access,

     and we lock ODY_TB.VHCL_PLT_HIST in view ody.VHCL_PLT_HIST for

     access.

  2) Next, we do an all-AMPs RETRIEVE step from ODY_TB.VHCL_MVS in view

     ody.VHCL_MVS by way of an all-rows scan with a condition of (

     "(ODY_TB.VHCL_MVS in view ody.VHCL_MVS.erac_rnt_agr_nbr IS NULL)

     AND ((NOT (ODY_TB.VHCL_MVS in view ody.VHCL_MVS.vhcl_vhcl_unit_nbr

     IS NULL )) AND (ODY_TB.VHCL_MVS in view

     ody.VHCL_MVS.rdy_rnt_agr_nbr = 431685748. ))") into Spool 3

     (all_amps) (compressed columns allowed), which is duplicated on

     all AMPs.  The input table will not be cached in memory, but it is

     eligible for synchronized scanning.  The size of Spool 3 is

     estimated with low confidence to be 3,840 rows (111,360 bytes).

     The estimated time for this step is 37.75 seconds.

  3) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an

     all-rows scan, which is joined to ODY_TB.VHCL_PLT_HIST in view

     ody.VHCL_PLT_HIST by way of an all-rows scan with a condition of (

     "(ODY_TB.VHCL_PLT_HIST in view ody.VHCL_PLT_HIST.eff_dt <=

     TIMESTAMP '2015-02-20 09:35:38') AND ((ODY_TB.VHCL_PLT_HIST in

     view ody.VHCL_PLT_HIST.exp_dt >= TIMESTAMP '2015-02-20 09:35:38')

     AND ((ODY_TB.VHCL_PLT_HIST in view ody.VHCL_PLT_HIST.reg_typ >

     'TP') OR (ODY_TB.VHCL_PLT_HIST in view ody.VHCL_PLT_HIST.reg_typ <

     'TP')))").  Spool 3 and ODY_TB.VHCL_PLT_HIST are joined using a

     dynamic hash join, with a join condition of (

     "ODY_TB.VHCL_PLT_HIST.vhc_vhcl_unit_nbr = vhcl_vhcl_unit_nbr").

     The result goes into Spool 2 (all_amps) (compressed columns

     allowed), which is built locally on the AMPs.  The size of Spool 2

     is estimated with no confidence to be 37,065 rows (2,854,005

     bytes).  The estimated time for this step is 0.88 seconds.

  4) We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by

     way of an all-rows scan into Spool 6 (Last Use), which is

     redistributed by hash code to all AMPs.  The result rows are put

     into Spool 1 (group_amps), which is built locally on the AMPs.

     The size is estimated with no confidence to be 1 row (99 bytes).

  5) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 1 are sent back to the user as the result of

     statement 1.

The tables index information is as follows:

VHCL_PLT_HIST

vhc_vhcl_unit_nbr

lp_reg_nbr

lp_rar_reg_area_cd

lp_rar_rat_reg_area_typ_cd

lp_rar_rat_cry_arimp_cry_cd

eff_dt

VHCL_MVS

mv_seq_nbr

rdy_rnt_arg_nbr

Any and all help would be appreciated