SQL WHERE xx IN ('qq' , 'ww' ...) 性能 - 更多的值(value)，更少的时间？-6ren

SQL WHERE xx IN ('qq' , 'ww' ...) 性能 - 更多的值(value)，更少的时间？

转载作者：行者123 更新时间：2023-12-04 16:11:06

我终于问了我的第一个问题(虽然我是一个长期跟踪者)。

前几天在工作中，一个 SQL 查询引起了我的注意。问题在于 WHERE 的性能子句，当使用 IN 将索引与可能的值进行比较时运算符(operator)。

SELECT   SUM (parts.quantity) AS quantity,
       concessions.concessionCode,
       concessions.description AS concessionDesc,
       parts.type,
       activities.activityCode,
       REPLACE (activities.activityCode, activities.lvl2 || '-', '') AS activityCodeDisplay,
       strings.activityDesc,
       strings.activityDesc2,
       strings.activityDesc3
FROM   tb_parts parts,
       tb_activities activities,
       tb_strings strings,
       tb_concessions concessions
WHERE       parts.activityCode = activities.activityCode
       AND parts.concessionCode = activities.concessionCode
       AND activities.concessionCode = concesions.concessionCode
       AND activities.concessionCode = strings.concessionCode
       AND activities.activityCode = strings.activityCode
       AND strings.language = 'ENG'
       --AND parts.concesionCode IN ('ZD', 'G9', 'TR', 'JS0')
       AND parts.concesionCode IN ('ZD', 'G9')
       AND parts.date >= TO_DATE ('01/01/2013 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
       AND parts.date <= TO_DATE ('30/04/2013 23:59:59', 'DD/MM/YYYY HH24:MI:SS')
       AND parts.type IN ('U', 'M')
       AND parts.value = 'E'
GROUP BY   concesions.concessionCode,
       concesions.description,
       parts.type,
       activities.activityCode,
       REPLACE (activities.activityCode, activities.lvl2|| '-', ''),
       strings.activityDesc,
       strings.activityDesc2,
       strings.activityDesc3
ORDER BY   concesions.concessionCode;

我遇到的问题是 - 如果查询像这样运行( IN 有两个值)，则需要 30 秒。如果它使用四个值运行(就像在注释行中一样)，则查询需要 5 秒。我希望将索引与多个值进行比较会花费更多时间，但似乎并非如此。我在白天多次重复“测试”，它们总是或多或少相同(30 +-1s，5 +-1s)。

任何有关为什么会以这种方式行事的见解都将不胜感激!

附言我已经翻译了表/列的名称，如果有任何差异，请见谅。

P.P.S.我已经用连接重写了这段代码，它的速度要快得多，但这种异常背后的原因仍然困扰着我:)

编辑 : 终于上类了!经过一些修补，我已经能够为这两个版本创建执行计划，甚至为查询的第三个版本(使用 4 和 2 where 中的值，时间约为 600 毫秒) .另外，关于表格中的数据有几个问题，所以这里有一些信息:

All the stats are analyzed the day that queries were executed

Table parts 
           total rows           - 3.2  M 
           matches for 2 values - 1.08 M (~34%)
           matches for 4 values - 1.30 M (~41%)
Table activities
           total rows           - 3866 
           matches for 2 values - 321    (~ 8%)
           matches for 4 values - 644    (~16%)
Table strings 
           total rows           - 7436
           matches for 2 values - 642    (~ 8%)
           matches for 4 values - 1288   (~17%)

Index in_parts
           codConcession
           username
           date

正因为如此，我认为使用动态采样时没有大的区别(除了+2/3s)(如果我做对了，也就是说，在 /*+ dynamic_sampling(tb_parts 10) */ 关键字之后使用 SELECT)

对于 两个值 :

----------------------------------------------------------------------------------------------------- 
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                   |                |     1 |   186 |   864   (1)| 00:00:11 | 
|   1 |  SORT ORDER BY                     |                |     1 |   186 |   864   (1)| 00:00:11 | 
|   2 |   HASH GROUP BY                    |                |     1 |   186 |   864   (1)| 00:00:11 | 
|*  3 |    TABLE ACCESS BY INDEX ROWID     | tb_parts       |     1 |    37 |   818   (1)| 00:00:10 | 
|   4 |     NESTED LOOPS                   |                |     1 |   186 |   862   (1)| 00:00:11 | 
|   5 |      NESTED LOOPS                  |                |     1 |   149 |    44   (0)| 00:00:01 | 
|   6 |       NESTED LOOPS                 |                |    34 |  2108 |    10   (0)| 00:00:01 | 
|   7 |        INLIST ITERATOR             |                |       |       |            |          | 
|   8 |         TABLE ACCESS BY INDEX ROWID| tb_concesions  |     2 |    54 |     2   (0)| 00:00:01 | 
|*  9 |          INDEX UNIQUE SCAN         | pk_concession  |     2 |       |     1   (0)| 00:00:01 | 
|  10 |        TABLE ACCESS BY INDEX ROWID | tb_activities  |    17 |   595 |     4   (0)| 00:00:01 | 
|* 11 |         INDEX RANGE SCAN           | pk_activity    |    17 |       |     2   (0)| 00:00:01 | 
|  12 |       TABLE ACCESS BY INDEX ROWID  | tb_strings     |     1 |    87 |     1   (0)| 00:00:01 | 
|* 13 |        INDEX UNIQUE SCAN           | pk_string      |     1 |       |     0   (0)| 00:00:01 | 
|* 14 |      INDEX RANGE SCAN              | in_parts       |   454 |       |   648   (1)| 00:00:08 | 
----------------------------------------------------------------------------------------------------- 

PLAN_TABLE_OUTPUT                                                                                     
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):                                                   
---------------------------------------------------                                                   
   3 - filter("parts"."value"='E' 
              AND ("parts"."type"='M' OR "parts"."type"='U') 
              AND "parts"."activityCode"="activities"."activityCode")   

   9 - access("concessions"."concessionCode"='G9' 
              OR "concessions"."concessionCode"='ZD')  

  11 - access("activities"."concessionCode"="concessions"."concessionCode")                           
       filter("activities"."concessionCode"='G9' 
              OR "activities"."concessionCode"='ZD')               

  13 - access("activities"."concessionCode"="strings"."concessionCode" 
              AND "activities"."activityCode"="strings"."activityCode" 
              AND "strings"."language"='ENG')    
       filter("strings"."concessionCode"='G9' 
              OR "strings"."concessionCode"='ZD')                     

  14 - access("parts"."concessionCode"="activities"."concessionCode" 
              AND "parts"."date">=TO_DATE('2013-01-01 00:00:00', 
                                          'syyyy-mm-dd hh24:mi:ss') 
              AND "parts"."date"<=TO_DATE(' 2013-04-30 23:59:59', 
                                          'syyyy-mm-dd hh24:mi:ss'))                                                   
       filter("parts"."date">=TO_DATE('2013-01-01 00:00:00', 
                                      'syyyy-mm-dd hh24:mi:ss')               
              AND ("parts"."concessionCode"='G9' 
                   OR "parts"."concessionCode"='ZD') 
              AND "parts"."date"<=TO_DATE(' 2013-04-30 23:59:59', 
                                          'syyyy-mm-dd hh24:mi:ss'))

对于 四个值 :

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |     1 |   186 |  7412   (2)| 00:01:29 |
|   1 |  SORT ORDER BY                    |                |     1 |   186 |  7412   (2)| 00:01:29 |
|   2 |   HASH GROUP BY                   |                |     1 |   186 |  7412   (2)| 00:01:29 |
|   3 |    NESTED LOOPS                   |                |     1 |   186 |  7410   (2)| 00:01:29 |
|*  4 |     HASH JOIN                     |                |    17 |  1683 |  7393   (2)| 00:01:29 |
|*  5 |      HASH JOIN                    |                |   136 |  8432 |    21   (5)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID| tb_concesions  |     4 |   108 |     2   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN         | pk_concession  |     4 |       |     1   (0)| 00:00:01 |
|*  9 |       TABLE ACCESS FULL           | tb_activities  |   644 | 22540 |    18   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS FULL            | tb_parts       |  4310 |   155K|  7372   (2)| 00:01:29 |
|  11 |     TABLE ACCESS BY INDEX ROWID   | tb_strings     |     1 |    87 |     1   (0)| 00:00:01 |
|* 12 |      INDEX UNIQUE SCAN            | pk_string      |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):                                                 

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------
---------------------------------------------------                                                 

   4 - access("parts"."activityCode"="activities"."activityCode" 
              AND "parts"."concessionCode"="activities"."concessionCode")                              

   5 - access("activities"."concessionCode"="concessions"."concessionCode")                         

   8 - access("concessions"."concessionCode"='G9' 
              OR "concessions"."concessionCode"='JS0' 
              OR "concessions"."concessionCode"='TR' 
              OR "concessions"."concessionCode"='ZD')           

   9 - filter("activities"."concessionCode"='G9' 
              OR "activities"."concessionCode"='JS0' 
              OR "activities"."concessionCode"='TR' 
              OR "activities"."concessionCode"='ZD')             
  10 - filter("parts"."date">=TO_DATE(' 2013-01-01 00:00:00', 
                                      'syyyy-mm-dd hh24:mi:ss')             
              AND "parts"."value"='E' 
              AND ("parts"."type"='M' OR "parts"."type"='U') 
              AND ("parts"."concessionCode"='G9' 
                   OR "parts"."concessionCode"='JS0'
                   OR "parts"."concessionCode"='TR' 
                   OR "parts"."concessionCode"='ZD') 
              AND "parts"."date"<=TO_DATE(' 2013-04-30 23:59:59',    
                                          'syyyy-mm-dd hh24:mi:ss'))                                                            

  12 - access("activities"."concessionCode"="strings"."concessionCode" 
              AND "activities"."activityCode"="strings"."activityCode" 
              AND "strings"."language"='ENG')  
       filter("strings"."concessionCode"='G9' 
              OR "strings"."concessionCode"='JS0' 
              OR "strings"."concessionCode"='TR' 
              OR "strings"."concessionCode"='ZD')

最后 六个值 :

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |     1 |   186 |  4525   (1)| 00:00:55 |
|   1 |  SORT ORDER BY                    |                |     1 |   186 |  4525   (1)| 00:00:55 |
|   2 |   HASH GROUP BY                   |                |     1 |   186 |  4525   (1)| 00:00:55 |
|   3 |    NESTED LOOPS                   |                |     1 |   186 |  4523   (1)| 00:00:55 |
|*  4 |     HASH JOIN                     |                |     9 |   891 |  4514   (1)| 00:00:55 |
|*  5 |      HASH JOIN                    |                |   136 |  8432 |    21   (5)| 00:00:01 |
|   6 |       INLIST ITERATOR             |                |       |       |            |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| tb_concesions  |     4 |   108 |     2   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN         | pk_concession  |     4 |       |     1   (0)| 00:00:01 |
|*  9 |       TABLE ACCESS FULL           | tb_activities  |   644 | 22540 |    18   (0)| 00:00:01 |
|  10 |      INLIST ITERATOR              |                |       |       |            |          |
|* 11 |       TABLE ACCESS BY INDEX ROWID | tb_parts       |  2155 | 79735 |  4493   (1)| 00:00:54 |
|* 12 |        INDEX RANGE SCAN           | in_parts       |  8620 |       |  1277   (1)| 00:00:16 |
|  13 |     TABLE ACCESS BY INDEX ROWID   | tb_strings     |     1 |    87 |     1   (0)| 00:00:01 |
|* 14 |      INDEX UNIQUE SCAN            | pk_string      |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT                                                                                   
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):                                                 
---------------------------------------------------                                                 

   4 - access("parts"."activityCode"="activities"."activityCode" 
   AND "parts"."concessionCode"="activities"."concessionCode")   

   5 - access("activities"."concessionCode"="concessions"."concessionCode")                         

   8 - access("concessions"."concessionCode"='G9' 
              OR "concessions"."concessionCode"='JS0' 
              OR "concessions"."concessionCode"='TR' 
              OR "concessions"."concessionCode"='ZD')           

   9 - filter("activities"."concessionCode"='G9' 
              OR "activities"."concessionCode"='JS0' 
              OR "activities"."concessionCode"='TR' 
              OR "activities"."concessionCode"='ZD')             

  11 - filter("parts"."value"='E' 
              AND ("parts"."type"='M' OR "parts"."type"='U'))                   

  12 - access(("parts"."concessionCode"='G9' 
                 OR "parts"."concessionCode"='ZD') 
              AND "parts"."date">=TO_DATE(' 2013-01-01 00:00:00', 
                                          'syyyy-mm-dd hh24:mi:ss') 
              AND "parts"."date"<=TO_DATE(' 2013-04-30 23:59:59', 
                                          'syyyy-mm-dd hh24:mi:ss'))            
       filter("parts"."date">=TO_DATE(' 2013-01-01 00:00:00', 
                                      'syyyy-mm-dd hh24:mi:ss')             
              AND "parts"."date"<=TO_DATE(' 2013-04-30 23:59:59', 
                                          'syyyy-mm-dd hh24:mi:ss'))        

  14 - access("activities"."concessionCode"="strings"."concessionCode" 
              AND "activities"."activityCode"="strings"."activityCode" 
              AND "strings"."language"='ENG')  
       filter("strings"."concessionCode"='G9'  
              OR "strings"."concessionCode"='JS0' 
              OR "strings"."concessionCode"='TR' 
              OR "strings"."concessionCode"='ZD')

由于这是我第一次与执行计划会面，我只能猜测延迟的原因是什么。
在 4 到 6 个值之间，我猜这是从 FULL ACCESS 到 ACCESS BY INDEX 的变化。此外，在访问表时，四个值 (id 10) 的过滤器包含所有四个让步值；而对于六个值，两个特许值在访问部分，过滤器只包含日期、类型和值。

最佳答案

一般来说，出现这种异常的原因是查询优化器无法准确预测成本。准确了解成本的唯一方法是使用不同的执行计划实际运行语句多次。相反，它使用统计数据来估算成本，有时估算是错误的。

当您比较“有两个值”和“有四个值”的执行计划时，您可以看到后者产生更高的成本，并且计划完全不同。优化器可以在这两个执行计划之间进行选择，并且一定认为第一个有两个值更好，而第二个有四个值更好。然而，实际上在这两种情况下，第二种都更好。

如果您仔分割析此类异常，您通常会得出一些见解，例如某些值的组合在您的数据中被高估或低估。在统计数据中使用直方图可为优化器提供更多线索，它可以更好地处理“倾斜数据”，但其预测能力仍将受到限制。

在实践中，解决方案正是您所做的:重写 SQL，直到获得可接受的性能。通常，“提示”(在 Oracle 中)也可以为优化器提供更多线索。

关于SQL WHERE xx IN ('qq' , 'ww' ...) 性能 - 更多的值(value)，更少的时间？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/16120485/

文章推荐： angularjs - AngularJS 定义中的命名冲突

文章推荐： bitcoin - Coinbase 地址回调不起作用？

文章推荐： WPF:如何触发 GUI 行为以响应 View 模型事件？

文章推荐： smtp - 防止电子邮件分组到 Gmail 中的对话 View

javascript - 将 json 编码的字符串转换为 [value, value],[value, value]
我正在尝试使用 flot 绘制 SQL 数据库中的数据图表，这是使用 php 收集的，然后使用 json 编码的。目前看起来像: [{"month":"February","data":482},
php - php数组的结果是[value][value]，我怎样才能得到像[value,value]这样的结果
我有一个来自 php 行的 json 结果，类似于 ["value"]["value"] 我尝试使用内爆函数，但得到的结果是“value”“value” |id_kategori|created_at
javascript - 为什么 select.setAttribute ('value' ,value) 产生与 select.value=value 不同的结果？
脚本 1 将记录 two 但浏览器仍会将 select 元素呈现为 One。该表单还将提交值 one。脚本 2 将记录、呈现和提交两个。我希望它们是同义词并做同样的事情。请解释它们为何不同，以及我
Python如何做列表字典的字典的.values().values()
我的python字典结构是这样的: ips[host][ip] 每行 ips[host][ip] 看起来像这样: [host, ip, network, mask, broadcast, mac, g
c# - 这是什么意思/做什么？ "value < 0 ? -value : value;"
在 C# 中我正在关注的一本书对设置和获取属性提出了这样的建议: double pri_test; public double Test { get { return pri_test; }
c++ - if (mask & VALUE) 还是 if ((mask & VALUE) == VALUE)？
您可能熟悉 enum 位掩码方案，例如: enum Flags { FLAG1 = 0x1, FLAG2 = 0x2, FLAG3 = 0x4, FLAG4 = 0x8
java - (String)value 和 value.toString() ， new Long(value) 和 (Long)value 之间的区别
在一些地方我看到了(String)value。在一些地方value.toString() 这两者有什么区别，在什么情况下我需要使用哪一个。 new Long(value) 和 (Long)value
javascript - 当 "!value ? null : value[0]"不等同于 "value ? value[0] : null"时，Javascript 中是否存在任何时间？
有没有什么时候 var result = !value ? null : value[0]; 不会等同于 var result = value ? value[0] : null; 最佳答案在此处将
javascript - 如何修复 "My first scan value is not same as my second scan value and the value scan in HTML is not same as value scan in notepad?"
我正在使用扫描仪检测设备。目前，我的条形码的值为 2345345 A1。因此，当我扫描到记事本或文本编辑器时，输出将类似于 2345345 A1，这是正确的条形码值。问题是: 当我第一次将条形码扫描
c# - 如何转换 Json key :value into value:value in C#?
我正在读取 C# 中的资源文件并将其转换为 JSON 字符串格式。现在我想将该 JSON 字符串的值转换为键。例子， [ { "key": "CreateAccount", "text":
Python( Pandas ): replace value if previous value is same as next value
我有以下问题: 我有一个数据框，最多可能有 600 万行左右。此数据框中的一列包含某些 ID。 ID NaN NaN D1 D1 D1 NaN D1 D1 NaN NaN NaN NaN D2 NaN
java - (Float value + Integer value + long value) 如何给出意想不到的结果？
import java.util.*; import java.lang.*; class Main { public static void main (String[] args) thr
android - values、values-v11 和 values-v14 文件夹的样式和主题
我目前正在开发我的应用程序，使其设计基于 Holo 主题。在全局范围内我想做的是工作，但我对文件夹 values、values-v11 和 values-v14. 所以我知道: values 的目标是
java ； HttpURL连接；查询项重复为 `paramName=value, value` 。预计为 `paramName=value`
我遇到了一个非常奇怪的问题。我的公司为我们的各种 Assets 使用集中式用户注册网络服务。我们一般通过HttpURLConnection使用请求方法GET向Web服务发送请求，通过qs设置参数。这
mySQL UPDATE value based on SELECT value of value +1 递增列值
查询: UPDATE nominees SET votes = ( SELECT votes FROM nominees WHERE ID =1 ) +1 错误: You can't specify
javascript - mathjs 评估错误 : (intermediate value)(intermediate value)(intermediate value) is not a function
如果我运行一段代码: obj = {}; obj['number'] = 1; obj['expressionS'] = 'Sin(0.5 * c1)'; obj['c
android - 错误 : String types not allowed (at 'fail' with value) @values/values. xml
我正在为我的应用创建一个带有 Twitter 帐户的登录页面。当我构建我的项目时会发生上述错误。 values/strings.xml @dimen/abc_text_size_medium
mysql - View 中的 SUM(table2.value * table2.value) (+ table1.value)
我在搜索引擎中使用以下 View : CREATE VIEW msr_joined_view AS SELECT table1.id AS msr_id, table1.msr_number, tab
xhtml - 验证错误 "Value Error : background-position Too many values or values are not"如何解决？
为什么验证会返回此错误。如何解决？ ul#navigation li#navigation-3 a.current Value Error : background-position Too
Python 数据帧 : find previous row's value before a specific value with same value in other columns
我有一个数据名如下 import pandas as pd d = { 'Name' : ['James', 'John', 'Peter', 'Thomas', 'Jacob', 'Andr

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

SQL WHERE xx IN ('qq' , 'ww' ...) 性能 - 更多的值(value)，更少的时间？