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

How much of that time is CPU time,
how much is I/O wait time. It strikes
me that a lot of the time will be
spent calling the pl/sql function 60 million times per scan.

What much data collapse takes place
in the function sf_year_camp ? Is there
a one to one between inputs and output,
or is there a many to one ? The answer
to that

As a starting point, you could look at what the analytic functions can do for you when applied to a single in-line view - something along the lines of:

select

        branch_code,
        branch_id,
        units,
        sum(units) over ( .. clause for branch_id ..) branch_units,
        sum(units) over ( .. clause for brand_code ..) brand_units,
        sum(units) over () total_units

    (SELECT brand_code, branch_id, SUM(units) units     FROM orders
    WHERE SF_YEAR_CAMP(year,camp) BETWEEN &1 AND &2     GROUP BY branch_id, brand_code) b

You could then consider whether to force your function-based index into action, or whether to create a materialized view on

    year, camp, brand_code, branch_id, sum(units) or

    sf_year_camp(year, camp), brand_code, branch_id, sum(units)

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Dino Hsu wrote in message ...


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

>