Re: Will the function in the GROUP BY clause be calculated?

QiHua,

    Consider a GROUP BY as distributing the rows among a set of boxes; you only want to keep the boxes that contain more than two rows. If assigning one row to one box depends on the result of one function, then you have to compute the function for each and every row, otherwise you don't know where to store it. I therefore means that you have to compute your function one million times simply to know where your row goes. However, you DON'T need to compute it 200,000 times more - once your rows are dealt, you know, by definition, the result of the function for each box that contain them.

I hope it is clear,

Stéphane Faroult

qihua wu wrote:

> Please help me with this statement:
> select *complex_fun(col_a)* from table group by *complex_fun(col_a)*
> having count(*)>2
>
> For every row in the table, complex_fun(col_a) will be calculated when
> oracle scans the table, so will it calculate the function again when
> oracle encounters the GROUP BY clause? Or it just use the result of
> calculations in the SELECT clause?
>
> Support there are 1 million rows in the table, and the result set has
> 0.2 million. So will oracle calculate the function 1 million times or
> 1+0.2=1.2 million times?
>
> Thanks
> Qihua
>
>
> ------------------------------------------------------------------------
> Sneak preview the all-new Yahoo.com
> <http://us.rd.yahoo.com/evt=40762/*http://www.yahoo.com/preview>. It's
> not radically different. Just radically better.

--
http://www.freelists.org/webpage/oracle-l