Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: intermittent commit on insert ?
On 5 Jun 2002, galenboyer_at_hotpop.com wrote:
> On Wed, 5 Jun 2002, stephen.bell_at_cgi.ca wrote:
> HERE'S THE AUTHOR:
>
> About the author:
>
> Prashant Sarode is currently working with Syntel India Ltd as a
> Technical Analyst. Syntel is a Global Technlogy Company
>
> that is adept at developing and managing the latest advanced
> technologies such as CORBA, J2EE, .NET, XML, WAP, etc. Prashant's
> skill sets include Java, EJB, C, C++, Coldfusion, VB 6.0, CGI
> scripts in C on the Linux platform, MySQL, and Oracle/ Developer
> 2000. Prashant can be reached at prashuss_at_hotmail.com.
Here's another brilliant thought!
14.Use WHERE in Place of HAVING
Avoid including a HAVING clause in SELECT statements. The HAVING clause filters selected rows only after all rows have been fetched. This could include sorting, summing, etc. Restricting rows via the WHERE clause, rather than the HAVING clause, helps reduce these overheads. For example:
Least Efficient :
SELECT REGION, AVG(LOC_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION != ž±SYDNEYž² AND REGION != ž±PERTHž² Most Efficient : SELECT REGION, AVG(LOC_SIZE) FROM LOCATION GROUP BY REGION WHERE REGION != ž±SYDNEYž² AND REGION != ž±PERTHž²
Of course you don't use having in the case he talks about! You use having when you need to further restrict on the results of a group by. But, some people will read this and decide to never use having, when it is probably one of the more underused features of SQL, IMHO (I went at least 4 years in databases until I was introduced to having.) I use having all the time. Here's the type of query I run daily, many times.
select fld1,fld2,...,fldn,count(*)
from table
group by fld1,fld2,...,fldn
having count(*) > some_number;
I use it so much I have it bound to an interactive abbreviation, "scgh"
I guess I should avoid having? How the flip do people get to publish such bunk?
-
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.
Received on Wed Jun 05 2002 - 23:37:07 CDT