oracle實驗記錄 （連線選擇率,範圍與null）

~------------------------------------
optimizer_dynamic_sampling     2
LSE

 先看下 10 G 動態取樣

SQL> select num_distinct ,column_name from dba_tab_col_statistics where table_na
me='T3';

no rows selected

L> select * from t3;

       A
--------
       1

L> select num_distinct ,column_name from dba_tab_col_statistics where table_na
='T3';

 rows selected

L> select num_distinct ,column_name from dba_tab_col_statistics where table_na

SQL> exec dbms_stats.gather_table_stats('sys','T3');

PL/SQL procedure successfully completed.
SQL> select num_distinct ,column_name from dba_tab_col_statistics where table_na
me='T3';

NUM_DISTINCT COLUMN_NAME
------------ ------------------------------
           1 A

動採不會寫入資料字典

 

~~~~~~~~關於範圍連線
中包含null
SQL> select column_name,num_distinct,num_nulls from dba_tab_col_statistics where
 table_name='T2';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS
------------------------------ ------------ ----------
FILTER                                   50        100
JOIN1                                    40          0
V1                                    10000          0
PADDING                                   1          0

SQL> select column_name,num_distinct,num_nulls from dba_tab_col_statistics where
 table_name='T1';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS
------------------------------ ------------ ----------
FILTER                                   25        200
JOIN1                                    30        500
V1                                    10000          0
PADDING                                   1          0

SQL> set autotrace traceonly explain
SQL> select     t1.v1, t2.v1
  2  from
  3     t1,
  4     t2
  5  where
  6     t1.filter = 1
  7  and        t2.join1 > t1.join1
  8  and        t2.filter = 1
  9  ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=ALL_ROWS (Cost=68 Card=3881 Bytes
          =131954)

   1    0   MERGE JOIN (Cost=68 Card=3881 Bytes=131954)
   2    1     SORT (JOIN) (Cost=34 Card=198 Bytes=3366)
   3    2       TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=18 Card=198
          Bytes=3366)

   4    1     SORT (JOIN) (Cost=35 Card=392 Bytes=6664)
   5    4       TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=18 Card=392
          Bytes=6664)

 

SQL> select 198*392*0.05 from dual;~~~~~~~~~~~~~~~按 5%規定算,不是按公式 所以與null無關

198*392*0.05
------------
      3880.8

t1 過濾基數=1/(num_distinct) *(10000-200(null))=SQL> select 1/25*(10000-200) from dual;

1/25*(10000-200)
----------------
             392
t2=
SQL> select 1/50*(10000-100) from dual;

1/50*(10000-100)
----------------
             198

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-607804/，如需轉載，請註明出處，否則將追究法律責任。