Rob, thanks for testing this problem =)
COUNT(1) is often on the web as better way and as I wrote before, I
thought it was optimized, because neither I did noticed * different
(cause I have no DWH ;-) )
hoppo
On 14.1.2010 18:05, Rob Wolfe wrote:
On Jan 14, 11:07 am, Andrej Hopko<[email protected]> wrote:
By my knowledge brackets of COUNT statement specify which data should be
projected and then counted
so if there is COLUMN then that column is projected (projection as
database operation) and counted
* projects all columns
and COUNT(1) projects only 1 as value for each row that is OK with WHERE
conditions
so in bad optimized database engine is COUNT(1) fastest choice
but by my short testing same problem few months ago oracle has this one
well optimized
still I use COUNT(1) as best practice because it may only do good, no
bad (in case you don't need to count distinct values of column, but all
selected rows)
hoppo
On 14.1.2010 16:08, Midana wrote:
Hello,
who knows the diference between this selects.
1 - SELECT COUNT(*) FROM TABLE
2 - SELECT COUNT(1) FROM TABLE
3 - SELECT COUNT(COLUMN) FROM TABLE
Midana Sana- Hide quoted text -
- Show quoted text -
Just out of morbid curiousity i ran the three statements on one of my
data warehouse tables that has about 130 million rows (i think that
qualifies as reasonably large). Bearing in mind that it is a
partitioned table I got back exactly the same execution plans for all
three selects (i used a uniquely indexed column for the third just in
case that helped)
The results I got were
1) 618 seconds -- my guess is that it counts rowids and doesn't
actually do the projection because if the optimizer isnt smart enough
to pull off something as elementary as that then it really sucks
2) 660 seconds
3) 633 seconds
so less than 10% difference between the fastest and slowest in this
case. Is that worth thinking about? To me it isnt because it is well
within the performance range that can happen due to the load on the
machine.
Rob
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
