《Cardinality?》

2012-10-15

摘要:《Cardinality?》

執行計劃會顯示每一個 Statement 的步驟，每一個步驟又會顯示其 cost、cardinality，cost 顯而易見應該就是成本； cardinality 又代表什麼意思呢?

Google 翻譯是 “基數”。

http://www.oratechinfo.co.uk/cardinality.html

This is the number of rows that Oracle expects that step in the plan to evaluate.

Ask Tom 也有很實在的範例解釋。

以下是個人測試過程：


SQL> create table t as select * from dba_objects;
已建立表格.

SQL> create index t_idx on t (object_id);
已建立索引.

SQL> alter session set optimizer_mode=rule;
已更改階段作業.

Ps. Change to RBO


SQL> set autotrace traceonly
SQL> select * from t where object_id=27;

執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)

Ps. Rule Base & no statistics won’t show cost and cardinality!


統計值
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          2  physical reads
          0  redo size
       1182  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> alter session set optimizer_mode=first_rows;
已更改階段作業.

Ps. Change to CBO


SQL> select * from t where object_id=27;

執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=491 Bytes =86907)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=491 Bytes=86907)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card= 196)

Ps. CBO will show cost & cardinality, but it’s not correct.


統計值
----------------------------------------------------------
        262  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
       1182  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> analyze table t compute statistics for all indexes for all columns;
已分析表格.

SQL> select * from t where object_id=27;

執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=85)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=85)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1)


統計值
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1182  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

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

結論: cardinality 指的是 oracle 估算會取得的筆數。

所以定期分析、統計資料表，將有助於執行計劃的選擇。

回首頁