Oracle SQL Tuning_选择率与基数公式04_其他类型

单表 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 BETWEEN :b1 AND :b2”)
Sel = 0.0025 * A4Nulls

d) Negation (NOT A)   Note: “A” is any predicate
Sel = 0.05 * A4Nulls

多表 Join 选择率

相等Join谓词（没有直方图）

Join Sel = 1/Greatest(RS1.j1.NDV,RS2.j1.NDV)           --Greatest取最大值
Join Card = RS1.Comp_Card * Rs2.Comp_Card*Join Sel

创建测试环境

create table t100 as select * from dba_objects;
create table t200 as select * from dba_tables;

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ZYLONG',
tabname => 'T100',
estimate_percent => 100,
method_opt => 'for all columns size 1',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE);
END;
/

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ZYLONG',
tabname => 'T200',
estimate_percent => 100,
method_opt => 'for all columns size 1',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE);
END;
/

查看执行计划  Rows (4653)

explain plan for  select * from t100 a,t200 b where a.object_name=b.table_name;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 2412991213

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  4653 |  1544K|   304   (1)| 00:00:04 |
|*  1 |  HASH JOIN         |      |  4653 |  1544K|   304   (1)| 00:00:04 |
|   2 |   TABLE ACCESS FULL| T200 |  2805 |   662K|    25   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T100 | 86294 |  8258K|   278   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   1 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

查询统计信息

select a.table_name,b.column_name,b.num_distinct,b.num_nulls,a.num_rows,b.histogram from user_tables a,user_tab_columns b where a.table_name = b.table_name and b.table_name = 'T100' and b.column_name = 'OBJECT_NAME';

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS   NUM_ROWS HISTOGRAM
------------------------------ ------------------------------ ------------ ---------- ---------- ---------------
T100                           OBJECT_NAME                           52019          0      86294 NONE

select a.table_name,b.column_name,b.num_distinct,b.num_nulls,a.num_rows,b.histogram from user_tables a,user_tab_columns b where a.table_name = b.table_name and b.table_name = 'T200' and b.column_name = 'TABLE_NAME';

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS   NUM_ROWS HISTOGRAM
------------------------------ ------------------------------ ------------ ---------- ---------- ---------------
T200                           TABLE_NAME                             2801          0       2805 NONE

选择率和基数的计算过程

Join Sel = 1/Greatest(RS1.j1.NDV, RS2.j1.NDV)
         = 1/Greatest(T100.NUM_DISTINCT, T200.NUM_DISTINCT)
         = 1/Greatest(52019, 2801)
         = 1/52019
Join Card = round(RS1.Comp_Card * Rs2.Comp_Card * Join Sel)
          = round(T100.NUM_ROWS * T200.NUM_ROWS * Join Sel)
          = round(86294 * 2805 * 1/52019)
          = 4653

注： 因为查询SQL中没加过滤条件，所以Comp_Card=NUM_ROWS

两个谓词(没有直方图)

Join Sel = 1/Greatest(RS1.j1.NDV * RS1.j2.NDV, RS2.j1.NDV * RS2.j2.NDV)
Join Card = RS1.Comp_Card * Rs2.Comp_Card * Join Sel

不等连接（没有直方图）

Join Sel = 0.05
Join Card = RS1.Comp_Card * Rs2.Comp_Card * Join Sel