Re: How to tune a SQL with 4 sbu-queries?

hi Dino

As Sybrand said, post your explain plan first. A few points to note (Corrections welcome) which could change your sql to run faster

1.<<. I have added a function-based index to help the SF_YEAR_CAMP>> Dino, since you have bind variables in the where clause, this index may not be very helpful. bind varibles are useful for repetetive operations to avoid compiling the sql again and again. Changing the bind variables to literal values could help a lot. The oracle optimizer will have better information if you give it literal values.

2. If Step 1 is not useful to you, Consider adding a composite index to branch_id,brand_code. Your database size might increase. You seem to have a group by clause GROUP BY on branch_id,brand_code and also in the where clause

 Good luck and let us know how you optimized it so we can learn more too. !!!

Dino Hsu <dino1.nospam_at_ms1.hinet.net> wrote in message news:<vbu0rtk2r9hkusee848vt15322vqpbmrk2_at_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 Wed Sep 26 2001 - 00:19:44 CDT