On Jan 14, 4:15 pm, ddf <orat...@msn.com> wrote: > On Jan 14, 12:11 pm, Andrej Hopko <ado.ho...@gmail.com> wrote: > > > > > > > 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<ado.ho...@gmail.com> 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- Hide quoted text - > > > - Show quoted text - > > The web can be wrong. COUNT(*) and COUNT(1) are identical, as proven > time after time after time by Tom Kyte and others. COUNT(1) is not > more efficient because it repeatedly produces the exact same plan as > COUNT(*): > > SQL> select count(*) > 2 from dbaobjs; > > COUNT > (*) > ---------- > > 69226 > > Execution Plan > ---------------------------------------------------------- > Plan hash value: > 708967174 > > ---------------------------------------------------------------------- > | Id | Operation | Name | Rows | Cost (%CPU)| Time > | > ---------------------------------------------------------------------- > | 0 | SELECT STATEMENT | | 1 | 286 (1)| 00:00:04 > | > | 1 | SORT AGGREGATE | | 1 | | > | > | 2 | TABLE ACCESS FULL| DBAOBJS | 69226 | 286 (1)| 00:00:04 > | > ---------------------------------------------------------------------- > > Statistics > ---------------------------------------------------------- > 287 recursive > calls > 0 db block > gets > 1067 consistent > gets > 1024 physical > reads > 0 redo > size > 420 bytes sent via SQL*Net to > client > 416 bytes received via SQL*Net from > client > 2 SQL*Net roundtrips to/from > client > 6 sorts > (memory) > 0 sorts > (disk) > 1 rows > processed > > SQL> alter system flush shared_pool; > > System altered. > > SQL> select count(1) > 2 from dbaobjs > 3 / > > COUNT > (1) > ---------- > > 69226 > > Execution Plan > ---------------------------------------------------------- > Plan hash value: > 708967174 > > ---------------------------------------------------------------------- > | Id | Operation | Name | Rows | Cost (%CPU)| Time > | > ---------------------------------------------------------------------- > | 0 | SELECT STATEMENT | | 1 | 286 (1)| 00:00:04 > | > | 1 | SORT AGGREGATE | | 1 | | > | > | 2 | TABLE ACCESS FULL| DBAOBJS | 69226 | 286 (1)| 00:00:04 > | > ---------------------------------------------------------------------- > > Statistics > ---------------------------------------------------------- > 293 recursive > calls > 0 db block > gets > 1067 consistent > gets > 0 physical > reads > 0 redo > size > 420 bytes sent via SQL*Net to > client > 416 bytes received via SQL*Net from > client > 2 SQL*Net roundtrips to/from > client > 6 sorts > (memory) > 0 sorts > (disk) > 1 rows > processed > > SQL> > > Can you prove that COUNT(1) is 'better'? My tests show otherwise. > People think that COUNT(1) is better because they think that Oracle > expands the * to include all columns yet I see no proof of that. > COUNT > (*) does not execute the same way as SELECT * as Oracle 'expands' the > * in the COUNT() function to mean all rows, not all columns. So > rather than have Oracle return a 1 for every non-null row in table X, > then execute a running count of the 1's returned (which Oracle would > do if the developers hadn't decided to optimize COUNT(*) and simply > and silently rewrite COUNT(1) to COUNT(*) behind the scenes) Oracle > chose instead to optimize the most common query for count (COUNT(*)) > and convert COUNT(1) on the fly to the optimized version. Even COUNT > (ROWID) isn't any more efficient: > > SQL> select count(*) > 2 from test; > > COUNT > (*) > ---------- > > 11046 > > Elapsed: 00:00:00.07 > > Execution Plan > ---------------------------------------------------------- > Plan hash value: > 3467505462 > > ------------------------------------------------------------------- > | Id | Operation | Name | Rows | Cost (%CPU)| Time > | > ------------------------------------------------------------------- > | 0 | SELECT STATEMENT | | 1 | 13 (8)| 00:00:01 > | > | 1 | SORT AGGREGATE | | 1 | | > | > | 2 | TABLE ACCESS FULL| TEST | 11046 | 13 (8)| 00:00:01 > | > ------------------------------------------------------------------- > > Note > ----- > - dynamic sampling used for this > statement > > Statistics > ---------------------------------------------------------- > 5 recursive > calls > 0 db block > gets > 91 consistent > gets > 42 physical > reads > 0 redo > size > 210 bytes sent via SQL*Net to > client > 246 bytes received via SQL*Net from > client > 2 SQL*Net roundtrips to/from > client > 0 sorts > (memory) > 0 sorts > (disk) > 1 rows > processed > > SQL> > SQL> select count(rowid) > 2 from test; > > COUNT > (ROWID) > ------------ > > 11046 > > Elapsed: 00:00:00.21 > > Execution Plan > ---------------------------------------------------------- > Plan hash value: > 3467505462 > > --------------------------------------------------------------------------- > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| > Time | > --------------------------------------------------------------------------- > | 0 | SELECT STATEMENT | | 1 | 12 | 13 (8)| > 00:00:01 | > | 1 | SORT AGGREGATE | | 1 | 12 | > | | > | 2 | TABLE ACCESS FULL| TEST | 11046 | 129K| 13 (8)| > 00:00:01 | > --------------------------------------------------------------------------- > > Note > ----- > - dynamic sampling used for this > statement > > Statistics > ---------------------------------------------------------- > 27 recursive > calls > 0 db block > gets > 93 consistent > gets > 0 physical > reads > 0 redo > size > 231 bytes sent via SQL*Net to > client > 246 bytes received via SQL*Net from > client > 2 SQL*Net roundtrips to/from > client > 0 sorts > (memory) > 0 sorts > (disk) > 1 rows > processed > > SQL> > > In fact it requires more recursive calls than COUNT(*) than COUNT > (ROWID). > > Since Oracle has optimized COUNT(*) so well why try to poorly re- > invent the wheel? > > David Fitzjarrell- Hide quoted text - > > - Show quoted text -
I was hoping that you would weigh in on this David. Thank you!
-- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
