oracle实验记录（连接选择率,范围与null）-CSDN博客

~------------------------------------
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-607804/