Please advice me on this query as this is taking more than 2 secs. if any background process is running.
See the attached file with planreport.
Thanks
Sathi
SQL Statement -- search.sql:
SELECT /*+ NO_INDEX (vp xtmpvehicle_price) */
vd.vehicle_id, vd.registration_nbr, vdr.vehicle_desc, vd.
non_gm_vehicle_flg, vo2.option_desc colour, vd.vehicle_status_id, vd.
vehicle_category_cd, vd.defleet_mileage_value, vd.vat_category_cd, vd.
location_cd, sc.sales_channel_nm, se.sale_event_nm, b.bulkpack_nm, bv.
delete_flg, vg.grade_cd, TRUNC(SYSDATE - vd.first_defleet_dt) stockage,
va.cherry_pick_flg, vp.vat_amt, vp.net_amt
FROM vehicle_detail vd, vehicle_derivative vdr, vehicle_option_relationship
vor2, vehicle_option vo2, vehicle_allocation va, sales_channel sc,
sale_event se, vehicle_grade vg, bulkpack_vehicle bv, bulkpack b,
vehicle_price vp
WHERE vd.pseudo_cd = vdr.pseudo_cd
AND vdr.delete_flg = 'N'
AND vdr.manufacturer_cd = '9'
AND vd.non_gm_vehicle_flg = 'N'
AND vd.vehicle_id = vor2.vehicle_id
AND vor2.manufacturer_cd = vo2.manufacturer_cd
AND vor2.model_year = vo2.model_year
AND vor2.model_year_suffix_typ = vo2.model_year_suffix_typ
AND vor2.option_cd = vo2.option_cd
AND UPPER(vor2.option_typ) = 'CC'
AND vor2.delete_flg = 'N'
AND vd.remarketing_company_id IN (1)
AND vd.vehicle_id = va.vehicle_id
AND va.cherry_pick_flg = 'N'
AND va.sales_channel_id = sc.sales_channel_id
AND sc.delete_flg = 'N'
AND sc.status_id = 10
AND va.sale_event_id = se.sale_event_id(+)
AND (se.delete_flg = 'N'
OR se.delete_flg IS NULL)
AND (se.remain_orig_channel_flg = 'Y'
OR se.remain_orig_channel_flg IS NULL)
AND (NOT se.status_id IN (102, 101)
OR se.status_id IS NULL)
AND vd.grade_id = vg.grade_id(+)
AND vd.vehicle_status_id = 39
AND vd.vehicle_id = vp.vehicle_id
AND LOWER(vp.price_typ) = 'sc'
AND vp.delete_flg = 'N'
AND vd.bulkpack_reserved_id IS NULL
AND vd.accepted_bulkpack_id IS NULL
AND vd.vehicle_id = bv.vehicle_id(+)
AND bv.bulkpack_id = b.bulkpack_id(+)
UNION
SELECT /*+ NO_INDEX (vp xtmpvehicle_price) */
vd.vehicle_id, vd.registration_nbr, vdr.vehicle_desc, vd.
non_gm_vehicle_flg, ' ' colour, vd.vehicle_status_id, vd.
vehicle_category_cd, vd.defleet_mileage_value, vd.vat_category_cd, vd.
location_cd, sc.sales_channel_nm, se.sale_event_nm, b.bulkpack_nm, bv.
delete_flg, vg.grade_cd, TRUNC(SYSDATE - vd.first_defleet_dt) stockage,
va.cherry_pick_flg, vp.vat_amt, vp.net_amt
FROM vehicle_detail vd, vehicle_derivative vdr, vehicle_allocation va,
sales_channel sc, sale_event se, vehicle_grade vg, bulkpack_vehicle bv,
bulkpack b, vehicle_price vp
WHERE vd.pseudo_cd = vdr.pseudo_cd
AND vdr.delete_flg = 'N'
AND vdr.manufacturer_cd = '9'
AND vd.non_gm_vehicle_flg = 'Y'
AND vd.remarketing_company_id IN (1)
AND vd.vehicle_id = va.vehicle_id
AND va.cherry_pick_flg = 'N'
AND va.sales_channel_id = sc.sales_channel_id
AND sc.delete_flg = 'N'
AND sc.status_id = 10
AND va.sale_event_id = se.sale_event_id(+)
AND (se.delete_flg = 'N'
OR se.delete_flg IS NULL)
AND (se.remain_orig_channel_flg = 'Y'
OR se.remain_orig_channel_flg IS NULL)
AND (NOT se.status_id IN (102, 101)
OR se.status_id IS NULL)
AND vd.grade_id = vg.grade_id(+)
AND vd.vehicle_status_id = 39
AND vd.vehicle_id = vp.vehicle_id
AND LOWER(vp.price_typ) = 'sc'
AND vp.delete_flg = 'N'
AND vd.bulkpack_reserved_id IS NULL
AND vd.accepted_bulkpack_id IS NULL
AND vd.vehicle_id = bv.vehicle_id(+)
AND bv.bulkpack_id = b.bulkpack_id(+)
ORDER BY stockage DESC
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Given that you're outputing 2'500 rows somewhere (report?) - do you actually NEED it to run any faster?
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman