1 Reply Latest reply: Nov 1, 2012 11:29 AM by Jim Smith RSS

    Need help in tuning this query

    906050
      Hello,

      We have basically Three large tables

      DFU - contains tour packages information
      DFU DEPARTURE - for each package list departure or start date and other info. Most of packages are weekly so we can assume for each row in DFU table this table will contain almost 52 records.
      TRANSACTION - Bookings information for each package departure

      Below query is used in application. It takes on average 91 seconds to complete and returns 903K records. Problem is I want to avoid full table scan of above three tables which as per explain plan for below table , is happning. Please help thanks in advance.

      /* Formatted on 10/31/2012 5:41:57 PM (QP5 v5.185.11230.41888) */
      SELECT a1.dfu_id dfu_id,
      a1.departure_date departure_date,
      a1.tracs_code1 tracs_code1,
      a1.contract_ind1 contract_ind1,
      a1.synthetic_tracs_id1 synthetic_tracs_id1,
      a1.tracsinv1_id tracs_inventory1_id,
      a1.first_accommodation_offset first_accommodation_offset,
      a1.first_accommodation_nights first_accommodation_nights,
      a1.inventory1_is_generic inventory1_is_generic,
      a1.inventory1_is_dummy inventory1_is_dummy,
      a1.tracs_code2 tracs_code2,
      a1.contract_ind2 contract_ind2,
      a1.synthetic_tracs_id2 synthetic_tracs_id2,
      a1.second_accommodation_offset second_accommodation_offset,
      a1.second_accommodation_nights second_accommodation_nights,
      a1.inventory2_is_generic inventory2_is_generic,
      a1.inventory2_is_dummy inventory2_is_dummy,
      a1.resort_airport resort_airport,
      a1.uk_airport uk_airport,
      a1.outbound_flight_series_id outbound_flight_series_id,
      a1.departure_date outbound_flight_date,
      NVL (b1.tui_capacity_override, b1.tui_capacity)
      outbound_flight_capacity,
      b1.adult_flight_costs outbound_adult_flight_costs,
      b1.child_flight_costs outbound_child_flight_costs,
      b1.airline_capacity outbound_airline_capacity,
      b1.airline_booked outbound_airline_booked,
      b1.is_airline_capacity_fixed outbound_is_airline_cap_fixed,
      a1.inbound_flight_series_id inbound_flight_series_id,
      a1.return_date inbound_flight_date,
      NVL (c1.tui_capacity_override, c1.tui_capacity)
      inbound_flight_capacity,
      c1.adult_flight_costs inbound_adult_flight_costs,
      c1.child_flight_costs inbound_child_flight_costs,
      c1.airline_capacity inbound_airline_capacity,
      c1.airline_booked inbound_airline_booked,
      c1.is_airline_capacity_fixed inbound_is_airline_cap_fixed,
      a1.subproduct_code subproduct_code,
      a1.main_subproduct_code main_subproduct_code,
      a1.duration duration,
      a1.rooms rooms,
      a1.seats seats,
      a1.fcstdraft_rdf fcstdraft_rdf,
      a1.seats_to_bookings_ratio seats_to_bookings_ratio,
      a1.rooms_to_bookings_ratio rooms_to_bookings_ratio,
      a1.adult_brochure_price adult_brochure_price,
      a1.adult_current_price adult_current_price,
      a1.kid_current_price kid_current_price,
      a1.second_kid_current_price second_kid_current_price,
      a1.allow_free_kids allow_free_kids,
      a1.free_kid_fluid_exclusive free_kid_fluid_exclusive,
      a1.product_code product_code,
      a1.adult_to_bookings adult_to_bookings,
      a1.first_child_to_bookings first_child_to_bookings,
      a1.second_child_to_bookings second_child_to_bookings,
      a1.season season,
      a1.selling_id selling_id,
      a1.meal_plan_code meal_plan_code,
      a1.airport_tax airport_tax,
      a1.commission_percentage commission_percentage,
      a1.tracs_selling_code tracs_selling_code,
      a1.room_type_group room_type_group,
      a1.t_rating t_rating,
      a1.sub_resort_code sub_resort_code,
      a1.time_slot time_slot,
      a1.is_live_priced is_live_priced,
      a1.printed_brochure_price printed_brochure_price,
      bld.brochure_launch_date brochure_launch_date,
      dpl.departure_from paper_departure_from,
      dpl.departure_to paper_departure_to,
      dpl.paper_launch paper_launch,
      dpl.paper_discontinue paper_discontinue,
      dpl.hold_price_to_date paper_hold_price_to_date,
      dpl.stop_bars_to_date paper_stop_bars_to_date,
      a1.cumul_price_change cumul_price_change,
      a1.previous_price previous_price,
      a1.price_change price_change,
      a1.last_reject_price_change_date last_reject_price_change_date,
      a1.last_reject_price_change last_reject_price_change,
      a1.last_reject_bar_date last_reject_bar_date,
      a1.last_reject_unbar_date last_reject_unbar_date,
      a1.apply_brochure_launch_rules apply_brochure_launch_rules,
      a1.apply_campaign_rules apply_campaign_rules,
      a1.discount_applies_to_panel discount_applies_to_panel,
      a1.use_brochure_price_rules use_brochure_price_rules,
      a1.flight_tolerance_applies flight_tolerance_applies,
      a1.child_price_is_percentage child_price_is_percentage,
      a1.nested_inventory_indicator nested_inventory_indicator,
      a1.use_t3_max_price use_t3_max_price,
      a1.apply_max_discount_amount_rule apply_max_discount_amount_rule,
      a1.flight_supplement flight_supplement,
      a1.norm_forecast norm_forecast,
      a1.first_child_reduction first_child_reduction,
      a1.first_child_percentage_amount first_child_percentage_amount,
      a1.second_child_reduction second_child_reduction,
      a1.second_child_percentage_amount second_child_percentage_amount,
      a1.start_promotion_price start_promotion_price,
      a1.last_accept_price_change_date last_accept_price_change_date,
      a1.last_accept_price_change last_accept_price_change,
      a1.package_type_indicator package_type_indicator,
      a1.override_rdf override_rdf,
      a1.selling_unit_description selling_unit_description,
      ddo.rdf_allocation rdf_allocation,
      apr.additional_package_revenue additional_package_revenue,
      a1.hold_price hold_price,
      a1.price_per_unit_person price_per_unit_person,
      a1.life_cycle_indicator life_cycle_indicator,
      a1.increase_discount increase_discount,
      a1.increase_premium increase_premium,
      a1.last_overnight_man_chg,
      (SELECT t_rating_group
      FROM t_rating
      WHERE a1.t_rating = t_rating)
      t_rating_group,
      a1.pti_override,
      NVL (a1.fixed_costs, 0) fixed_costs,
      a1.EXCHANGE_RATE,
      a1.CURRENCY_CODE
      FROM (SELECT a.dfu_id,
      a.departure_date,
      a.return_date,
      a.adult_brochure_price,
      a.adult_current_price,
      a.kid_current_price,
      a.second_kid_current_price,
      a.allow_free_kids,
      DECODE (z.adult,
      NULL, NVL (a.adult_to_bookings, 0),
      (NVL (a.adult_to_bookings, 0) + z.adult))
      adult_to_bookings,
      DECODE (z.first_child,
      NULL, NVL (a.first_child_to_bookings, 0),
      (NVL (a.first_child_to_bookings, 0) + z.first_child))
      first_child_to_bookings,
      DECODE (
      z.second_child,
      NULL, NVL (a.second_child_to_bookings, 0),
      (NVL (a.second_child_to_bookings, 0) + z.second_child))
      second_child_to_bookings,
      DECODE (z.seat,
      NULL, NVL (a.seats_to_bookings_ratio, 0),
      (NVL (a.seats_to_bookings_ratio, 0) + z.seat))
      seats_to_bookings_ratio,
      DECODE (z.room,
      NULL, NVL (a.rooms_to_bookings_ratio, 0),
      (NVL (a.rooms_to_bookings_ratio, 0) + z.room))
      rooms_to_bookings_ratio,
      b.season,
      b.outbound_flight_series_id,
      b.inbound_flight_series_id,
      b.selling_id,
      b.tracs_selling_code,
      b.subproduct_code,
      b.main_subproduct_code,
      b.duration,
      b.first_accommodation_offset,
      b.first_accommodation_nights,
      b.second_accommodation_offset,
      b.second_accommodation_nights,
      d.tracs_inventory_code tracs_code1,
      e.tracs_inventory_code tracs_code2,
      d.contract_ind contract_ind1,
      e.contract_ind contract_ind2,
      f.synthetic_id synthetic_tracs_id1,
      g.synthetic_id synthetic_tracs_id2,
      c.tracsinv1_id tracsinv1_id,
      f.inventory1_is_dummy,
      g.inventory2_is_dummy,
      c.meal_plan_code,
      b.resort_airport,
      b.uk_airport,
      p.airport_tax,
      cc.commission_percentage,
      DECODE (t.generic_base_inventory_code, NULL, 'N', 'Y')
      inventory1_is_generic,
      DECODE (u.generic_base_inventory_code, NULL, 'N', 'Y')
      inventory2_is_generic,
      CASE
      WHEN '201' = '293' THEN NVL (a.committed_rooms, 0)
      ELSE NVL (w.rooms, 0)
      END
      rooms,
      NVL (w.seats, 0) seats,
      NVL (x.qty, 0) fcstdraft_rdf,
      b.free_kid_fluid_exclusive,
      b.product_code,
      DECODE (c.is_twin_centre, 'N', d.room_group, e.room_group)
      room_type_group,
      DECODE (c.is_twin_centre, 'N', d.t_rating, e.t_rating)
      t_rating,
      DECODE (c.is_twin_centre,
      'N', l.tui_corporate_code,
      m.tui_corporate_code)
      sub_resort_code,
      ts.time_slot,
      a.cumul_price_change,
      a.previous_price,
      a.price_change,
      a.last_reject_price_change_date,
      a.last_reject_price_change,
      a.last_reject_bar_date,
      a.last_reject_unbar_date,
      a.printed_brochure_price,
      sp.is_live_priced,
      sp.apply_brochure_launch_rules,
      sp.apply_campaign_rules,
      sp.discount_applies_to_panel,
      sp.use_brochure_price_rules,
      sp.flight_tolerance_applies,
      sp.child_price_is_percentage,
      sp.nested_inventory_indicator,
      sp.use_t3_max_price,
      sp.apply_max_discount_amount_rule,
      a.flight_supplement,
      nf.qty norm_forecast,
      a.first_child_reduction,
      a.first_child_percentage_amount,
      a.second_child_reduction,
      a.second_child_percentage_amount,
      NVL (a.start_promotion_price, a.adult_current_price)
      start_promotion_price,
      a.last_accept_price_change_date,
      a.last_accept_price_change,
      b.package_type_indicator,
      NVL (y.quantity, 0) override_rdf,
      c.selling_unit_description,
      DECODE (hp.validity_date, NULL, 'N', 'Y') hold_price,
      b.price_per_unit_person,
      a.life_cycle_indicator,
      a.increase_discount,
      a.increase_premium,
      a.last_overnight_man_chg,
      p.pti_override,
      a.fixed_costs,
      b.EXCHANGE_RATE EXCHANGE_RATE,
      b.CURRENCY_CODE CURRENCY_CODE
      FROM (SELECT *
      FROM dfu_departure
      WHERE is_scheduled = 'Y'
      AND ( departure_date BETWEEN TO_DATE ('01/11/2006',
      'MM/DD/YYYY')
      AND TO_DATE ('09/11/2006',
      'MM/DD/YYYY')
      OR return_date BETWEEN TO_DATE ('01/11/2006',
      'MM/DD/YYYY')
      AND TO_DATE ('09/11/2006',
      'MM/DD/YYYY'))) a,
      (SELECT dfu_id,
      d.season,
      outbound_flight_series_id,
      inbound_flight_series_id,
      selling_id,
      tracs_selling_code,
      subproduct_code,
      main_subproduct_code,
      duration,
      first_accommodation_offset,
      first_accommodation_nights,
      second_accommodation_offset,
      second_accommodation_nights,
      resort_airport,
      uk_airport,
      free_kid_fluid_exclusive,
      package_type_indicator,
      flight_time,
      product_code,
      price_per_unit_person,
      NVL (CUR.EXCHANGE_RATE, 1) EXCHANGE_RATE,
      NVL (CUR.CURRENCY_CODE, '') CURRENCY_CODE
      FROM dfu d,
      airport ap,
      optimisation_resort_group org,
      currency cur
      WHERE d.resort_airport = ap.airport_code
      AND ap.optimisation_resort_group =
      org.optimisation_resort_group
      AND CUR.CURRENCY_ID(+) = D.LOCAL_CURRENCY_ID
      -- AND cur.season(+) = d.season
      AND org.optimisation_resort_group =
      (SELECT optimisation_resort_group
      FROM airport
      WHERE airport_code = 'ZTH')) b,
      (SELECT selling_id,
      is_twin_centre,
      inventory1_id tracsinv1_id,
      DECODE (consumption_order,
      1, inventory1_id,
      inventory2_id)
      inventory1_id,
      DECODE (consumption_order,
      2, inventory1_id,
      inventory2_id)
      inventory2_id,
      meal_plan_code,
      selling_unit_description
      FROM selling_unit) c,
      norm_forecast nf,
      inventory d,
      inventory e,
      base_inventory f,
      base_inventory g,
      accommodation_unit k,
      geography l,
      geography m,
      channel_commission cc,
      time_slot ts,
      subproduct sp,
      airport_tax p,
      (SELECT DISTINCT generic_base_inventory_code, season
      FROM generic_product_map
      WHERE is_active = 'Y') t,
      (SELECT DISTINCT generic_base_inventory_code, season
      FROM generic_product_map
      WHERE is_active = 'Y') u,
      ( SELECT a.dfu_id,
      a.departure_date,
      SUM (seats) seats,
      SUM (rooms) rooms
      FROM transaction_history a,
      booking_status b,
      dfu d,
      airport ap,
      optimisation_resort_group org
      WHERE d.resort_airport = ap.airport_code
      AND ap.optimisation_resort_group =
      org.optimisation_resort_group
      AND org.optimisation_resort_group =
      (SELECT optimisation_resort_group
      FROM airport
      WHERE airport_code = 'ZTH')
      AND d.dfu_id = a.dfu_id
      AND a.booking_status_code = b.booking_status_code
      AND b.is_cancellation_code = 'N'
      GROUP BY a.dfu_id, a.departure_date) w,
      (SELECT dfu_id, startdate, qty
      FROM stsc.dfu a, denorm_forecast b
      WHERE a.dmdunit = b.dmdunit
      AND a.dmdgroup = b.dmdgroup
      AND a.loc = b.loc
      AND b.model = 'FDF'
      AND b.TYPE = 5) x,
      ( SELECT b.dfu_id, b.start_date, SUM (quantity) "QUANTITY"
      FROM dfu_departure_override b,
      dfu d,
      airport ap,
      optimisation_resort_group org
      WHERE d.resort_airport = ap.airport_code
      AND ap.optimisation_resort_group =
      org.optimisation_resort_group
      AND org.optimisation_resort_group =
      (SELECT optimisation_resort_group
      FROM airport
      WHERE airport_code = 'ZTH')
      AND d.dfu_id = b.dfu_id
      GROUP BY b.dfu_id, b.start_date) y,
      (SELECT dfu_id,
      start_date,
      adult,
      first_child,
      second_child,
      seat,
      room
      FROM dfu_departure_ratio_over ) z,
      (SELECT dfu_id,
      departure_date,
      validity_date,
      hold_price_from_date,
      hold_price_to_date
      FROM (SELECT dfu_id,
      departure_date,
      validity_date,
      hold_price_from_date,
      hold_price_to_date,
      MAX (validity_date)
      OVER (PARTITION BY dfu_id, departure_date)
      AS max_validity_date
      FROM hold_prices
      WHERE validity_date <=
      TO_DATE ('01/11/2006', 'MM/DD/YYYY'))
      WHERE validity_date = max_validity_date) hp,
      pti_lci_map p
      WHERE b.dfu_id = a.dfu_id
      AND DECODE (p.pti, '*', b.package_type_indicator, p.pti) =
      b.package_type_indicator
      AND DECODE (NVL (a.life_cycle_indicator, '*'), 'L', 'L', '*') =
      NVL (p.lci, '*')
      AND b.selling_id = c.selling_id
      AND a.dfu_id = nf.dfu_id(+)
      AND NEXT_DAY (a.departure_date, 'MON') - 7 = nf.startdate(+)
      AND c.inventory1_id = d.inventory_id(+)
      AND c.inventory2_id = e.inventory_id(+)
      AND d.tracs_inventory_code = f.base_inventory_code(+)
      AND e.tracs_inventory_code = g.base_inventory_code(+)
      AND e.unit_id = k.unit_id(+)
      AND k.sub_resort_id = l.geographic_id(+)
      AND k.region_id = m.geographic_id(+)
      AND b.uk_airport = p.uk_airport_code(+)
      AND b.resort_airport = p.resort_airport_code(+)
      AND b.season = p.season(+)
      AND b.season = cc.season(+)
      AND b.subproduct_code = cc.subproduct_code(+)
      AND b.product_code = cc.product_code(+)
      AND d.tracs_inventory_code = t.generic_base_inventory_code(+)
      AND d.inventory_season = t.season(+)
      AND e.tracs_inventory_code = u.generic_base_inventory_code(+)
      AND e.inventory_season = u.season(+)
      AND a.dfu_id = w.dfu_id(+)
      AND a.departure_date = w.departure_date(+)
      AND a.dfu_id = x.dfu_id(+)
      AND NEXT_DAY (a.departure_date, 'MON') - 7 = x.startdate(+)
      AND a.dfu_id = y.dfu_id(+)
      AND a.departure_date = y.start_date(+)
      AND a.dfu_id = z.dfu_id(+)
      AND a.departure_date = z.start_date(+)
      AND TO_DATE (TO_CHAR (b.flight_time, 'HH24:MI:SS'),
      'HH24:MI:SS') BETWEEN TO_DATE (
      TO_CHAR (
      ts.time_slot_start(+),
      'HH24:MI:SS'),
      'HH24:MI:SS')
      AND TO_DATE (
      TO_CHAR (
      ts.time_slot_end(+),
      'HH24:MI:SS'),
      'HH24:MI:SS')
      AND b.subproduct_code = sp.subproduct_code(+)
      AND a.dfu_id = hp.dfu_id(+)
      AND a.departure_date = hp.departure_date(+)
      AND TO_DATE ('01/11/2006', 'MM/DD/YYYY') BETWEEN hp.hold_price_from_date(+)
      AND hp.hold_price_to_date(+)) a1,
      flight b1,
      flight c1,
      brochure_launch_dates bld,
      discount_paper_launch dpl,
      dfu_departure_optimisation ddo,
      additional_package_revenue apr
      WHERE a1.outbound_flight_series_id = b1.flight_series_id(+)
      AND a1.departure_date = b1.flight_date(+)
      AND a1.inbound_flight_series_id = c1.flight_series_id(+)
      AND a1.return_date = c1.flight_date(+)
      AND a1.subproduct_code = bld.subproduct_code(+)
      AND a1.product_code = bld.product_code(+)
      AND a1.season = bld.season(+)
      AND a1.dfu_id = dpl.dfu_id(+)
      AND a1.departure_date >= dpl.departure_from(+)
      AND a1.departure_date <= dpl.departure_to(+)
      AND a1.dfu_id = ddo.dfu_id(+)
      AND a1.departure_date = ddo.departure_date(+)
      AND a1.dfu_id = apr.dfu_id(+)
      AND a1.departure_date >= apr.departure_from(+)
      AND a1.departure_date <= apr.departure_to(+)
      ORDER BY a1.dfu_id ASC, a1.departure_date ASC;

      Thanks
      Niraj Rathi