Original Query

*************************************************************************


SELECT CONSUMER_TRAN.TRAN_REF_ID
    , CONSUMER_TRAN.SND_TRAN_DATE
    , CONSUMER_MERGE_XREF_S.PRM_CNSMR_ID 
FROM
   CONSUMER_TRAN
  , CONSUMER_MERGE_XREF_S 
WHERE
 CONSUMER_MERGE_XREF_S.CNSMR_ID = CONSUMER_TRAN.SND_CNSMR_ID
AND CONSUMER_MERGE_XREF_S.PRM_CNSMR_ID <> CONSUMER_MERGE_XREF_S.CNSMR_ID 
AND  CONSUMER_TRAN.LYLTY_PGM_MBSHP_ID is null

************************************************************************************

Tuned Query

*******************************************************************************

SELECT /*+ first_rows(10) */ CONSUMER_TRAN.TRAN_REF_ID
    , CONSUMER_TRAN.SND_TRAN_DATE
    , CONSUMER_MERGE_XREF_S.PRM_CNSMR_ID 
FROM
   CONSUMER_TRAN
  , CONSUMER_MERGE_XREF_S 
WHERE
 CONSUMER_MERGE_XREF_S.CNSMR_ID = CONSUMER_TRAN.SND_CNSMR_ID
AND CONSUMER_MERGE_XREF_S.PRM_CNSMR_ID <> CONSUMER_MERGE_XREF_S.CNSMR_ID 
AND  SYS_OP_MAP_NONNULL(CONSUMER_TRAN.LYLTY_PGM_MBSHP_ID) is not null


*********************************************************************************