Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> How to tune a SQL with 4 sbu-queries?
Dear all,
I have a SQL something like this: (branch_id is for branch stores, brand_code is for product brands)
SELECT branch_id, brand_code, branch_brand_units, branch_units,
brand_units, units
FROM
(SELECT branch_id, brand_code, SUM(units) AS branch_brand_units
FROM orders
WHERE SF_YEAR_CAMP(year,camp) BETWEEN &1 AND &2
GROUP BY branch_id, brand_code) a,
(SELECT branch_id, SUM(units) AS branch_units
FROM orders
WHERE SF_YEAR_CAMP(year,camp) BETWEEN &1 AND &2
GROUP BY branch_id) b,
(SELECT brand_code, SUM(units) AS brand_units
FROM orders
WHERE SF_YEAR_CAMP(year,camp) BETWEEN &1 AND &2
GROUP BY brand_code) c,
(SELECT SUM(units) AS units
FROM orders
WHERE SF_YEAR_CAMP(year,camp) BETWEEN &1 AND &2) d
WHERE
a.branch_id = b.branch_id AND
a.brand_code = c.brand_code;
The table orders is a fact table with 60 million records for 3 years of sales, and this SQL is normally run with a 1-year range (1 year = 18 camp or campaigns), i.e., against 20 million records. I have added a function-based index to help the SF_YEAR_CAMP filtering, but it doesn't help to get an acceptable performance. This SQL executes 4 table scans (since 4 sub-queries), each of them runs for 2 hours, giving 8 hours as a whole. Any way to improve the performance of this SQL? The table orders is seldom updated, the sales from the previous working day is loaded every night. I am considering to store this SQL as a materialized view, I guess it'll still take 8 hours when it is first created, but it'll be quickly refreshed after the daily load, but I am not sure. Furthermore, I wonder whether it's better to have 5 views (4 views for each sub-query and one view to join the 4) instead of one. Any comments?
Thanks in advance.
Dino
P.S. maybe you'll suggest to create a cube for easy manipulation, but this report is used to generate weight numbers to be integrated into an auto-replenishment application. Received on Tue Sep 25 2001 - 08:12:03 CDT