Re: help with group by

G Quesnel wrote:
> One small note on your first point ...
> I agree that doing a count on a numeric column can lead to
> misunderstanding, but take the case of doing a count on a text column
> or date column, then it becomes very clear. My point is that
> count(column) does have something to do with the column value, it
will
> only count non null value... and I have come across real business
case
> where we would do both count(column), count(*) in the same select
> statement (but we didn't care about distinct values).
>

And so I learn something new...

SQL> SELECT COUNT(*), COUNT(c1), COUNT(DISTINCT c1) FROM 2 (

3      SELECT NULL c1 FROM dual UNION ALL
4      SELECT 'a' c1 FROM dual UNION ALL
5      SELECT 'a' c1 FROM dual

COUNT(*) COUNT(C1) COUNT(DISTINCTC1)

--------- --------- -----------------
3         2                 1

Okay, scratch that first point, which comes from my having run across many examples where people selected COUNT(<column name>) and have meant to select COUNT(DISTINCT <column name>). Or really irritatingly, they have selected COUNT(1), which will always return the same as COUNT(*), but obscures the fact that you are, in fact, counting the number of rows.

> Question on your second option:
> Do you know if there is a "significant" cost incured with the inline
> view. If people had a million rows and since we don't do any row
> triming, would there be a sub table built with 2 columns and a
million
> rows ? (I still think it's good, I am just curioius)

There shouldn't be any cost, "significant" or otherwise. Certainly, the database wouldn't *need* to materialize the inline query before grouping. In a test script, the execution plan of either approach was identical, both for CBO and RBO. Admittedly, I can use a NO_MERGE hint to *force* Oracle to materialize the inlined query before the group by. However, the CBO would only do this in the rare case that it is in the best interest of overall performance. Received on Fri Jan 14 2005 - 11:06:52 CST