 | |
#41
 
| |||
| |||
|
Re: count(*) ? -
01-17-2008
, 05:03 AM
|
"Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> schreef in bericht news:ee096$478f27b0$524b5c40$17089 (AT) cache3 (DOT) tilbu1.nb.home.nl... shakespeare wrote: "Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> schreef in bericht news:7767f$478cff47$524b5c40$12171 (AT) cache5 (DOT) tilbu1.nb.home.nl... nick wrote: I understand that when you feed the count( ) function an asterisk as an argument it runs slower than if you use a column name as an argument. Can someone tell me why this is so? Thanks Test it - it is not so - who do you believe?!? -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up O yes it is! If you have a sparse column (say col_a), indexed in a very large table, count(col_a) will use the index , where count(*) doesn't (and they will return different values as well). I think you confused this with select (*) and select (1) (which perform the same, although you might find a DBA at your current working place who is convinced that count (8) is faster.....). They ARE not the same though, for count(*) from table in a view will not invalidate a view when a column is added to the table, where count(1) will..... Shakespeare No I was not confused, though have to admit I misread the column name bit. The title had the start of another count(1) (or count(42), for that matter) is better (or worse) that count(*). That myth I wanted to stop right here. As far as the count(col) vs count(*) myth: http://www.oracledba.co.uk/tips/count_speed.htm Of course, assuming you want to know how many entries your table has (so not hanky-panky with nullable columns!) -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up Sorry, that's what I meant too, but I wrote "select" where I should have written "count". And about hanky panky: it's nullable columns that make the difference in count(column) and count(*) (different speed, different results). The "myth" article is about count(1), count(*) and count (rowid) and things like that. Shakespeare Shakespeare As well as Primary Key columns - bottom line: |
http://asktom.oracle.com/pls/asktom/...15 6159920245
--
Regards,
Frank van Bortel
Top-posting in UseNet newsgroups is one way to shut me up
#42
| |||
| |||
|
|
shakespeare wrote: mmm I must correct that, both views become invalid. Have seen examples of the contrary, will look them up and report.... Shakespeare Got it.... in the pythian blogs: http://www.pythian.com/blogs/627/ora...unt-and-count1 and seems to be an 11g issue, does not reproduce in my 10g database. Shakespeare And that case has nothing to do with the counting, but with fine grained object dependency tracking. -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up |
Shakespeare
#43
| |||
| |||
|
|
shakespeare wrote: mmm I must correct that, both views become invalid. Have seen examples of the contrary, will look them up and report.... Shakespeare Got it.... in the pythian blogs: http://www.pythian.com/blogs/627/ora...unt-and-count1 and seems to be an 11g issue, does not reproduce in my 10g database. Shakespeare And that case has nothing to do with the counting, but with fine grained object dependency tracking. -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up |
Shakespeare
#44
| |||
| |||
|
|
shakespeare wrote: mmm I must correct that, both views become invalid. Have seen examples of the contrary, will look them up and report.... Shakespeare Got it.... in the pythian blogs: http://www.pythian.com/blogs/627/ora...unt-and-count1 and seems to be an 11g issue, does not reproduce in my 10g database. Shakespeare And that case has nothing to do with the counting, but with fine grained object dependency tracking. -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up |
Shakespeare
#45
| |||
| |||
|
|
shakespeare wrote: mmm I must correct that, both views become invalid. Have seen examples of the contrary, will look them up and report.... Shakespeare Got it.... in the pythian blogs: http://www.pythian.com/blogs/627/ora...unt-and-count1 and seems to be an 11g issue, does not reproduce in my 10g database. Shakespeare And that case has nothing to do with the counting, but with fine grained object dependency tracking. -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up |
Shakespeare
#46
| |||
| |||
|
|
shakespeare wrote: "Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> schreef in bericht news:ee096$478f27b0$524b5c40$17089 (AT) cache3 (DOT) tilbu1.nb.home.nl... shakespeare wrote: "Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> schreef in bericht news:7767f$478cff47$524b5c40$12171 (AT) cache5 (DOT) tilbu1.nb.home.nl... nick wrote: I understand that when you feed the count( ) function an asterisk as an argument it runs slower than if you use a column name as an argument. Can someone tell me why this is so? Thanks Test it - it is not so - who do you believe?!? -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up O yes it is! If you have a sparse column (say col_a), indexed in a very large table, count(col_a) will use the index , where count(*) doesn't (and they will return different values as well). I think you confused this with select (*) and select (1) (which perform the same, although you might find a DBA at your current working place who is convinced that count (8) is faster.....). They ARE not the same though, for count(*) from table in a view will not invalidate a view when a column is added to the table, where count(1) will..... Shakespeare No I was not confused, though have to admit I misread the column name bit. The title had the start of another count(1) (or count(42), for that matter) is better (or worse) that count(*). That myth I wanted to stop right here. As far as the count(col) vs count(*) myth: http://www.oracledba.co.uk/tips/count_speed.htm Of course, assuming you want to know how many entries your table has (so not hanky-panky with nullable columns!) -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up Sorry, that's what I meant too, but I wrote "select" where I should have written "count". And about hanky panky: it's nullable columns that make the difference in count(column) and count(*) (different speed, different results). The "myth" article is about count(1), count(*) and count (rowid) and things like that. Shakespeare Shakespeare As well as Primary Key columns - bottom line: it does not matter. http://asktom.oracle.com/pls/asktom/...15 6159920245 -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up |
#47
| |||
| |||
|
|
shakespeare wrote: "Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> schreef in bericht news:ee096$478f27b0$524b5c40$17089 (AT) cache3 (DOT) tilbu1.nb.home.nl... shakespeare wrote: "Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> schreef in bericht news:7767f$478cff47$524b5c40$12171 (AT) cache5 (DOT) tilbu1.nb.home.nl... nick wrote: I understand that when you feed the count( ) function an asterisk as an argument it runs slower than if you use a column name as an argument. Can someone tell me why this is so? Thanks Test it - it is not so - who do you believe?!? -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up O yes it is! If you have a sparse column (say col_a), indexed in a very large table, count(col_a) will use the index , where count(*) doesn't (and they will return different values as well). I think you confused this with select (*) and select (1) (which perform the same, although you might find a DBA at your current working place who is convinced that count (8) is faster.....). They ARE not the same though, for count(*) from table in a view will not invalidate a view when a column is added to the table, where count(1) will..... Shakespeare No I was not confused, though have to admit I misread the column name bit. The title had the start of another count(1) (or count(42), for that matter) is better (or worse) that count(*). That myth I wanted to stop right here. As far as the count(col) vs count(*) myth: http://www.oracledba.co.uk/tips/count_speed.htm Of course, assuming you want to know how many entries your table has (so not hanky-panky with nullable columns!) -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up Sorry, that's what I meant too, but I wrote "select" where I should have written "count". And about hanky panky: it's nullable columns that make the difference in count(column) and count(*) (different speed, different results). The "myth" article is about count(1), count(*) and count (rowid) and things like that. Shakespeare Shakespeare As well as Primary Key columns - bottom line: it does not matter. http://asktom.oracle.com/pls/asktom/...15 6159920245 -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up |
#48
| |||
| |||
|
|
shakespeare wrote: "Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> schreef in bericht news:ee096$478f27b0$524b5c40$17089 (AT) cache3 (DOT) tilbu1.nb.home.nl... shakespeare wrote: "Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> schreef in bericht news:7767f$478cff47$524b5c40$12171 (AT) cache5 (DOT) tilbu1.nb.home.nl... nick wrote: I understand that when you feed the count( ) function an asterisk as an argument it runs slower than if you use a column name as an argument. Can someone tell me why this is so? Thanks Test it - it is not so - who do you believe?!? -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up O yes it is! If you have a sparse column (say col_a), indexed in a very large table, count(col_a) will use the index , where count(*) doesn't (and they will return different values as well). I think you confused this with select (*) and select (1) (which perform the same, although you might find a DBA at your current working place who is convinced that count (8) is faster.....). They ARE not the same though, for count(*) from table in a view will not invalidate a view when a column is added to the table, where count(1) will..... Shakespeare No I was not confused, though have to admit I misread the column name bit. The title had the start of another count(1) (or count(42), for that matter) is better (or worse) that count(*). That myth I wanted to stop right here. As far as the count(col) vs count(*) myth: http://www.oracledba.co.uk/tips/count_speed.htm Of course, assuming you want to know how many entries your table has (so not hanky-panky with nullable columns!) -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up Sorry, that's what I meant too, but I wrote "select" where I should have written "count". And about hanky panky: it's nullable columns that make the difference in count(column) and count(*) (different speed, different results). The "myth" article is about count(1), count(*) and count (rowid) and things like that. Shakespeare Shakespeare As well as Primary Key columns - bottom line: it does not matter. http://asktom.oracle.com/pls/asktom/...15 6159920245 -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up |
#49
| |||
| |||
|
|
shakespeare wrote: "Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> schreef in bericht news:ee096$478f27b0$524b5c40$17089 (AT) cache3 (DOT) tilbu1.nb.home.nl... shakespeare wrote: "Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> schreef in bericht news:7767f$478cff47$524b5c40$12171 (AT) cache5 (DOT) tilbu1.nb.home.nl... nick wrote: I understand that when you feed the count( ) function an asterisk as an argument it runs slower than if you use a column name as an argument. Can someone tell me why this is so? Thanks Test it - it is not so - who do you believe?!? -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up O yes it is! If you have a sparse column (say col_a), indexed in a very large table, count(col_a) will use the index , where count(*) doesn't (and they will return different values as well). I think you confused this with select (*) and select (1) (which perform the same, although you might find a DBA at your current working place who is convinced that count (8) is faster.....). They ARE not the same though, for count(*) from table in a view will not invalidate a view when a column is added to the table, where count(1) will..... Shakespeare No I was not confused, though have to admit I misread the column name bit. The title had the start of another count(1) (or count(42), for that matter) is better (or worse) that count(*). That myth I wanted to stop right here. As far as the count(col) vs count(*) myth: http://www.oracledba.co.uk/tips/count_speed.htm Of course, assuming you want to know how many entries your table has (so not hanky-panky with nullable columns!) -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up Sorry, that's what I meant too, but I wrote "select" where I should have written "count". And about hanky panky: it's nullable columns that make the difference in count(column) and count(*) (different speed, different results). The "myth" article is about count(1), count(*) and count (rowid) and things like that. Shakespeare Shakespeare As well as Primary Key columns - bottom line: it does not matter. http://asktom.oracle.com/pls/asktom/...15 6159920245 -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.