ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (连接选择率,范围与null)

oracle实验记录 (连接选择率,范围与null)

原创 Linux操作系统 作者:fufuh2o 时间:2009-06-29 14:03:58 0 删除 编辑

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

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426913