Re: Help! Long data types

From: Rudy Zung <zungr_at_prograph-inc.com>
Date: Thu, 18 Mar 1999 19:32:31 -0500
Message-ID: <7cs5tq$bls$1@mailhost.prograph-inc.com>

Arthur Merar wrote in message <36f19370.67093756_at_news.chaven.com>...
>
>Hello,
>
>Actually I have two questions.
>
>First, what is it with these long data types? Why can't I do
anything
>with them? I need to compare to fields called NOTES in two different
>tables.....the NOTES field is of LONG data type.....how can I do
this?
>I keep getting errors......
>
>Also, I run this query:
>
>select count(*) from TMP_POHEADER where
>ponum NOT IN (select ponum from POHEADER);
>
>That is a simple query right? There are even indexes on the PONUM
>field. Why then after 3 hours do I not get any results? There are
>only 30,000 records in the tables.....
>
>Any help would be appreciated....
>
>Thanks,
>
>Arthur
>amerar_at_unsu.com

Long data types are special to Oracle, and as you've discovered they don't handle nicely. Try using varchar2(2000) if possible, or under Oracle 8 varchar2(4000). Failing which, maybe ConText option might help; I have no experience with ConText.

The NOT IN operator is very inefficient: for each record in TMP_POHEADER, Oracle has to execute the POHEADER; it might also have to do a full scan of the POHEADER query to guarantee the NOT IN. (I could be wrong on why NOT IN is inefficient, but I am correct in that it is generally a bad idea unless you do something small like NOT IN ('SAT', 'SUN') ). Anyway, try this instead:

   select count(*)
   from

      (select * from TMP_POHEADER
         where PONUM in
            (select PONUM from TMP_POHEADER
              minus
              select PONUM from POHEADER))

Depending on what your tables look like, you probably can get rid of some of the nested subqueries, but the biggie here is the last subquery with the minus in it: that will get you the TMP_POHEADER.PONUMs that don't exist in the POHEADER. The next enclosing scope is to select all those POHEADERs that were found by the minus subquery, then you COUNT(*) all those records. You might be able to just COUNT(*) the minus subquery and not bother with getting the TMP_POHEADER records.

There is an alternate method using outer joins and counting only those records that were selected based on the POHEADER.PONUM(+) being
NULL, but that is left as an exercise to the reader.

...Ru Received on Thu Mar 18 1999 - 18:32:31 CST