oracle cascade=>true,Oracle预估的基数算法

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index idx_t on t(object_id);

Index created.

SQL> BEGIN

2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',

3 tabname => 'T',

4 estimate_percent => 100,

5 method_opt => 'for all columns size auto',

6 degree => DBMS_STATS.AUTO_DEGREE,

7 cascade => TRUE);

8 END;

9 /

SQL> alter session set optimizer_features_enable='9.2.0';

Session altered.

SQL> explain plan for select owner from t where object_id<1000;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

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

Plan hash value: 1594971208

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

| Id | Operation | Name | Rows | Bytes | Cost |

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

| 0 | SELECT STATEMENT | |958 | 10538 | 26 |

| 1 | TABLE ACCESS BY INDEX ROWID| T |958 | 10538 | 26 |

|* 2 | INDEX RANGE SCAN | IDX_T |958 | | 4 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"<1000)

Note

-----

- cpu costing is off (consider enabling it)

18 rows selected.

那么这个958 Oracle是怎么估算的呢？

Oracle预估的基数等于有效选择性*(num_rows-num_nulls)

其中 有效选择性 ，< 的有效选择性算法为:

(limit-low_value)/(high_value-low_value)

set linesize 200

SQL> select b.num_rows,

a.num_distinct,

a.num_nulls,

utl_raw.cast_to_number(high_value) high_value,

utl_raw.cast_to_number(low_value) low_value,

(b.num_rows - a.num_nulls) "NUM_ROWS-NUM_NULLS",

utl_raw.cast_to_number(high_value) -

utl_raw.cast_to_number(low_value) "HIGH_VALUE-LOW_VALUE"

from dba_tab_col_statistics a, dba_tables b

where a.owner = b.owner

and a.table_name = b.table_name

and a.owner = 'TEST'

and a.table_name = upper('T')

and a.column_name = 'OBJECT_ID'; 2 3 4 5 6 7 8 9 10 11 12 13 14

NUM_ROWS NUM_DISTINCT NUM_NULLS HIGH_VALUE LOW_VALUE NUM_ROWS-NUM_NULLS HIGH_VALUE-LOW_VALUE

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

73964 73964 077085 2 73964 77083

那么估算为:

SQL>

select ceil((1000-2)/77083*73964) from dual;SQL>

CEIL((1000-2)/77083*73964)

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

958

Oracle 就是根据这个算法的