| Oracle NOT IN query is slow [message #320668] |
Thu, 15 May 2008 17:31  |
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 #320686 is a reply to message #320669] |
Thu, 15 May 2008 21:23  |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
