oracle 查詢計劃中的基數cardinality概念(二)

  執行計劃（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(*)

----------

        93

已用時間:  00: 00: 00.01

執行計劃

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

Plan hash value: 4030891848

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

| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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(*)

----------

        83

已用時間:  00: 00: 00.00

執行計劃

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

Plan hash value: 403089184

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

| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

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

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

 

這裡的基數變成了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

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

| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

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

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

 

公式=基本選擇率+基本選擇率+基本選擇率= 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

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

| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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 |

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