title: How can I optimize a loop, which is calling an object in Oracle?
body: <p>Below is the code which needs optimization.   </p>
  
  <pre><code>for i in 1 .. p_in_util_data_list(j).factlist.count LOOP
              SELECT count(*)
                INTO v_non_factor_exists
                FROM engine_usage_factors
               WHERE usage_month = v_usage_month
                 AND contract_seq_id = p_in_contractId
                 AND engine_serial_number = p_in_util_data_list(j).esn
                 AND nvl(upper(subfleet_id), 'X') =
                     nvl(upper(p_in_util_data_list(j).fleet), 'X')
                 AND nvl(upper(tail_number), 'X') =
                     nvl(upper(p_in_util_data_list(j).tail), 'X')
                 AND nvl(upper(factor_name), 'X') =
                     nvl(upper(p_in_util_data_list(j).factlist(i).name), 'X')
                 AND avg_flag = 'N'
                 AND recon_ind = 0;
  
              IF v_non_factor_exists &gt; 0 THEN
                DELETE FROM engine_usage_factors
                 WHERE usage_month = v_usage_month
                   AND contract_seq_id = p_in_contractId
                   AND engine_serial_number = p_in_util_data_list(j).esn
                   AND nvl(upper(subfleet_id), 'X') =
                       nvl(upper(p_in_util_data_list(j).fleet), 'X')
                   AND nvl(upper(tail_number), 'X') =
                       nvl(upper(p_in_util_data_list(j).tail), 'X')
                   AND nvl(upper(factor_name), 'X') =
                       nvl(upper(p_in_util_data_list(j).factlist(i).name),
                           'X')
                   AND avg_flag = 'N'
                   AND recon_ind = 0;
                COMMIT;
              END IF;
  
              IF UPPER(P_IN_UTIL_DATA_LIST(J).FACTLIST(I).NAME) = 'THRUST' THEN
                V_VALUE := 0;
              ELSE
                V_VALUE := P_IN_UTIL_DATA_LIST(J).FACTLIST(I).VALUE;
              END IF;
  
  
              IF UPPER(p_in_util_data_list(j).factlist(i).name) = 'THRUST' THEN
  
  
  
                UPDATE engine_usage
                   SET THRUST_RATING    = p_in_util_data_list(j).factlist(i)
                                          .value,
                       last_updated_by  = p_in_login_id,
                       last_update_date = sysdate
                 WHERE usage_month = v_usage_month
                   AND engine_serial_number =
                       trim(p_in_util_data_list(j).esn)
                   AND recon_ind = 0
                   AND from_date = v_from_date
                   AND contract_seq_id = p_in_contractId 
                   AND hybrid_payment_type = p_in_billingType;
                COMMIT;
  
  
  
                v_value := 0;
  
              END IF;
  
              INSERT INTO engine_usage_factors
                (usage_month,
                 contract_seq_id,
                 engine_serial_number,
                 subfleet_id,
                 tail_number,
                 factor_name,
                 factor_value,
                 recon_ind,
                 from_date,
                 avg_flag,
                 created_by,
                 created_date,
                 last_updated_by,
                 last_update_date)
              values
                (v_usage_month,
                 p_in_contractId,
                 p_in_util_data_list(j).esn,
                 p_in_util_data_list(j).fleet,
                 NVL(p_in_util_data_list(j).tail, 'DUMMY'),
                 p_in_util_data_list(j).factlist(i).name,
                 v_value,
                 0,
                 v_from_date,
                 'N',
                 p_in_login_id,
                 sysdate,
                 p_in_login_id,
                 sysdate);
  
              commit;
            END LOOP;
  </code></pre>
  
  <p>This loop is being called 700 times and takes alot of time.</p>