The total time taken to execute this update statement is 49 seconds.But after putting index the update statement took 52 seconds.?
SQL> update intcdr.inf_prop_security ips set ips.security_to = 2
  2  where trim(ips.proposal_facility_code) in
  3  (select trim(fd.cis_facility_code) from intcdr.inf_cbs_facility_detail fd where fd.is_unrat_bor
 = 1);

184805 rows updated.


Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE
   1    0   UPDATE OF 'INF_PROP_SECURITY'
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'INF_PROP_SECURITY'
   5    2       SORT (JOIN)
   6    5         VIEW OF 'VW_NSO_1'
   7    6           SORT (UNIQUE)
   8    7             TABLE ACCESS (FULL) OF 'INF_CBS_FACILITY_DETAIL'


Statistics
----------------------------------------------------------
        128  recursive calls
     189127  db block gets
       6138  consistent gets
      39274  physical reads
   43836868  redo size
        616  bytes sent via SQL*Net to client
        698  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          3  sorts (disk)
     184805  rows processed

SQL> update intcdr.inf_prop_security ips set ips.security_to = 2
  2  where trim(ips.proposal_facility_code) in
  3  (select trim(fd.cis_facility_code) from intcdr.inf_cbs_facility_detail fd where fd.is_unrat_bor
 = 1);

184805 rows updated.

Elapsed: 00:00:52.05

Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE
   1    0   UPDATE OF 'INF_PROP_SECURITY'
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'INF_PROP_SECURITY'
   5    2       SORT (JOIN)
   6    5         VIEW OF 'VW_NSO_1'
   7    6           SORT (UNIQUE)
   8    7             TABLE ACCESS (BY INDEX ROWID) OF 'INF_CBS_FACILI
          TY_DETAIL'

   9    8               INDEX (RANGE SCAN) OF 'IDX_INF_CBS_DETAIL' (NO
          N-UNIQUE)

Statistics
----------------------------------------------------------
        128  recursive calls
     189107  db block gets
       6377  consistent gets
      38283  physical reads
   43741832  redo size
        623  bytes sent via SQL*Net to client
        698  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          3  sorts (disk)
     184805  rows processed