Problem solve Get help with specific problems with your technologies, process and projects.

Generating a list of unique objects in Oracle versus in MS Access

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:

  1. Try to avoid NOT IN if possible.
  2. Table joins usually perform better than subqueries.
  3. If you must use a subquery, use EXISTS instead of IN.
For instance, if your query is written like this:
  FROM table_b
 WHERE item NOT IN (SELECT item FROM table_a) ;
Another way to write the query could be like this:
  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:
  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

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.