Jeff,
First point is to look at the from clause and compare it to the where
clause. In your case the driving table is table C but the first portion of the
where clause to be evaluated is on table A. On top of that table B is totally
useless since you've no useful reference to it. Try re-writing it as:
select A.value 1, C.value2, A.value3
from Table C, Table A
where C.id = A.id
and (A.ZIPS like ('%54016%')
etc...
Dick Goulet
____________________Reply Separator____________________
Author: "Jeff Wiegard" <[EMAIL PROTECTED]>
Date: 10/31/2001 9:10 AM
I've been asked to review some troublesome SQL queries, and since I
don't know what to do with this one, I thought I'd see what you all
can come up with.
They are doing the following:
select A.value 1, C.value2, A.value3
from Table A, Table B, Table C
where A.id = B.id
and B.id = C.id
and (A.ZIPS like ('%54016%')
or A.ZIPS like ( '%54021%')
or A.ZIPS like ( '%54351%')
or A.ZIPS like ( '%54025%')
or A.ZIPS like ( '%54246%')
or A.ZIPS like ( '%54071%')
or A.ZIPS like ( '%54023%')
or A.ZIPS like ( '%54029%')
or A.ZIPS like ( '%54078%')
or A.ZIPS like ( '%54651%')
or A.ZIPS like ( '%54901%')
or A.ZIPS like ( '%55514%'))
The only problem is that the actual data in the ZIPS field could be
in the format of one of the following 3 samples:
55306:50
55358:100 56601:100
56301:25 56304:25 56379:25
The joins are on 400,000 row tables, and it's doing a full table
scan on all three tables because of the first % wildcard in the like
clause. It would be easy to get the values if they were all like the
first format, because I could use 'substr(A.zips,1,5) = '55514'',
but I don't know what to do with the second two samples, where there
is more than one zipcode in the field.
I would like to be able to find the ':' character and subtract 5 to
get the zipcode, but there could be 3 ':' characters corresponding
to 3 zips in one field. Any ideas?
Thanks,
Jeff
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jeff Wiegard
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
