Randy Harris wrote:
> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1115649252.689168_at_yasure...
>
>>vegas_girlie wrote:
>>
>>
>>>hi,
>>>i am trying to extract data out from an oracle server and am having a
>>>tough time with the most basic sql statement. when i run
>>>
>>>
>>>SELECT distinct tcof.EML_ADDRESS from
>>>test.EML a,
>>>eml_addr_t b
>>>where rtrim(a.EML_ADDRESS) = a.EML_ADDR
>>>
>>>
>>>i get returned values. however when i run
>>>
>>>
>>>SELECT distinct a.EML_ADDRESS from
>>>test.eml a,
>>>eml_addr_t b
>>>where rtrim(a.EML_ADDRESS) <> b.EML_ADDR
>>>
>>>
>>>the query runs forever. i also tried the != and NOT IN and they all
>>>run for hours. there are only about 6500 records in the a table and
>>>the b table contains 30 million records. is it because i dont have any
>>>indexes on table a?
>>
>>You have created a classic Cartesian join. Consider this:
>>
>>Take row 1 in table a. It does not match almost every row in b
>>Take row 2 in table a. It does not match almost every row in b
>>Take row 3 in table a. It does not match almost every row in b
>>
>>Repeat this step for every row in table a and the result will be
>>the same. Your assumption that <> is the reverse of = is not valid.
>>
>>State the business problem and there is a simple solution: This isn't it.
>>--
>>Daniel A. Morgan
>>University of Washington
>>damorgan_at_x.washington.edu
>>(replace 'x' with 'u' to respond)
>
>
> Let's see... 6500 X 30 Million? That's a whole lot of Cartesian.
>
> Isn't the "customary" method of finding unmatched records to do an outer
> join then look for Nulls on the non preserved side?
How about NOT EXISTS?
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Mon May 09 2005 - 10:29:05 CDT
