It looks as if it would help - even ignoring the
bit about the partition clause, as most of
the performance benefit may come in the
GROUP BY of the in-line
--
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 ...
>On Wed, 26 Sep 2001 20:56:10 +0100, "Jonathan Lewis"
><jonathan_at_jlcomp.demon.co.uk> wrote:
>
>>snip
>>
>>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)
>
>In the SQL reference, there is a note about the query partition
>clause:
>
><quote begin>
>Note: If the objects being queried have the parallel attribute, and if
>you specify an analytic function with the query_partition_clause, then
>the function computations are parallelized as well.
><quote end>
>
>Do you think it is useful to set parallel attribute for the big table
>orders?
>
>Dino
>
Received on Thu Sep 27 2001 - 15:04:43 CDT
