any way to speed up count(*)?
in [Oracle Help]
|
From: mh on 24 Nov 2009 19:22 I've got an application being put together with cake/php. It's pretty nice, but their data pager does this: SELECT COUNT(*) AS COUNT FROM foo f LEFT JOIN bar b ON (f.asset_group_id = b.asset_group_id) WHERE 1 = 1 Any way possible to speed this up? Many TIA! Mark -- Mark Harrison Pixar Animation Studios
From: Charles Hooper on 25 Nov 2009 09:47 On Nov 24, 7:22 pm, m...(a)pixar.com wrote: > I've got an application being put together with cake/php. It's > pretty nice, but their data pager does this: > > SELECT COUNT(*) AS COUNT > FROM foo f > LEFT JOIN bar b > ON (f.asset_group_id = b.asset_group_id) > WHERE 1 = 1 > > Any way possible to speed this up? > > Many TIA! > Mark > > -- > Mark Harrison > Pixar Animation Studios You probably need to determine what the SQL statement is doing to see if it is possible to speed up the execution. Helpful methods: * Generate a 10046 trace at level 8 for the SQL statement - execute a simple SQL statement, such as SELECT SYSDATE FROM DUAL; after the SQL statement to increase the chances of the STAT lines (row source execution plan) being written to the trace file. Review the trace file manually, or use TKPROF. * Add a /*+ GATHER_PLAN_STATISTICS */ hint to the SQL statement, and use DBMS_XPLAN with 'ALLSTATS LAST' specified as the third parameter. If it were a single table, an index on a column with a not NULL constraint (such as a primary key column) could be used to hopefully speed up a COUNT(*) operation, but that might not work in your case due to the table join. What Oracle release are you using? 10.2.0.4? Charles Hooper Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table" IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: Mark D Powell on 25 Nov 2009 11:19 On Nov 24, 7:22 pm, m...(a)pixar.com wrote: > I've got an application being put together with cake/php. It's > pretty nice, but their data pager does this: > > SELECT COUNT(*) AS COUNT > FROM foo f > LEFT JOIN bar b > ON (f.asset_group_id = b.asset_group_id) > WHERE 1 = 1 > > Any way possible to speed this up? > > Many TIA! > Mark > > -- > Mark Harrison > Pixar Animation Studios Mark, you tune a select count like you tune any other SQL statement. Start with the explain plan. Here is an article on how Oracle performs select count. Is there a good way of counting the number of rows in a table ? http://www.jlcomp.demon.co.uk/faq/count_rows.html The best optimization is not to perform the count. That is, if you are going to fetch the data anyway just go ahead and fetch it if possible. If you just need to know if a row exists but do not truely need to know how many you can use either where rownum = 1 or an exists subquery to cut the counting off when a row is found. That is, you get a count of 1 for a hit and zero where there are not hits (rows matching query criteria). In you your example why do you have where 1 = 1 ? Kind of unnecessary isn't it. HTH -- Mark D Powell --
From: Robert Klemme on 25 Nov 2009 12:58 On 25.11.2009 17:19, Mark D Powell wrote: > On Nov 24, 7:22 pm, m...(a)pixar.com wrote: >> I've got an application being put together with cake/php. It's >> pretty nice, but their data pager does this: >> >> SELECT COUNT(*) AS COUNT >> FROM foo f >> LEFT JOIN bar b >> ON (f.asset_group_id = b.asset_group_id) >> WHERE 1 = 1 >> >> Any way possible to speed this up? >> >> Many TIA! >> Mark >> >> -- >> Mark Harrison >> Pixar Animation Studios > > Mark, you tune a select count like you tune any other SQL statement. > Start with the explain plan. Here is an article on how Oracle > performs select count. > > Is there a good way of counting the number of rows in a table ? > http://www.jlcomp.demon.co.uk/faq/count_rows.html > > The best optimization is not to perform the count. That is, if you > are going to fetch the data anyway just go ahead and fetch it if > possible. If you just need to know if a row exists but do not truely > need to know how many you can use either where rownum = 1 or an exists > subquery to cut the counting off when a row is found. That is, you > get a count of 1 for a hit and zero where there are not hits (rows > matching query criteria). > > In you your example why do you have where 1 = 1 ? Kind of unnecessary > isn't it. For the count the left join might be unnecessary as well. This would be the case if there would be at most one b.asset_group_id per f.asset_group_id. Am I missing something? Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
From: joel garry on 25 Nov 2009 13:27
On Nov 25, 8:19 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > On Nov 24, 7:22 pm, m...(a)pixar.com wrote: > > > In you your example why do you have where 1 = 1 ? Kind of unnecessary > isn't it. I have an example pinned up on my cube of an OCI generated code that has 48 1=1 statements in it. It would be even more unnecessary for me to try to get rid of them. :-) I'm sure there must be more somewhere in this kind of code, that one just happened to catch my eye in EM one day. Doesn't seem to bother the optimizer at all. jg -- @home.com is bogus. Death of wikipedia, news at 11. http://news.cnet.com/8301-1023_3-10403467-93.html |