Re: Messy Messy SQL

Thanks - I was thinking about breaking it out as well, but thought there might be some trick out there from SQL For Smarties or something.

>>> yosi_at_comhill.com 10/31/01 11:37AM >>>
Jeff,

That's a pretty silly way to store zip codes. Could you create a zipcode-id table, where you break out each zip and its id? Then search that table and join back to table a?

Or, if you're at 8i, and if you only get up to a really small number
(like the 3 you display) of zips in a combined zip field, you could
create the first index on the zip field, and two additional function-
based indexes on the second and third zip codes. Then instead of using like '%99999%', you could use the function with which you created the index, like where substr(instr(zip_field, ' ')+1) like '99999%'.

Some thoughts, hope they help.

God bless America.

Yosi

Jeff Wiegard wrote:

> 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: Yosi Greenfield
  INET: yosi_at_comhill.com 

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: ListGuru_at_fatcity.com (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: Jeff Wiegard
  INET: JWIEGARD_at_ngwmail.des.state.mn.us

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: ListGuru_at_fatcity.com (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).