Re: sql query

I don't think you could use the analytics effectively for this. The COUNT() would be returned for every row in the table - you still have to get a GROUP BY into the query somehow to reduce the number of rows returned.

On the other hand, it is interesting to ask what the effective is of having the TRUNC() in the SELECT list and in the GROUP BY list. Does it get applied in both places, or does Oracle 'remember' that it has worked it out.

Should the query use an inline view to reduce the CPU cost, e.g:

select

    quarter, count(*)
from (

    select /+ no_merge */ trunc(date_col,'Q') quarter     from big_table
)
group by quarter
;

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

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Vladimir M. Zakharychev wrote in message ...


>

>on a sample table with one row for each month of 1996. No idea about

>performance of such query though. Can it be that equivalent query with

>analytic  count(*) over ( partition by trunc(order_date,'Q') ) would do

>better on 100m rows?

>

>--

>Vladimir Zakharychev (bob_at_dpsp-yes.com)

http://www.dpsp-yes.com