摘自《催华-基于ORACLE的SQL优化》
链接地址:http://blog.csdn.net/launch_225/article/details/25474427
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
N1 NUMBER
V1 VARCHAR2(8)
SQL> select * from t1;
N1 V1
---------- --------
1 a
2 b
2 c
4 c
5 d
6 e
6 f
6 f
9 g
10 h
11 i
12 i
12 i
14 j
15 k
16 l
16 m
18 n
18 rows selected.
TABLE_NAME COLUMN_NAME NUM_BUCKETS LOW_VALUE HIGH_VALUE HISTOGRAM
1 T1 N1 10 C102 C113 HEIGHT BALANCED
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
1 AIKI T1 N1 0 1
2 AIKI T1 N1 1 2
3 AIKI T1 N1 2 4
4 AIKI T1 N1 4 6
5 AIKI T1 N1 5 10
6 AIKI T1 N1 6 12
7 AIKI T1 N1 7 14
8 AIKI T1 N1 8 15
9 AIKI T1 N1 9 16
10 AIKI T1 N1 10 18
1.查询值为popular value:
cardinality=num_rows * SELECTIVITY
SELECTIVITY=(BUCKETS_THIS_POPULAR_VALUE/BUCKETS_TOTAL)* NULL_ADJUST
NULL_ADJUST=(NUM_ROWS-NUM_NULLS)/NUM_ROWS
BUCKETS_THIS_POPULAR_VALUE:POPULAR VALUE所点的bucket的数量,buckets_total:bucket的总数
select * from t1 where n1=6;
SELECTIVITY=(2/10)*1
SQL> select (2/10)*1*18 from dual;
(2/10)*1*18
-----------
3.6
SQL> select * from t1 where n1=6;
Execution Plan
----------------------------------------------------------
Plan hash value: 1577308413
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 4 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_N1 | 4 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=6)
--2.非popular value
--(>=10.2.0.4版本)
selectivity=newdensity * null_adjust
null_adjust=(num_rows-num_nulls)/num_rows
newdensity=(buckets_total-buckets_all_popular_values)/buckets_total/(ndv-popular_value.count)
olddensity=sum(np.count(i) * np.count(i))/((num_rows-num_nulls)* sum(np.count(i)))
ndv=num_distinct
olddensity存储在dba_***_col_statistics的density中
buckets_all_popular_values:所有的pupular value所占的bucket的数量,buckets_total:bucket的总数
popular_values.count:pupular value的个数,NP.count(i):每个nopopular value在目标表中的记录数
newdensity=(10-(4-2))/10/(13-1)
selectivity=(10-(4-2))/10/(13-1)
cardinality=(10-(4-2))/10/(13-1)*18
SQL> select (10-(4-2))/10/(13-1)*18 from dual;
(10-(4-2))/10/(13-1)*18
-----------------------
1.2
SQL> select * from t1 where n1=12; --12为非popular value
Execution Plan
----------------------------------------------------------
Plan hash value: 1577308413
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_N1 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=12)
--2.1.版本10.2.0.4(不含10.2.0.4,10.2.0.1)以上版本
selectivity=olddensity * null_adjust
null_adjust=(num_rows-num_nulls)/num_rows
olddensity=sum(np.count(i)* np.count(i))/((num_rows-num_nulls)*sum(np.count(i)))
olddensity存储在dba_***_col_statistics的density中
NP.count(i):每个nopopular value在目标表中的记录数
--2.1版本10.2.0.1
selectivity=olddensity * null_adjust
null_adjust=(num_rows-num_nulls)/num_rows
olddensity=sum(dv.count(i) * dv.count(i))/((num_rows-num_nulls)* sum(dv.count(i)))
olddensity用于实际计算可选择率和结果集的Cardinality
olddensity也会存储在数据字典DBA_TAB_COL_STATISTICS,DBA_PART_COL_STATISTICS和DBA_SUBPART_COL_STATISTICS中的DENSITY中
DV.count(i)表示的是目标列的每个DISTINCT值在目标表中的记录数