`

【转载】查询计划中集的势(Cardinality)的计算

 
阅读更多

【转载】查询计划中集的势(Cardinality)的计算

原文:http://www.hellodba.com/reader.php?ID=124&lang=cn

 

 

 

当使用CBO模式的优化器时,oracle在生成查询计划时,会计算各个访问路径的代价,选择代价最小的访问路径作为查询计划。这个选择过程我们可以通过做一个10053trace来观察。

在做代价估算时,有一个很重要的参数作为代价计算的因数,这就扫描字段的集的势(cardinality)。那么这个值是如何计算的呢?这个值的计算根据索引情况及查询条件不同而不同,因而它的计算也比较复杂。下面我们只讨论在使用绑定变量的情况下集的势的计算。

集的势总的计算公式是:

集的势 = MAX(集的势因子 * 记录数, 1)

 

可以看出,影响集的势的值的主要因素是集的势因子。在不同情况下,这个因子的计算公式不同,下面我们就讨论不同情况下的集的势因子的计算。

索引字段

对于建立了索引(可以是复合索引)的字段,如果查询条件是“=”,字段的集的势计算公式如下:

集的势因子 = 1 / 字段上的唯一值数

 

让我们做个测试看,

SQL> create table T_PEEKING3 (a NUMBER, b char(1), c char(5));
 
Table created.
 
SQL>
SQL> create index T_PEEKING3_IDX1 on T_PEEKING3(b, c);
 
Index created.
 
SQL>
SQL> begin
  2    for i in 1..1000 loop
  3      insert into T_PEEKING3 values (i, mod(i, 10), mod(i, 13));
  4    end loop;
  5    commit;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> analyze table T_PEEKING3 compute statistics for table for all indexes for all indexed columns;
 
Table analyzed.

 

表的记录数为1000,字段(B, C)上建立了索引,它们的唯一值数分别为:

SQL> select count(distinct b) from T_PEEKING3;
 
COUNT(DISTINCTB)
----------------
              10
 
SQL>
SQL> select count(distinct c) from T_PEEKING3;
 
COUNT(DISTINCTC)
----------------
              13
 
SQL>
SQL> select count(*) from
  2  (
  3  select distinct b, c from T_PEEKING3
  4  );
 
  COUNT(*)
----------
       130

 

因此,B字段的集的势为round(1/10 * 1000) = 100,

select /*+index(a T_PEEKING3_IDX1)*/ * from T_PEEKING3 a where b=:V;
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=55  Cardinality=100   Bytes=1500
 TABLE ACCESS BY INDEX ROWID       Object name=T_PEEKING3    Cost=55  Cardinality=100  Bytes=1500
  INDEX RANGE SCAN        Object name=T_PEEKING3_IDX1        Cost=1   Cardinality=100  

 

因此,C字段的集的势为round(1/13 * 1000) = 77,

select /*+index(a T_PEEKING3_IDX1)*/ * from T_PEEKING3 a where c=:V;
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=21  Cardinality=77    Bytes=1386
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=21  Cardinality=77    Bytes=1386
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=77    

 

 

 

如果索引字段查询条件是“<”“>”“<=”“>=”,则计算公式为,

集的势因子 = (1 / 字段上的唯一值数) + (1/记录数)

 

例:当查询条件为c > :1,它的集的势为round((1/13 + 1/1000)*1000) = 78

select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where c > :1
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=22  Cardinality=78    Bytes=1404
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=22  Cardinality=78    Bytes=1404
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=78             

 

 

如果索引字段查询条件是in,则计算公式为,

集的势因子 = in条件中的变量数 / 字段上的唯一值数

 

例:当查询条件为c in (:1, :2, :3),它的集的势为round(3/13 * 1000) = 231

select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  c in (:1, :2, :3);
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=57  Cardinality=231   Bytes=4158
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=57  Cardinality=231   Bytes=4158
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=231   

 

 

如果索引字段查询条件是“<>”,则计算公式为,

集的势因子 = (1 – (1/字段上的唯一值数))

这时的集的势值也是这个字段上可以达到的最大集的势值。

 

例:当查询条件为c <> :1,它的集的势为round((1 – 1/13) * 1000) = 923

select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  c <> :1
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=219 Cardinality=923   Bytes=16614
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=219 Cardinality=923   Bytes=16614
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=923   

 

 

当查询条件为not in时,计算就更为复杂了。它是根据not in中的变量值按阶计算的。

集的势因子 = (1 – (1/字段上的唯一值数))^(not in中变量数)

 

例:当查询条件为c not in (:1, :2, :3),它的集的势为round((1 – 1/13)^3 * 1000) = 787

select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  c not in (:1, :2, :3)
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=187 Cardinality=787   Bytes=14166
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=187 Cardinality=787   Bytes=14166
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=787   

非索引字段

当查询条件为 “=”、“in” 时,非索引字段的集的势因子是,

集的势因子 = 1/100

 

例:以下集的势为 1/100 * 1000 = 10

select * from T_PEEKING3 where a = :1;




SELECT STATEMENT, GOAL = CHOOSE                     Cost=2   Cardinality=10    Bytes=180
 TABLE ACCESS FULL Object name=T_PEEKING3   Cost=2   Cardinality=10    Bytes=180

 

当查询条件为“<”、“>”、“<=”、“>=”、“<>”、“not in” 时,非索引字段的集的势因子是,

集的势因子 = 1/20

 

例:以下集的势为 1/100 * 1000 = 10

select * from T_PEEKING3 where a < :1;




SELECT STATEMENT, GOAL = CHOOSE                     Cost=2   Cardinality=10    Bytes=180
 TABLE ACCESS FULL Object name=T_PEEKING3   Cost=2   Cardinality=50    Bytes=180

 

多字段

对于多个字段同时在查询条件中,集的势因子计算公式如下,

集的势因子 字段1的集的势因子 * 字段2的集的势因子 * … *字段n的集的势因子

 

例:以下两个字段的复合集的势为round(((1/10 + 1/1000)*(1/13)) * 1000) = 8,

select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  b > :1 and c = :2;







SELECT STATEMENT, GOAL = CHOOSE             Cost=4   Cardinality=8     Bytes=144
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=4   Cardinality=8     Bytes=144
  INDEX RANGE SCAN        Object name=T_PEEKING3_IDX1        Cost=1   Cardinality=8             

 

例:以下查询的集的势为round(((3/10) * 1/13) * 1000) = 23

select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  b in (:1, :2, :3) and c = :2;
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=7   Cardinality=23    Bytes=414
 INLIST ITERATOR                                    
  TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=7   Cardinality=23    Bytes=414
   INDEX RANGE SCAN Object name=T_PEEKING3_IDX1     Cost=1   Cardinality=23    

 

例:以下查询的集的势为round((2/10) * (1/13 + 1/1000) * 1000) = 16

select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  b in (:1, :2) and c > :2;
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=5   Cardinality=16    Bytes=288
 INLIST ITERATOR                                    
  TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=5   Cardinality=16    Bytes=288
   INDEX RANGE SCAN Object name=T_PEEKING3_IDX1     Cost=1   Cardinality=16             

 

例:以下查询的集的势为round((2/10) * (3/13) * 1000) = 46

select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  b in (:1, :2) and c in (:1, :2, :3);
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=12  Cardinality=46    Bytes=828
 INLIST ITERATOR                                    
  TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=12  Cardinality=46    Bytes=828
   INDEX RANGE SCAN Object name=T_PEEKING3_IDX1     Cost=1   Cardinality=46    

 

例:以下查询的集的势为round((1-1/10) * ((1- 1/13)^2) * 1000) = 767

select /*+index(a T_PEEKING3_IDX1)*/* from T_PEEKING3 a where  b <>:1  and c not in (:2, :3)
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=183 Cardinality=767   Bytes=13806
 TABLE ACCESS BY INDEX ROWID Object name=T_PEEKING3 Cost=183 Cardinality=767   Bytes=13806
  INDEX FULL SCAN Object name=T_PEEKING3_IDX1       Cost=3   Cardinality=767   

 

例:以下查询的集的势为round((1/20) * (1/13 + 1/1000) * 1000) = 4

select * from T_PEEKING3 where a not in (:1) and c > :2;
 
SELECT STATEMENT, GOAL = CHOOSE Cost=68     Cardinality=4    Bytes=732
 TABLE ACCESS FULL Object name=T_PEEKING3   Cost=2   Cardinality=4     Bytes=732        

 

全表扫描

对于全表扫描,如果没有查询条件时,

集的势因子 = 1

 

例:以下集的势为 1 * 1000 = 1000

select * from T_PEEKING3;
 
SELECT STATEMENT, GOAL = CHOOSE                     Cost=2   Cardinality=1000  Bytes=18000
 TABLE ACCESS FULL Object name=T_PEEKING3   Cost=2   Cardinality=1000  Bytes=18000

 

 

【转载】查询计划中集的势(Cardinality)的计算

原文:http://www.hellodba.com/reader.php?ID=124&lang=cn

分享到:
评论

相关推荐

    A Preprocessing Scheme for High-Cardinality Categorical Attributes

    这类问题通常被称为“高基数”(high cardinality)问题,因为“基数”指的是类别变量的不同取值数量。"A Preprocessing Scheme for High-Cardinality Categorical Attributes"这个主题探讨的就是如何有效地处理这类...

    Oracle优化器 Cardinality基数与Selectivity选择性

    Oracle优化器是数据库的核心组成部分,负责为SQL查询生成最佳执行计划。优化器有两种主要类型:基于规则的优化器(RBO)和基于成本的优化器(CBO)。这两种优化器在选择执行路径时遵循不同的策略。 RBO,即Rule-...

    简单解析MySQL中的cardinality异常

    这个数值对于优化查询性能至关重要,因为它影响着MySQL如何选择最佳的查询执行计划。当Cardinality异常时,可能导致数据库查询效率降低,进而影响应用程序的性能。 在上述问题中,报警显示php-fpm进程数量超过阈值...

    Stream summarizer and cardinality estimator..zip

    在数据库系统中,基数估计可以辅助优化查询计划,避免全表扫描,提高查询性能。 综上所述,"Stream summarizer and cardinality estimator"涉及的是Java中使用Stream API处理数据流的方法,以及通过基数估计算法...

    PostgreSQL Like模糊查询优化方案

    综上所述,优化PostgreSQL中的LIKE模糊查询涉及多种策略,包括使用全文搜索、相似度查询、优化索引结构以及调整查询计划。针对具体的应用场景和数据特性,结合这些方法,可以显著提升查询效率,降低数据库的负载,...

    impala单机部署手册

    查询执行 Impala 分为 frontend 和 backend 两个层次,frontend 用 Java 实现(通过 JNI 嵌入 Impalad),负责查询计划生成,而 backend 用 C++ 实现,负责查询执行。Frontend 生成查询计划分为两个阶段: 1. 生成...

    cardinality

    在IT行业中,"cardinality"一词通常与数据库设计、数据结构或集合论中的概念相关。在CSS(层叠样式表)领域,它可能是指选择器的权重或元素出现的次数。这里我们将深入探讨这两个方面的cardinality概念。 首先,...

    怎样看懂Oracle的执行计划

    在这里,我们可以看到,执行计划的每个步骤都有其对应的成本、Cardinality 和 Bytes 等信息。执行计划的顺序是从上到下,从左到右。 Oracle 执行计划是 Oracle 数据库在执行查询语句时所采取的访问路径的表示。了解...

    oracle执行计划详解

    1. **基数(Cardinality)**:指的是执行计划中每个操作预计返回的行数。基数估计对于优化器选择最优的执行策略至关重要。 2. **访问方法(Access Method)**:表示数据库访问数据的方式,例如全表扫描(TABLE ACCESS ...

    ElasticSearch常用查询的Java实现

    ElasticSearch查询term,terms,match,...ElasticSearch查询cardinality,range,extended_stats聚合统计aggregations查询 ElasticSearch查询geo_distance,geo_bounding_box,geo_polygon地图检索geo查询 的Java实现

    Oracle 执行计划

    - 基数(Cardinality):指执行计划中每个步骤预计返回的行数。 - 访问方法(Access Method):指执行计划中用于访问数据的基本方法,如全表扫描(TABLE ACCESS FULL)或索引访问(INDEX UNIQUE SCAN)等。 - 联接...

    基于势均衡多伯努利滤波的SLAM方法.pdf

    针对这些问题,本文提出了一种名为基于势均衡多伯努利滤波的SLAM(Cardinality Balanced Multi-Bernoulli-SLAM,CBMBer-SLAM)算法。 多伯努利滤波是随机有限集(RFS)理论在多目标跟踪和估计问题中的应用,它通过...

    Lec14-查询优化-part 21

    在掌握了谓词选择率后,优化器能够估算每个运算符处理的数据量,进而计算出每个运算符的开销,最终确定整个查询计划的成本。这一过程被称为计划列举(Plan Enumeration)。对于简单的单表查询,优化器可能仅依据启发...

    分布式数据库聚合计算性能优化.pdf

    实验表明,采用高基数粒度的分片片键可以让数据在集群上均匀地分布在各个数据节点上,同时,舍弃索引使用全表查询能够有效提高聚合计算的速度。因此,通过合理的片键选择和索引优化策略,可以有效提高聚合计算性能,...

Global site tag (gtag.js) - Google Analytics