num_distinct、density和选择度的问题

在计算Cardinality的时候，ORACLE首先会利用到DENSITY。如果手工修改了NUM_DISTINCT那么DENSITY也会跟着变化。但是反过来，如果修改了DENSITY，NUM_DISTINCT就不会改变。

SQL> SELECT * FROM V$VERSION;

 

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

 

SQL> CREATE TABLE CARD_TEST(ID ,NAME ,VALUE) AS SELECT OBJECT_ID,OBJECT_NAME,ROUND(DBMS_RANDOM.VALUE(1,10)) FROM ALL_OBJECTS WHERE ROWNUM<=100;

 

Table created.

 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'CARD_TEST',method_opt=>null);

 

PL/SQL procedure successfully completed.

 

SQL> SELECT MIN(VALUE),MAX(VALUE),COUNT(1) FROM CARD_TEST;

 

MIN(VALUE) MAX(VALUE)   COUNT(1)
---------- ---------- ----------
         1         10        100

 

SQL> COL COLUMN_NAME FORMAT A15

SQL> SELECT COLUMN_NAME,NUM_DISTINCT,DENSITY FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='CARD_TEST';

 

COLUMN_NAME     NUM_DISTINCT    DENSITY
--------------- ------------ ----------
ID                       100        .01
NAME                     100        .01
VALUE                     10         .1

 

SQL>  EXEC DBMS_STATS.SET_COLUMN_STATS(USER,'CARD_TEST','VALUE',distcnt=>5);

 

PL/SQL procedure successfully completed.

 

SQL>  SELECT COLUMN_NAME,NUM_DISTINCT,DENSITY FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='CARD_TEST';

 

COLUMN_NAME     NUM_DISTINCT    DENSITY
--------------- ------------ ----------
VALUE                      5         .2

可以看到，NUM_DISTINCT变化了,DENSITY也跟着变化。

此时，选择度也就是0.2，因此Cardinality=100*0.2=20

 

SQL> SET AUTOT TRACEONLY EXP

 

SQL> SELECT COUNT(1) FROM CARD_TEST WHERE VALUE=5;

Execution Plan
----------------------------------------------------------
Plan hash value: 1890630942

----------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |           |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| CARD_TEST |    20 |    60 |     2 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("VALUE"=5)

Note
-----
   - cpu costing is off (consider enabling it)

 

SQL> SET AUTOT OFF

SQL> SELECT COLUMN_NAME,NUM_DISTINCT,DENSITY FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME='CARD_TEST';

 

COLUMN_NAME     NUM_DISTINCT    DENSITY
--------------- ------------ ----------
ID                       100        .01
NAME                     100        .01
VALUE                      5         .2

 

SQL> EXEC DBMS_STATS.SET_COLUMN_STATS(USER,'CARD_TEST','VALUE',DENSITY=>0.5);

 

PL/SQL procedure successfully completed.

 

SQL> SELECT COLUMN_NAME,NUM_DISTINCT,DENSITY FROM USER_TAB_COL_STATISTICS WHERE

TABLE_NAME='CARD_TEST';

 

COLUMN_NAME     NUM_DISTINCT    DENSITY
--------------- ------------ ----------
ID                       100        .01
NAME                     100        .01
VALUE                      5         .5

可以看到,DENSITY变化了，NUM_DISTINCT不会跟着变化。
此时，选择度也就是0.5，因此Cardinality=100*0.5=50

SQL> SET AUTOT TRACEONLY EXP

SQL> SELECT COUNT(1) FROM CARD_TEST WHERE VALUE=5;

 

Execution Plan
----------------------------------------------------------
Plan hash value: 1890630942

----------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     3 |     2 |
|   1 |  SORT AGGREGATE    |           |     1 |     3 |       |
|*  2 |   TABLE ACCESS FULL| CARD_TEST |    50 |   150 |     2 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("VALUE"=5)

Note
-----
   - cpu costing is off (consider enabling it)