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

Jonathan,

Reading your post, it strikes me with: "gosh, I should have bought your book." Are these skills included in your book?

On Wed, 26 Sep 2001 20:56:10 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

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

Do you mean to explain plan first?

>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
>from
> (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)
This saves me one middle layer of materialized view.

Dino Received on Wed Sep 26 2001 - 19:42:23 CDT