I'm wondering which of the following should run faster and whether
there is a 3rd and better way to get the results I'm after. Basically
I'm after a chunk of data from the us, pl, oi and si tables. The 1st
way just grabs all the data and filters at the end, the 2nd way filters
the data before the union at the cost of repetitive code.
Tests indicate the 2nd query has a slight performance advantage but I'm
wondering if there may be scaling issues.
- Query 1 ***
select msa.customer_name, msa.servnum, costtype, a.servnum,
a.description, qty, gst_exclud, gst, charge
from mv_service_allocations msa
JOIN
(
select 'Service & Equipment' costtype, mid, servnum, si.trans_desc as
description, nvl(si.qty, 1) as qty, si.GST_EXCLUD, si.gst, si.charge
from si
union all
select 'Usage' costtype, mid, servnum, cat.cat_desc as description,
nvl(us.qty, 1) as qty, us.GST_EXCLUD, us.gst, us.charge
FROM us JOIN cat ON us.cat_ref = cat.cat_ref
UNION ALL
SELECT 'Plans' costtype, mid, servnum, pl.trans_desc as description,
nvl(pl.qty, 1) as qty, pl.GST_EXCLUD, pl.gst, pl.charge
FROM pl
UNION ALL
SELECT 'Other Charges & Credits' costtype, mid, servnum as description,
oi.oi_desc, nvl(oi.qty, 1) as qty, oi.GST_EXCLUD, oi.gst, oi.charge
FROM oi
) a
on a.servnum = msa.servnum
join dt on a.mid = dt.mid and dt.folder_fk = 347
where msa.ancestor_pk = 29863
- Query 2 ***
select msa.customer_name, msa.servnum, costtype, a.servnum,
a.description, qty, gst_exclud, gst, charge
from mv_service_allocations msa
JOIN
(
select 'Service & Equipment' costtype, si.mid, si.servnum,
si.trans_desc as description, nvl(si.qty, 1) as qty, si.GST_EXCLUD,
si.gst, si.charge
from si
join dt on si.mid = dt.mid and dt.folder_fk = 347
union all
select 'Usage' costtype, us.mid, us.servnum, cat.cat_desc as
description, nvl(us.qty, 1) as qty, us.GST_EXCLUD, us.gst, us.charge
FROM us JOIN cat ON us.cat_ref = cat.cat_ref
join dt on us.mid = dt.mid and dt.folder_fk = 347
UNION ALL
SELECT 'Plans' costtype, pl.mid, pl.servnum, pl.trans_desc as
description, nvl(pl.qty, 1) as qty, pl.GST_EXCLUD, pl.gst, pl.charge
FROM pl
join dt on pl.mid = dt.mid and dt.folder_fk = 347
UNION ALL
SELECT 'Other Charges & Credits' costtype, oi.mid, oi.servnum as
description, oi.oi_desc, nvl(oi.qty, 1) as qty, oi.GST_EXCLUD, oi.gst,
oi.charge
FROM oi
join dt on oi.mid = dt.mid and dt.folder_fk = 347
) a
on a.servnum = msa.servnum
where msa.ancestor_pk = 29863
Cheers,
Pete
Received on Tue Feb 15 2005 - 22:55:38 CST
