关键字中的oracle子查询在12c上运行缓慢 [英] oracle subquery in keyword is slow on 12c

问题描述

与关键字中的11g，12c(当前使用12.1.0.2版本)相比，它是如此缓慢.

compared to 11g, 12c (currently using 12.1.0.2 version) in keyword is so slow.

SELECT * FROM 
    DATA_TABLE WHERE
    OID IN (
        SELECT OID FROM ID_TABLE WHERE (condition)
    )

结果

• 11克:1秒以内

• 11g : under 1 sec

12c:超过10秒

以下查询在11g和12c中都足够快(让您知道真正的问题是在子查询中"查询

below query is fast enough in both 11g and 12c (to let you know real problem is 'in subquery' query

SELECT OID FROM ID_TABLE WHERE (condition)

我可以通过如下更改查询来解决此问题

I can solve this problem with changing query as below

SELECT * FROM 
    DATA_TABLE D,
    (
        SELECT OID FROM ID_TABLE WHERE (condition)
    ) O
WHERE D.OID = O.OID

结果

• 11克:1秒以内

• 11g : under 1 sec

12c:不到1秒

OR

SELECT * FROM 
    DATA_TABLE WHERE
    OID IN (
        "AA", "BB", "CC", "DD, "EE"
    )

结果

• 11克:1秒以内

• 11g : under 1 sec

12c:不到1秒

仅在在子查询中"存在问题. INDEX的两个表都做得很好. 有人解决了这个问题吗?

Problem is only on 'in sub query'. INDEX is well made both table. Have Anyone solved this problem?

推荐答案

您将必须提供说明计划，以更好地评估为什么一个人的表现要优于另一个人.但是，通常，通过将IN条件改为EXISTS条件，对于这种类型的查询，您可以获得更好的或至少更可预测的结果:

You would have to provide the explain plans to better assess why one is performing better than the other. But, in general, you can get better, or at least, more predictible results for this type of query by changing the IN condition to an EXISTS condition instead:

select *
  from data_table t1
 where exists (select null
                 from id_table t2
                where t2.oid = t1.oid
                  and (other conditions))

这篇关于关键字中的oracle子查询在12c上运行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！