oracle实验记录 (oracle单表选择率与基数计算(2))

最新推荐文章于 2024-08-15 01:26:58 发布
最新推荐文章于 2024-08-15 01:26:58 发布
阅读量116 收藏
点赞数
文章标签： 数据库

 

在查询中使用区间谓词 选择率和基数card计算

先开始,>=,<= 这几种无边界情况(所谓无边界 就是一侧没有边界 )
SQL> create table t5 (a int);

表已创建。

SQL> declare
  2  begin
  3  for i in 1..10000 loop
  4  insert into t5 values(i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> execute dbms_stats.gather_table_stats('SYS','T5');

PL/SQL 过程已成功完成。
SQL> select table_name,density,num_nulls,num_distinct from user_tab_col_statisti
cs where table_name='T5';

TABLE_NAME                        DENSITY  NUM_NULLS NUM_DISTINCT
------------------------------ ---------- ---------- ------------
T5                                  .0001          0        10000
SQL> select num_rows from dba_tables where table_name='T5';

  NUM_ROWS
----------
     10000

SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select count(*) from t5 where a>2;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |  9999 | 29997 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A">2)

SQL> select count(*) from t5 where a>=2;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   | 10000 | 30000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A">=2)

SQL> select count(*) from t5 where a<2;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |     1 |     3 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<2)

SQL> select count(*) from t5 where a<=2;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |     2 |     6 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<=2)

>=，<=是闭区间， >

分析a>2 是一个无边 开区间(所谓无边界 就是一侧没有边界 ，a>10 and a<100就是一个有边界的 两边开区间)
a>2选择率=(high_value – limit) / (high_value – low_value)


SQL> select (10000-2)/(10000-1) from dual;

(10000-2)/(10000-1)
-------------------
          .99989999
card=num_rows*选择率
SQL> select  10000*.99989999 from dual;

10000*.99989999
---------------
      9998.9999～～～～～～～～～～～～～～～可以看到10gr2是向上舍入(ceil)

trace
SINGLE TABLE ACCESS PATH
  Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
  Table: T5  Alias: T5    
    Card: Original: 10000  Rounded: 9999  Computed: 9999.00  Non Adjusted: 9999.00
  Access Path: TableScan
    Cost:  6.14  Resp: 6.14  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2142429
      Resp_io: 6.00  Resp_cpu: 2142429
  Best:: AccessPath: TableScan
         Cost: 6.14  Degree: 1  Resp: 6.14  Card: 9999.00  Bytes: 0

 

分析a>= 是一个无边界，闭区间 ,闭区间的特点就是必须包含闭合值的行（2）
选择率=(high_value – limit) / (high_value – low_value) + 1/num_distinct
SQL> select (10000-2)/(10000-1)+1/10000 from dual;

(10000-2)/(10000-1)+1/10000
---------------------------
                  .99999999

card=10000*.99999999=10000

SQL> select ceil(10000*.99999999) from dual;

CEIL(10000*.99999999)
---------------------
                10000
trace
SINGLE TABLE ACCESS PATH
  Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
  Table: T5  Alias: T5    
    Card: Original: 10000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  6.14  Resp: 6.14  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2142429
      Resp_io: 6.00  Resp_cpu: 2142429
  Best:: AccessPath: TableScan
         Cost: 6.14  Degree: 1  Resp: 6.14  Card: 10000.00  Bytes: 0


 分析a<2无边界 开区间
选择率=(limit – low_value) / (high_value – low_value)**********
card=num_rows*选择率
SQL> select (2-1)/(10000-1) from dual
  2  ;

(2-1)/(10000-1)
---------------
      .00010001

SQL> select 10000*.00010001 from dual;

10000*.00010001
---------------
         1.0001

trace
SINGLE TABLE ACCESS PATH
  Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
  Table: T5  Alias: T5    
    Card: Original: 10000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  6.14  Resp: 6.14  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2142429
      Resp_io: 6.00  Resp_cpu: 2142429
  Best:: AccessPath: TableScan
         Cost: 6.14  Degree: 1  Resp: 6.14  Card: 1.00  Bytes: 0


分析a<=2 无边界，闭区间 需要将闭合值的行加进来
选择率=(limit – low_value) / (high_value – low_value)+1/num_distinct

card=num_rows*选择率
SQL> select (2-1)/(10000-1)+1/10000 from dual
  2  ;

(2-1)/(10000-1)+1/10000
-----------------------
              .00020001

SQL> select 10000*.00020001 from dual
  2  ;

10000*.00020001
---------------
         2.0001

trace
SINGLE TABLE ACCESS PATH
  Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
  Table: T5  Alias: T5    
    Card: Original: 10000  Rounded: 2  Computed: 2.00  Non Adjusted: 2.00
  Access Path: TableScan
    Cost:  6.14  Resp: 6.14  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2142429
      Resp_io: 6.00  Resp_cpu: 2142429
  Best:: AccessPath: TableScan
         Cost: 6.14  Degree: 1  Resp: 6.14  Card: 2.00  Bytes: 0

 

*******************************
接下来看between and, a>x and a=x and a=x and a<=y ,a>x and a<=y 这几种类型 有边界情况


SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select count(*) from t5 where a between 2 and 10;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |    10 |    30 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<=10 AND "A">=2)

SQL> select count(*) from t5 where a>2 and a<10;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |     8 |    24 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<10 AND "A">2)

 

SQL> select count(*) from t5 where a>=2 and a<10;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |     9 |    27 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<10 AND "A">=2)

SQL> select count(*) from t5 where a>2 and a<=10;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |     9 |    27 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<=10 AND "A">2)

SQL> select count(*) from t5 where a>=2 and a<=10;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |    10 |    30 |     6   (0)| 00:00:01 |~~~~~~~~~看到与 between and card一样
---------------------------------------------------------------------------

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

   2 - filter("A"<=10 AND "A">=2)


分析 a between 2 and 10   就等于>= and <=  
选择率=（hight_limit-low_limit）/(hight_value-low_value) +1/num_distinct+1/num_distinct(因为是2个闭区间 所以2个闭合值行都要加进来)
card=num_rows*选择率
SQL> select (10-2)/(10000-1)+1/10000+1/10000 from dual;

(10-2)/(10000-1)+1/10000+1/10000
--------------------------------
                       .00100008

SQL> select 10000*.00100008 from dual;

10000*.00100008
---------------
        10.0008

between and 等同于 〉=and<= 所以  a>=2 and a<=10  card也等于10 计算方法也是一样的
 
trace
SINGLE TABLE ACCESS PATH
  Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
  Table: T5  Alias: T5    
    Card: Original: 10000  Rounded: 10  Computed: 10.00  Non Adjusted: 10.00
  Access Path: TableScan
    Cost:  6.14  Resp: 6.14  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2142929
      Resp_io: 6.00  Resp_cpu: 2142929
  Best:: AccessPath: TableScan
         Cost: 6.14  Degree: 1  Resp: 6.14  Card: 10.00  Bytes: 0

 

分析a>2 and a<10  有边界，两边都开区间
选择率=（hight_limit-low_limit）/(hight_value-low_value)
card=num_rows*选择率
SQL> select (10-2)/(10000-1) from dual;

(10-2)/(10000-1)
----------------
       .00080008

SQL> select 10000*((10-2)/(10000-1)) from dual;

10000*((10-2)/(10000-1))
------------------------
              8.00080008
trace
SINGLE TABLE ACCESS PATH
  Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
  Table: T5  Alias: T5    
    Card: Original: 10000  Rounded: 8  Computed: 8.00  Non Adjusted: 8.00
  Access Path: TableScan
    Cost:  6.14  Resp: 6.14  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2142879
      Resp_io: 6.00  Resp_cpu: 2142879
  Best:: AccessPath: TableScan
         Cost: 6.14  Degree: 1  Resp: 6.14  Card: 8.00  Bytes: 0


分析 a>=2 and a<10 ，a>2 and a<=10 这两个都是 有边界，半开区间（有一闭合的，所以要加上闭合值的行(闭合值分别是 2和10)）
所以他们公式相同~ card相同

选择率=（hight_limit-low_limit）/(hight_value-low_value)+1/num_distinct
card=num_rows*选择率

SQL> select (10-2)/(10000-1)+1/10000 from dual;

(10-2)/(10000-1)+1/10000
------------------------
               .00090008

SQL> select 10000*((10-2)/(10000-1)+1/10000) from dual;

10000*((10-2)/(10000-1)+1/10000)
--------------------------------
                      9.00080008
SINGLE TABLE ACCESS PATH
  Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
  Table: T5  Alias: T5    
    Card: Original: 10000  Rounded: 9  Computed: 9.00  Non Adjusted: 9.00
  Access Path: TableScan
    Cost:  6.14  Resp: 6.14  Degree: 0
      Cost_io: 6.00  Cost_cpu: 2142929
      Resp_io: 6.00  Resp_cpu: 2142929
  Best:: AccessPath: TableScan
         Cost: 6.14  Degree: 1  Resp: 6.14  Card: 9.00  Bytes: 0

 


针对bind 变量情况

SQL> variable a number
SQL> variable b number
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select count(*) from t5 where a>:a;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |   500 |  1500 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A">TO_NUMBER(:A))

SQL> select count(*) from t5 where a>=:a;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |   500 |  1500 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A">=TO_NUMBER(:A))

SQL> select count(*) from t5 where a<:a>

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |   500 |  1500 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"

SQL> select count(*) from t5 where a<=:a;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |   500 |  1500 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<=TO_NUMBER(:A))

SQL> select count(*) from t5 where a between :a and :b;

执行计划
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T5   |    25 |    75 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
   3 - filter("A">=TO_NUMBER(:A) AND "A"<=TO_NUMBER(:B))

SQL> select count(*) from t5 where a>:a and a<:b>

执行计划
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T5   |    25 |    75 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:A)   3 - filter("A">TO_NUMBER(:A) AND "A"

SQL> select count(*) from t5 where a>=:a and a<=:b;

执行计划
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T5   |    25 |    75 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
   3 - filter("A">=TO_NUMBER(:A) AND "A"<=TO_NUMBER(:B))

SQL> select count(*) from t5 where a>:a and a<=:b;

执行计划
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T5   |    25 |    75 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:A)   3 - filter("A">TO_NUMBER(:A) AND "A"<=TO_NUMBER(:B))

 


SQL> select count(*) from t5 where a>=:a and a<:b>

执行计划
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T5   |    25 |    75 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:A)   3 - filter("A">=TO_NUMBER(:A) AND "A"


分析以上可以看出 规则很简单

只是按 是否具有边界计算的
无边界（,>=,<=） 选择率为5%
card=num_rows*选择率
SQL> select 10000*.05 from dual
  2  ;

 10000*.05
----------
       500
有边界 （between and,> and = and<= ,> and<= ,>= and选择率=5%*5%
card=num_rows*选择率
SQL> select 10000*.0025 from dual
  2  ;

10000*.0025
-----------
         25

 

其它情况

首先是超过边界情况
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select count(*) from t5 where a>10001;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |     1 |     3 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A">10001)


分析超过边界的时候oracle 按边 字面值来计算 
选择率=1/num_distinct
card=10000*1/10000=1


SQL> select count(*) from t5 where a>2 and a<=10002;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |  9999 | 29997 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A">2 AND "A"<=10002)

这个oracle检测出 半部分超出边界
选择率=（hight_limit-low_limit）/(hight_value-low_value)+1/num_distinct  oracle并没有用这个公式
SQL> select (10002-2)/(10000-1)+1/10000 from dual;

(10002-2)/(10000-1)+1/10000
---------------------------
                 1.00020001~~~~~~~~~~~~~~~~~~~~~~没这样计算 返回的card当然也不对(10000*1.00020001)

而是只用了左边 a>2的来计算
(high_value – limit) / (high_value – low_value)

SQL> select 10000*((10000-2)/(10000-1)) from dual;

10000*((10000-2)/(10000-1))
---------------------------
                  9998.9999           CARD*选择率

 

SQL> select count(*) from t5 where a>=2 and a<=10002;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   | 10000 | 30000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A">=2 AND "A"<=10002)

 

这个一样 只用了左边 右边超过边界没算
a>=2
(high_value – limit) / (high_value – low_value)+1/num_distinct
SQL> select 10000*((10000-2)/(10000-1)+1/10000) from dual;

10000*((10000-2)/(10000-1)+1/10000)
-----------------------------------
                          9999.9999~~~~~~~~~~~~~~~~~card


SQL> select count(*) from t5 where a>=-3 and a<=10;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |    10 |    30 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<=10 AND "A">=(-3))

oracle 发现左边 边界太小
只计算了 a<=10

选择率=(limit – low_value) / (high_value – low_value)+1/num_distinct

SQL> select 10000*((10-1)/(10000-1)+1/10000) from dual;

10000*((10-1)/(10000-1)+1/10000)
--------------------------------
                      10.0009001

 


SQL> select count(*) from t5 where a>=-3 and a<10;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |     9 |    27 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<10 AND "A">=(-3))

 

同上边一样 发现边界不对，只用了
a<10
选择率=(limit – low_value) / (high_value – low_value)
SQL> select 10000*(10-1)/(10000-1) from dual;

10000*(10-1)/(10000-1)
----------------------
            9.00090009

 

 

 

SQL> select count(*) from t5 where a>10001 and a<=10002;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |     1 |     3 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A">10001 AND "A"<=10002)

当超出了 边界 2边 那么 card=num_rows*1/num_distinct=1

 


另一种情况 or

SQL> select count(*) from t5 where a>3 or a<10 ;

执行计划
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T5   |  9998 | 29994 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

 

or的 选择率公式

a>3+a<10-(a>3*a<10)
a>3选择率=(high_value – limit) / (high_value – low_value)=.99979998
a<10选择率=(limit – low_value) / (high_value – low_value)=.00020002
a>3*a<10=.00019998

SQL> select (10000-3)/(10000-1) from dual;

(10000-3)/(10000-1)
-------------------
          .99979998

SQL> select (3-1)/(10000-1) from dual;

(3-1)/(10000-1)
---------------
      .00020002

SQL> select .99979998*.00020002 from dual;

.99979998*.00020002
-------------------
          .00019998

 

SQL> select .99979998+.00020002-.00019998 from dual;

.99979998+.00020002-.00019998
-----------------------------
                    .99980002

SQL> select 10000*.99980002 from dual;

10000*.99980002
---------------
      9998.0002~~~~~~~~~~~~~~~~~~~card

3个or 是这样的
sel(A) + sel(B) + sel(C) – Sel(A)sel(B) – Sel(B)sel(C) – sel(C)sel(A) + Sel(A)Sel(B)Sel(C)

如果要都是 bind
5% (column < :b1) +5% (column > :b2) -0.25% (column < :b1 and column > :b2) =9.75%

 

混合类~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~简单看下下面类型
SQL> variable a number
SQL> set autotrace traceonly explain
SQL> select count(*) from t5 where a>2 and a<:a>

执行计划
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T5   |   500 |  1500 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter(2   3 - filter("A"2)

SQL> select count(*) from t5 where a>100 and a<:a>

执行计划
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T5   |   495 |  1485 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter(100   3 - filter("A"100)

SQL> select count(*) from t5 where a>:a and a<100;

执行计划
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T5   |     5 |    15 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:A)<100)
   3 - filter("A"<100 AND "A">TO_NUMBER(:A))

SQL> select count(*) from t5 where a>:a and a<=100;

执行计划
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T5   |     5 |    15 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:A)<100)
   3 - filter("A"<=100 AND "A">TO_NUMBER(:A))

上面的 计算采用的是  双谓词计算
选择率=谓词1 选择率+谓词2选择率
例
a>2 and a<:a>A>2无边界开区间 选择率 =(high_value – limit) / (high_value – low_value)=.99989999
SQL> select (10000-2)/(10000-1) from dual;

(10000-2)/(10000-1)
-------------------
          .99989999
a<:a>所以a>2 and a<:a>SQL> select 0.05*.99989999 from dual;

0.05*.99989999
--------------
       .049995

CARD=NUM_ROWS*选择率
SQL> select 10000*.049995 from dual;

10000*.049995
-------------
       499.95


a>:a and a<=100
A<=100是一个无边界 闭区间 选择率=(limit-low_value) / (high_value – low_value)+1/num_distinct=.01000099

SQL> select (100-1)/(10000-1)+1/10000 from dual;

(100-1)/(10000-1)+1/10000
-------------------------
                .01000099

a>:a选择率为5%

a>:a and a<=100 选择率=.00050005
SQL> select 0.05*((100-1)/(10000-1)+1/10000) from dual;

0.05*((100-1)/(10000-1)+1/10000)
--------------------------------
                       .00050005
card=下面值

SQL> select 10000*(0.05*((100-1)/(10000-1)+1/10000)) from dual;

10000*(0.05*((100-1)/(10000-1)+1/10000))
----------------------------------------
                              5.00049505

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-614518/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-614518/

cuiye1492
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle优化器 Cardinality基数与Selectivity选择性
12-14
优化器（optimizer）是oracle数据库内置的一个核心子系统。优化器的目的是按照一定的判断原则来得到它认为的目标SQL在当前的情形下的最高效的执行路径，也就是为了得到目标SQL的最佳执行计划。依据所选择执行计划时所用的判断原则，oracle数据库里的优化器又分为RBO和CBO两种。 一、基于规则的优化器。《RBO: Rule-Based Optimization》      Oracle会在代码里事先为各种类型的执行路径定一个等级，一共15个等级，从等级1到等级15，oracle认为等级1的执行路径是效率最高的，等级15是执行效率最差的。对于等级相同的执行计划，oracle根据目标对象
Oracle SQL Tuning_选择率与基数公式01_单表选择率_没有直方图_在Range内
u010692693的专栏
11-06 194
基本概念 Predicate: 查询中所有的或部分的where子句 Range: 表列中最大值和最小值的范围 NumRows: 表的行数(DBA_TABLES.NUM_ROWS) Card: Cardinality 满足一个谓词的行数 Sel: 满足需求的行数/可用的总行数 Orig Card: 在用谓词过滤前的行数 Comp Card: 满足谓...
Oracle SQL Tuning_选择率与基数公式04_其他类型
u010692693的专栏
11-06 166
单表 No bind peeking a) Equality predicate on bind variable (“col = :b”) Sel = (1 / NDV) * A4Nulls b) Unbounded open or closed (>, <, >=, =<) Sel = 0.05 * A4Nulls c) Bounded closed (“col...
（1）oracle单表选择率（selectivity）——计算执行计划的基数
事后诸葛亮的程序人生（微信：zq9017197）
01-25 4600
CBO优化器是基于对当前经过特定测试的数据集中预期的行比率估计来计算基数的。此处的行数之比是一个数值，称为选择率（selectivity）。得到选择率之后，将其与输入行数进行简单相乘既可得到基数。 在理解选择性之前，必须得对user_tab_col_statistics视图有一定了解： SQL> desc user_tab_col_statistics 名称
Oracle基础表的选择,【Oracle】单表的选择率
weixin_35450735的博客
04-03 139
本文只是记录《基于成本的oracle 优化法则》的简单的笔记(因为要重新搭建RAC 把之前的环境删掉了，oracle 环境还没建好，现在先纸上谈兵一下)优化器并没有智能--只是一个软件！1 对含有空值的字段基本选择率=1/num_distinctnum_nulls=120num_rows=1200选择率=基本选择率*(num_rows-num_nulls)/num_rows=1/12*((1200...
DB2与Oracle的数据仓库技术关键能力对比研究.pdf
10-09
- **位图索引**：Oracle的位图索引通过Bitmap键值映射数据表记录，适合于低基数（即某些列的唯一值较少）的查询优化，能有效减少I/O操作。DB2也支持位图索引，但可能在特定场景下不如Oracle优化得彻底。 - **数据...
ORACLE数据库索引的设计与维护研究.pdf
10-09
2. **Bitmap索引**：在特定场景下，如低基数（较少的唯一值）的列，Bitmap索引更为适用。它使用位图来表示每个索引值，适合于多值比较和复杂查询，但不适用于频繁的更新操作。 3. **索引设计原则** - **选择性**：...
oracle_sql_tuning.rar_oracle
09-22
在Oracle环境中，SQL语句的执行效率直接影响到整个应用系统的响应速度和资源利用率。以下是对"oracle_sql_tuning.rar_oracle"压缩包中的内容进行的详细解释和扩展。 1. **SQL优化基础** - **执行计划**：了解SQL...
Oracle_SQL-Optimization.rar_oracle_sql优化
09-20
通过EXPLAIN PLAN或使用DBMS_XPLAN，可以查看Oracle如何执行查询，包括表的访问方式（全表扫描、索引扫描等）、连接顺序和操作成本。 2. **索引优化**：创建合适的索引能显著提高查询速度。B树索引适用于等值查询，...
oracle实验记录 (oracle单表选择率与基数计算(1))
cuiye1492的博客
09-09 153
单表选择率与 基数(cardinality)有关系，也就是执行计划中的CARD 信息CARD表示oracle 认为此操作将返回多少rows 基本的CARD 计算 就等于 num_rows*选择率 CARD 对于otimi...
webjs 数据库 离线读取本地数据库源码-SAAS 本地化及未来之窗行业应用跨平台架构
最新发布
cybersnow精通 28 门计算机语言，凭借其超凡的技术能力，成功开发过上万个应用，广泛涉及政府、商业、个人等众多领域，甚至在检察院、环保局、公安局等专业场景中也大放异彩。不仅熟练掌握单片机和物联网开发，在软件架构设计方面更是独树一帜，自创了跨平台软件
08-15 154
【代码】webjs 数据库 离线读取本地数据库源码-SAAS 本地化及未来之窗行业应用跨平台架构。
[Qt][Qt窗口][下]详细讲解
SnowK博客
08-13 3655
[Qt][Qt窗口][下]详细讲解
mysql介绍
weixin_57763462的博客
08-14 293
丰富的存储引擎：MySQL 提供了多种存储引擎，如 InnoDB、MyISAM、Memory 等，每种存储引擎都有其特定的应用场景和优势。多语言支持：MySQL 支持多种编程语言，包括 C、C++、Python、Java、Perl、PHP 等，并提供了相应的 API。安全性：MySQL 提供了灵活的权限和密码系统，支持基于主机的验证，并且所有的密码传输都采用加密形式，确保了数据的安全性。管理工具：MySQL 提供了丰富的管理工具，如命令行客户端、图形界面管理工具等，方便用户进行数据库的管理和维护。
贷齐乐系统sql注入漏洞
banliyaoguai的博客
08-12 659
然后在这里取值的时候.和_会区分开，然后我们可以在第一个i_d写我们的payload，在i.d写正常数据用来绕过waf。可以使用使用php小特性和全局污染绕过，在这里的时候会将.转换成下划线，然后桡骨第一个waf。这里由于等号被过滤乐所以使用like，然后查表名的时候由于'被过滤所以使用了16进制编码。首先php在遇到两个相同名字参数时，php是取后一个的,如下图最终i_d的值为1。代码先走这里将输入的数据进行过滤。然后再继续往下走，再进行一个查询。然后继续往下，用=进行分割。
健身房管理系统
寒夜
08-13 827
【代码】springboot健身房管理系统
Mybatis XML 数据源为 Oracle 之批量插入或更新 Merge Into 的具体介绍与使用
欢迎来到 ABin-阿斌的博客：写一生代码，创一世佳话，筑一览芳华。
08-12 670
由于近期在所开发的项目中，对于数据入库，有存在即更新，不存在则插入的需求，因此发现了 Oracle中的 MergeInto 命令。本文将对MergeInto的用法进行介绍并将 MergeInto 和批量插入进行结合，同时还会对在 MergeInto 开发中遇到的问题进行总结。MergeInto 命令是 Oracle9i 中新增的命令，有了 MergeInto 语句，我们对数据能够不仅仅只做单一的插入或单一的更新，而是可以将更新与插入一起操作。
基于JAVA生产制造执行系统设计与实现(源码+论文+讲解等)
程序员阿龙的博客
08-13 901
当下，如果 还依然使用纸质文档来记录并且管理相关信息，可能会出现很多问题，比如原始文件的丢失，因为采用纸质文档，很容易受潮或者怕火，不容易备份，需要花费大量的人员和资金来管理用纸质文档存储的信息，最重要的是数据出现问题寻找起来很麻烦，并且修改也困难，如果还这样操作会造成很大的资源浪费和经济损失。所以，对于本课题研究的产品生产制造信息的管理就需要有一款软件来代替手工操作，因此MES生产制造执行系统就应运而生。MES生产制造执行系统运用的工具包括Eclipse，Tomcat以及Navicat等。
基于vue框架的NBA球星管理系统1878g（程序+源码+数据库+调试部署+开发环境）系统界面在最后面。
h2345678的博客
08-12 1008
随着互联网的普及和体育产业的蓬勃发展，篮球运动在全球范围内拥有庞大的粉丝群体，NBA（美国职业篮球联赛）作为其中的佼佼者，其球星更是备受关注。传统的球星信息管理方式往往依赖于纸质文档或简单的电子表格，这种方式不仅效率低下，难以实现数据的即时更新与共享，也无法满足球迷和媒体对球星信息深度挖掘的需求。因此，开发一套基于Vue框架的NBA球星管理系统，通过信息化手段整合球星信息，实现数据的快速录入、存储、查询与分析，对于提升管理效率、满足用户需求具有重要意义。，处理前端请求并提供业务逻辑。
《零散知识点 · Vue-admin 模板项目》
山人行
08-12 811
permission.js 里面的：const whiteList = [‘/login’] // 不重定向白名单配在route里面的顶部即可。this获取方式：提示：{{this.$route.params.alert}}提示：{{this.$route.query.alert}}
Oracle普通表转分区表详解及步骤
接下来，开始创建分区表，可以选择基于时间（如月份、年份）、区间或其他逻辑划分方式。具体的创建语法会依赖于你的业务需求和Oracle版本。 创建完成后，通过IMP或IMPDP工具将导出的数据导入到新分区表中。例如，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值