Home » SQL & PL/SQL » SQL & PL/SQL » Issue with NOT Exists when No Data
Issue with NOT Exists when No Data [message #197613] Thu, 12 October 2006 01:13 Go to next message
tramanathan
Messages: 3
Registered: October 2006
Junior Member
Hi,

I am facing a peculiar issue. I am trying to use a Not Exists clause on a subquery which used DB Link. The query gives proper output when there are some records, but when the data is such that the result is no row, the query never ends goes on into lock mode and finally throws ORA-01555 or ORA-02049 error (i.e Snap Shot too old or distributed lock wait).

SELECT c.masc_code, c.masc_desc, c.country_code
FROM masc_test c
WHERE NOT EXISTS (SELECT a.masc_cd
FROM mclaims_masc_codes a, mclaims_svl_masc b
WHERE a.masc_cd = b.masc_cd AND a.masc_cd = c.masc_code)

Where mclaims_masc_codes is a synonym for a remote DB.

If there is any data satisfying the above query the output comes out quickly but when there is no matching data the query gets into an infinite loop.

Please help me out in this

Thanks
Re: Issue with NOT Exists when No Data [message #197617 is a reply to message #197613] Thu, 12 October 2006 01:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You'll probably find its not infinite.

Let's make an analogy and see if it helps:

If I give you Stephen King's latest 1000 page blockbuster novel, and ask you to tell me it the word "THE" does not exist in the novel, you will probably report back pretty quickly, because you will find "THE" on the first page.

But what if I asked you to find the word "DISCOMBOBULATE"? How long do you reckon it would take? You'd have to read the entire 1000 pages of course before you could tell me that it does NOT EXIST.

Your SQL is the same. The sub-query searches some big tables in-efficiently. If it finds the target row quickly, it drops out. Otherwise it has to finish the long search.

"So why doesn't it just use an index?" (I hear you say) Because you are doing a join between a local and a remote table. Because of this, Oracle (by default) brings the remote table over to the local server in its entirity and then performs the join/sub-query. Unfortunately, this temporary local version is not indexed, so access on it is a bit slow.

I need to know three things:
- How many rows are in mclaims_masc_codes?
- What about the other tables?
- Obtain the EXPLAIN PLAN for the SQL and post it here.

Ross Leishman
Re: Issue with NOT Exists when No Data [message #197626 is a reply to message #197617] Thu, 12 October 2006 01:49 Go to previous messageGo to next message
tramanathan
Messages: 3
Registered: October 2006
Junior Member
Table or Join No of Records
********************** **************
mclaims_masc_codes 3198
mclaims_svl_masc 2565
mclaims_masc_codes a = mclaims_svl_masc b 2553
slink_masc 2553

I manulally verified that all the 2553 records match between the Outer Query and Inner subquery which are joined using Not Exists condition, ideally the output of the above query will be zero rows. I have attached the TOAD screen shot of the explain plan.


Thanks
  • Attachment: PlanTable.GIF
    (Size: 30.35KB, Downloaded 173 times)
Re: Issue with NOT Exists when No Data [message #197633 is a reply to message #197626] Thu, 12 October 2006 02:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
See if this helps:
SELECT /*+ DRIVING_SITE(b)*/ c.masc_code, c.masc_desc, c.country_code 
FROM masc_test c 
, mclaims_masc_codes a
, mclaims_svl_masc b
WHERE a.masc_cd (+) = c.masc_code
AND   b.masc_cd (+) = a.masc_cd
AND   b.rowid IS NULL

If not, send the EXPLAIN PLAN again.

Ross Leishman
Re: Issue with NOT Exists when No Data [message #197641 is a reply to message #197633] Thu, 12 October 2006 02:27 Go to previous messageGo to next message
tramanathan
Messages: 3
Registered: October 2006
Junior Member
Thanks a lot Ross, it works. But can u tell me what is the actuall issue, why the Not Exists hangs when there is no output. Is there any Oracle Internal logic which causes this delay?

Thanks Again.
Re: Issue with NOT Exists when No Data [message #197651 is a reply to message #197641] Thu, 12 October 2006 03:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not hanging - it's just doing an awful lot of work.
Have a look in the view V$SESS_IO for the session that is running the query. If you look twice separated by about 5 seconds you should see the amount of IO that the query has performed in that period.
Re: Issue with NOT Exists when No Data [message #197652 is a reply to message #197641] Thu, 12 October 2006 03:26 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Your original plan translates as:

For each row in C
  Copy remote table A to local
  For each row in local-A
    If c.masc_code = a.masc_code Then

      Copy remote table B to local
      For each row in Local-B
        If b.masc_cd = a.masc_cd Then
          Exit 2 (back to 1st loop)
        End If
      End Loop
    End If
  End Loop
End Loop

If you get a row in C that does not exist in A and B, then B must be dragged across the network 3198 times (once for each row in A).

Hope that helps.

Ross Leishman
Previous Topic: floating value
Next Topic: How to Create Database Tables from Excel Spreadsheets
Goto Forum:
  


Current Time: Sun Dec 11 02:36:29 CST 2016

Total time taken to generate the page: 0.09054 seconds