Oracle SQL Tuning_选择率与基数公式01_单表选择率_没有直方图_在Range内

最新推荐文章于 2022-03-11 08:00:00 发布
最新推荐文章于 2022-03-11 08:00:00 发布
阅读量194 收藏
点赞数
分类专栏： oracle优化学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010692693/article/details/102933002
版权

基本概念

Predicate:  查询中所有的或部分的where子句
Range:      表列中最大值和最小值的范围
NumRows:    表的行数(DBA_TABLES.NUM_ROWS)
Card:       Cardinality 满足一个谓词的行数
Sel:        满足需求的行数/可用的总行数
Orig Card:  在用谓词过滤前的行数
Comp Card:  满足谓词的行数
Join Card:  Join操作返回的行数
Join Sel:   Join Card/笛卡尔join返回的行数
RS:         执行计划中对表或索引等操作所输出的行数
NDV:        列上Ｄistinct值的个数(dba_tab_columns.num_distinct)
NNulls:     列上NULL值的个数(dba_tab_columns.num_nulls)
A4Nulls:    NULLS调整
BucketSize: 一个直方图bucket里的行数
Density:    密度dba_tab_columns.density

1. Equality predicate (“col = val”)

A4Nulls = (Orig_Card - NNulls) / Orig_Card
Sel = (1 / NDV) * A4Nulls
Comp_Card = Orig_Card * Sel

创建测试环境

create table t1 as select * from dba_objects;

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

查看执行计划  Rows (2)

explain plan for select object_id from t1 where object_name = 'EMP';
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    60 |   278   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    60 |   278   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='EMP')

查询统计信息

set feed on
col table_name for a30
col column_name for a30
set line 300
set pages 300
select a.table_name,b.column_name,a.num_rows,b.num_distinct,b.num_nulls,b.density,
b.histogram 
from user_tables a,user_tab_columns b
where a.table_name=b.table_name
and a.table_name='T1' and b.coLumn_name=upper('&colname');

Enter value for colname: OBJECT_NAME

TABLE_NAME     COLUMN_NAME         NUM_ROWS NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
-------------- ----------------- ---------- ------------ ---------- ---------- ---------------
T1             OBJECT_NAME            86290        52015          0 .000019225 NONE

选择率和基数的计算过程

A4Nulls = (Orig_Card - NNulls) / Orig_Card
        = (NUM_ROWS - NUM_NULLS) / NUM_ROWS
        = (86290 - 0) / 86290
        = 1
Sel = (1 / NDV) * A4Nulls
    = (1 / NUM_DISTINCT) * A4Nulls
    = (1 / 52015) * 1
    = .000019225
Comp_Card = round((1/52015)*(86290-0)/86290*86290) = 2

2.Conjunctive equality predicates (“colA = val1 AND colB = val2”)

A4Nulls_A = (Orig_Card_A - NNulls_A) / Orig_Card_A
A4Nulls_B = (Orig_Card_B - NNulls_B) / Orig_Card_B
SelA1 = (1 / NDV_A) * A4Nulls_A
SelB2 = (1 / NDV_B) * A4Nulls_B
Sel = SelA1 * SelB2
Comp_Card = Orig_Card * Sel

查看执行计划  Rows (1)

explain plan for select object_id from t1 where object_name = 'EMP' and OWNER='GYJ';
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    36 |   278   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    36 |   278   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='EMP' AND "OWNER"='GYJ')

查询统计信息

set feed on
col table_name for a30
col column_name for a30
set line 300
set pages 300
select a.table_name,b.column_name,a.num_rows,b.num_distinct,b.num_nulls,b.density,
b.histogram 
from user_tables a,user_tab_columns b
where a.table_name=b.table_name
and a.table_name='T1' and (b.coLumn_name=upper('&colname1') OR  b.coLumn_name=upper('&colname2'));

Enter value for colname1: OBJECT_NAME
Enter value for colname2: OWNER

TABLE_NAME     COLUMN_NAME         NUM_ROWS NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
-------------- ----------------- ---------- ------------ ---------- ---------- ---------------
T1             OWNER                  86290           24          0 .041666667 NONE
T1             OBJECT_NAME            86290        52015          0 .000019225 NONE

选择率和基数的计算过程

A4Nulls_A = (Orig_Card_A - NNulls_A) / Orig_Card_A
          = (OBJECT_NAME.NUM_ROWS - OBJECT_NAME.NUM_NULLS) / OBJECT_NAME.NUM_ROWS
          = (86290 - 0) / 86290
          = 1
A4Nulls_B = (Orig_Card_B - NNulls_B) / Orig_Card_B
          = (OWNER.NUM_ROWS - OWNER.NUM_NULLS) / OWNER.NUM_ROWS
          = (86290 - 0) / 86290
          = 1
SelA1 = (1 / NDV_A) * A4Nulls_A
      = (1 / OBJECT_NAME.NUM_DISTINCT) * A4Nulls_A
      = (1 / 52015) * 1
      = .000019225
SelB2 = (1 / NDV_B) * A4Nulls_B
      = (1 / OWNER.NUM_DISTINCT) * A4Nulls_B
      = (1 / 24) * 1
      = .041666667
Sel = SelA1 * SelB2
    = (1 / 52015) * 1 * (1 / 24) * 1
    = 8.0105E-07
Comp_Card = ceil((1 / 52015) * 1 * (1 / 24) * 1 * 86290) = 1

3.Disjunctive equality predicates (“colA = val1 OR colB = val2”)

A4Nulls_A = (Orig_Card_A - NNulls_A) / Orig_Card_A
A4Nulls_B = (Orig_Card_B - NNulls_B) / Orig_Card_B
SelA1 = (1 / NDV_A) * A4Nulls_A
SelB2 = (1 / NDV_B) * A4Nulls_B
Sel = SelA1 + SelB2 - (SelA1 * SelB2)
Comp_Card = Orig_Card * Sel

查看执行计划  Rows (3597)

explain plan for select object_id from t1 where object_name = 'EMP' or OWNER='GYJ';
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3597 |   126K|   278   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |  3597 |   126K|   278   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OWNER"='GYJ' OR "OBJECT_NAME"='EMP')

查询统计信息

set feed on
col table_name for a30
col column_name for a30
set line 300
set pages 300
select a.table_name,b.column_name,a.num_rows,b.num_distinct,b.num_nulls,b.density,
b.histogram 
from user_tables a,user_tab_columns b
where a.table_name=b.table_name
and a.table_name='T1' and (b.coLumn_name=upper('&colname1') OR  b.coLumn_name=upper('&colname2'));

Enter value for colname1: OBJECT_NAME
Enter value for colname2: OWNER

TABLE_NAME     COLUMN_NAME         NUM_ROWS NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
-------------- ----------------- ---------- ------------ ---------- ---------- ---------------
T1             OWNER                  86290           24          0 .041666667 NONE
T1             OBJECT_NAME            86290        52015          0 .000019225 NONE

选择率和基数的计算过程

A4Nulls_A = (Orig_Card_A - NNulls_A) / Orig_Card_A
          = (OBJECT_NAME.NUM_ROWS - OBJECT_NAME.NUM_NULLS) / OBJECT_NAME.NUM_ROWS
          = (86290 - 0) / 86290
          = 1
A4Nulls_B = (Orig_Card_B - NNulls_B) / Orig_Card_B
          = (OWNER.NUM_ROWS - OWNER.NUM_NULLS) / OWNER.NUM_ROWS
          = (86290 - 0) / 86290
          = 1
SelA1 = (1 / NDV_A) * A4Nulls_A
      = (1 / OBJECT_NAME.NUM_DISTINCT) * A4Nulls_A
      = (1 / 52015) * 1
      = .000019225
SelB2 = (1 / NDV_B) * A4Nulls_B
      = (1 / OWNER.NUM_DISTINCT) * A4Nulls_B
      = (1 / 24) * 1
      = .041666667
Sel = SelA1 + SelB2 - (SelA1 * SelB2)
    = (1 / 52015) * 1 + (1 / 24) * 1 - ((1 / 52015) * 1 * (1 / 24) * 1)
    = .041685091
Comp_Card = round(86290 * .041685091) = 3597

4.Disjunctive equality predicates (“colA = val1 OR colA = val2”)

A4Nulls = (Orig_Card - NNulls) / Orig_Card
Sel = (1 / NDV) * A4Nulls * 2
Comp_Card = Orig_Card * Sel

查看执行计划  Rows (3)

explain plan for select object_id from t1 where object_name = 'EMP' or object_name = 'DEPT';
select * from table(dbms_xplan.display);

SQL> 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    90 |   278   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |     3 |    90 |   278   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='DEPT' OR "OBJECT_NAME"='EMP')

查询统计信息

set feed on
col table_name for a30
col column_name for a30
set line 300
set pages 300
select a.table_name,b.column_name,a.num_rows,b.num_distinct,b.num_nulls,b.density,
b.histogram 
from user_tables a,user_tab_columns b
where a.table_name=b.table_name
and a.table_name='T1' and b.coLumn_name=upper('&colname');
Enter value for colname: OBJECT_NAME

TABLE_NAME                     COLUMN_NAME                      NUM_ROWS NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------------------ ------------------------------ ---------- ------------ ---------- ---------- ---------------
T1                             OBJECT_NAME                         86290        52015          0 .000019225 NONE

选择率和基数的计算过程

A4Nulls = (Orig_Card - NNulls) / Orig_Card
        = (NUM_ROWS - NUM_NULLS) / NUM_ROWS
        = (86290 - 0) / 86290
        = 1
Sel = (1 / NDV) * A4Nulls * 2
    = (1 / NUM_DISTINCT) * A4Nulls * 2
    = (1 / 52015) * 1 * 2
    = .00003845
Comp_Card = round(86290 * .00003845) = 3

5.IN list (“col IN (val1, val2, … , valN)”)

A4Nulls = (Orig_Card - NNulls) / Orig_Card
Sel = (1 / NDV) * A4Nulls * N
Comp_Card = Orig_Card * Sel

查看执行计划  Rows (7)

explain plan for select object_id from t1 where object_name = 'EMP' or object_name = 'DEPT' or object_name = 'IDX_RANGE' or object_name='NESTLOOP1' ;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   210 |   278   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |     7 |   210 |   278   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='DEPT' OR "OBJECT_NAME"='EMP' OR
              "OBJECT_NAME"='IDX_RANGE' OR "OBJECT_NAME"='NESTLOOP1')

查询统计信息

set feed on
col table_name for a30
col column_name for a30
set line 300
set pages 300
select a.table_name,b.column_name,a.num_rows,b.num_distinct,b.num_nulls,b.density,
b.histogram 
from user_tables a,user_tab_columns b
where a.table_name=b.table_name
and a.table_name='T1' and b.coLumn_name=upper('&colname');
Enter value for colname: OBJECT_NAME

TABLE_NAME                     COLUMN_NAME                      NUM_ROWS NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------------------ ------------------------------ ---------- ------------ ---------- ---------- ---------------
T1                             OBJECT_NAME                         86290        52015          0 .000019225 NONE

选择率和基数的计算过程

A4Nulls = (Orig_Card - NNulls) / Orig_Card
        = (NUM_ROWS - NUM_NULLS) / NUM_ROWS
        = (86290 - 0) / 86290
        = 1
Sel = (1 / NDV) * A4Nulls * 4
    = (1 / NUM_DISTINCT) * A4Nulls * 4
    = (1 / 52015) * 1 * 4
    = .000076901
Comp_Card = round(86290 * .000076901) = 7

6. Unbounded open ("col > val")

A4Nulls = (Orig_Card - NNulls) / Orig_Card
Sel = ((high_value - val) / (high_value - low_value)) * A4Nulls
Comp_Card = Orig_Card * Sel

查看执行计划  Rows (84319)

explain plan for select object_id from t1 where object_id > '2000';
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 84319 |   411K|   278   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   | 84319 |   411K|   278   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID">2000)

查询统计信息

set feed on
col table_name for a30
col column_name for a30
set line 300
set pages 300
select a.table_name,b.column_name,a.num_rows,b.num_distinct,b.num_nulls,b.density,b.low_value,b.high_value,b.histogram 
from user_tables a,user_tab_columns b
where a.table_name=b.table_name
and a.table_name='T1' and b.coLumn_name=upper('&colname');
Enter value for colname: OBJECT_ID

TABLE_NAME        COLUMN_NAME         NUM_ROWS NUM_DISTINCT  NUM_NULLS    DENSITY LOW_VALUE            HIGH_VALUE          HISTOGRAM
----------------- ----------------- ---------- ------------ ---------- ---------- -------------------- ------------------- ---------------
T1                OBJECT_ID              86290        86290          0 .000011589 C103                 C3094B59            NONE

LOW_VALUE(最小值) 和 HIGH_VALUE(最大值) 值是RAW类型，可以用 utl_raw 包的 cast_to_number 函数进行类型转换。

SQL> select utl_raw.cast_to_number('C103') LOW_VALUE, utl_raw.cast_to_number('C3094B59') HIGH_VALUE from dual;

 LOW_VALUE HIGH_VALUE
---------- ----------
         2      87488

选择率和基数的计算过程

A4Nulls = (Orig_Card - NNulls) / Orig_Card
        = (NUM_ROWS - NUM_NULLS) / NUM_ROWS
        = (86290 - 0) / 86290
        = 1
Sel = ((high_value - val) / (high_value - low_value)) * A4Nulls
    = ((87488 - 2000) / (87488 - 2)) * 1
    = .97716206
Comp_Card = round(86290 * .97716206) = 84319

7.Unbounded open (“col < val”)

A4Nulls = (Orig_Card - NNulls) / Orig_Card
Sel = ((val - low_value) / (high_value - low_value)) * A4Nulls
Comp_Card = Orig_Card * Sel

查看执行计划  Rows (1971)

explain plan for select object_id from t1 where object_id < '2000';
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1971 |  9855 |   278   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |  1971 |  9855 |   278   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"<2000)

查询统计信息

set feed on
col table_name for a30
col column_name for a30
set line 300
set pages 300
select a.table_name,b.column_name,a.num_rows,b.num_distinct,b.num_nulls,b.density,b.low_value,b.high_value,b.histogram 
from user_tables a,user_tab_columns b
where a.table_name=b.table_name
and a.table_name='T1' and b.coLumn_name=upper('&colname');
Enter value for colname: OBJECT_ID

TABLE_NAME        COLUMN_NAME         NUM_ROWS NUM_DISTINCT  NUM_NULLS    DENSITY LOW_VALUE            HIGH_VALUE          HISTOGRAM
----------------- ----------------- ---------- ------------ ---------- ---------- -------------------- ------------------- ---------------
T1                OBJECT_ID              86290        86290          0 .000011589 C103                 C3094B59            NONE

LOW_VALUE(最小值) 和 HIGH_VALUE(最大值) 值是RAW类型，可以用 utl_raw 包的 cast_to_number 函数进行类型转换。

SQL> select utl_raw.cast_to_number('C103') LOW_VALUE, utl_raw.cast_to_number('C3094B59') HIGH_VALUE from dual;

 LOW_VALUE HIGH_VALUE
---------- ----------
         2      87488

选择率和基数的计算过程

A4Nulls = (Orig_Card - NNulls) / Orig_Card
        = (NUM_ROWS - NUM_NULLS) / NUM_ROWS
        = (86290 - 0) / 86290
        = 1
Sel = ((val - low_value) / (high_value - low_value)) * A4Nulls
    = ((2000 - 2) / (87488 - 2)) * 1
    = .02283794
Comp_Card = round(86290 * .02283794) = 1971

8.Unbounded closed (“col ≥ val”)

A4Nulls = (Orig_Card - NNulls) / Orig_Card
Sel = ((high_value - val) / (high_value - low_value) + (1 / NDV)) * A4Nulls
Comp_Card = Orig_Card * Sel

查看执行计划  Rows (84320)

explain plan for select object_id from t1 where object_id >= '2000';
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 84320 |   411K|   278   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   | 84320 |   411K|   278   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID">=2000)

查询统计信息

set feed on
col table_name for a30
col column_name for a30
set line 300
set pages 300
select a.table_name,b.column_name,a.num_rows,b.num_distinct,b.num_nulls,b.density,b.low_value,b.high_value,b.histogram 
from user_tables a,user_tab_columns b
where a.table_name=b.table_name
and a.table_name='T1' and b.coLumn_name=upper('&colname');

Enter value for colname: OBJECT_ID

TABLE_NAME        COLUMN_NAME         NUM_ROWS NUM_DISTINCT  NUM_NULLS    DENSITY LOW_VALUE            HIGH_VALUE          HISTOGRAM
----------------- ----------------- ---------- ------------ ---------- ---------- -------------------- ------------------- ---------------
T1                OBJECT_ID              86290        86290          0 .000011589 C103                 C3094B59            NONE

LOW_VALUE(最小值) 和 HIGH_VALUE(最大值) 值是RAW类型，可以用 utl_raw 包的 cast_to_number 函数进行类型转换。

SQL> select utl_raw.cast_to_number('C103') LOW_VALUE, utl_raw.cast_to_number('C3094B59') HIGH_VALUE from dual;

 LOW_VALUE HIGH_VALUE
---------- ----------
         2      87488

选择率和基数的计算过程

A4Nulls = (Orig_Card - NNulls) / Orig_Card
        = (NUM_ROWS - NUM_NULLS) / NUM_ROWS
        = (86290 - 0) / 86290
        = 1
Sel = ((high_value - val) / (high_value - low_value) + (1 / NDV)) * A4Nulls
    = ((87488 - 2000) / (87488 - 2) + (1 / 86290)) * 1
    = .977173649
Comp_Card = round(86290 * .977173649) = 84320

9.Unbounded closed (“col ≤ val”)

A4Nulls = (Orig_Card - NNulls) / Orig_Card
Sel = ((val - low_value) / (high_value - low_value) + (1 / NDV)) * A4Nulls
Comp_Card = Orig_Card * Sel

查看执行计划  Rows (1972)

explain plan for select object_id from t1 where object_id <= '2000';
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1972 |  9860 |   278   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |  1972 |  9860 |   278   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"<=2000)

查询统计信息

set feed on
col table_name for a30
col column_name for a30
set line 300
set pages 300
select a.table_name,b.column_name,a.num_rows,b.num_distinct,b.num_nulls,b.density,b.low_value,b.high_value,b.histogram 
from user_tables a,user_tab_columns b
where a.table_name=b.table_name
and a.table_name='T1' and b.coLumn_name=upper('&colname');
Enter value for colname: OBJECT_ID

TABLE_NAME        COLUMN_NAME         NUM_ROWS NUM_DISTINCT  NUM_NULLS    DENSITY LOW_VALUE            HIGH_VALUE          HISTOGRAM
----------------- ----------------- ---------- ------------ ---------- ---------- -------------------- ------------------- ---------------
T1                OBJECT_ID              86290        86290          0 .000011589 C103                 C3094B59            NONE

LOW_VALUE(最小值) 和 HIGH_VALUE(最大值) 值是RAW类型，可以用 utl_raw 包的 cast_to_number 函数进行类型转换。

SQL> select utl_raw.cast_to_number('C103') LOW_VALUE, utl_raw.cast_to_number('C3094B59') HIGH_VALUE from dual;

 LOW_VALUE HIGH_VALUE
---------- ----------
         2      87488

选择率和基数的计算过程

A4Nulls = (Orig_Card - NNulls) / Orig_Card
        = (NUM_ROWS - NUM_NULLS) / NUM_ROWS
        = (86290 - 0) / 86290
        = 1
Sel = ((val - low_value) / (high_value - low_value) + (1 / NDV)) * A4Nulls
    = ((2000 - 2) / (87488 - 2) + (1 / 86290)) * 1
    = .022849529
Comp_Card = round(86290 * .022849529) = 1972

 

张玉龙
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle SQL Tuning_选择率与基数公式02_单表选择率_没有直方图_在Range之外(out of range)
u010692693的专栏
11-06 310
基本概念 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...
oracle实验记录 (oracle单表选择率与基数计算(2))
cuiye1492的博客
09-12 116
在查询中使用区间谓词 选择率和基数card计算 先开始,>=,<= 这几种无边界情况(所谓无边界 就是一侧没有边界 )SQL> create table t5 (a int); 表已创建。 SQL&...
SQL语句大全-程序员必备（Oracle版）
ys214992的博客
03-15 777
一、创建表空间、用户及授权 Oracle数据库创建时，会自动创建一些默认表空间，他们不仅包含用来管理用户数据的表空间，也包括用来管理Oracle系统内部数据（如数据字典）的表空间。以下是 Oracle11g 默认创建的主要表空间： 查看已创建的表空间名称 SQL>select tablespace_name from dba_tablespaces; 每个表空间（逻辑存储结构）至少包含一
oracle迁移到新环境,[oracle] 迁移表到新表空间
weixin_33810040的博客
04-02 137
查看表是否是分区表select t.owner,p.table_name,count(p.partition_name) from dba_tab_partitions p,dba_tables t where p.table_name = t.table_name and t.table_name like '%xx%' group by t.owner,p.table_name;无对已下表进...
Oracle-sql-Performance-Tuning-30Tips.rar_oracle_tom kyte
最新发布
09-23
"Oracle SQL Performance Tuning 39Tips" 提供了由Oracle专家Tom Kyte精心总结的30个SQL优化技巧，这些技巧旨在帮助用户提升数据库查询速度，减少资源消耗，提高系统整体性能。 1. **索引优化**：Tom Kyte强调了...
oracle_sql_tuning.rar_oracle
09-22
以下是对"oracle_sql_tuning.rar_oracle"压缩包中的内容进行的详细解释和扩展。 1. **SQL优化基础** - **执行计划**：了解SQL执行计划是调优的第一步。通过EXPLAIN PLAN或使用DBMS_XPLAN可以查看查询的执行步骤，...
Oracle_SQL优化脚本_完整实用资源
01-25
这个"Oracle_SQL优化脚本_完整实用资源"压缩包包含了一系列工具和方法，旨在帮助你优化在Oracle数据库上运行的SQL查询，从而提高数据库的响应速度和整体效率。 1. **SQL执行计划分析**：在Oracle中，通过`EXPLAIN ...
Audio_Tuning_Tool_exe_v2.2016.rar
09-25
在移动设备领域，音质的优化与调校是提升用户体验的关键环节之一。针对MTK（MediaTek）平台，开发者们推出了Audio_Tuning_Tool_exe_v2.2016这一专业工具，以帮助工程师和爱好者对设备的音频性能进行精细化调整。本文...
New_audio_tuning_tool_use_guide v2.1.pdf
09-25
MTK平台 音频调试工具使用指南 New_audio_tuning_tool_use_guide v2.1.pdf
oracle 11g 体系结构
树叶子的博客
04-01 1183
1概述： 3个重要概念： 1.实例：实例指一组oracle后台进程以及在服务器中分配的共享内存区域 2.数据库：数据库是由存储在磁盘上的数据文件、控制文件、日志文件、参数文件、归档日志文件等组成的物理文件集合 3.数据库服务器：数据库服务器是指管理数据库的各种软件工具(例如：sqlplus、OEM等)和实例及数据库3个部分 关系：实例用于管理和控制...
oracle并行收集统计信息慢,Oracle 学习之性能优化（四）收集统计信息
weixin_39741101的博客
04-05 1358
emp表有如下数据。SQL>selectename,deptnofromemp;ENAMEDEPTNO----------------------------------------SMITH20ALLEN30WARD30JONES20MARTIN30BLAKE30CLARK10...
col的用法
我的博客
01-27 1871
<br />col的用法<br /> <br /><br />col 的用法以前就接触过，可是后来一直用pl/sql developer。就一直没有用到<br />结果遇到在sqlplus中执行<br />select d.file_name,d.tablespace_name from Dba_Data_Files d<br />显示却不是想要的结果<br />col file_name for a50;<br />col TABLESPACE_NAME for a20;<br />set linesiz
Oracle 分析表
JANE_ZDr的博客
09-29 675
分析表 select owner,table_name,TABLESPACE_NAME from dba_tables where table_name=upper('&1'); select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from dba_indexes where TABLE_NAME='&1'; select a.column_name, b.num_rows, a.num_distinct C...
三大数据库 sequence 之华山论剑 (四)
DBADaily 的博客
03-11 332
Oracle PostgreSQL MySQL sequence 十年经验总结
oracle如何查看表水位线,oracle查看表的高水位
weixin_42373066的博客
04-02 2327
col owner for a10col table_name for a30SELECT OWNER,SEGMENT_NAME TABLE_NAME,SEGMENT_TYPE,GREATEST (ROUND ( 100 * ( NVL (HWM - AVG_USED_BLOCKS, 0 ) /GREATEST (NVL (HWM, 1), 1 )),2 ),0 ) WASTE_PERFROM (...
Oracle SQL plus环境设置与使用
yumushui的专栏
07-18 2377
目录 glogin.sql配置替代变量08、定制SQL.PLUS环境spool用法 glogin.sql配置 配置文件位置：$ORACLE_HOME/sqlplus/admin/glogin.sql 修改默认的sql缓存区编辑器 define _editor='/usr/bin/vi' set line 120 col parameter for a30
配置SqlPlus输出格式及提示符显示登陆用户和数据库
robbie的专栏
02-29 5068
cat $ORACLE_HOME/sqlplus/admin/login.sql  set serveroutput on size 1000000 set trimspool on set long 5000 set linesize 100 set pagesize 9999 set linesize 154 col GROUP# for 99 col THREAD# for
Oracle数据库第二章上机作业,Oracle复习之第二章
weixin_31953691的博客
04-06 296
1.查看Oracle系统是否采用归档模式：col name format a30;select dbid,name,log_mode from v$database;2.查看有哪些日志文件：col member format a50;select member from v$logfile;3.查看有哪些控制文件：col name format a60;select name from v$con...
优化ORACLE SQL性能：多用COMMIT-ORACLE_SQL_tuning策略
在SQL优化方面，课程涵盖了丰富的内容，从基础的性能管理与问题识别，到SQL执行过程、Oracle优化器的工作原理、SQL执行计划分析，以及SQL编码标准。课程内容深入到SQL语句处理的各个阶段，包括共享SQL区域、共享游标...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值