Join Order

Hi, Here's my question: I have one table (A) indexed (unique) on two columns in the given order: fld1, fld2 I have another table (B) indexed on two columns (NON-unique) in the given order: fld1, fld2 I have an application that defines a join between these two tables as: A.fld2=B.fld2 and A.fld1=B.fld1 Will query performance be affected in this case? Does it matter if the join is set as: A.fld1=B.fld1 and A.fld2=B.fld2 (the results are the same but will performance be different?) Thank you, Tony

There sohuld be no difference. Update statistics is always your friend. If query performs slowly generate a explain of it and given the case use hints to the optimizer. J. -----Original Message----- From: "Demeis, Tony" <Tony.Demeis@moh.gov.on.ca> To: ids@iiug.org Date: Thu, 1 Sep 2005 16:40:36 -0400 (EDT) Subject: Join Order [5687] Hi, Here's my question: I have one table (A) indexed (unique) on two columns in the given order: fld1, fld2 I have another table (B) indexed on two columns (NON-unique) in the given order: fld1, fld2 I have an application that defines a join between these two tables as: A.fld2=B.fld2 and A.fld1=B.fld1 Will query performance be affected in this case? Does it matter if the join is set as: A.fld1=B.fld1 and A.fld2=B.fld2 (the results are the same but will performance be different?) Thank you, Tony Jean Sagi jeansagi@myrealbox.com jeansagi@gmail.com

The query should use the indexes on both the tables. In your case, the order of the where clause doesn't matter. Run the query and have "SET EXPLAIN ON;" as the first statement. Look at the sqexplain.out file. It would show you a cost and whether it used the indexes. Thank you, Kannan Thirugnanam -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Demeis, Tony Sent: Thursday, September 01, 2005 4:41 PM To: ids@iiug.org Subject: Join Order [5687] Hi, Here's my question: I have one table (A) indexed (unique) on two columns in the given order: fld1, fld2 I have another table (B) indexed on two columns (NON-unique) in the given order: fld1, fld2 I have an application that defines a join between these two tables as: A.fld2=B.fld2 and A.fld1=B.fld1 Will query performance be affected in this case? Does it matter if the join is set as: A.fld1=B.fld1 and A.fld2=B.fld2 (the results are the same but will performance be different?) Thank you, Tony ***** Jackson Hewitt Email Disclaimer ***** The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. The sender's business entity is not liable for any loss or damage arising in any way from this message or its attachments. Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply email.

To see the performance of Both the Query You can use the set explain on Utility of Informix which would create a file whose name is sqexplain.out And which gives the information regarding the process time of Query "Demeis, Tony" <Tony.Demeis@moh.gov.on.ca> wrote: Hi, Here's my question: I have one table (A) indexed (unique) on two columns in the given order: fld1, fld2 I have another table (B) indexed on two columns (NON-unique) in the given order: fld1, fld2 I have an application that defines a join between these two tables as: A.fld2=B.fld2 and A.fld1=B.fld1 Will query performance be affected in this case? Does it matter if the join is set as: A.fld1=B.fld1 and A.fld2=B.fld2 (the results are the same but will performance be different?) Thank you, Tony __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

On 9/1/05, prateek jain <prateek_mjv@yahoo.com> wrote: > > To see the performance of Both the Query > You can use the set explain on Utility of Informix > which would create a file whose name is sqexplain.out > And which gives the information regarding the process time of Query > > "Demeis, Tony" <Tony.Demeis@moh.gov.on.ca> wrote: > Here's my question: > > I have one table (A) indexed (unique) on two columns in the given order: > fld1, fld2 > > I have another table (B) indexed on two columns (NON-unique) in the given > order: fld1, fld2 > > I have an application that defines a join between these two tables as: > A.fld2=B.fld2 and A.fld1=B.fld1 > > > Will query performance be affected in this case? > Does it matter if the join is set as: > A.fld1=B.fld1 and A.fld2=B.fld2 > > > (the results are the same but will performance be different?) > There should be no difference between the query plans. Once upon a very long time ago, the heuristic optimizer might have been affected (though even it should have dealt with this); the cost based optimizer should not ever have been affected by the ordering of clauses in the WHERE clause. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/