我正在尝试在Oracle中运行以下SQL语句，并且运行很长一段时间：

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

如果我只运行IN子句中的子部分，那么它将在Oracle中非常快速地运行，即

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

为什么在Oracle中整个语句要花这么长时间？在SQL Server中，整个语句运行很快。

另外，我应该使用更简单/不同/更好的SQL语句吗？

有关该问题的更多详细信息：

• 每个订单由许多任务组成
• 每个订单将被分配(其一个或者多个任务将设置有engineer1和engineer2)或者该订单可被取消分配(其所有任务的engineer字段均具有空值)
• 我正在尝试查找所有未分配的orderID。

以防万一，请稍候，表中约有12万行，每个订单有3个任务，因此约有40k个不同的订单。

对答案的回应：

• 我希望可以在SQL Server和Oracle中都可以使用的SQL语句。
• 任务仅在orderID和taskID上具有索引。
• 我尝试了该语句的NOT EXISTS版本，但在取消它之前运行了3分钟以上。也许需要该语句的JOIN版本？
• 还有一个带有" orderID"列的" orders"表。但是我试图通过不将其包含在原始SQL语句中来简化该问题。

我猜想在原始SQL语句中，子查询每次都针对SQL语句第一部分中的每一行运行，即使它是静态的，也只需要运行一次？

执行中

ANALYZE TABLE tasks COMPUTE STATISTICS;

使我原来的SQL语句执行得更快。

尽管我仍然很好奇为什么必须这样做，以及是否/何时需要再次运行它？

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.

解决方案

在Oracle中，" IN"子句非常慢。实际上，Oracle中的内部查询优化器不能很好地处理带有" IN"的语句。尝试使用" 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");`

警告：请检查查询是否生成相同的数据结果。

伊迪丝(Edith)说：糟糕，查询的格式不正确，但总体思路是正确的。 Oracle必须对第二个(内部)查询执行全表扫描，生成结果，然后将它们与第一个(外部)查询进行比较，这就是它变慢的原因。尝试

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)

或者类似的东西;-)

查询与

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

？

怎么样 ：

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));

我不是优化专家，但也许我们也忽略了Oracle数据库中的某些索引。

另一种选择是使用减号(MSSQL上的EXCEPT)

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

子查询对于Oracle来说是"不好的"。通常最好使用连接。

这是一篇有关如何使用join重写子查询的文章：
http://www.dba-oracle.com/sql/t_rewrite_subqueries_performance.htm

这是我认为可以提供我们想要的替代方法：

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

我不确定在HAVING子句中是否要使用" AND"或者" OR"。听起来，根据业务逻辑，这两个字段应该都填充或者都为NULL；如果可以保证，则可以将条件减少为仅检查engineer1.

我认为原始查询会为每个orderID提供多行，而我的只会给出一行。我猜这很好，因为我们只获取orderID。

一些问题：

• 任务中有几行？
• 在其上定义了哪些索引？
• 最近对表格进行了分析吗？

编写相同查询的另一种方法是：

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

但是，我希望查询包含一个" orders"表：

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

或者

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

或者

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
)

我同意TZQTZIO，但没有收到查询。

如果我们假设查询确实有意义，那么我们可能希望像某些建议一样尝试使用EXISTS并避免使用IN。 IN并不总是很糟糕，并且在某些情况下可能表明它实际上比EXISTS表现更好。

问题标题不是很有帮助。我可以在一个Oracle数据库中设置此查询，使其运行缓慢，而在另一个数据库中运行快速。有许多因素决定数据库如何解析查询，对象统计信息，SYS模式统计信息和参数以及服务器性能。 Sqlserver vs. Oracle并不是这里的问题。

对于那些对查询调优和性能感兴趣并且想要了解更多信息的人，其中包括" oak table oracle"和" oracle jonathan lewis"。

我会尝试使用联接

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

如果将原始查询指定为：

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

(假设我们有列出所有订单的订单表)

然后可以使用连接重写为：

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

如果我们在Engineer1和Engineer2列上没有索引，那么我们将始终在SQL Server中生成一个表扫描，而在Oracle中则等效。

如果只需要具有未分配任务的订单，则以下命令在两个平台上都可以正常工作，但是我们还应该考虑将索引添加到"任务"表中以提高查询性能。

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

如果决定创建一个ORDERS表，我将向其添加一个ALLOCATED标志，并创建一个位图索引。这种方法还迫使我们修改业务逻辑以使标记保持更新，但是查询将很快完成。这取决于对应用程序的查询有多关键。

关于答案，在这种情况下越简单越好。忘记子查询，联接，独特和分组依据，根本不需要它们！

我认为几个人几乎拥有正确的SQL，但缺少内部查询和外部查询之间的连接。
试试这个：

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)

如果我们分析所涉及的表，这种类型的问题通常会消失(因此，Oracle对数据的分布有了更好的了解)

ANALYZE TABLE tasks COMPUTE STATISTICS;

表中有多少行符合条件" engineer1不为空，engineer2不为空"？

这(大致)告诉我们是否值得尝试使用索引来检索关联的订单ID。

在Oracle中编写可以很好处理未索引情况的查询的另一种方法是：

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

Oracle优化器在处理MINUS语句方面做得很好。如果我们使用MINUS重新编写查询，则查询可能会很快运行：

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

新的。

Iff：

• COUNT()函数不计算NULL值

和

• 我们需要所有任务都没有将engineer1或者engineer2设置为值的所有任务的orderID

那么这应该做你想要的：

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

请测试一下。

"尽管我仍然很好奇为什么我必须这样做，以及是否/何时需要再次运行它？"

统计信息为Oracle提供了基于成本的优化器信息，它确定了不同执行计划的效率：例如，表中的行数，行的平均宽度，每列的最高和最低值，每列的不同值的数量，索引的聚类因子等。

在小型数据库中，我们可以设置一个工作来每晚收集统计信息，而不必理会它。实际上，这是10g以下的默认设置。对于较大的实现，通常必须权衡执行计划的稳定性和数据更改的方式，这是一个棘手的平衡。

Oracle还具有称为"动态采样"的功能，该功能用于对表进行采样以确定执行时的相关统计信息。它更常与数据仓库一起使用，在数据仓库中，长时间运行的查询的采样开销超过了潜在的性能提升。

我同意和wearejimbo的要求，查询应该是...

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

我不了解SQL Server，但此查询将无法利用任何索引，因为索引中没有空行。解决方案是重新编写查询，以允许创建仅包含空值行的基于函数的索引。可以使用NVL2完成此操作，但可能无法移植到SQL Server。

我认为最好的答案不是符合条件的答案，那就是为每个平台写一个最适合该平台的声明。