agnt_dim ----415884
clear_daily_fact_vw --- 3298510
PRDCT_DIM ----8446
cov_dim ---3165302
CLNT_DIM ---2098316

SELECT *
FROM   (SELECT A.PLCY_NBR,
               A.CO_CD,
               A.PLCY_STATUS_CD PLCY_STATUS_CD,
               TO_CHAR(B.RISK_COMMNCD_DT, 'YYYY-MM-DD') RISK_COMMNCD_DT,
               C.PLAN_TXT_DESC PRDCT_CATGRY_NAME,
               DECODE(B.PYMT_FREQ_CD, 'A', 1, 'M', 12, 'Q', 4, 'S', 2, 0) * B.TOTL_PREM_INSMNT_AMT CRRNT_ANNUAL_PREM_AMT,
               TO_CHAR(B.HOME_OFFC_RCVD_DT, 'YYYY-MM-DD') HOME_OFFC_RCVD_DT,
               C.BASIC_RIDER_IND,
               ROW_NUMBER() OVER(PARTITION BY B.PLCY_NBR, B.CO_CD ORDER BY C.BASIC_RIDER_IND, B.ELMNT_NBR DESC) SEQ_NBR
        FROM   CLEAR_DAILY_FACT_VW A,
               COV_DIM             B,
               PRDCT_DIM           C,
               CLNT_DIM            E
        WHERE  A.COV_DIM_ID = B.COV_DIM_ID 
        AND A.PRDCT_DIM_ID = C.PRDCT_DIM_ID 
        AND A.PLCY_STATUS_CD = 'A' 
        AND E.FIRST_NAME LIKE 'AL%' 
        AND A.CLNT_REF_NBR = E.CLNT_REF_NBR 
        AND (B.SVCNG_AGNT_NBR IN (SELECT AGNT_NBR FROM AGNT_DIM WHERE 1 = 1 
                                  AND AGNT_FIRST_NAME LIKE 'RE%')
            )
       )
WHERE  SEQ_NBR = 1
ORDER  BY PLCY_NBR,
          CO_CD