关键字中的 oracle 子查询在 12c 上很慢

2022-01-23 00:00:00 subquery oracle

相比11g，12c(目前使用12.1.0.2版本)在keyword中速度太慢了.

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

结果

  • 11g : 不到 1 秒

  • 11g : under 1 sec

12c:超过 10 秒

12c : over 10 sec

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

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

结果

  • 11g : 不到 1 秒

  • 11g : under 1 sec

12c : 不到 1 秒

12c : under 1 sec

或

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

结果

  • 11g : 不到 1 秒

  • 11g : under 1 sec

12c : 不到 1 秒

12c : under 1 sec

问题仅在子查询中".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))

相关文章