How to improve query performance in Oracle

Question

Below sql query is taking too much time for execution. It might be due to repetitive use of same table in from clause. I am not able to find out how to fix this query so that performance would be improve. Can anyone help me out with this?

Thanks in advance !!

select  --
  from t_carrier_location   act_end,
       t_location           end_loc,
       t_carrier_location   act_start,
       t_location           start_loc,
       t_vm_voyage_activity va,
       t_vm_voyage          v,
       t_location_position  lp_start,
       t_location_position  lp_end
 where act_start.carrier_location_id = va.carrier_location_id
   and act_start.carrier_id = v.carrier_id
   and act_end.carrier_location_id =
       decode((select cl.carrier_location_id
                from t_carrier_location cl
               where cl.carrier_id = act_start.carrier_id
                 and cl.carrier_location_no =
                     act_start.carrier_location_no + 1),
              null,
              (select cl2.carrier_location_id
                 from t_carrier_location cl2, t_vm_voyage v2
                where v2.hire_period_id = v.hire_period_id
                  and v2.voyage_id =
                      (select min(v3.voyage_id)
                         from t_vm_voyage v3
                        where v3.voyage_id > v.voyage_id
                          and v3.hire_period_id = v.hire_period_id)
                  and v2.carrier_id = cl2.carrier_id
                  and cl2.carrier_location_no = 1),
              (select cl.carrier_location_id
                 from t_carrier_location cl
                where cl.carrier_id = act_start.carrier_id
                  and cl.carrier_location_no =
                      act_start.carrier_location_no + 1))
   and lp_start.location_id = act_start.location_id
   and lp_start.from_date <=
       nvl(act_start.actual_dep_time, act_start.actual_arr_time)
   and (lp_start.to_date is null or
       lp_start.to_date >
       nvl(act_start.actual_dep_time, act_start.actual_arr_time))
   and lp_end.location_position_id = act_end.location_id
   and lp_end.from_date <=
       nvl(act_end.actual_dep_time, act_end.actual_arr_time)
   and (lp_end.to_date is null or
       lp_end.to_date >
       nvl(act_end.actual_dep_time, act_end.actual_arr_time))
   and act_end.location_id = end_loc.location_id
   and act_start.location_id = start_loc.location_id;

have you tried indexing the table based on data and requirement. It must be decided properly. — Raghvendra
– Raghvendra, Commented May 10, 2017 at 6:31

Player_Neo · Accepted Answer · 2017-05-10 07:12:19Z

3

There is no Stright forward one answer for your question and the query you've mentioned. In order to get a better response time of any query, you need to keep few things in mind while writing your queries. I will mention few here which appeared to be important for your query

Use joins instead of subqueries.
Use EXPLAIN to determine queries are functioning appropriately.
Use the columns which are having indexes with your where clause else create an index on those columns. here use your common sense which are the columns to be indexed ex: foreign key columns, deleted, orderCreatedAt, startDate etc.
Keep the order of the select columns as they appear at the table instead of arbitrarily selecting columns.

The above four points are enough for the query you've provided.

To dig deep about SQL optimization and tuning refer this https://docs.oracle.com/database/121/TGSQL/tgsql_intro.htm#TGSQL130

answered May 10, 2017 at 7:12

Player_Neo

1,6953 gold badges21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sach Over a year ago

Thanks for your valuable response !!

Collectives™ on Stack Overflow

How to improve query performance in Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related