SQL> create index test_idx1 on test(owner);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size 1',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
);
END;
SQL> select owner,blocks from dba_tables where owner='TEST' and table_name='TEST';
OWNER BLOCKS
------------------------------ ----------
TEST 32910
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from test;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
23563
SQL> show parameter multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
parallel_adaptive_multi_user boolean TRUE
SQL> select /*+ index(test test_idx1)*/ * from test where owner='SYS';
已选择986880行。
执行计划
----------------------------------------------------------
Plan hash value: 2624864549
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77444 | 7336K| 2241 (1)| 00:00:27 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 77444 | 7336K| 2241 (1)| 00:00:27 |
|* 2 | INDEX RANGE SCAN | TEST_IDX1 | 77444 | | 185 (1)| 00:00:03 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
169962 consistent gets
27475 physical reads
915836 redo size
108497026 bytes sent via SQL*Net to client
724117 bytes received via SQL*Net from client
65793 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
986880 rows processed
成本为2241
SQL> select leaf_blocks,blevel,clustering_factor from dba_indexes where owner='TEST' and index_name='TEST_IDX1';
LEAF_BLOCKS BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
5452 2 61635
LEAF_BLOCKS 叶子块 5452个
BLEVEL 索引高度-1
SQL> select leaf_blocks,blevel,clustering_factor from dba_indexes where owner='TEST' and index_name='TEST_IDX1';
LEAF_BLOCKS BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
5452 2 61635
SQL> set linesize 200
SQL> select b.num_rows,
2 a.density * (b.num_rows - a.num_nulls) ,
3 a.num_distinct,
4 a.num_nulls,
5 utl_raw.cast_to_number(high_value) high_value,
6 utl_raw.cast_to_number(low_value) low_value,
7 (b.num_rows - a.num_nulls) "NUM_ROWS-NUM_NULLS",
8 utl_raw.cast_to_number(high_value) -
9 utl_raw.cast_to_number(low_value) "HIGH_VALUE-LOW_VALUE"
10 from dba_tab_col_statistics a, dba_tables b
11 where a.owner = b.owner
12 and a.table_name = b.table_name
13 and a.owner = 'TEST'
14 and a.table_name = upper('TEST')
15 and a.column_name = 'OWNER';
NUM_ROWS A.DENSITY*(B.NUM_ROWS-A.NUM_NULLS) NUM_DISTINCT NUM_NULLS HIGH_VALUE LOW_VALUE NUM_ROWS-NUM_NULLS HIGH_VALUE-LOW_VALUE
---------- ---------------------------------- ------------ ---------- ---------- ---------- ------------------ --------------------
2323328 .5 30 0 2323328 .000021321
单个谓词过滤：
= 基数计算公式 选择性计算为：1/num_distinct*(num_rows-num_nulls),如果有直方图，基数计算公式=(num_rows-num_nulls)*density
SQL> select a.column_name,
2 b.num_rows,
3 a.num_distinct Cardinality,
4 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
5 a.histogram,
6 a.num_buckets
7 from dba_tab_col_statistics a, dba_tables b
8 where a.owner = b.owner
9 and a.table_name = b.table_name
10 and a.owner = 'TEST'
11 and a.table_name = 'TEST'
12 and a.column_name='OWNER';
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
OWNER 2323328 30 0 FREQUENCY 30
索引扫描的计算公式如下：
cost =
blevel +
celiling(leaf_blocks *effective index selectivity) +
celiling(clustering_factor * effective table selectivity)
选择性为:1/30
SQL> select 2+ceil(5452 *(1/30))+ceil(61635*(1/30)) from dual;
2+CEIL(5452*(1/30))+CEIL(61635*(1/30))
--------------------------------------
2239
接近成本2241