SELECT sr_no, agent_code, agent_name, report_to      
      FROM ams_lts_lead_summary
          WHERE agent_code IN (SELECT  ld_allocate_to_id
                                                 FROM  ams_lts_lead_details
                                              group by ld_allocate_to_id );    


Plan with group by
SELECT STATEMENT  CHOOSECost: 1 K  Bytes: 168 K  Cardinality: 2 K  				
5 HASH JOIN RIGHT SEMI  Cost: 1 K  Bytes: 168 K  Cardinality: 2 K  			
3 VIEW VIEW SYS.VW_NSO_1 Cost: 171  Bytes: 17 K  Cardinality: 2 K  		
2 HASH GROUP BY  Cost: 171  Bytes: 16 K  Cardinality: 2 K  	
1 INDEX FAST FULL SCAN INDEX AMS.IDX_LTS_LEAD_DETAILS Cost: 161  Bytes: 1 M  Cardinality: 146 K  
4 TABLE ACCESS FULL TABLE AMS.AMS_LTS_LEAD_SUMMARY Cost: 1 K  Bytes: 27 M  Cardinality: 335 K  


SELECT sr_no, agent_code, agent_name, report_to
           FROM ams_lts_lead_summary
          WHERE agent_code IN (SELECT distinct ld_allocate_to_id
                                                FROM ams_lts_lead_details);   


Plan with distinct
SELECT STATEMENT  CHOOSECost: 2 K  Bytes: 167 K  Cardinality: 2 K  			
4 HASH JOIN  Cost: 2 K  Bytes: 167 K  Cardinality: 2 K  		
2 SORT UNIQUE  Cost: 127  Bytes: 1 M  Cardinality: 146 K  	
1 INDEX FAST FULL SCAN INDEX AMS.IDX_LEAD_DTL_ALLOC_TO Cost: 127  Bytes: 1 M  Cardinality: 146 K  
3 TABLE ACCESS FULL TABLE AMS.AMS_LTS_LEAD_SUMMARY Cost: 1 K  Bytes: 27 M  Cardinality: 335 K