Informix SQL question
This is a multi-part message in MIME format.
------=_NextPart_000_004F_01BD8F43.26C88C20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I have a simple question. Suppose you have a table whose primary key is =
a composite
key of the columns col1,col2,col3,col4 (primary key is in that order). =
If you do a query to=20
fetch a record by the primary key, does it matter what order you ask for =
the columns=20
in the where clause?
For example, does =20
SELECT * FROM someTable WHERE col4=3D? AND col1=3D? AND
col2=3D? AND col3=3D? =20
get the record as efficiently as the statement
SELECT * FROM someTable WHERE col1=3D? AND col2=3D? AND col3=3D? AND =col4=3D?
I always order the columns in the where clause in the order of the =
primary key or the
index I'd like the optimizer to use. I've noticed in the IBM Visual Age =
for Java that some
of the code generated to retrieve a row by a primary key may mix up the =
order.
Thanks!
Candy
--
Candy McCall
ONLINE Computing, Inc.
(candym@olcinc.com)
------=_NextPart_000_004F_01BD8F43.26C88C20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>
<META content=3Dtext/html;charset=3Diso-8859-1 =
http-equiv=3DContent-Type>
<META content=3D'"MSHTML 4.71.1712.3"' name=3DGENERATOR>
</HEAD>
<BODY bgColor=3D#d8d0c8>
<DIV><FONT color=3D#000000 size=3D2>I have a simple question. =
Suppose you have=20
a table whose primary key is a composite</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2>key of the columns =
col1,col2,col3,col4 (primary=20
key is in that order). If you do a query to </FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2>fetch a record by the </FONT><FONT =
color=3D#000000=20
size=3D2>primary key, does it matter what order you ask for the columns=20
</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2>in the where clause?</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>For example, does =
</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2> SELECT * FROM =
someTable WHERE=20
col4=3D? AND col1=3D? AND</FONT></DIV>
<DIV><FONT color=3D#000000 =
size=3D2> =20
col2=3D? AND col3=3D? </FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2> get the record as efficiently =
as the=20
statement</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2> SELECT * FROM =
someTable WHERE=20
col1=3D? AND col2=3D? AND col3=3D? AND col4=3D?</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>I always order the columns in the =
where clause=20
in the order of the primary key or the</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2>index I'd like the optimizer to =
use. I've=20
noticed in the IBM Visual Age for Java that some</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2>of the code generated to retrieve a =
row by a=20
primary key may mix up the order.</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>Thanks!</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>Candy</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2>--<BR>Candy McCall<BR>ONLINE =
Computing,=20
Inc.<BR>(<A=20
href=3D"mailto:candym@olcinc.com">candym@olcinc.com</A>)</FONT></DIV></BO=
DY></HTML>
------=_NextPart_000_004F_01BD8F43.26C88C20--