SELECT /*+ORDERED FULL(a) PARALLEL(a) FULL(b) PARALLEL(b)USE_HASH(a fim)*/
       a.record_seq_num,
       a.cusip,
       a.primary_exchange,
       a.underly_sec_cusip,
       a.frst_call_strk_pr,
       upper(a.primary_symbol) symbol,
       fb_proc.get_date(b.record_dt) record_dt,
       'USD' ntv_country_cd,
       b.s_p_rating,
       b.moody_rating
FROM   (SELECT a_in.*,
               rank() over(PARTITION BY a_in.cusip ORDER BY a_in.record_seq_num DESC) rnk
        FROM   fb_prs_desc_a a_in) a
LEFT   JOIN fb_prs_desc_b b ON b.record_seq_num = a.record_seq_num
                        AND    b.cusip = a.cusip
LEFT   JOIN (SELECT --/*+ FULL(fim_in) FULL(fo)*/
                    fim_in.*,
                    fo.expiration_dt,
                    fo.instr_master_id fo_instr_master_id
             FROM   fi_instr_master fim_in
             LEFT   JOIN fi_option fo ON fim_in.instr_master_id = fo.instr_master_id
             WHERE  rownum > 0
             AND    fim_in.data_prov_id IN (11, 23)
             AND    fim_in.definer_id IS NULL) fim ON  fim.cusip = a.cusip
                                                   AND (fim.fo_instr_master_id IS NULL
                                                        OR fim.instr_type_cd = 'RIGHTS' 
                                                        OR fim.is_actv = 1)

SELECT STATEMENT, GOAL = CHOOSE  
 NESTED LOOPS OUTER  
  HASH JOIN OUTER  
   VIEW Object owner=SMACKEY2 
    TABLE ACCESS FULL Object owner=SMACKEY2 Object name=FB_PRS_DESC_A
   TABLE ACCESS FULL Object owner=SMACKEY2 Object name=FB_PRS_DESC_B
  VIEW Object owner=SYS 
   VIEW Object owner=SMACKEY2 
    COUNT  
     FILTER  
      NESTED LOOPS OUTER  
       TABLE ACCESS BY INDEX ROWID Object owner=SMACKEY2 Object name=FI_INSTR_MASTER
        INDEX SKIP SCAN Object owner=SMACKEY2 Object name=FI_INSTR_MASTER_IX20
       INDEX UNIQUE SCAN Object owner=SMACKEY2 Object name=FI_OPTION_PK