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

"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.
>

Obviously we would need to post the explain plan results as evidently with so little background we can't reproduce the behavior. My initial guess would be the use of a function in all where clauses forces a full table scan, at least if that function contains sql, it also forces recursive sql, and that is IMO one of the reasons why it is never going to be efficient.

One note: please don't state you are using subqueries. You are not. You are using inline views. As they are inline views, they could be replaced by real, maybe materialized views.

If you continue to use inline views, you should have one inline view dealing with the where clause, and all the other inline views should select from *that view* as you now will have the where clause only once. First of all I would proceed in that direction, you are now processing the table 4 times instead of 1.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Tue Sep 25 2001 - 12:22:23 CDT