Home » RDBMS Server » Server Administration » NOT IN operator - very slow
NOT IN operator - very slow [message #370901] Sat, 04 March 2000 11:58 Go to next message
Halvor
Messages: 3
Registered: March 2000
Junior Member
This statement uses more than 1 hour (executed from SQL Worksheet):
Select max(REVISION), ITEM_KEY From ITEM
where (ITEM_TYPE)=0
and ITEM_KEY NOT IN (SELECT PARENT_ITEM
from ITEM_PS_DEF
where PARENT_REVISION = ITEM.REVISION)
group by ITEM_KEY

ITEM table contains 26.000 records and ITEM_PS_DEF contains 140.000 records

When using another syntax from MS Access, using the same tables (linked with Oracle ODBC) it only takes two seconds. The Query result is
the same.

SELECT ITEM.ITEM_KEY, Max(ITEM.REVISION) AS MaxOfREVISION, Last(ITEM.ITEM_NAME) AS LastOfITEM_NAME
FROM ITEM LEFT JOIN ITEM_PS_DEF ON (ITEM.REVISION = ITEM_PS_DEF.PARENT_REVISION) AND (ITEM.ITEM_KEY
= ITEM_PS_DEF.PARENT_ITEM)
WHERE (((ITEM.ITEM_TYPE)=0) AND ((ITEM_PS_DEF.PARENT_ITEM) Is Null) AND ((ITEM_PS_DEF.PARENT_REVISION)
Is Null))
GROUP BY ITEM.ITEM_KEY;

This fast Query uses an Outer Join where all records from the ITEM table is selected and only matching records from ITEM_PS_DEF . When
the columns selected fro ITEM_PS_DEF "IS Null", it means there is NO matching record from this table (or that the records are NOT IN).

The Oracle ODBC driver understands this, so it has to be a similar method in Oracle SQL.

Regards Halvor

A great Oracle tool:
http://members.tripod.com/easydoc/dim.htm
http://members.tripod.com/Nybbies/vb.htm
Re: NOT IN operator - very slow [message #370919 is a reply to message #370901] Tue, 07 March 2000 05:31 Go to previous message
Atavur Rahaman S.A
Messages: 23
Registered: January 2000
Junior Member
Hello,

Good Day!

Try to use Row Identifier(ROWID) access method. Consider that there are two tables; try to select that Table as TABLE1 which has got more rows and TABLE2 as the driving Table.
You can see that specifying the correct driving table makes a huge difference in performance. Try to reset the ARRAYSIZE parameter in order to reduce the number of physical calls.

Thanks in Advance....

Regards

Atavur Rahaman S.A
Previous Topic: LEFT JOIN, RIGHT JOIN syntax in Oracle - Urgent!!!!
Next Topic: how to use the statistical information from analyze command?
Goto Forum:
  


Current Time: Sat Oct 24 04:28:37 CDT 2020