Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> How to tune a SQL with 4 sbu-queries?

How to tune a SQL with 4 sbu-queries?

From: Dino Hsu <dino1.nospam_at_ms1.hinet.net>
Date: Tue, 25 Sep 2001 21:12:03 +0800
Message-ID: <vbu0rtk2r9hkusee848vt15322vqpbmrk2@4ax.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US