Article View: borland.public.interbase.sql
Article #161HAVING or WHERE
From: Hs
Date: Tue, 24 Jun 2008 16:20
Date: Tue, 24 Jun 2008 16:20
38 lines
1994 bytes
1994 bytes
I have two Tables:
CREATE TABLE "BUY"
(
"CENTER_ID" INTEGER NOT NULL,
"ID" INTEGER NOT NULL,
"FARMER_ID" INTEGER NOT NULL,
"FACTORY_ID" INTEGER NOT NULL,
"BUY_WEIGHT" INTEGER,
PRIMARY KEY ("CENTER_ID", "BUY_ID")
);
CREATE TABLE "FARMER"
(
"CENTER_ID" INTEGER NOT NULL,
"ID" INTEGER NOT NULL,
"NAME" VARCHAR(25),
PRIMARY KEY ("CENTER_ID", "ID")
);
I executed two queries:
1) SELECT FARMER.ID,SUM(BUY_WEIGHT) FROM FARMER INNER JOIN BUY ON FARMER.ID=BUY.FARMER_ID
WHERE BUY.FACTORY_ID=1 AND BUY.CENTER_ID=1
GROUP BY FARMER.ID
2)SELECT FARMER.ID,SUM(BUY_WEIGHT) FROM FARMER INNER JOIN BUY ON FARMER.ID=BUY.FARMER_ID
WHERE BUY.FACTORY_ID=1
GROUP BY FARMER.ID
HAVING BUY.CENTER_ID=1
for both the result was same but the execution of the first query longs more than 90(s) and of second query less than 1(s) !!
The plan for the first was: PLAN SORT (JOIN (FARMER NATURAL,BUY INDEX (RDB$PRIMARY4)))
and for second was: PLAN SORT (MERGE (SORT (BUY NATURAL),SORT (FARMER NATURAL)))
What is happening? Generaly what is the difference in resuls and in background between calling HAVING or WHERE for non aggregation fields?
Thanks.
Technical Details
Message-ID:
<48611ee3$1@newsgroups.borland.com>Path:
arc1-ca.pugleaf.net!g2news1.google.com!news1.google.com!border1.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!newspeer1.nac.net!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mailShow Full Headers
Complete Article Headers
X-Google-Thread: 114a0e,8b9906a31c39f622,start X-Google-Attributes: gid114a0e,domainid0,public,usenet X-Google-Language: ENGLISH,ASCII-7-bit From: "Hs" <prg_stu@yahoo.com> Sender: "Hs" <prg_stu@yahoo.com> Reply-To: "Hs" <prg_stu@yahoo.com> Subject: HAVING or WHERE Newsgroups: borland.public.interbase.sql X-User-Info: 80.191.189.114 NNTP-Posting-Host: 207.105.83.66 Message-ID: <48611ee3$1@newsgroups.borland.com> Date: 24 Jun 2008 09:20:51 -0700 X-Trace: newsgroups.borland.com 1214324451 207.105.83.66 (24 Jun 2008 09:20:51 -0700) Lines: 38 Path: arc1-ca.pugleaf.net!g2news1.google.com!news1.google.com!border1.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!newspeer1.nac.net!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail X-Original-Bytes: 1821