# Cardinality的计算

Cardinality=MAX(Cardinality Factor * Rowcount，1)

***********

***********

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

Cardinality Factor = 1 / 字段上的唯一值的数量

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 1月 15 12:50:38 2008

SQL> conn / as sysdba;

SQL> create table t ( a number , b char(1), c char(5));

SQL> create index t_idx1 on t (b,c);

SQL> begin
2     for i in 1 .. 1000 loop
3      insert into t values(i,mod(i,10),mod(i,13));
4     end loop;
5     commit;
6  end;
7  /

PL/SQL 过程已成功完成。

SQL> analyze table t compute statistics
2  for table
3  for all indexes
4  for all indexed columns;

SQL> select count(distinct b) from t;

COUNT(DISTINCTB)
----------------
10

SQL> select count(distinct c) from t;

COUNT(DISTINCTC)
----------------
13

SQL> select count(*) from
2  (select distinct b,c from t);

COUNT(*)
----------
130

SQL> variable V char
SQL> exec :V := 8

PL/SQL 过程已成功完成。

SQL> set autotrace traceonly
SQL> select /*+index(t t_idx1)*/ * from t where b=:V;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=100 Bytes=15
00)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=41 Card=100 Byt
es=1500)

2    1     INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=2 Card
=100)

SQL> variable V char
SQL> exec :V := 3

PL/SQL 过程已成功完成。

SQL> select /*+index(t t_idx1)*/ * from t where c=:V;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=35 Card=77 Bytes=115
5)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=35 Card=77 Byte
s=1155)

2    1     INDEX (FULL SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=5 Card=
77)

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

Cardinality Factor = (1 / 字段上的唯一值的数量) + (1 / 记录数)

SQL> select /*+index(t t_idx1)*/ * from t where b > :V;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=101 Bytes=151
5)

1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=101 Bytes=1515)

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

Cardinality Factor = in条件中的变量数 / 字段上的唯一值的数量

SQL> variable A char
SQL> variable B char
SQL> variable C char
SQL> exec :A := 2

PL/SQL 过程已成功完成。

SQL> exec :B := 3

PL/SQL 过程已成功完成。

SQL> exec :C := 4

PL/SQL 过程已成功完成。

SQL> select /*+index(t t_idx10*/ * from t where b in (:A,:B,:C);

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=300 Bytes=450
0)

1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=300 Bytes=4500)

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

Cardinality Factor = ( 1 - (1 / 字段上的唯一值的数量 ) )

SQL> select /*+index(t t_idx1)*/ * from t where c <> :V;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=365 Card=923 Bytes=1
3845)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=365 Card=923 By
tes=13845)

2    1     INDEX (FULL SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=5 Card=
923)

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

Cardinality Factor = ( 1 - (1 / 字段上的唯一值的数量 ) ) ^ (not in 变量数)

SQL> select /*+index(t t_idx1)*/ * from t where b not in (:A,:B,:C);

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=290 Card=729 Bytes=1
0935)

1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=290 Card=729 By
tes=10935)

2    1     INDEX (FULL SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=5 Card=
729)

**************

**************

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

Cardinality Factor = 1 / 100

SQL> select * from t where a = :V;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=10 Bytes=150)
1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=10 Bytes=150)

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

Cardinality Factor = 1 / 20

SQL> select * from t where a >= :V;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=50 Bytes=750)
1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=50 Bytes=750)

SQL> select * from t where a <= :V;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=50 Bytes=750)
1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=50 Bytes=750)

SQL> select * from t where a <> :V;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=50 Bytes=750)
1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=50 Bytes=750)

********

********

Cardinality Factor = 字段1的Cardinality Factor * 字段2的Cardinality Factor * ... * 字段n的Cardinality Factor

SQL> select /*+index(t t_idx1)*/ * from t where b > :V and c =:A;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=6 Card=8 Bytes=120)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=6 Card=8 Bytes=
120)

2    1     INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=2 Card
=101)

c=:A的Cardinality Factor = (1/13)

**********

**********

Cardinality Factor=1;

SQL> select * from t;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
000)

1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1000 Bytes=15000)

• 博文量
284
• 访问量
796870