On Jan 14, 8:37 pm, Rob Wolfe <wolfe....@gmail.com> wrote: > 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!- Hide quoted text - > > - Show quoted text -
waiting for the "fat man" to speak? :D David Fitzjarrell
-- 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
