Optimising a SELECT query that runs slow on Oracle which runs quickly on SQL Server

Question

I'm trying to run the following SQL statement in Oracle, and it takes ages to run:

SELECT orderID FROM tasks WHERE orderID NOT IN 
(SELECT DISTINCT orderID FROM tasks WHERE
 engineer1 IS NOT NULL AND engineer2 IS NOT NULL)

If I run just the sub-part that is in the IN clause, that runs very quickly in Oracle, i.e.

SELECT DISTINCT orderID FROM tasks WHERE
engineer1 IS NOT NULL AND engineer2 IS NOT NULL

Why does the whole statement take such a long time in Oracle? In SQL Server the whole statement runs quickly.

Alternatively is there a simpler/different/better SQL statement I should use?

Some more details about the problem:

• Each order is made of many tasks
• Each order will be allocated (one or more of its task will have engineer1 and engineer2 set) or the order can be unallocated (all its task have null values for the engineer fields)
• I am trying to find all the orderIDs that are unallocated.

Just in case it makes any difference, there are ~120k rows in the table, and 3 tasks per order, so ~40k different orders.

Responses to answers:

• I would prefer a SQL statement that works in both SQL Server and Oracle.
• The tasks only has an index on the orderID and taskID.
• I tried the NOT EXISTS version of the statement but it ran for over 3 minutes before I cancelled it. Perhaps need a JOIN version of the statement?
• There is an "orders" table as well with the orderID column. But I was trying to simplify the question by not including it in the original SQL statement.

I guess that in the original SQL statement the sub-query is run every time for each row in the first part of the SQL statement - even though it is static and should only need to be run once?

Executing

ANALYZE TABLE tasks COMPUTE STATISTICS;

made my original SQL statement execute much faster.

Although I'm still curious why I have to do this, and if/when I would need to run it again?

The statistics give Oracle's cost-based optimzer information that it needs to determine the efficiency of different execution plans: for example, the number of rowsin a table, the average width of rows, highest and lowest values per column, number of distinct values per column, clustering factor of indexes etc.

In a small database you can just setup a job to gather statistics every night and leave it alone. In fact, this is the default under 10g. For larger implementations you usually have to weigh the stability of the execution plans against the way that the data changes, which is a tricky balance.

Oracle also has a feature called "dynamic sampling" that is used to sample tables to determine relevant statistics at execution time. It's much more often used with data warehouses where the overhead of the sampling it outweighed by the potential performance increase for a long-running query.

Answer 1

The "IN" - clause is known in Oracle to be pretty slow. In fact, the internal query optimizer in Oracle cannot handle statements with "IN" pretty good. try using "EXISTS":

SELECT orderID FROM tasks WHERE orderID NOT EXISTS 
    (SELECT DISTINCT orderID FROM tasks WHERE
         engineer1 IS NOT NULL AND engineer2 IS NOT NULL)`print("code sample");`

Caution: Please check if the query builds the same data results.

Edith says: ooops, the query is not well formed, but the general idea is correct. Oracle has to fulfill a full table scan for the second (inner) query, build the results and then compare them to the first (outer) query, that's why it's slowing down. Try

SELECT orderID AS oid FROM tasks WHERE NOT EXISTS 
    (SELECT DISTINCT orderID AS oid2 FROM tasks WHERE
         engineer1 IS NOT NULL AND engineer2 IS NOT NULL and oid=oid2)

or something similiar ;-)

Answer 2

Isn't your query the same as

SELECT orderID FROM tasks
WHERE engineer1 IS NOT NULL OR engineer2 IS NOT NULL

?

Answer 3

How about :

SELECT DISTINCT orderID FROM tasks t1 WHERE NOT EXISTS (SELECT * FROM tasks t2 WHERE t2.orderID=t1.orderID AND (engineer1 IS NOT NULL OR engineer2 IS NOT NULL));

I am not a guru of optimization, but maybe you also overlooked some indexes in your Oracle database.

Answer 4

Another option is to use MINUS (EXCEPT on MSSQL)

SELECT orderID FROM tasks
MINUS
SELECT DISTINCT orderID FROM tasks WHERE engineer1 IS NOT NULL 
AND engineer2 IS NOT NULL

Answer 5

Sub-queries are "bad" with Oracle. It's generally better do use joins.

Here's an article on how to rewrite your subqueries with join : http://www.dba-oracle.com/sql/t_rewrite_subqueries_performance.htm

Answer 6

Here is an alternate approach which I think gives what you want:

SELECT orderID
 FROM tasks
 GROUP BY orderID
 HAVING COUNT(engineer1) = 0 OR COUNT(engineer2) = 0

I'm not sure if you want "AND" or "OR" in the HAVING clause. It sounds like according to business logic these two fields should either both be populated or both be NULL; if this is guaranteed then you could reduce the condition to just checking engineer1.

Your original query would, I think, give multiple rows per orderID, whereas mine will only give one. I am guessing this is OK since you are only fetching the orderID.

Answer 7

Some questions:

• How many rows are there in tasks?
• What indexes are defined on it?
• Has the table been analyzed recently?

Another way to write the same query would be:

select orderid from tasks
minus
select orderid from tasks
where engineer1 IS NOT NULL AND engineer2 IS NOT NULL

However, I would rather expect the query to involve an "orders" table:

select orderid from ORDERS
minus
select orderid from tasks
where engineer1 IS NOT NULL AND engineer2 IS NOT NULL

or

select orderid from ORDERS
where orderid not in
( select orderid from tasks
  where engineer1 IS NOT NULL AND engineer2 IS NOT NULL
)

or

select orderid from ORDERS
where not exists
( select null from tasks
  where tasks.orderid = orders.orderid
  and   engineer1 IS NOT NULL OR engineer2 IS NOT NULL
)

Answer 8

Your query:

SELECT orderID
FROM tasks 
WHERE orderID NOT IN (
  SELECT DISTINCT orderID
  FROM tasks
  WHERE engineer1 IS NOT NULL AND engineer2 IS NOT NULL
)
;

Is equivalent to:

SELECT DISTINCT orderID
FROM tasks 
WHERE NOT ( engineer1 IS NOT NULL AND engineer2 IS NOT NULL )
;

Which can be further simplified to:

SELECT DISTINCT orderID
FROM tasks 
WHERE engineer1 IS NULL OR engineer2 IS NULL
;

Maybe Oracle does not realize how to optimize this but SQL Server does?

Answer 9

I agree with TZQTZIO, I don't get your query.

If we assume the query did make sense then you might want to try using EXISTS as some suggest and avoid IN. IN is not always bad and there are likely cases which one could show it actually performs better than EXISTS.

The question title is not very helpful. I could set this query up in one Oracle database and make it run slow and make it run fast in another. There are many factors that determine how the database resolves the query, object statistics, SYS schema statistics, and parameters, as well as server performance. Sqlserver vs. Oracle isn't the problem here.

For those interested in query tuning and performance and want to learn more some of the google terms to search are "oak table oracle" and "oracle jonathan lewis".

Answer 10

I would try using joins instead

SELECT 
    t.orderID 
FROM 
    tasks  t
    LEFT JOIN tasks t1
     ON t.orderID = t1.orderID
     AND t1.engineer1 IS NOT NULL 
     AND t1.engineer2 IS NOT NULL
WHERE
    t1.orderID IS NULL

also your original query would probably be easier to understand if it was specified as:

SELECT orderID FROM orders WHERE orderID NOT IN 
(SELECT DISTINCT orderID FROM tasks WHERE
 engineer1 IS NOT NULL AND engineer2 IS NOT NULL)

(assuming you have orders table with all the orders listed)

which can be then rewritten using joins as:

SELECT 
    o.orderID 
FROM 
    orders o
    LEFT JOIN tasks t
     ON o.orderID = t.orderID
     AND t.engineer1 IS NOT NULL 
     AND t.engineer2 IS NOT NULL
WHERE
    t.orderID IS NULL

Answer 11

If you have no index over the Engineer1 and Engineer2 columns then you are always going to generate a Table Scan in SQL Server and the equivalent whatever that may be in Oracle.

If you just need the Orders that have unallocated tasks then the following should work just fine on both platforms, but you should also consider adding the indexes to the Tasks table to improve query perfomance.

SELECT DISTINCT orderID 
FROM tasks 
WHERE (engineer1 IS NULL OR engineer2 IS NULL)

Answer 12

If you decide to create an ORDERS table, I'd add an ALLOCATED flag to it, and create a bitmap index. This approach also forces you to modify the business logic to keep the flag updated, but the queries will be lightning fast. It depends on how critical are the queries for the application.

Regarding the answers, the simpler the better in this case. Forget subqueries, joins, distinct and group bys, they are not needed at all!

Answer 13

I think several people have pretty much the right SQL, but are missing a join between the inner and outer queries.
Try this:

SELECT t1.orderID 
FROM   tasks t1
WHERE  NOT EXISTS
       (SELECT 1 
        FROM   tasks t2 
        WHERE  t2.orderID   = t1.orderID
        AND    t2.engineer1 IS NOT NULL 
        AND    t2.engineer2 IS NOT NULL)

Answer 14

Often this type of problem goes away if you analyze the tables involved (so Oracle has a better idea of the distribution of the data)

ANALYZE TABLE tasks COMPUTE STATISTICS;

Answer 15

What proportion of the rows in the table meet the condition "engineer1 IS NOT NULL AND engineer2 IS NOT NULL"?

This tells you (roughly) whether it might be worth trying to use an index to retrieve the associated orderid's.

Another way to write the query in Oracle that would handle unindexed cases very well would be:

select distinct orderid
from
(
select orderid,
       max(case when engineer1 is null and engineer2 is null then 0 else 1)
          over (partition by orderid)
          as max_null_finder
from   tasks
)
where max_null_finder = 0

Answer 16

The Oracle optimizer does a good job of processing MINUS statements. If you re-write your query using MINUS, it is likely to run quite quickly:

SELECT orderID FROM tasks
MINUS
SELECT DISTINCT orderID FROM tasks WHERE
 engineer1 IS NOT NULL AND engineer2 IS NOT NULL

Answer 17

New take.

Iff:

• The COUNT() function does not count NULL values

and

• You want the orderID of all tasks where none of the tasks have either engineer1 or engineer2 set to a value

then this should do what you want:

SELECT orderID
FROM tasks
GROUP BY orderID
HAVING COUNT(engineer1) = 0 AND COUNT(engineer2) = 0

Please test it.

Answer 18

"Although I'm still curious why I have to do this, and if/when I would need to run it again?"

The statistics give Oracle's cost-based optimzer information that it needs to determine the efficiency of different execution plans: for example, the number of rowsin a table, the average width of rows, highest and lowest values per column, number of distinct values per column, clustering factor of indexes etc.

In a small database you can just setup a job to gather statistics every night and leave it alone. In fact, this is the default under 10g. For larger implementations you usually have to weigh the stability of the execution plans against the way that the data changes, which is a tricky balance.

Oracle also has a feature called "dynamic sampling" that is used to sample tables to determine relevant statistics at execution time. It's much more often used with data warehouses where the overhead of the sampling it outweighed by the potential performance increase for a long-running query.

Answer 19

For your followup question, you may be interested in this:

http://stackoverflow.com/questions/79229/how-often-should-oracle-database-statistics-be-run

Answer 20

Could you provide the explain plans for both queries, so we have some idea what the optimizer thinks about them both?

Answer 21

I agree with ΤΖΩΤΖΙΟΥ and wearejimbo that your query should be...

SELECT DISTINCT orderID FROM Tasks 
WHERE Engineer1 IS NULL OR Engineer2 IS NULL;

I don't know about SQL Server, but this query won't be able to take advantage of any indexes because null rows aren't in indexes. The solution to this would be to re-write the query in a way that would allow a function based index to be created that only includes the null value rows. This could be done with NVL2, but would likely not be portable to SQL Server.

I think the best answer is not one that meets your criteria and that is write a different statement for each platform that is best for that platform.