RE: Performance off "count(*)"
- From: Tanel Poder <tanel.poder.003@xxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- 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;
Table created.
SQL> select count(*) from t;
COUNT(*)
----------
1
SQL> select count(a) from t;
COUNT(A)
----------
0
--
Regards,
Tanel Poder
http://blog.tanelpoder.com
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
> Sent: Sunday, July 20, 2008 15:42
> To: oracle-l@xxxxxxxxxxxxx
> 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.
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
