Oracle Many OR vs IN的SQL性能调优()

问题描述

我手头没有解释计划".您能帮忙告诉您以下哪项更有效吗?

I dont have "explain plan" on hand. Could you help to tell which of the below is more efficient?

选项1:

select ... 
    from VIEW_ABC 
    where STRING_COL = 'AA' 
       OR STRING_COL = 'BB' 
       OR STRING_COL = 'BB' 
       OR ...
       OR STRING_COL = 'ZZ'

选项2:

select ... 
    from VIEW_ABC 
    where STRING_COL IN ('AA','BB',...,'ZZ')

推荐答案

这是一个类似的问题: IN与Oracle的OR相比，哪个更快?

This is a similar question:IN vs OR of Oracle, which faster?

看看OMG Ponies给出的答案，他认为IN比OR更优化.

Look at the answer given by OMG Ponies, he suggests that IN is more optimized than OR.

还有一个问题: SQL WHERE子句中的IN vs OR

尽管它不是特定于数据库的，但是从Oracle的角度来看，它有一些不错的答案.

Though it is non database specific, it has a few good answers from an Oracle perspective.

就其价值而言，IN的使用将更容易阅读，因此从长远来看更易于支持.仅此一项就可能会影响您的选择，尤其是(如我所怀疑的)，如果Oracle优化器以相同的方式对待它们两者.

For what it's worth, the use of IN would be far easier to read and therefore easier to support in the long run. That alone might sway your choice, especially if (as I suspect) the Oracle optimiser treats them both the same way.

但是，如果您的值列表很大，那么您可能应该寻找一种完全不同的方法来检索结果.

If, however, your list of values is very large then you should probably look into a totally different method for retrieving your results.

这篇关于Oracle Many OR vs IN的SQL性能调优()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！