CBO中基数（cardinality）、可选择率（selectivity）的计算公式

Cardinality（基数）是Oracle预估的返回行数，即对目标SQL的某个具体执行步骤的执行结果所包含记录数的估算值。如果是针对整个目标SQL，那么此时的Cardinality就表示该SQL最终执行结果所包含记录数的估算值。例如，一张表T有1000行数据，列COL1上没有直方图，没有空值，并且不重复的值（Distinct Value）有500个。那么，在使用条件“WHERE COL1=”去访问表的时候，优化器会假设数据均匀分布，它估计出会有1000/500=2行被选出来，2就是这步操作的Cardinality。通常情况下，Cardinality越准确，生成的执行计划就会越高效。

可选择率（Selectivity）是指施加指定谓词条件后返回结果集的记录数占未施加任何谓词条件的原始结果集的记录数的比率。可选择率的取值范围显然是0～1，它的值越小，就表明可选择性越好。当可选择率为1时的可选择性是最差的。CBO就是用可选择率来估算对应结果集的Cardinality的，可选择率和Cardinality之间的关系如下所示：

cardinality=NUM_ROWS*selectivity

其中，NUM_ROWS表示表的总行数。

在Oracle数据库中，Oracle会默认认为SQL语句的WHERE条件中出现的各列彼此之间是独立的，是没有关联关系的。所以，如果目标SQL语句各列之间是以AND来组合的话，那么该SQL语句整个WHERE条件的组合可选择率就等于各个列各自施加查询条件后可选择率的乘积。在得到了SQL语句整个WHERE条件的组合可选择率后，Oracle会用它来估算整个SQL语句返回结果集的Cardinality，估算的方法就是用目标表的总记录数（NUM_ROWS）乘组合可选择率。但Oracle默认认为的各列之间是独立的、没有关联关系的前提条件并不总是正确的，在实际的应用中各列之间有关联关系的情况实际上并不罕见。在这种情况下如果还用上述计算方法来计算目标SQL语句整个WHERE条件的组合可选择率并用它来估算返回结果集的Cardinality的话，那么估算结果可能就会与实际结果有较大的偏差，进而可能导致CBO选错执行计划，所以Oracle又引入了动态采样和多列统计信息。

下表给出了一些常见的可选择率计算公式：

下面给出一个示例：

DROP TABLE T_ROWS_20170605_LHR;

CREATE TABLE T_ROWS_20170605_LHR AS SELECT ROWNUM ID,'NAME1' SAL FROM DUAL CONNECT BY LEVEL<=10000;

UPDATE T_ROWS_20170605_LHR T SET T.ID='' WHERE T.ID<=100;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_ROWS_20170605_LHR',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',estimate_percent => 100);

LHR@orclasm > COL LOW_VALUE FORMAT A20

LHR@orclasm > COL HIGH_VALUE FORMAT A20

LHR@orclasm > SELECT D.LOW_VALUE,D.HIGH_VALUE,UTL_RAW.CAST_TO_NUMBER(D.LOW_VALUE) LOW_VALUE2,UTL_RAW.CAST_TO_NUMBER(D.HIGH_VALUE) HIGH_VALUE2, D.NUM_DISTINCT,D.NUM_NULLS FROM DBA_TAB_COL_STATISTICS D WHERE D.TABLE_NAME = 'T_ROWS_20170605_LHR' AND D.OWNER = 'LHR' AND D.COLUMN_NAME='ID';

LOW_VALUE            HIGH_VALUE           LOW_VALUE2 HIGH_VALUE2 NUM_DISTINCT  NUM_NULLS

-------------------- -------------------- ---------- ----------- ------------ ----------

C20202               C302                        101       10000         9900        100

LHR@orclasm > SELECT MIN(T.ID),DUMP(MIN(T.ID),16) LOW_VALUE,MAX(T.ID),DUMP(MAX(T.ID),16) HIGH_VALUE FROM  T_ROWS_20170605_LHR T;

 MIN(T.ID) LOW_VALUE             MAX(T.ID) HIGH_VALUE

---------- -------------------- ---------- --------------------

       101 Typ=2 Len=3: c2,2,2       10000 Typ=2 Len=2: c3,2

下面分别执行如下4条SQL语句并获取执行计划：

SELECT COUNT(1) FROM T_ROWS_20170605_LHR T WHERE T.ID=1000;--1

SELECT COUNT(1) FROM T_ROWS_20170605_LHR T WHERE T.ID>1000; --9000

SELECT COUNT(1) FROM T_ROWS_20170605_LHR T WHERE T.ID>=1000; --9001

SELECT COUNT(1) FROM T_ROWS_20170605_LHR T WHERE T.ID BETWEEN 1000 AND 1100; --101

LHR@orclasm > set autot on exp

LHR@orclasm > SELECT COUNT(1) FROM T_ROWS_20170605_LHR T WHERE T.ID=1000;

  COUNT(1)

----------

         1

Execution Plan

----------------------------------------------------------

Plan hash value: 612708570

------------------------------------------------------------------------------------------

| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                     |     1 |     4 |     9   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |                     |     1 |     4 |            |          |

|*  2 |   TABLE ACCESS FULL| T_ROWS_20170605_LHR |     1 |     4 |     9   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("T"."ID"=1000)

-- ROUND(NUM_ROWS*(1/NUM_DISTINCT)*((NUM_ROWS-NUM_NULLS)/NUM_ROWS))

LHR@orclasm > SELECT ROUND(10000*1/9900*((10000-100)/10000)) VALUE FROM DUAL;

     VALUE

----------

         1

LHR@orclasm > SELECT COUNT(1) FROM T_ROWS_20170605_LHR T WHERE T.ID>1000;

  COUNT(1)

----------

      9000

Execution Plan

----------------------------------------------------------

Plan hash value: 612708570

------------------------------------------------------------------------------------------

| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                     |     1 |     4 |     9   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |                     |     1 |     4 |            |          |

|*  2 |   TABLE ACCESS FULL| T_ROWS_20170605_LHR |  9001 | 36004 |     9   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("T"."ID">1000)

--ROUND(NUM_ROWS*((HIGH_VALUE-VAL)/(HIGH_VALUE-LOW_VALUE))*((NUM_ROWS-NUM_NULLS)/NUM_ROWS))

LHR@orclasm > SELECT ROUND(10000*((10000-1000)/(10000-101))*((10000-100)/10000)) VALUE FROM DUAL;

     VALUE

----------

      9001

LHR@orclasm > SELECT COUNT(1) FROM T_ROWS_20170605_LHR T WHERE T.ID>=1000;

  COUNT(1)

----------

      9001

Execution Plan

----------------------------------------------------------

Plan hash value: 612708570

------------------------------------------------------------------------------------------

| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                     |     1 |     4 |     9   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |                     |     1 |     4 |            |          |

|*  2 |   TABLE ACCESS FULL| T_ROWS_20170605_LHR |  9002 | 36008 |     9   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("T"."ID">=1000)

--ROUND(NUM_ROWS*((HIGH_VALUE-VAL)/(HIGH_VALUE-LOW_VALUE)+1/NUM_DISTINCT)*((NUM_ROWS-NUM_NULLS)/NUM_ROWS))

LHR@orclasm > SELECT ROUND(10000*((10000-1000)/(10000-101)+1/9900)*((10000-100)/10000)) VALUE FROM DUAL;

     VALUE

----------

      9002

LHR@orclasm > SELECT COUNT(1) FROM T_ROWS_20170605_LHR T WHERE T.ID BETWEEN 1000 AND 1100;

  COUNT(1)

----------

       101

Execution Plan

----------------------------------------------------------

Plan hash value: 612708570

------------------------------------------------------------------------------------------

| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                     |     1 |     4 |     9   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |                     |     1 |     4 |            |          |

|*  2 |   TABLE ACCESS FULL| T_ROWS_20170605_LHR |   102 |   408 |     9   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("T"."ID"<=1100 AND "T"."ID">=1000)

LHR@orclasm >

--ROUND(NUM_ROWS*(((VAL2-VAL1)/(HIGH_VALUE-LOW_VALUE)+2/NUM_DISTINCT)*((NUM_ROWS-NUM_NULLS)/NUM_ROWS)))

LHR@orclasm > SELECT ROUND(10000*(((1100-1000)/(10000-101)+2/9900)*((10000-100)/10000))) VALUE FROM DUAL;

     VALUE

----------

       102

可见预估行数和用公式计算出来的结果相吻合。

下面再查看有频率直方图的时候基数的计算。

DROP TABLE T_ROWS_20170605_LHR;

CREATE TABLE T_ROWS_20170605_LHR AS SELECT ROWNUM ID,'NAME1' SAL FROM DUAL CONNECT BY LEVEL<=10000;

UPDATE T_ROWS_20170605_LHR T SET T.ID='' WHERE T.ID<=100;

UPDATE T_ROWS_20170605_LHR SET ID=2 WHERE ID BETWEEN 101 AND 200;

UPDATE T_ROWS_20170605_LHR SET ID=3 WHERE ID BETWEEN 200 AND 3000;

UPDATE T_ROWS_20170605_LHR SET ID=9 WHERE ID BETWEEN 3000 AND 9999;

SELECT T.ID,COUNT(*) FROM T_ROWS_20170605_LHR T GROUP BY T.ID;

查看数据分布：

LHR@orclasm > SELECT T.ID,COUNT(*) FROM T_ROWS_20170605_LHR T GROUP BY T.ID;

        ID   COUNT(*)

---------- ----------

                  100

     10000          1

         2        100

         3       2800

         9       6999

收集频率直方图：

LHR@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_ROWS_20170605_LHR',CASCADE=>TRUE,METHOD_OPT=>'FOR COLUMNS ID SIZE 6',estimate_percent => 100);

PL/SQL procedure successfully completed.

LHR@orclasm > SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_NULLS,D.NUM_BUCKETS,D.HISTOGRAM,D.DENSITY FROM Dba_Tab_Columns D WHERE D.TABLE_NAME = 'T_ROWS_20170605_LHR' AND D.COLUMN_NAME='ID';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM          DENSITY

------------------------------ ------------ ---------- ----------- --------------- ----------

ID                                        4        100           4 FREQUENCY       .000050505

LHR@orclasm > COL COLUMN_NAME FORMAT A6

LHR@orclasm > SELECT TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,NVL((ENDPOINT_NUMBER-(LAG(ENDPOINT_NUMBER) OVER (ORDER BY ENDPOINT_VALUE))),ENDPOINT_NUMBER) COUNTS FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME='T_ROWS_20170605_LHR' AND COLUMN_NAME='ID';

TABLE_NAME                     COLUMN ENDPOINT_NUMBER ENDPOINT_VALUE     COUNTS

------------------------------ ------ --------------- -------------- ----------

T_ROWS_20170605_LHR            ID                 100              2        100

T_ROWS_20170605_LHR            ID                2900              3       2800

T_ROWS_20170605_LHR            ID                9899              9       6999

T_ROWS_20170605_LHR            ID                9900          10000          1

当目标列有频率直方图并且对目标列施加等值查询条件时，如果查询条件的输入值等于目标列的某个Bucket的ENDPOINT_VALUE，那么cardinality=Current_ENDPOINT_NUMBER-Previous_ENDPOINT_NUMBER：

LHR@orclasm > SET AUTOT ON

LHR@orclasm > SELECT COUNT(1) FROM T_ROWS_20170605_LHR T WHERE T.ID=3;

  COUNT(1)

----------

      2800

Execution Plan

----------------------------------------------------------

Plan hash value: 612708570

------------------------------------------------------------------------------------------

| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                     |     1 |     3 |     9   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |                     |     1 |     3 |            |          |

|*  2 |   TABLE ACCESS FULL| T_ROWS_20170605_LHR |  2800 |  8400 |     9   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("T"."ID"=3)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

         23  consistent gets

          0  physical reads

          0  redo size

        526  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

可见，预估行数为2800，和直方图中存储的值吻合（2900-100）。

当目标列有频率直方图并且对目标列施加等值查询条件时，如果查询条件的输入值不等于目标列的任意一个Bucket的ENDPOINT_VALUE，那么cardinality=MIN(Current_ENDPOINT_NUMBER-Previous_ENDPOINT_NUMBER)/2：

LHR@orclasm > SELECT COUNT(1) FROM T_ROWS_20170605_LHR T WHERE T.ID=4;

  COUNT(1)

----------

         0

Execution Plan

----------------------------------------------------------

Plan hash value: 612708570

------------------------------------------------------------------------------------------

| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                     |     1 |     3 |     9   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |                     |     1 |     3 |            |          |

|*  2 |   TABLE ACCESS FULL| T_ROWS_20170605_LHR |     1 |     3 |     9   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("T"."ID"=4)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

         23  consistent gets

          0  physical reads

          0  redo size

        525  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

LHR@orclasm > select round(1/2) from dual;

ROUND(1/2)

----------

         1

在直方图中，由于MIN(Current_ENDPOINT_NUMBER-Previous_ENDPOINT_NUMBER)=1，所以，ROUND(1/2)=1，和执行计划中的预估行数相吻合。

在理解选择性之前，必须得对user_tab_col_statistics视图有一定了解：

[sql] view plain copy

 print?

1. SQL> desc user_tab_col_statistics  
2.  名称                                      是否为空? 类型  
3.  ----------------------------------------- -------- ----------------------------  
4.  TABLE_NAME                                         VARCHAR2(30)  表名  
5.  COLUMN_NAME                                        VARCHAR2(30)  列名  
6.  NUM_DISTINCT                                       NUMBER        列中distinct值的数目  
7.  LOW_VALUE                                          RAW(32)       列的最小值  
8.  HIGH_VALUE                                         RAW(32)       列的最大值  
9.  DENSITY                                            NUMBER        当对列创建了直方图，则值不再等于1/NUM_DISTINCT。  
10.  NUM_NULLS                                          NUMBER        列中的NULL值数目。  
11.  NUM_BUCKETS                                        NUMBER        Number of buckets in histogram for the column  
12.  LAST_ANALYZED                                      DATE          最近分析时间。  
13.  SAMPLE_SIZE                                        NUMBER        分析样本大小。  
14.  GLOBAL_STATS                                       VARCHAR2(3)   对分区采样，则-NO，否则-YES。  
15.  USER_STATS                                         VARCHAR2(3)   统计信息由用户导入，则YES，否则-NO。  
16.  AVG_COL_LEN                                        NUMBER        列的平均长度(以字节为单位)  
17.  HISTOGRAM                                          VARCHAR2(15)  Indicates existence/type of histogram: NONE、FREQUENCY、HEIGHT BALANCED  

下面创建一张测试表，并收集统计信息：

[sql] view plain copy

 print?

1. SQL> create table audience as  
2.   2  select  
3.   3    trunc(dbms_random.value(1,13))  month_no  
4.   4  from  
5.   5    all_objects  
6.   6  where  
7.   7    rownum <= 1200  
8.   8  ;  
9.   
10. 表已创建。  
11.   
12. SQL> begin  
13.   2    dbms_stats.gather_table_stats(  
14.   3      user,  
15.   4      'audience',  
16.   5      cascade => true,  
17.   6      estimate_percent => null,  
18.   7     );ethod_opt => 'for all columns size 1'  
19.     method_opt => 'for all columns size 1'  
20.   8  );  
21.   9  end;  
22.  10  /  
23.   
24. PL/SQL 过程已成功完成。  

先查看一下上面表和列的统计信息：

[sql] view plain copy

 print?

1. SQL> select t.TABLE_NAME, t.NUM_ROWS, t.BLOCKS, t.SAMPLE_SIZE  
2.   2    from user_tables t;  
3.   
4. TABLE_NAME   NUM_ROWS     BLOCKS SAMPLE_SIZE  
5. ---------- ---------- ---------- -----------  
6. AUDIENCE         1200          5        1200  
7.   
8. SQL> select s.table_name,  
9.        s.column_name,  
10.        s.num_distinct,  
11.   4         s.low_value,  
12.        s.high_value,  
13.        s.density,  
14.   7         s.num_nulls,  
15.   8         s.sample_size,  
16.   9         s.avg_col_len  
17.  10    from user_tab_col_statistics s;  
18.   
19. TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY  NUM_NULLS SAMPLE_SIZE AVG_COL_LEN  
20. ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------- -----------  
21. AUDIENCE   MONTH_NO             12 C102       C10D       .083333333          0        1200           3  
22.   
23. SQL> select rawtohex(1), rawtohex(12) from dual;  
24.   
25. RAWT RAWT  
26. ---- ----  
27. C102 C10D  

[sql] view plain copy

 print?

1. SQL> select dump(1,16),dump(12,16) from dual;  
2.    
3. DUMP(1,16)        DUMP(12,16)  
4. ----------------- -----------------  
5. Typ=2 Len=2: c1,2 Typ=2 Len=2: c1,d  
6.    
7. SQL> select utl_raw.cast_to_number('c102'),utl_raw.cast_to_number('c10d') from dual;  
8.    
9. UTL_RAW.CAST_TO_NUMBER('C102') UTL_RAW.CAST_TO_NUMBER('C10D')  
10. ------------------------------ ------------------------------  
11.                              1                             12   --可以看见上面的LOW_VALUE和HIGH_VALUE的值分别就是1和12.  

[sql] view plain copy

 print?

1. SQL> select count(a.month_no) from AUDIENCE a;  
2.   
3. COUNT(A.MONTH_NO)  
4. -----------------  
5.              1200     --可以看见，这里的值和NUM_ROWS是一样的。  
6.   
7. SQL> select count(distinct a.month_no) from AUDIENCE a;  
8.   
9. COUNT(DISTINCTA.MONTH_NO)  
10. -------------------------  
11.                        12       --可以看见，这里的值也和NUM_DISTINCT的值是一样的。  

[sql] view plain copy

 print?

1. SQL> select 1/12 from dual;  
2.   
3.       1/12  
4. ----------  
5. .083333333               --这里的值和DENSITY一样的，计算公式为1/NUM_DISTINCT。  

1、假如在上面创建了一张表，里面包含1200个人，如何才能确定其中有多少人的生日是在12月份。

[sql] view plain copy

 print?

1. SQL> select count(*) from AUDIENCE where month_no=12;  
2.   
3. 执行计划  
4. ----------------------------------------------------------  
5. Plan hash value: 3337892515  
6.   
7. -------------------------------------------------------------------------------  
8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
9. -------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     3   (0)| 00:00:01 |  
13. -------------------------------------------------------------------------------  
14.   
15. Predicate Information (identified by operation id):  
16. ---------------------------------------------------  
17.   
18.    2 - filter("MONTH_NO"=12)  

可以看见CBO计算出1200里面，12月份生日的人是100人（在ID=2行的rows）。这和我们通常所理解的是一样的，我们知道月份只有12个，在1200人中在某一个月出生的人，算概率也是100人（CBO也是这样做得）。

计算方法为：DENSITY * NUM_ROWS = 1 / 12 * 1200 = 100。

2、现在假设有10%的人不记得自己的生日了，那么CBO会怎么计算呐？

[sql] view plain copy

 print?

1. SQL> drop table audience purge;  
2.   
3. 表已删除。  
4.   
5. SQL> create table audience as  
6.   2  select  
7.   3    rownum        id,  
8.   4    trunc(dbms_random.value(1,13))  month_no  
9.   5  from  
10.   6    all_objects  
11.   7  where  
12.   8    rownum <= 1200;  
13.   
14. 表已创建。  
15.   
16. SQL> update    
17.   2    audience  
18.   3  set  month_no = null  
19.   4  where  mod(id,10) = 0;         --10%的用户不记得自己的生日。  
20.   
21. 已更新120行。  
22.   
23. SQL> commit;  
24.   
25. 提交完成。  
26.   
27. SQL> begin  
28.   2    dbms_stats.gather_table_stats(  
29.   3      user,  
30.   4      'audience',  
31.   5      cascade => true,  
32.   6      estimate_percent => null,  
33.   7      method_opt => 'for all columns size 1'  
34.   8    );  
35.   9  end;  
36.  10  /  
37.   
38. PL/SQL 过程已成功完成。  
39.   
40. SQL> select t.TABLE_NAME, t.NUM_ROWS, t.BLOCKS, t.SAMPLE_SIZE from user_tables t;  
41.   
42. TABLE_NAME   NUM_ROWS     BLOCKS SAMPLE_SIZE  
43. ---------- ---------- ---------- -----------  
44. AUDIENCE         1200          5        1200  
45.   
46. SQL> select s.table_name,  
47.   2         s.column_name,  
48.   3         s.num_distinct,  
49.   4         s.low_value,  
50.   5         s.high_value,  
51.   6         s.density,  
52.   7         s.num_nulls,  
53.   8         s.sample_size,  
54.   9         s.avg_col_len  
55.  10    from user_tab_col_statistics s;  
56.   
57. TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY  NUM_NULLS SAMPLE_SIZE AVG_COL_LEN  
58. ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------- -----------  
59. AUDIENCE   MONTH_NO             12 C102       C10D       .083333333        120        1080           3   --这里可以看见，NUM_NULLS的值确实为120。  
60. AUDIENCE   ID                 1200 C102       C20D       .000833333          0        1200           4  

[sql] view plain copy

 print?

1. SQL> select count(*) from AUDIENCE where month_no=12;  
2.   
3. 执行计划  
4. ----------------------------------------------------------  
5. Plan hash value: 3337892515  
6.   
7. -------------------------------------------------------------------------------  
8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
9. -------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |    90 |   270 |     3   (0)| 00:00:01 |  
13. -------------------------------------------------------------------------------  
14.   
15. Predicate Information (identified by operation id):  
16. ---------------------------------------------------  
17.   
18.    2 - filter("MONTH_NO"=12)  

调整后的选择率：DENSITY * ((NUM_ROWS-NUM_NULLS)/NUM_ROWS) = 1 / 12 * ((1200 - 120) / 1200) = 0.075。

返回的记录数（ROWS）：调整后的选择率 * NUM_ROWS = 0.075 * 1200 = 90行。

或者我们可以换一种方法思考，通过前面可以很容易的知道12分月有100人生日（其中这里就包含了不记得生日的人）。然后1200人中有10%的人不记得自己的生日，也就是120，那么12月份不记得自己生日的人就平摊到10个人，100-10=90。

3、现在假如我们想知道在6、7、8月份生日的人有多少呐？

[sql] view plain copy

 print?

1. SQL> select count(*) from AUDIENCE where month_no in(6,7,8);  
2.   
3. 执行计划  
4. ----------------------------------------------------------  
5. Plan hash value: 3337892515  
6.   
7. -------------------------------------------------------------------------------  
8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
9. -------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |   270 |   810 |     3   (0)| 00:00:01 |  
13. -------------------------------------------------------------------------------  
14.   
15. Predicate Information (identified by operation id):  
16. ---------------------------------------------------  
17.   
18.    2 - filter("MONTH_NO"=6 OR "MONTH_NO"=7 OR "MONTH_NO"=8)  

6、7、8月份的选择率：6月份选择率 + 7月份选择率 + 8月份选择率 = 0.075 * 3 = 0.225

返回的记录数（ROWS）：6、7、8月份的选择率 * NUM_ROWS = 0.225 * 1200 = 270行。

4、下面来一个更复杂一点的，我们想知道不在6、7、8月份生日的人有多少呐？

[sql] view plain copy

 print?

1. SQL> select count(*) from AUDIENCE where month_no not in(6,7,8);  
2.   
3. 执行计划  
4. ----------------------------------------------------------  
5. Plan hash value: 3337892515  
6.   
7. -------------------------------------------------------------------------------  
8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
9. -------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |   674 |  2022 |     3   (0)| 00:00:01 |  
13. -------------------------------------------------------------------------------  
14.   
15. Predicate Information (identified by operation id):  
16. ---------------------------------------------------  
17.   
18.    2 - filter("MONTH_NO"<>6 AND "MONTH_NO"<>7 AND "MONTH_NO"<>8)  

选择率：1 - 6、7、8月份的选择率 = 1 - 0.075 * 3

返回记录数：(1-0.075*3)*1200 = 930。

month_no in{specific list} 的基数 + month_no not in{specific list} 的基数 = NUM_ROWS，这里计算出来是相等的，但是在数据库中看见的却不想等，需要注意！

5、现在我们求8月份以后出生的人，不包含8月份。

[sql] view plain copy

 print?

1. SQL> select count(*) from AUDIENCE where month_no>8;  
2.   
3. 执行计划  
4. ----------------------------------------------------------  
5. Plan hash value: 3337892515  
6.   
7. -------------------------------------------------------------------------------  
8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
9. -------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |   393 |  1179 |     3   (0)| 00:00:01 |  
13. -------------------------------------------------------------------------------  
14.   
15. Predicate Information (identified by operation id):  
16. ---------------------------------------------------  
17.   
18.    2 - filter("MONTH_NO">8)  

选择率：((HIGH_VALUE - LIMIT) / (HIGH_VALUE - LOW_VALUE)) * ((NUM_ROWS - NUM_NULLS) / NUM_ROWS)

返回的记录数：选择率 * NUM_ROWS = ((HIGH_VALUE - LIMIT) / (HIGH_VALUE - LOW_VALUE)) * ((NUM_ROWS - NUM_NULLS) / NUM_ROWS) * NUM_ROWS = round(((12-8)/(12-1))*((1200-120)/1200)*1200) = 393。

如果是求8月份以后出生的人，包含8月份。

[sql] view plain copy

 print?

1. SQL> select count(*) from AUDIENCE where month_no>=8;  
2.   
3. 执行计划  
4. ----------------------------------------------------------  
5. Plan hash value: 3337892515  
6.   
7. -------------------------------------------------------------------------------  
8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
9. -------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |   483 |  1449 |     3   (0)| 00:00:01 |  
13. -------------------------------------------------------------------------------  
14.   
15. Predicate Information (identified by operation id):  
16. ---------------------------------------------------  
17.   
18.    2 - filter("MONTH_NO">=8)  

选择率：((HIGH_VALUE - LIMIT) / (HIGH_VALUE - LOW_VALUE) + 1 / DENSITY) * ((NUM_ROWS - NUM_NULLS) / NUM_ROWS)

返回记录数：选择率 * NUM_ROWS = ((HIGH_VALUE - LIMIT) / (HIGH_VALUE - LOW_VALUE) + 1 / DENSITY) * ((NUM_ROWS - NUM_NULLS) / NUM_ROWS) * NUM_ROWS = round(((12-8)/(12-1)+1/12)*((1200-120)/1200)*1200) = 483。

如果是<8，选择率：((LIMIT - LOW_VALUE) / (HIGH_VALUE - LOW_VALUE)) * ((NUM_ROWS - NUM_NULLS) / NUM_ROWS)

如果是<=8，选择率：((LIMIT - LOW_VALUE) / (HIGH_VALUE - LOW_VALUE) + 1 / DENSITY) * ((NUM_ROWS - NUM_NULLS) / NUM_ROWS)

6、现在我们想知道6月份到8月份出生的人的数量？

[sql] view plain copy

 print?

1. SQL> select count(*) from AUDIENCE where month_no>=6 and month_no<=8;  
2.   
3. 执行计划  
4. ----------------------------------------------------------  
5. Plan hash value: 3337892515  
6.   
7. -------------------------------------------------------------------------------  
8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
9. -------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |   376 |  1128 |     3   (0)| 00:00:01 |  
13. -------------------------------------------------------------------------------  
14.   
15. Predicate Information (identified by operation id):  
16. ---------------------------------------------------  
17.   
18.    2 - filter("MONTH_NO">=6 AND "MONTH_NO"<=8)  

选择率：((HIGH_LIMIT - LOW_LIMIT) / (HIGH_VALUE - LOW_VALUE) + 1 / DENSITY + 1 / DENSITY) * ((NUM_ROWS - NUM_NULLS) / NUM_ROWS)

返回记录数：round(((8-6)/(12-1)+1/12+1/12)*((1200-120)/1200)*1200) = 376。

7、下面看两个谓词的情况下，CBO是怎么计算选择率的。

[sql] view plain copy

 print?

1. SQL> drop table audience purge;  
2.   
3. 表已删除。  
4.   
5. SQL> create table audience as  
6.   2  select  
7.   3  rownum id,  
8.   4  trunc(dbms_random.value(1,13))month_no,  
9.   5  trunc(dbms_random.value(1,16))eu_country  
10.   6  from  
11.   7  all_objects  
12.   8  where  
13.   9  rownum <= 1200;  
14.   
15. 表已创建。  
16.   
17. SQL> begin  
18.   2  dbms_stats.gather_table_stats(  
19.   3  user,  
20.   4  'audience',  
21.   5  cascade => true,  
22.   6  estimate_percent => null,  
23.   7  method_opt => 'for all columns size 1'  
24.   8  );  
25.   9  end;  
26.  10  /  
27.   
28. PL/SQL 过程已成功完成。  
29.   
30. SQL> select t.TABLE_NAME, t.NUM_ROWS, t.BLOCKS, t.SAMPLE_SIZE from user_tables t;  
31.   
32. TABLE_NAME   NUM_ROWS     BLOCKS SAMPLE_SIZE  
33. ---------- ---------- ---------- -----------  
34. AUDIENCE         1200          6        1200  
35.   
36. SQL> select s.table_name,  
37.   2         s.column_name,  
38.   3         s.num_distinct,  
39.   4         s.low_value,  
40.   5         s.high_value,  
41.   6         s.density,  
42.   7         s.num_nulls,  
43.   8         s.sample_size,  
44.   9         s.avg_col_len  
45.  10    from user_tab_col_statistics s;  
46.   
47. TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY  NUM_NULLS SAMPLE_SIZE AVG_COL_LEN  
48. ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------- -----------  
49. AUDIENCE   EU_COUNTRY           15 C102       C110       .066666667          0        1200           3  
50. AUDIENCE   MONTH_NO             12 C102       C10D       .083333333          0        1200           3  
51. AUDIENCE   ID                 1200 C102       C20D       .000833333          0        1200           4  

[sql] view plain copy

 print?

1. SQL> select count(*) from audience where month_no=12 and eu_country=8;  
2.   
3. 执行计划  
4. ----------------------------------------------------------  
5. Plan hash value: 3337892515  
6.   
7. -------------------------------------------------------------------------------  
8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
9. -------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |          |     1 |     6 |     3   (0)| 00:00:01 |  
11. |   1 |  SORT AGGREGATE    |          |     1 |     6 |            |          |  
12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |     7 |    42 |     3   (0)| 00:00:01 |  
13. -------------------------------------------------------------------------------  
14.   
15. Predicate Information (identified by operation id):  
16. ---------------------------------------------------  
17.   
18.    2 - filter("EU_COUNTRY"=8 AND "MONTH_NO"=12)  

选择率：month_no选择率 * eu_contry选择率 = 1/12 * 1/15

返回记录：round(1/12*1/15*1200) = 7。

[sql] view plain copy

 print?

1. SQL> select count(*) from audience where month_no=12 or eu_country=8;  
2.   
3. 执行计划  
4. ----------------------------------------------------------  
5. Plan hash value: 3337892515  
6.   
7. -------------------------------------------------------------------------------  
8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
9. -------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |          |     1 |     6 |     3   (0)| 00:00:01 |  
11. |   1 |  SORT AGGREGATE    |          |     1 |     6 |            |          |  
12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |   173 |  1038 |     3   (0)| 00:00:01 |  
13. -------------------------------------------------------------------------------  
14.   
15. Predicate Information (identified by operation id):  
16. ---------------------------------------------------  
17.   
18.    2 - filter("MONTH_NO"=12 OR "EU_COUNTRY"=8)  

选择率：month_no选择率 + eu_contry选择率 - month_no选择率 * eu_contry选择率 = 1/12+1/15-1/12*1/15

返回记录：round((1/12+1/15-1/12*1/15)*1200) = 173。

[sql] view plain copy

 print?

1. SQL> select count(*) from audience where month_no<>12;  
2.   
3. 执行计划  
4. ----------------------------------------------------------  
5. Plan hash value: 3337892515  
6.   
7. -------------------------------------------------------------------------------  
8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
9. -------------------------------------------------------------------------------  
10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |  1100 |  3300 |     3   (0)| 00:00:01 |  
13. -------------------------------------------------------------------------------  
14.   
15. Predicate Information (identified by operation id):  
16. ---------------------------------------------------  
17.   
18.    2 - filter("MONTH_NO"<>12)  

选择率：1- month_no选择率 = 1- 1/12

返回记录：(1-1/12)*1200 = 1100。

8、总结：

[sql] view plain copy

 print?

1. 单个谓词过滤：  
2. =  基数计算公式 ：1/num_distinct*(num_rows-num_nulls),如果有直方图，基数计算公式=(num_rows-num_nulls)*density  
3. >  基数计算公式：(high_value-limit)/(high_value-low_value)*(num_rows-num_nulls)  
4. >= 基数计算公式：((high_value-limit)/(high_value-low_value)+1/num_distinct)*(num_rows-num_nulls)  因为有=，所以要加上=的选择率，=的选择率为1/num_distinct  
5. <  基数计算公式：(limit-low_value)/(high_value-low_value)*(num_rows-num_nulls)  
6. <= 基数计算公式：((limit-low_value)/(high_value-low_value)+1/num_distinct)*(num_rows-num_nulls)  
7.   
8. between ... and ... 的基数计算公式等价于 xxx<= high_limit ,xxxx>=low_limit   
9. 基数计算公式：((high_limit-low_limit)/(high_value-low_value)+2/num_distinct)*(num_rows-num_nulls)  
10. low_limit<xxx and</xxx  xxx<high_limit 基数计算公式：(high_limit-low_limit) (high_value-low_value)*(num_rows-num_nulls)  
11. low_limit<=xxx and xxx<high_limit 基数计算公式：(high_limit-low_limit) (high_value-low_value)+1="" num_distinct)*(num_rows-num_nulls)  
12.   
13. 双谓词，多谓词：  
14. A AND B 选择率计算公式=A选择率*B选择率  
15. A OR B  选择率计算公式=A+B-(A AND B)  
16. NOT A   选择率计算公式=1-A选择率  

oracle通过隐含参数“_optimizer_cost_model"控制使用哪种代价模型

1io代价模型

cost=iocost

2cpu代价模型

cost=iocost+cpucost

在代价转换的过程中，所有的代价都转换为单数据块读的单次代价

存在四种类型的io，单块读，多块读，直接数据块读和直接数据块写

iocost=上述四种io的消耗

选择率与密度，空值，唯一值，以及柱状图数据有关。过滤条件的选择率

a and b a的选择率*b的选择率

a or b a的选择率+b的选择率-a*b

not a 1-a的选择率

在未引入系统统计信息之前，oracle会假设下面的2中情况是成立的：

1 单块读的平均消耗时间和多块读的平均消耗时间相等

2全表扫的时候，根据下面的情况计算全表扫描的成本

mbdivisor=1.675*power(db_file_multiblock_read_count,0.6581)

tsc=blocks/mbdivisor

10.2中的mbdivisor大约是4.4,11.2中的改值大约是3.6

cpu cost model在开启的情况下，全表扫描的计算公式如下：

tsc cost=i/o cost+cpu cost

i/o cost =1+ceil(blocks/mbrc)*(mreadtim/sreadtim)

cpu cost=round(#cpucycles/cpuspeed/1000/sreadtim)

系统的统计信息放在了aux_stats$中

查看统计信息收集的历史

wri$_optstat_tab_history

执行计划中的记录数是数据记录数*选择率，运行时间的估算，总的单数据块读的io代价*单次数据块读时间

全表扫描的大概估计：

索引范围扫描：分支节点读取的数据块数和叶子节点数据块数决定。分支节点数据块的读取数等于索引数的分支节点层数。叶子节点的计算是叶子节点数*条件选择率

所以索引的代价大概是层数+叶结点数*选择率*optimizer_index_cost_adj

索引rowid访问表的计算：这边涉及到一个聚簇因子，它是代表了数据块的聚集性。

iocost=索引树高度+叶子数据块数量*access的选择率+聚簇因子*filter的选择率*optica/100

索引范围扫描的计算公式：

i/o cost= index access i/o cost + table access i/o cost

index access i/o cost= blevel+ceil(#leaf_block*ix_sel)

table acess i/o cost=ceil(clustering_factor*ix_sel_with_filters)

列上没有直方图，且没有null值得情况下，列的选择率=（1/num_distinct)

列上没有直方图，且有null值的情况下，列的选择率=(1/num_distinct)*((num_rows-num_nulls)/num_rows)

列上的low_value和high_value是用来评估列做范围查询时的可选择率

目标列大于指定值val,且val处于low_value与high_value之间

selectivity=((high_value-val)/(high_value-low_value)*null_adjust;

null_adjust=(num_rows-num_nulls)/num_rows

目标列小于指定val,且val处于low和high之间

selectivity=(val-low_value)/(high_value-low_value)*null_adjust;

null_adjust=(num_rows-num_nulls)/num_rows

目标列大于或等于指定值val，且val处于low和high之间

selectivity=((high-val)/(high-low)+1/num_distinct)*null_adjust

目标列小于或等于指定值val，且val处于low和high之间

selectivity=((val-low)/high-low)+1/num_distinct)*null_adjust

目标列在指定值val1和val2之间，且val1和val2都在low和high之间

selectivity=（（val2-val1)/high-low)+2/num_distinct)*null_adjust

对于使用了绑定变量的sql，启动绑定变量窥探，则对于>的范围查询

Selectivity = (high_value - limit) / (high_value - low_value)  limit是绑定变量的值

Cardinality = rows*selectivity

对于使用了绑定变量的sql，如果没有启用绑定变量窥探，则对于>的范围查询

selectivity=0.05

没有启用绑定变量窥探，对于between and的查询

selectivity=0.0025

--测试选择率

SQL> select count(*) from tt where object_id is null;

  COUNT(*)
----------
        99

SQL> select count(*) from tt;

  COUNT(*)
----------
     49859

 exec dbms_stats.gather_table_stats(ownname => 'baixyu',tabname => 'TT',method_opt => 'for all columns size 1');

SQL> select count(*) from tt where object_id is null;

  COUNT(*)
----------
        99

SQL> select count(*) from tt;

  COUNT(*)
----------
     49859

 select num_distinct,low_value,high_value,num_nulls from user_tab_col_statistics s where s.table_name='TT' and s.column_name='OBJECT_ID';

49760 C2020BC3061C2299

SQL> select * from tt where object_id=120;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   159   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TT   |     1 |    93 |   159   (2)| 00:00:02 |
--------------------------------------------------------------------------

SQL> select 1/49760*((49859-99)/49859)*49859 from dual;

1/49760*((49859-99)/49859)*49859
--------------------------------
                               1

SQL> select * from tt where object_id>120 and object_id<200;

已选择78行。

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    76 |  7068 |   159   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TT   |    76 |  7068 |   159   (2)| 00:00:02 |
--------------------------------------------------------------------------

select  (((200-120)/(52744-101)+2/49760)*49760/49859)*49859 from dual;

(((200-120)/(52744-101)+2/49760)*49760/49859)*49859
---------------------------------------------------
                                         77.6187907

在列上没有直方图的情况下，统计信息中的density可以认为是1/num_distinct,在列上有等频直方图时，density的计算公式为

density=1/(2*num_rows*null_adjust)

null_adjust-(num_rows-num_nulls)/num_rows

如果查询条件的输入值等于目标列的某个bucket的endpoint_value那么 列的cardinality的计算公式是

cardinality=num_rows*selectivity

selectivity=bucketsize/num_rows

bucketsize=current_endpoint_number-previous_endpoint_number

如果查询条件的输入值不等于目标列的任意一个bucket的endpoint_value，计算公式如下

cardinality=num_rows*selectivity

selectivity=min(bucketsize)/(2*num_rows)

bucketsize=current_endpoint_number-previous_endpoint_number;

---怎么不直接设置成0？

当列上的直方图信息是等高直方图的时候，等值查询的估算是

1如果查询条件输入值是popular value

cardinality=num_rows*selectivity

selectivity=(buckets_this_popular_value/buckets_totoal)*null_adjust

null_adjust=(num_rows-num_nulls)/num_rows

2如果是unpopular value

分成三种情况。。。。

1、前导零

创建一个包含2000000行数据的表，它有一个id列，采用序号并利用0来进行填充其他位。因此典型的存储值将是A00000000000000001，系统中绝大部分使用这种策略的查询，类似于where id={string constant}的形式；但是，如果它们使用了基于区间的谓词，可能将出现一些奇怪的性能问题。

[sql] view plain copy

 print?

1. SQL> create table t1   
2.   2  nologging  
3.   3  pctfree 0  
4.   4  as  
5.   5  with generator as (  
6.   6    select  
7.   7      rownum   id  
8.   8    from  all_objects   
9.   9    where  rownum <= 2000  
10.  10  )  
11.  11  select  
12.  12    /*+ ordered use_nl(v2) */  
13.  13    trunc((rownum-1)/10000)    grp_id,  
14.  14    'A' || lpad(rownum, 17, '0')  id  
15.  15  from  
16.  16    generator  v1,  
17.  17    generator  v2  
18.  18  where  
19.  19    rownum <= 2000000  
20.  20  ;  
21.   
22. 表已创建。  
23.   
24. SQL> begin  
25.   2  dbms_stats.gather_table_stats(  
26.   3  ownname=> user,  
27.   4  tabname=> 't1',  
28.   5  cascade=> true,  
29.   6  estimate_percent=> null,   
30.   7  method_opt=>'for all columns size 1'  
31.   8  );  
32.   9  end;  
33.  10  /  
34.   
35. PL/SQL 过程已成功完成。  

[sql] view plain copy

 print?

1. SQL> set autotrace traceonly;  
2. SQL> select * from t1 where id between 'A00000000000060000' and 'A00000000000070000';  
3.   
4. 已选择10001行。  
5.   
6.   
7. 执行计划  
8. ----------------------------------------------------------  
9. Plan hash value: 3617692013  
10.   
11. --------------------------------------------------------------------------  
12. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
13. --------------------------------------------------------------------------  
14. |   0 | SELECT STATEMENT  |      |    17 |   374 |  1190   (3)| 00:00:15 |  
15. |*  1 |  TABLE ACCESS FULL| T1   |    17 |   374 |  1190   (3)| 00:00:15 |  
16. --------------------------------------------------------------------------  
17.   
18. Predicate Information (identified by operation id):  
19. ---------------------------------------------------  
20.   
21.    1 - filter("ID"<='A00000000000070000' AND "ID">='A00000000000060000')  
22.   
23.   
24. 统计信息  
25. ----------------------------------------------------------  
26.           1  recursive calls  
27.           0  db block gets  
28.        7520  consistent gets  
29.        6849  physical reads  
30.           0  redo size  
31.      325111  bytes sent via SQL*Net to client  
32.        7741  bytes received via SQL*Net from client  
33.         668  SQL*Net roundtrips to/from client  
34.           0  sorts (memory)  
35.           0  sorts (disk)  
36.       10001  rows processed  

针对该查询得到的基数是非常低的（17），但是很明显却返回了10001行。下面重新创建直方图，直方图的默认值为75个桶。

[sql] view plain copy

 print?

1. SQL> set autotrace off;  
2. SQL> begin  
3.   2  dbms_stats.gather_table_stats(  
4.   3  ownname=> user,  
5.   4  tabname=> 't1',  
6.   5  cascade=> true,  
7.   6  estimate_percent=> null,   
8.   7  method_opt=>'for all columns size 75'  
9.   8  );  
10.   9  end;  
11.  10  /  
12.   
13. PL/SQL 过程已成功完成。  
14.   
15. SQL> set autotrace traceonly;  
16. SQL> select * from t1 where id between 'A00000000000060000' and 'A00000000000070000';  
17.   
18. 已选择10001行。  
19.   
20.   
21. 执行计划  
22. ----------------------------------------------------------  
23. Plan hash value: 3617692013  
24.   
25. --------------------------------------------------------------------------  
26. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
27. --------------------------------------------------------------------------  
28. |   0 | SELECT STATEMENT  |      |  8924 |   191K|  1190   (3)| 00:00:15 |  
29. |*  1 |  TABLE ACCESS FULL| T1   |  8924 |   191K|  1190   (3)| 00:00:15 |  
30. --------------------------------------------------------------------------  
31.   
32. Predicate Information (identified by operation id):  
33. ---------------------------------------------------  
34.   
35.    1 - filter("ID"<='A00000000000070000' AND "ID">='A00000000000060000')  
36.   
37.   
38. 统计信息  
39. ----------------------------------------------------------  
40.           0  recursive calls  
41.           0  db block gets  
42.        7520  consistent gets  
43.        6849  physical reads  
44.           0  redo size  
45.      325111  bytes sent via SQL*Net to client  
46.        7741  bytes received via SQL*Net from client  
47.         668  SQL*Net roundtrips to/from client  
48.           0  sorts (memory)  
49.           0  sorts (disk)  
50.       10001  rows processed  

直方图的默认值为75个桶，执行计划显示估计的基数为8924——这个结果至少大体上是正确的。创建直方图是一种方法，如果id列的值没有字符，是可以转换为数字的字符串（00000000000000001），那么在id列上面创建一个函数索引也是一种办法：create index t1_i1 on t1(grp_id, to_number(id));

2、致命的默认值

即时是数据库应用程序正确的使用了日期类型，也仍然需要避免null值。为了不让任何列为null，每个可空的列都有一个默认值。因此，大部分独立于数据库的开发人员会选择一个什么样的值来表示null日期呢？如果表示很久以后的日期呢？比如4000年12月31日。

[sql] view plain copy

 print?

1. SQL> create table t1   
2.   2  as  
3.   3  with generator as (  
4.   4  select  
5.   5  rownum id  
6.   6  from all_objects   
7.   7  where rownum <= 2000  
8.   8  )  
9.   9  select  
10.  10  /*+ ordered use_nl(v2) */  
11.  11  decode(  
12.  12      mod(rownum - 1,1000),  
13.  13        0,to_date('4000-12-31','yyyy-mm-dd'),  
14.  14          to_date('2000-01-01','yyyy-mm-dd') + trunc((rownum - 1)/100)   
15.  15    )  date_closed  
16.  16  from  
17.  17    generator  v1,  
18.  18    generator  v2  
19.  19  where rownum <= 1827 * 100;  
20.   
21. 表已创建。  
22.   
23. SQL> begin  
24.   2  dbms_stats.gather_table_stats(  
25.   3  ownname=> user,  
26.   4  tabname=> 't1',  
27.   5  cascade=> true,  
28.   6  estimate_percent=> null,   
29.   7  method_opt=>'for all columns size 1'    --直方图的默认值为1桶。  
30.   8  );  
31.   9  end;  
32.  10  /  
33.   
34. PL/SQL 过程已成功完成。  
35.   
36. SQL> set autotrace traceonly;  
37. SQL> select *  
38.   2    from t1  
39.   3   where date_closed between to_date('2003-01-01', 'yyyy-mm-dd') and  
40.   4         to_date('2003-12-31', 'yyyy-mm-dd');  
41.   
42. 已选择36463行。  
43.   
44.   
45. 执行计划  
46. ----------------------------------------------------------  
47. Plan hash value: 3617692013  
48.   
49. --------------------------------------------------------------------------  
50. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
51. --------------------------------------------------------------------------  
52. |   0 | SELECT STATEMENT  |      |   291 |  2328 |    61   (4)| 00:00:01 |  
53. |*  1 |  TABLE ACCESS FULL| T1   |   291 |  2328 |    61   (4)| 00:00:01 |  
54. --------------------------------------------------------------------------  
55.   
56. Predicate Information (identified by operation id):  
57. ---------------------------------------------------  
58.   
59.    1 - filter("DATE_CLOSED"<=TO_DATE(' 2003-12-31 00:00:00',  
60.               'syyyy-mm-dd hh24:mi:ss') AND "DATE_CLOSED">=TO_DATE(' 2003-01-01  
61.               00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  
62.   
63.   
64. 统计信息  
65. ----------------------------------------------------------  
66.           1  recursive calls  
67.           0  db block gets  
68.        2759  consistent gets  
69.           0  physical reads  
70.           0  redo size  
71.      494301  bytes sent via SQL*Net to client  
72.       27145  bytes received via SQL*Net from client  
73.        2432  SQL*Net roundtrips to/from client  
74.           0  sorts (memory)  
75.           0  sorts (disk)  
76.       36463  rows processed     --这里实际放回了36463行记录，但是oracle却计算错误了（291）。  
77.   
78. SQL> set autotrace off;  
79. SQL> begin  
80.   2  dbms_stats.gather_table_stats(  
81.   3  ownname=> user,  
82.   4  tabname=> 't1',  
83.   5  cascade=> true,  
84.   6  estimate_percent=> null,   
85.   7  method_opt=>'for all columns size 11'    --直方图的默认值为11桶。  
86.   8  );  
87.   9  end;  
88.  10  /  
89.   
90. PL/SQL 过程已成功完成。  
91.   
92. SQL> set autotrace traceonly;  
93. SQL> select *  
94.   2    from t1  
95.   3   where date_closed between to_date('2003-01-01', 'yyyy-mm-dd') and  
96.   4         to_date('2003-12-31', 'yyyy-mm-dd');  
97.   
98. 已选择36463行。  
99.   
100.   
101. 执行计划  
102. ----------------------------------------------------------  
103. Plan hash value: 3617692013  
104.   
105. --------------------------------------------------------------------------  
106. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
107. --------------------------------------------------------------------------  
108. |   0 | SELECT STATEMENT  |      | 36320 |   283K|    61   (4)| 00:00:01 |  
109. |*  1 |  TABLE ACCESS FULL| T1   | 36320 |   283K|    61   (4)| 00:00:01 |  
110. --------------------------------------------------------------------------  
111.   
112. Predicate Information (identified by operation id):  
113. ---------------------------------------------------  
114.   
115.    1 - filter("DATE_CLOSED">=TO_DATE(' 2003-01-01 00:00:00',  
116.               'syyyy-mm-dd hh24:mi:ss') AND "DATE_CLOSED"<=TO_DATE(' 2003-12-31  
117.               00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  
118.   
119.   
120. 统计信息  
121. ----------------------------------------------------------  
122.           0  recursive calls  
123.           0  db block gets  
124.        2759  consistent gets  
125.           0  physical reads  
126.           0  redo size  
127.      494301  bytes sent via SQL*Net to client  
128.       27145  bytes received via SQL*Net from client  
129.        2432  SQL*Net roundtrips to/from client  
130.           0  sorts (memory)  
131.           0  sorts (disk)  
132.       36463  rows processed          --36463：36320这次oracle计算得比较准确了。  

可以看见加大了直方图的桶数之后，CBO估算的行数就比较接近真实值了，那我们再加大直方图的桶数试一下呐！

[sql] view plain copy

 print?

1. SQL> set autotrace off;  
2. SQL> begin  
3.   2  dbms_stats.gather_table_stats(  
4.   3  ownname=> user,  
5.   4  tabname=> 't1',  
6.   5  cascade=> true,  
7.   6  estimate_percent=> null,   
8.   7  method_opt=>'for all columns size 75'  
9.   8  );  
10.   9  end;  
11.  10  /  
12.   
13. PL/SQL 过程已成功完成。  
14.   
15. SQL> set autotrace trace;  
16. SQL> select *  
17.   2    from t1  
18.   3   where date_closed between to_date('2003-01-01', 'yyyy-mm-dd') and  
19.   4         to_date('2003-12-31', 'yyyy-mm-dd');  
20.   
21. 已选择36463行。  
22.   
23.   
24. 执行计划  
25. ----------------------------------------------------------  
26. Plan hash value: 3617692013  
27.   
28. --------------------------------------------------------------------------  
29. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
30. --------------------------------------------------------------------------  
31. |   0 | SELECT STATEMENT  |      | 36345 |   283K|    61   (4)| 00:00:01 |  
32. |*  1 |  TABLE ACCESS FULL| T1   | 36345 |   283K|    61   (4)| 00:00:01 |  
33. --------------------------------------------------------------------------  
34.   
35. Predicate Information (identified by operation id):  
36. ---------------------------------------------------  
37.   
38.    1 - filter("DATE_CLOSED">=TO_DATE(' 2003-01-01 00:00:00',  
39.               'syyyy-mm-dd hh24:mi:ss') AND "DATE_CLOSED"<=TO_DATE(' 2003-12-31  
40.               00:00:00', 'syyyy-mm-dd hh24:mi:ss'))  
41.   
42.   
43. 统计信息  
44. ----------------------------------------------------------  
45.           0  recursive calls  
46.           0  db block gets  
47.        2759  consistent gets  
48.           0  physical reads  
49.           0  redo size  
50.      494301  bytes sent via SQL*Net to client  
51.       27145  bytes received via SQL*Net from client  
52.        2432  SQL*Net roundtrips to/from client  
53.           0  sorts (memory)  
54.           0  sorts (disk)  
55.       36463  rows processed    --36463：36345 加大了直方图的桶数之后，CBO估算返回的行数误差更小了。  

3、离散数据的风险

考虑一个包含period列的计数系统——存储1~12月的数据，并额外再加一个月，其对应的数值为99（同时包含了第二种选择，即这个特殊的月给定的值为13）。

[sql] view plain copy

 print?

1. SQL> create table t1   
2.   2  as  
3.   3  with generator as (  
4.   4  select  
5.   5      rownum   id  
6.   6    from  all_objects   
7.   7    where  rownum <= 1000  
8.   8  )  
9.   9  select  
10.  10    /*+ ordered use_nl(v2) */  
11.  11    mod(rownum-1,13)  period_01,   
12.  12    mod(rownum-1,13)  period_02  
13.  13  from  
14.  14    generator  v1,  
15.  15    generator  v2  
16.  16  where  
17.  17    rownum <= 13000  
18.  18  ;  
19.   
20. 表已创建。  
21.   
22. SQL> update t1 set   
23.   2  period_01 = 99,  
24.   3  period_02 = 13  
25.   4  where   
26.   5  period_01 = 0;  
27.   
28. 已更新1000行。  
29.   
30. SQL> commit;  
31.   
32. 提交完成。  
33.   
34. SQL> begin  
35.   2  dbms_stats.gather_table_stats(  
36.   3  user,  
37.   4  't1',  
38.   5  cascade => true,  
39.   6  estimate_percent => null,  
40.   7  method_opt => 'for all columns size 1'  
41.   8  );  
42.   9  end;  
43.  10  /  
44.   
45. PL/SQL 过程已成功完成。  
46.   
47. SQL> set autotrace on exp;  
48. SQL> select count(*) from t1 where period_01 between 4 and 6;  
49.   
50.   COUNT(*)  
51. ----------  
52.       3000          --这里实际有3000行，但是oracle估算的是1663行，不准确！  
53.   
54.   
55. 执行计划  
56. ----------------------------------------------------------  
57. Plan hash value: 3724264953  
58.   
59. ---------------------------------------------------------------------------  
60. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
61. ---------------------------------------------------------------------------  
62. |   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |  
63. |   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |  
64. |*  2 |   TABLE ACCESS FULL| T1   |  1663 |  4989 |     6   (0)| 00:00:01 |  
65. ---------------------------------------------------------------------------  
66.   
67. Predicate Information (identified by operation id):  
68. ---------------------------------------------------  
69.   
70.    2 - filter("PERIOD_01"<=6 AND "PERIOD_01">=4)  
71.   
72. SQL> select count(*) from t1 where period_02 between 4 and 6;  
73.   
74.   COUNT(*)  
75. ----------  
76.       3000       --这里实际有3000行，但是oracle估算的是4167行，不准确！  
77.   
78.   
79. 执行计划  
80. ----------------------------------------------------------  
81. Plan hash value: 3724264953  
82.   
83. ---------------------------------------------------------------------------  
84. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
85. ---------------------------------------------------------------------------  
86. |   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |  
87. |   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |  
88. |*  2 |   TABLE ACCESS FULL| T1   |  4167 | 12501 |     6   (0)| 00:00:01 |  
89. ---------------------------------------------------------------------------  
90.   
91. Predicate Information (identified by operation id):  
92. ---------------------------------------------------  
93.   
94.    2 - filter("PERIOD_02"<=6 AND "PERIOD_02">=4)  
95.   
96. SQL> set autotrace off;  
97. SQL> begin  
98.   2  dbms_stats.gather_table_stats(  
99.   3  user,  
100.   4  't1',  
101.   5  cascade => true,  
102.   6  estimate_percent => null,  
103.   7  method_opt => 'for all columns size 254'           --重新收集直方图。  
104.   8  );  
105.   9  end;  
106.  10  /  
107.   
108. PL/SQL 过程已成功完成。  
109.   
110. SQL> set autotrace on exp;  
111. SQL> select count(*) from t1 where period_01 between 4 and 6;  
112.   
113.   COUNT(*)  
114. ----------  
115.       3000  
116.   
117.   
118. 执行计划  
119. ----------------------------------------------------------  
120. Plan hash value: 3724264953  
121.   
122. ---------------------------------------------------------------------------  
123. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
124. ---------------------------------------------------------------------------  
125. |   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |  
126. |   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |  
127. |*  2 |   TABLE ACCESS FULL| T1   |  3000 |  9000 |     6   (0)| 00:00:01 |  
128. ---------------------------------------------------------------------------  
129.   
130. Predicate Information (identified by operation id):  
131. ---------------------------------------------------  
132.   
133.    2 - filter("PERIOD_01"<=6 AND "PERIOD_01">=4)  
134.   
135. SQL> select count(*) from t1 where period_02 between 4 and 6;  
136.   
137.   COUNT(*)  
138. ----------  
139.       3000  
140.   
141.   
142. 执行计划  
143. ----------------------------------------------------------  
144. Plan hash value: 3724264953  
145.   
146. ---------------------------------------------------------------------------  
147. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
148. ---------------------------------------------------------------------------  
149. |   0 | SELECT STATEMENT   |      |     1 |     3 |     6   (0)| 00:00:01 |  
150. |   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |  
151. |*  2 |   TABLE ACCESS FULL| T1   |  3000 |  9000 |     6   (0)| 00:00:01 |  
152. ---------------------------------------------------------------------------  
153.   
154. Predicate Information (identified by operation id):  
155. ---------------------------------------------------  
156.   
157.    2 - filter("PERIOD_02"<=6 AND "PERIOD_02">=4)  

4、函数索引

[sql] view plain copy

 print?

1. SQL> create index idx_t1_PERIOD_01 on t1(upper(PERIOD_01));  
2.   
3. 索引已创建。  
4.   
5. SQL> select i.index_name,i.index_type from user_indexes i;  
6.   
7. INDEX_NAME                     INDEX_TYPE  
8. ------------------------------ ---------------------------  
9. IDX_T1_PERIOD_01               FUNCTION-BASED NORMAL  
10.   
11. SQL> select c.TABLE_NAME,c.COLUMN_NAME from user_tab_cols c;  
12.   
13. TABLE_NAME                     COLUMN_NAME  
14. ------------------------------ ------------------------------  
15. T1                             PERIOD_01  
16. T1                             PERIOD_02  
17. T1                             SYS_NC00003$  

需要牢记的是，如果创建了一个基于函数的索引，那么实际上是在虚拟列上创建了索引，当收集关于该表及其索引的统计信息时，同时也就收集了虚拟列上的统计信息。这在类似情况下，诸如upper(PERIOD_01)='xxx'的谓词将被优化为：SYS_NC00003$='xxx'。

5、相互关联的列

如果在谓词中使用相互依赖（相关）的列容易将问题复杂化，前提是这些相关联的列同时出现在where子句中。

[sql] view plain copy

 print?

1. SQL> create table t1   
2.   2  nologging  
3.   3  as  
4.   4  select  
5.   5  trunc(dbms_random.value(0,25))n1,  
6.   6  rpad('x',40)ind_pad,  
7.   7  trunc(dbms_random.value(0,20))n2,  
8.   8  lpad(rownum,10,'0')small_vc,  
9.   9  rpad('x',200)padding  
10.  10  from  
11.  11  all_objects  
12.  12  where  
13.  13  rownum  <= 10000  
14.  14  ;  
15.   
16. 表已创建。  
17.   
18. SQL> update t1 set n2 = n1;  
19.   
20. 已更新10000行。  
21.   
22. SQL> commit;  
23.   
24. 提交完成。  
25.   
26. SQL> begin  
27.   2  dbms_stats.gather_table_stats(  
28.   3  ownname => user,  
29.   4  tabname=> 'T1',  
30.   5  cascade=> true,  
31.   6  estimate_percent => null,  
32.   7  method_opt => 'for all columns size 1'  
33.   8  );  
34.   9  end;  
35.  10  /  
36.   
37. PL/SQL 过程已成功完成。  
38.   
39. SQL> set autotrace traceonly;  
40. SQL> select small_vc  
41.   2    from t1  
42.   3   where n1 = 2  
43.   4     and n2 = 2;  
44.   
45. 已选择420行。  
46.   
47.   
48. 执行计划  
49. ----------------------------------------------------------  
50. Plan hash value: 3617692013  
51.   
52. --------------------------------------------------------------------------  
53. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
54. --------------------------------------------------------------------------  
55. |   0 | SELECT STATEMENT  |      |    16 |   272 |    66   (0)| 00:00:01 |  
56. |*  1 |  TABLE ACCESS FULL| T1   |    16 |   272 |    66   (0)| 00:00:01 |  
57. --------------------------------------------------------------------------  
58.   
59. Predicate Information (identified by operation id):  
60. ---------------------------------------------------  
61.   
62.    1 - filter("N1"=2 AND "N2"=2)  
63.   
64.   
65. 统计信息  
66. ----------------------------------------------------------  
67.           1  recursive calls  
68.           0  db block gets  
69.         401  consistent gets  
70.           0  physical reads  
71.           0  redo size  
72.        9059  bytes sent via SQL*Net to client  
73.         712  bytes received via SQL*Net from client  
74.          29  SQL*Net roundtrips to/from client  
75.           0  sorts (memory)  
76.           0  sorts (disk)  
77.         420  rows processed  

根据以上执行计划，CBO估算将会返回16行数据，实际确实返回了420行，如果这个表要与多表关联，基数一旦算错，必然导致整个SQL的执行计划全部出错，从而导致SQL性能下降。

接下来再次在相关列上执行查询，不过sql语句中包含了dynamic_sampling提示。

[sql] view plain copy

 print?

1. SQL> select /*+ dynamic_sampling(t1 1) */  
2.   2   small_vc  
3.   3    from t1  
4.   4   where n1 = 2  
5.   5     and n2 = 2;  
6.   
7. 已选择420行。  
8.   
9.   
10. 执行计划  
11. ----------------------------------------------------------  
12. Plan hash value: 3617692013  
13.   
14. --------------------------------------------------------------------------  
15. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
16. --------------------------------------------------------------------------  
17. |   0 | SELECT STATEMENT  |      |   370 |  6290 |    66   (0)| 00:00:01 |  
18. |*  1 |  TABLE ACCESS FULL| T1   |   370 |  6290 |    66   (0)| 00:00:01 |  
19. --------------------------------------------------------------------------  
20.   
21. Predicate Information (identified by operation id):  
22. ---------------------------------------------------  
23.   
24.    1 - filter("N1"=2 AND "N2"=2)  
25.   
26. Note  
27. -----  
28.    - dynamic sampling used for this statement (level=2)  
29.   
30.   
31. 统计信息  
32. ----------------------------------------------------------  
33.           0  recursive calls  
34.           0  db block gets  
35.         401  consistent gets  
36.           0  physical reads  
37.           0  redo size  
38.        9059  bytes sent via SQL*Net to client  
39.         712  bytes received via SQL*Net from client  
40.          29  SQL*Net roundtrips to/from client  
41.           0  sorts (memory)  
42.           0  sorts (disk)  
43.         420  rows processed  

没有直方图的执行计划预估结果集行数计算公式

公式摘抄于《催华-基于ORACLE的SQL优化》

转载请注明链接地址：http://blog.csdn.net/launch_225/article/details/25432661

[html] view plain copy

 print?

1. SQL> select * from t1;  
2.   
3.         N1 V1  
4. ---------- --------  
5.          1 a  
6.          2 b  
7.          2 c  
8.          4 c  
9.          5 d  
10.          6 e  
11.          6 f  
12.          6 f  
13.          9 g  
14.         10 h  
15.         11 i  
16.   
17.         N1 V1  
18. ---------- --------  
19.         12 i  
20.         12 i  
21.         14 j  
22.         15 k  
23.         16 l  
24.         16 m  
25.         18 n  
26.   
27. 18 rows selected.  

create index idx_t1_n1 on t1(n1);

[html] view plain copy

 print?

1. 1. ====>没有直方图的预估结果集行数  
2. SQL> select count(1) from t1;  
3.   
4.   COUNT(1)  
5. ----------  
6.         18  
7.           
8. SQL> select count(distinct n1) from t1;  
9.   
10. COUNT(DISTINCTN1)  
11. -----------------  
12.                13  
13.                  
14. SQL> select min(n1),max(n1) from t1;  
15.   
16.    MIN(N1)    MAX(N1)  
17. ---------- ----------  
18.          1         18  
19.            
20.     TABLE_NAME  COLUMN_NAME DENSITY NUM_BUCKETS HISTOGRAM  
21. 1   T1  N1  0.0769230769230769  1   NONE  
22. 2   T1  V1  0.0714285714285714  1   NONE  
23.   
24. 1.1=>范围查询  
25. ------------------------------------------------------------------------------  
26. (1)目标列大于指定的val,且位于low_value and high_value之间  
27. selectivity=((HIGH_VALUE-VAL)/(HIGH_VALUE-LOW_VALUE))* NULL_ADJUST  
28. NULL_ADJUST=(NUM_ROWS-NUM_NULLS)/NUM_ROWS  
29.   
30. SQL> select ((18-5)/(18-1))*1*18 from dual;  
31.   
32. ((18-5)/(18-1))*1*18  
33. --------------------  
34.           13.7647059                     
35.   
36.   1* select * from t1 where n1>5  
37. SQL> /  
38.   
39. Execution Plan  
40. ----------------------------------------------------------  
41. Plan hash value: 1577308413  
42.   
43. -----------------------------------------------------------------------------------------  
44. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
45. -----------------------------------------------------------------------------------------  
46. |   0 | SELECT STATEMENT            |           |    14 |    70 |     2   (0)| 00:00:01 |  
47. |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |    14 |    70 |     2   (0)| 00:00:01 |  
48. |*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |    14 |       |     1   (0)| 00:00:01 |  
49. -----------------------------------------------------------------------------------------  
50.   
51. Predicate Information (identified by operation id):  
52. ---------------------------------------------------  
53.   
54.    2 - access("N1">5)  
55.      
56. (2)目标列小于指定的val,且位于low_value and high_value之间  
57. selectivity=((VAL-LOW_VALUE)/(HIGH_VALUE-LOW_VALUE))* NULL_ADJUST  
58. NULL_ADJUST=(NUM_ROWS-NUM_NULLS)/NUM_ROWS  
59.   
60. SQL> select ((5-1)/(18-1))*1*18 from dual;  
61.   
62. ((5-1)/(18-1))*1*18  
63. -------------------  
64.          4.23529412  
65.            
66. SQL> select * from t1 where n1<5    
67.   2  /  
68.   
69. Execution Plan  
70. ----------------------------------------------------------  
71. Plan hash value: 1577308413  
72.   
73. -----------------------------------------------------------------------------------------  
74. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
75. -----------------------------------------------------------------------------------------  
76. |   0 | SELECT STATEMENT            |           |     4 |    20 |     2   (0)| 00:00:01 |  
77. |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     4 |    20 |     2   (0)| 00:00:01 |  
78. |*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |     4 |       |     1   (0)| 00:00:01 |  
79. -----------------------------------------------------------------------------------------  
80.   
81. Predicate Information (identified by operation id):  
82. ---------------------------------------------------  
83.   
84.    2 - access("N1"<5)  
85.      
86. (3)目标列>=指定的val,且位于low_value and high_value之间  
87. selectivity=((HIGH_VALUE-VAL)/(HIGH_VALUE-LOW_VALUE)+1/NUM_DISTINCT)* NULL_ADJUST  
88. NULL_ADJUST=(NUM_ROWS-NUM_NULLS)/NUM_ROWS  
89.   
90. SQL> select ((18-5)/(18-1)+1/13)*1*18 from dual;  
91.   
92. ((18-5)/(18-1)+1/13)*1*18  
93. -------------------------  
94.                15.1493213  
95.                  
96. SQL> select * from t1 where n1>=5;      
97.   
98. Execution Plan  
99. ----------------------------------------------------------  
100. Plan hash value: 1577308413  
101.   
102. -----------------------------------------------------------------------------------------  
103. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
104. -----------------------------------------------------------------------------------------  
105. |   0 | SELECT STATEMENT            |           |    15 |    75 |     2   (0)| 00:00:01 |  
106. |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |    15 |    75 |     2   (0)| 00:00:01 |  
107. |*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |    15 |       |     1   (0)| 00:00:01 |  
108. -----------------------------------------------------------------------------------------  
109.   
110. Predicate Information (identified by operation id):  
111. ---------------------------------------------------  
112.   
113.    2 - access("N1">=5)    
114.      
115.      
116. (4)目标列<=指定的val,且位于low_value and high_value之间  
117. selectivity=((VAL-LOW_VALUE)/(HIGH_VALUE-LOW_VALUE)+1/NUM_DISTINCT)* NULL_ADJUST  
118. NULL_ADJUST=(NUM_ROWS-NUM_NULLS)/NUM_ROWS  
119.   
120. SQL> select ((5-1)/(18-1)+1/13)*1*18 from dual;  
121.   
122. ((5-1)/(18-1)+1/13)*1*18  
123. ------------------------  
124.                5.6199095  
125.   
126. SQL> select * from t1 where n1<=5;   
127.   
128. Execution Plan  
129. ----------------------------------------------------------  
130. Plan hash value: 1577308413  
131.   
132. -----------------------------------------------------------------------------------------  
133. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
134. -----------------------------------------------------------------------------------------  
135. |   0 | SELECT STATEMENT            |           |     6 |    30 |     2   (0)| 00:00:01 |  
136. |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     6 |    30 |     2   (0)| 00:00:01 |  
137. |*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |     6 |       |     1   (0)| 00:00:01 |  
138. -----------------------------------------------------------------------------------------  
139.   
140. Predicate Information (identified by operation id):  
141. ---------------------------------------------------  
142.   
143.    2 - access("N1"<=5)  
144.      
145. (5)列位于指定值VAL1和VAL2之间，且VAL1和VAL2在LOW_VALUE和HIGH_VALUE范围  
146. selectivity=((val2-val1)/(HIGH_VALUE-LOW_VALUE)+2/NUM_DISTINCT)* NULL_ADJUST  
147. NULL_ADJUST=(NUM_ROWS-NUM_NULLS)/NUM_ROWS  
148.   
149. SQL> select ((10-5)/(18-1)+2/13)*1*18 from dual;  
150.   
151. ((10-5)/(18-1)+2/13)*1*18  
152. -------------------------  
153.                8.06334842  
154.                  
155. SQL> select * from t1 where n1 between 5 and 10;   
156.   
157. Execution Plan  
158. ----------------------------------------------------------  
159. Plan hash value: 1577308413  
160.   
161. -----------------------------------------------------------------------------------------  
162. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
163. -----------------------------------------------------------------------------------------  
164. |   0 | SELECT STATEMENT            |           |     8 |    40 |     2   (0)| 00:00:01 |  
165. |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     8 |    40 |     2   (0)| 00:00:01 |  
166. |*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |     8 |       |     1   (0)| 00:00:01 |  
167. -----------------------------------------------------------------------------------------  
168.   
169. Predicate Information (identified by operation id):  
170. ---------------------------------------------------  
171.   
172.    2 - access("N1">=5 AND "N1"<=10)  
173. ------------------------------------------------------------------------------  
174. 1.2 =》等值查询  
175. (1) 等值查询且列上没有NULL值没有直方图的计算公式  
176. selectivity_without_null=(1/num_distinct)  
177.   
178. SQL> select 1/13*18 from dual;  
179.   
180.    1/13*18  
181. ----------  
182. 1.38461538  
183.   
184.   
185. SQL> select * from t1 where n1=8;  
186.   
187. Execution Plan  
188. ----------------------------------------------------------  
189. Plan hash value: 1577308413  
190.   
191. -----------------------------------------------------------------------------------------  
192. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
193. -----------------------------------------------------------------------------------------  
194. |   0 | SELECT STATEMENT            |           |     1 |     5 |     2   (0)| 00:00:01 |  
195. |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |     5 |     2   (0)| 00:00:01 |  
196. |*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |     1 |       |     1   (0)| 00:00:01 |  
197. -----------------------------------------------------------------------------------------  
198.   
199. Predicate Information (identified by operation id):  
200. ---------------------------------------------------  
201.   
202.    2 - access("N1"=8)  
203.      
204. (2)目标列没有直方图且有NULL值(通用于没有直方图时列的选择率的计算)  
205. selectivity_with_null=(1/num_distinct) * ((num_rows-num_nulls)/num_rows)     
206.   
207. SQL> select 1/13*((18-0)/18)*18 from dual;  
208.   
209. 1/13*((18-0)/18)*18  
210. -------------------  
211.          1.38461538  
212.         
213. --不存在空值也通用     
214. SQL> select * from t1 where n1=8;  
215.   
216. Execution Plan  
217. ----------------------------------------------------------  
218. Plan hash value: 1577308413  
219.   
220. -----------------------------------------------------------------------------------------  
221. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
222. -----------------------------------------------------------------------------------------  
223. |   0 | SELECT STATEMENT            |           |     1 |     5 |     2   (0)| 00:00:01 |  
224. |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |     5 |     2   (0)| 00:00:01 |  
225. |*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |     1 |       |     1   (0)| 00:00:01 |  
226. -----------------------------------------------------------------------------------------  
227.   
228. Predicate Information (identified by operation id):  
229. ---------------------------------------------------  
230.   
231.    2 - access("N1"=8)  
232.      
233.      

等频直方图预估结果集行计算公式

公式：摘自《催华-基于ORACLE的SQL优化》

链接地址：http://blog.csdn.net/launch_225/article/details/25472129

等频：唯一值《=桶数

[html] view plain copy

 print?

1. =>等频(频率)直方图   
2. -->density计算  
3. density=1/(2*num_rows*null_adjust)  
4. null_adjust=(num_rows-num_nulls)/num_rows  
5. -->等值查询,val在low_value and high_value之间,值等于某个endpoint_value  
6. cardinality=num_rows * selectivity  
7. selectivity=bucketsize/num_rows  
8. bucketsize=current_endpoint_number-previous_endpoint_number  
9.   
10.  TABLE_NAME COLUMN_NAME NUM_BUCKETS LOW_VALUE   HIGH_VALUE  HISTOGRAM  
11. 1   T1  N1  13  C102    C113    FREQUENCY  
12.   
13.   
14.   
15. -->density计算  
16. density=1/(2*num_rows*null_adjust)  
17.   
18. SQL> select 1/(2*18*1) from dual;  
19.   
20. 1/(2*18*1)  
21. ----------  
22. .027777778  
23.   
24.     OWNER   TABLE_NAME  NUM_DISTINCT    DENSITY COLUMN_NAME  
25. 1   AIKI    T1  13  0.0277777777777778  N1  
26.   
27.   
28. -->等值查询,val在low_value and high_value之间,值等于某个endpoint_value  
29. cardinality=num_rows * selectivity  
30. selectivity=bucketsize/num_rows  
31. bucketsize=current_endpoint_number-previous_endpoint_number  
32.   
33.   1*  select * from t1 where n1=6  
34. SQL> /  
35.   
36.   
37. Execution Plan  
38. ----------------------------------------------------------  
39. Plan hash value: 1577308413  
40.   
41. -----------------------------------------------------------------------------------------  
42. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
43. -----------------------------------------------------------------------------------------  
44. |   0 | SELECT STATEMENT            |           |     3 |    15 |     2   (0)| 00:00:01 |  
45. |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     3 |    15 |     2   (0)| 00:00:01 |  
46. |*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |     3 |       |     1   (0)| 00:00:01 |  
47. -----------------------------------------------------------------------------------------  
48.   
49. Predicate Information (identified by operation id):  
50. ---------------------------------------------------  
51.   
52.    2 - access("N1"=6)  
53.   
54.             
55. OWNER   TABLE_NAME  COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE  
56. AIKI    T1  N1  1   1  
57. AIKI    T1  N1  3   2  
58. AIKI    T1  N1  4   4  
59. AIKI    T1  N1  5   5       --previous_endpoint_number  
60. AIKI    T1  N1  8   6       --current_endpoint_number  
61. AIKI    T1  N1  9   9  
62. AIKI    T1  N1  10  10  
63. AIKI    T1  N1  11  11  
64. AIKI    T1  N1  13  12  
65. AIKI    T1  N1  14  14  
66. AIKI    T1  N1  15  15  
67. AIKI    T1  N1  17  16  
68. AIKI    T1  N1  18  18  
69.   
70. bucketsize=8-5=3;  
71. selectivity=3/18  
72. cardinality=18*(3/18)=3;  

等高直方图预估结果集行计算公式

摘自《催华-基于ORACLE的SQL优化》

链接地址：http://blog.csdn.net/launch_225/article/details/25474427

[html] view plain copy

 print?

1. SQL> desc t1  
2.  Name                                      Null?    Type  
3.  ----------------------------------------- -------- ----------------------------  
4.  N1                                                 NUMBER  
5.  V1                                                 VARCHAR2(8)  
6.   
7. SQL> select * from t1;  
8.   
9.         N1 V1  
10. ---------- --------  
11.          1 a  
12.          2 b  
13.          2 c  
14.          4 c  
15.          5 d  
16.          6 e  
17.          6 f  
18.          6 f  
19.          9 g  
20.         10 h  
21.         11 i  
22.         12 i  
23.         12 i  
24.         14 j  
25.         15 k  
26.         16 l  
27.         16 m  
28.         18 n  
29.   
30. 18 rows selected.  
31.   
32.   
33.  TABLE_NAME COLUMN_NAME NUM_BUCKETS LOW_VALUE   HIGH_VALUE  HISTOGRAM  
34. 1   T1  N1  10  C102    C113    HEIGHT BALANCED  
35.   
36.  OWNER  TABLE_NAME  COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE  
37. 1   AIKI    T1  N1  0   1  
38. 2   AIKI    T1  N1  1   2  
39. 3   AIKI    T1  N1  2   4  
40. 4   AIKI    T1  N1  4   6  
41. 5   AIKI    T1  N1  5   10  
42. 6   AIKI    T1  N1  6   12  
43. 7   AIKI    T1  N1  7   14  
44. 8   AIKI    T1  N1  8   15  
45. 9   AIKI    T1  N1  9   16  
46. 10  AIKI    T1  N1  10  18  
47.   
48.   
49.   
50. 1.查询值为popular value:  
51. cardinality=num_rows * SELECTIVITY  
52. SELECTIVITY=(BUCKETS_THIS_POPULAR_VALUE/BUCKETS_TOTAL)* NULL_ADJUST  
53. NULL_ADJUST=(NUM_ROWS-NUM_NULLS)/NUM_ROWS  
54. BUCKETS_THIS_POPULAR_VALUE:POPULAR VALUE所点的bucket的数量，buckets_total:bucket的总数  
55.   
56. select * from t1 where n1=6;  
57.   
58. SELECTIVITY=(2/10)*1  
59. SQL> select (2/10)*1*18 from dual;  
60.   
61. (2/10)*1*18  
62. -----------  
63.         3.6  
64.           
65. SQL> select * from t1 where n1=6;  
66.   
67.   
68. Execution Plan  
69. ----------------------------------------------------------  
70. Plan hash value: 1577308413  
71.   
72. -----------------------------------------------------------------------------------------  
73. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
74. -----------------------------------------------------------------------------------------  
75. |   0 | SELECT STATEMENT            |           |     4 |    20 |     2   (0)| 00:00:01 |  
76. |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     4 |    20 |     2   (0)| 00:00:01 |  
77. |*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |     4 |       |     1   (0)| 00:00:01 |  
78. -----------------------------------------------------------------------------------------  
79.   
80. Predicate Information (identified by operation id):  
81. ---------------------------------------------------  
82.   
83.    2 - access("N1"=6)  
84.      
85.   
86. --2.非popular value  
87. --(>=10.2.0.4版本)  
88. selectivity=newdensity * null_adjust  
89. null_adjust=(num_rows-num_nulls)/num_rows  
90. newdensity=(buckets_total-buckets_all_popular_values)/buckets_total/(ndv-popular_value.count)  
91. olddensity=sum(np.count(i) * np.count(i))/((num_rows-num_nulls)* sum(np.count(i)))  
92. ndv=num_distinct  
93. olddensity存储在dba_***_col_statistics的density中  
94. buckets_all_popular_values:所有的pupular value所占的bucket的数量，buckets_total:bucket的总数  
95. popular_values.count:pupular value的个数，NP.count(i):每个nopopular value在目标表中的记录数  
96.   
97.              
98. newdensity=(10-(4-2))/10/(13-1)  
99. selectivity=(10-(4-2))/10/(13-1)  
100. cardinality=(10-(4-2))/10/(13-1)*18  
101.   
102. SQL> select  (10-(4-2))/10/(13-1)*18 from dual;  
103.   
104. (10-(4-2))/10/(13-1)*18  
105. -----------------------  
106.                     1.2  
107.                       
108. SQL> select * from t1 where n1=12;  --12为非popular value  
109. Execution Plan  
110. ----------------------------------------------------------  
111. Plan hash value: 1577308413  
112.   
113. -----------------------------------------------------------------------------------------  
114. | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
115. -----------------------------------------------------------------------------------------  
116. |   0 | SELECT STATEMENT            |           |     1 |     5 |     2   (0)| 00:00:01 |  
117. |   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |     5 |     2   (0)| 00:00:01 |  
118. |*  2 |   INDEX RANGE SCAN          | IDX_T1_N1 |     1 |       |     1   (0)| 00:00:01 |  
119. -----------------------------------------------------------------------------------------  
120.   
121. Predicate Information (identified by operation id):  
122. ---------------------------------------------------  
123.   
124.    2 - access("N1"=12)  
125.   
126.                   
127. --2.1.版本10.2.0.4(不含10.2.0.4,10.2.0.1)以上版本  
128. selectivity=olddensity * null_adjust  
129. null_adjust=(num_rows-num_nulls)/num_rows  
130. olddensity=sum(np.count(i)* np.count(i))/((num_rows-num_nulls)*sum(np.count(i)))  
131. olddensity存储在dba_***_col_statistics的density中  
132. NP.count(i):每个nopopular value在目标表中的记录数  
133.   
134. --2.1版本10.2.0.1  
135. selectivity=olddensity * null_adjust  
136. null_adjust=(num_rows-num_nulls)/num_rows  
137. olddensity=sum(dv.count(i) * dv.count(i))/((num_rows-num_nulls)* sum(dv.count(i)))  
138.   
139. olddensity用于实际计算可选择率和结果集的Cardinality  
140. olddensity也会存储在数据字典DBA_TAB_COL_STATISTICS,DBA_PART_COL_STATISTICS和DBA_SUBPART_COL_STATISTICS中的DENSITY中  
141. DV.count(i)表示的是目标列的每个DISTINCT值在目标表中的记录数  

About Me

............................................................................................................................... ● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用 ● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● QQ群：230161599     微信群：私聊 ● 联系我请加QQ好友(646634621)，注明添加缘由 ● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................... 拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。