How come it takes 'ages' (to the point of interrupting the process) to generate a list of distinct items from a table with multiple records for each item that does not occur in another table (where the items are unique) in Oracle (i.e. find distinct items in table b where items not in(select items from table a)), while it only takes seconds in Access via ODBC (using the non-matched query)? Note that items are indexed in both tables.
I'm not really sure what SQL Access generates (the actual SQL statement itself) when you do a nonmatched query but I do know that using NOT IN will kill performance of most any query. Perhaps the SQL that Access creates is different than the SQL you use in your Oracle session and the way Access forms the query is more efficient (that's my guess anyway). I'd suggest that you check the SQL statement that Access is generating and make sure that the query you are executing in Oracle is basically the same (and try to avoid using NOT IN).
Here are some general guidelines to follow when writing queries with performance in mind:
- Try to avoid NOT IN if possible.
- Table joins usually perform better than subqueries.
- If you must use a subquery, use EXISTS instead of IN.
SELECT DISTINCT item FROM table_b WHERE item NOT IN (SELECT item FROM table_a) ;Another way to write the query could be like this:
SELECT DISTINCT b.item FROM table_b b WHERE NOT EXISTS (SELECT item FROM table_a WHERE item = b.item) ;Or, try writing your query like this instead and see if it helps:
SELECT DISTINCT b.item FROM table_a a, table_b b WHERE a.item (+) = b.item AND a.item IS NULL ;The bottom-line is that you'll have to experiment and test different ways of writing your SQL statement to determine which one offers the best performance. As I mentioned above, I'd start by making sure that your Oracle SQL statement matches Access's SQL statement and then proceed from there.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.