Home » RDBMS Server » Performance Tuning » Oracle NOT IN query is slow (Oracle 10g on Win2k3)
Oracle NOT IN query is slow [message #320668] Thu, 15 May 2008 17:31 Go to next message
nmaier
Messages: 4
Registered: May 2008
Junior Member
Hi,

I have the following query that takes about 1.5 minutes to run and was wondering if there was a more efficient query for Oracle.

SELECT Number_ FROM CompanyPort WHERE (Number_ NOT IN (SELECT Number_ FROM CompanyTN WHERE (CurrentData=1) AND (SystemID=77))) AND (CurrentData=1) AND (SystemID=77) AND (PortType=2) AND (Phantom=0)

Part of it is the number of rows I'm working with. There are 4256 CompanyPort.Number_ rows that match the WHERE criteriea(minus the sub-query). And the sub-query has 5026 rows.

By my calculations that is at most 21,390,656 comparisons. The exact same request to a MSSQL DB takes mere seconds, however the data rows are considerably smaller(384 rows and 3678 rows respectively).

Thanks for any help or insights!

Nate


Re: Oracle NOT IN query is slow [message #320669 is a reply to message #320668] Thu, 15 May 2008 17:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
Which of the suggestions from URL have you tried & what were the results?

Are statistics current?

Please post EXPLAIN_PLAN.
Re: Oracle NOT IN query is slow [message #320686 is a reply to message #320669] Thu, 15 May 2008 21:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Make sure Number_ is defined as NOT NULL in BOTH tables. If you cannot guarantee they will be NOT NULL, then you have to handle nulls. eg:
SELECT Number_ 
FROM CompanyPort 
WHERE NVL(Number_, -1) NOT IN (
    SELECT NVL(Number_, -1) 
    FROM CompanyTN 
    WHERE (CurrentData=1) 
    AND (SystemID=77)
)
AND (CurrentData=1) 
AND (SystemID=77) 
AND (PortType=2) 
AND (Phantom=0)


Also make sure both tables are analysed with DBMS_STATS.GATHER_TABLE_STATS().

This will permit Oracle to perform an anti-join instead of a filter, which is almost certainly the cause of your performance problem

Ross Leishman
Re: Oracle NOT IN query is slow [message #320903 is a reply to message #320668] Fri, 16 May 2008 12:04 Go to previous message
nmaier
Messages: 4
Registered: May 2008
Junior Member
Thanks Ross! NVL did the trick.

Nate
Previous Topic: connect by prior is killing me again
Next Topic: Find the query running time
Goto Forum:
  


Current Time: Sun Dec 11 03:51:50 CST 2016

Total time taken to generate the page: 0.08996 seconds