RE: Performance off "count(*)"
Date: Sun, 20 Jul 2008 17:21:20 +0800
Yep, speaking hypothetically, a pure plain count(*) on table without any where condtions on that table could be optimized *if* the row headers were in row directory.
However whenever there is a WHERE condition on any of the columns of the table, Oracle would still need to go through row pieces and compare against the actual fields (having the row header flag in row directory could probably help a little in cases with lots of deleted and/or chained rows... as the row header flag is likely on the same cache line where the rowdirectory entry, thus less memory line fetches and stalls would happen. but this is probably little win compared to physical IO wait times what you likely have when doing count(*) across large tables).
Also (as Chris mentioned), looks like the count(*) optimization is that Oracle can skip the row data. Which it can never do for count(col) as this is semantically different from count(*) - NULL columns aren't counted when using count(col):
SQL> create table t as select cast(null as varchar2(1)) a from dual;
SQL> select count(*) from t;
SQL> select count(a) from t;
-- Regards, Tanel Poder http://blog.tanelpoder.comReceived on Sun Jul 20 2008 - 04:21:20 CDT
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
> Sent: Sunday, July 20, 2008 15:42
> To: oracle-l_at_freelists.org
> Subject: Re: Performance off "count(*)"
> It was only when reading Greg's note that the real importance
> of the distinction between the 'block' header and 'row'
> header struck me. If Oracle moved the flag byte from the row
> header into the "row index" in the block header, then the
> count(*) could be highly optimized - but that's probably far
> too much risky coding effort for little reward.
> But the 'optimization' of count(col) to count(*) could simply
> be that count(*) can bound back and fore between the "row
> index" and the row header it's pointing at, while count(col)
> has to walk along each row to find the column and check it.
> Jonathan Lewis
> Author: Cost Based Oracle: Fundamentals
> The Co-operative Oracle Users' FAQ