Oracle 执行计划（2）-基数 cardinality

关注

原创最新推荐文章于 2026-04-20 10:04:11 发布 2w 阅读 AI 写同款· GEO 优化 ›

Oracle数据库sql优化基本概念：基数与选择性在数据库性能优化的过程中，理解并正确估计基数和选择性对于数据库管理员来说十分重要，它们直接影响到索引的创建和维护、查询计划的选择以及整个系统的性能表现。同时，由于Oracle并不直接支持在一个查询中直接计算列的选择性，所以这里采用了一个临时结果集（CTE）的方式来分步计算。基数（Cardinality）与选择性（Selectivity）是数据库理论和SQL优化领域中的两个重要概念，特别是在关系型数据库管理系统（如Oracle、MySQL等）中用于评估查询效率和索引效果的关键指标。上述脚本首先分别计算了。阅读详情

Oracle 执行计划（2）-基数

执行计划的cardinality列。基数是说从数据表，结果集或者索引返回多少行数据。

基数=表行数据*选择率。所以讲基数重点是讲选择率，选择率的计算！

版本10G 默认表空间信息：

TABLESPACE_NAME	USERS
BLOCK_SIZE	8192
EXTENT_MANAGEMENT	LOCAL
ALLOCATION_TYPE	SYSTEM
SEGMENT_SPACE_MANAGEMENT	AUTO
DEF_TAB_COMPRESSION	DISABLED
BIGFILE	NO

比如我们建个表

Create table student ( id number,name varchar2(50),birday date,month_no number(2))；

insert into student
select
trunc(dbms_random.value(1,1200)) id,
a.object_name,
sysdate-rownum as birday,
trunc(dbms_random.value(1,13)) month_no
from all_objects a
where rownum<=1200

分析下

SQL> analyze table student compute statistics;

看看数据分布

select month_no ,count(1) from student group by month_no;

MONTH_NO	COUNT(1)
1	104
2	87
3	100
4	91
5	114
6	102
7	102
8	96
9	100
10	114
11	97
12	93

数据分布得比较均匀

看看表列的情况:

select * from user_tab_col_statistics where table_name='STUDENT'

TABLE_NAME	COLUMN_NAME	NUM_DISTINCT	LOW_VALUE	HIGH_VALUE	DENSITY	NUM_NULLS	NUM_BUCKETS	AVG_COL_LEN
STUDENT	ID	762	C102	C20C61	0.00131233595800525	0	1	3
STUDENT	NAME	1192	41434345535324	5741524E494E475F53455454494E475324	0.000838926174496644	0	1	14
STUDENT	BIRDAY	1200	786C0C1F100D2E	7870040D100D2E	0.000833333333333333	0	1	7
STUDENT	MONTH_NO	12	C102	C10D	0.0833333333333333	0	1	2

列名解释:

1 NUM_DISTINCT 该列有多少个不同的值,非空.

2 LOW_VALUE 该列最小值

3 HIGH_VALUE 该列最大值

4 DENSITY 该列密度如果有直方图的话才有此值计算

5 NUM_NULLS 该列空置多少

6 NUM_BUCKETS 该列采用直方图用多少桶

7 AVG_COL_LEN 该列平均长度

select * from user_tables where table_name='STUDENT'

AVG_ROW_LEN	NUM_ROWS	EMPTY_BLOCKS	BLOCKS
33	1200	3	13

表信息：分别是平均长度，数据行数，空块数，全部块数。

OK 我们把数据搞定了！接下来就可以谈谈选择率了，看下这样的语句的执行计划。

SQL> set autotrace on

SQL> select count(*) from STUDENT where month_no=12;

COUNT(*)

----------

已用时间: 00: 00: 00.01

执行计划

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

Plan hash value: 4030891848

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

|* 2 | TABLE ACCESS FULL| STUDENT | 100 | 200 | 4 (0)| 00:00:01 |

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

SQLPLUS环境里的ROWS就是基数

实际有93行而计划里说有100行，根据公式基数=行数*选择率。那么选择率=基数/行数 => ？=100/1200=1/12.

执行计划是根据统计信息来计算，来估算返回行的数量。

选择率=1/ NUM_DISTINCT 或者是DENSITY 。返回上面看看表列的信息中的值。这就是基本选择率。

1 有空值情况下的选择率

把每个月的生日置空10 共120.

update student set month_no =null where month_no=1 and rownum<=10;

…

update student set month_no =null where month_no=12 and rownum<=10;

再度分析下表，并查看表列信息，可以看到month_no列NUM_NULLS=120

再次运行上面的语句得到执行计划：

SQL> select count(*) from STUDENT where month_no=12;

COUNT(*)

----------

已用时间: 00: 00: 00.00

执行计划

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

Plan hash value: 403089184

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

|* 2 | TABLE ACCESS FULL| STUDENT | 90 | 180 | 4 (0)| 00:00:01 |

这里的基数变成了90了

公式选择率=基本选择率*(num_rows-num_nulls)/num_rows。

这里的基本选择率是1/num_distinct 因为使用了= 的条件。

公式推导 => ?=1/12*(1200-120)/1200=0.075

基数=1200*0.075 =90

2 使用列表 in(…)

1 回滚 update student set month_no=12 where month_no is null and rownum<=10;

2 分析表

3 查看表列信息发现NUM_NULLS 变回了0值。

4 执行语句：select count(*) from STUDENT where month_no in(6,7,8);

SQL> select count(*) from STUDENT where month_no in(6,7,8);

COUNT(*)

----------

300

已用时间: 00: 00: 00.00

执行计划

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

Plan hash value: 4030891848

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

|* 2 | TABLE ACCESS FULL| STUDENT | 300 | 600 | 4 (0)| 00:00:01 |

公式=基本选择率+基本选择率+基本选择率= 3/12

如果in(15) or in(15，16) 基数分别 73，146 而不是100，200. 因为超过最大值，当超过最大与最小值距离(11) 为0.

距离15-12=3 间距12-1=11 平均递减100/11=9.09 15的基数100-(3*9.09)=72.73=73。这说明ORACLE衰减思想。

3 区间谓词的选择率

1 month_no >8 ,>=8,<8,<=8

2 between 6 and 9

3 >=6 and <=9; >=6 and <9; >6 and <=9 ; >6 and <9

4 >12

5 between 25 and 30

6 >=:val1,> < <=:val1 绑定变量

7 >= :val1 and <= :val2 between :val1 and :val2

公式=需要空间除以可用空间

可用空间=high_vlaue-low_value=12-1=11

需要空间=（high_vlaue-limit）或（limit-low_value）或（high_vlaue-low_value）

1 month_no>8 （high_vlaue-limit）/（high_vlaue-low_value）=(12-8)/11=4/11

2 month_no>=8 因为=是基本选择率 1/12 因此 4/11+1/12

3 month_no < 8 小于用最小值上面大于用最大值 (limit-low_value) /（high_vlaue-low_value）=(8-1)/11=7/11

4 between 6 and 9 or >=6 and <=9: （9-6）/(12-1)+1/12+1/12

5 (>=6,<9) (6>,<=9) :(9-6)/(12-1)+1/12

6 >6 and < 9 :(9-6)/(12-1)

7 >=:val1 固定选择率5%

8 >=:val1 and <=:val2 固定比率 2.5%

9 超过范围将逐步衰减。

4 双谓词

1 month >8 or month <=8

2 month >8 and month<=8

谓词1的选择率(12-8)/(12-1)=4/11=0.363636364

谓词2的选择率 (8-1)/(12-1)+1/12=7/11+1/12=0.71969697

公式 1 (谓词1 AND 谓词2)= 谓词1选择率*谓词2的选择率因此第2个是 4/11*(7/11+1/12)= 0.261707989

公式 2 (谓词1 or 谓词2)= 谓词1选择率+谓词2的选择率-(谓词1选择率*谓词2的选择率)

=4/11+((7/11+1/12)-( 4/11*(7/11+1/12)))

=0.363636364+0.71969697- 0.261707989

=0.821625344

基数=1200*0.821625344=985.9504128=986

看下执行计划：

SQL> select count(*) from student where month_no >8 or month_no<=8;

COUNT(*)

----------

1200

已用时间: 00: 00: 00.01

执行计划

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

Plan hash value: 4030891848

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 2 | | |

|* 2 | TABLE ACCESS FULL| STUDENT | 986 | 1972 | 4 (0)| 00:00:01 |

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

收录于

Oracle 开发

标签

#oracle #table #statistics #access #sql

Oracle优化器 Cardinality基数与Selectivity选择性优化器（optimizer）是oracle数据库内置的一个核心子系统。优化器的目的是按照一定的判断原则来得到它认为的目标SQL在当前的情形下的最高效的执行路径，也就是为了得到目标SQL的最佳执行计划。依据所选择执行计划时所用的判断原则，oracle数据库里的优化器又分为RBO和CBO两种。一、基于规则的优化器。《RBO: Rule-Based Optimization》 Oracle会在代码里事先为各种类型的执行路径定一个等级，一共15个等级，从等级1到等级15，oracle认为等级1的执行路径是效率最高的，等级15是执行效率最差的。对于等级相同的执行计划，oracle根据目标对象立即下载

Oracle 执行计划（2）-基数 cardinality

相关推荐