ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (oracle单表选择率与基数计算(3))

oracle实验记录 (oracle单表选择率与基数计算(3))

原创 Linux操作系统 作者:fufuh2o 时间:2009-09-14 21:55:51 0 删除 编辑

这次分析单表多谓词(不同列)选择率 和基数计算
补充不等于(<>,!=)的选择率,card计算


2个谓词(不同列) and

SQL> create table t2(a int ,b int);

表已创建。

 

SQL> ed
已写入 file afiedt.buf

  1  declare
  2  begin
  3  for i in 1..10000 loop
  4  insert into t2 values(i,i+1);
  5  end loop;
  6  commit;
  7* end;
  8  /

PL/SQL 过程已成功完成。

SQL> execute dbms_stats.gather_table_stats('SYS','T2');

PL/SQL 过程已成功完成。
SQL> select column_name,num_distinct,num_nulls from user_tab_col_statistics wher
e table_name='T2';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS
------------------------------ ------------ ----------
A                                     10000          0
B                                     10000          0
SQL> select num_rows from user_tables where table_name='T5';

  NUM_ROWS
----------
     10000

 

SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select count(*) from t2 where a>2 and b<100;

执行计划
----------------------------------------------------------
Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |    98 |   686 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("B"<100 AND "A">2)


多 谓词and选择率
=谓词1选择率×谓词2选择率(若 N 个 多 谓词and就是 N个 谓词选择率相乘)

a>2下(无边界,开区间)
=(high_value – limit) / (high_value – low_value)=
SQL> select (10000-2)/(10000-1) from dual;

(10000-2)/(10000-1)
-------------------
          .99989999
b<100(无边界,开区间)(limit – low_value) / (high_value – low_value)=
SQL> select (100-1)/(10000-1) from dual;

(100-1)/(10000-1)
-----------------
        .00990099

a>2 and b<100选择率=

SQL> select .00990099*.99989999 from dual;

.00990099*.99989999
-------------------
              .0099

SQL> select 10000*.00990099*.99989999 from dual;~~~~~~~~~~~~card

10000*.00990099*.99989999
-------------------------
                98.999998

 

 

SQL> select count(*) from t2 where a>2 or b<100;

执行计划
----------------------------------------------------------
Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |  9999 | 69993 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A">2 OR "B"<100)

 


2个谓词(不同列) OR
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select count(*) from t2 where a>2 or b<100;

执行计划
----------------------------------------------------------
Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |  9999 | 69993 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A">2 OR "B"<100)

P1 or P2 选择率=p1选择率+P2选择率-(P1选择率and p2选择率)
A>2选择率=.99989999
b<100选择率= .00990099
p1 or p2选择率=

SQL> select .99989999+ .00990099-.0099 from dual
  2  ;

.99989999+.00990099-.0099
-------------------------
                .99990098

SQL> select 10000*.99990098 from dual;

10000*.99990098
---------------
      9999.0098~~~~~~~~~~~~~~~~~~~~~~~~~~~~~card

 

 


~~~~~~~~~~~~不等于(<>,!=) ,NOT IN

SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

会话已更改。

SQL> select count(*) from t2 where a <>2;~~~~~~~~~~~~

执行计划
----------------------------------------------------------
Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |  9999 | 39996 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<>2)

a选择率=1- (a=该字面值的选择率)


SQL> select 1- 1/10000 from dual;

 1-1/10000
----------
     .9999

SQL> select 10000*(1- 1/10000) from dual;~~~~~~~~~card

10000*(1-1/10000)
-----------------
             9999

SQL> select count(*) from t2 where a not in (1);

执行计划
----------------------------------------------------------
Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |  9999 | 39996 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<>1)


SQL> select count(*) from t2 where a not in (1,2);

执行计划
----------------------------------------------------------
Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |  9998 | 39992 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"<>1 AND "A"<>2)

IN LIST选择率 如果不出区间(最大最小值),且oracle可判断出重复值不计算,选择率= 2*(1/num_distinct)
 SQL> select 2*1/10000 from dual;

 2*1/10000
----------
     .0002
NOT IN选择率=1-谓词选择率=
SQL> select 1-2*1/10000 from dual;

1-2*1/10000
-----------
      .9998
SQL> select 10000*(1-2*1/10000) from dual;

10000*(1-2*1/10000)
-------------------
               9998~~~~~~~~~~~~~~~~~~~~~~~~card

 

多个and

SQL> create table t3 (a int,b int, c int);

表已创建。

SQL> declare
  2  begin
  3  for i in 1..10000 loop
  4  insert into t3 values(i,i+1,i+2);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> execute dbms_stats.gather_table_stats('SYS','T3');

PL/SQL 过程已成功完成。

SQL>
SQL> select count(*) from t3 where a>2 and b<10 and c>3;

执行计划
----------------------------------------------------------
Plan hash value: 463314188

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    11 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| T3   |     8 |    88 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("B"<10 AND "A">2 AND "C">3)

a>1选择率=(high_value – limit) / (high_value – low_value)=
SQL> select (10000-2)/(10000-1) from dual;

(10000-2)/(10000-1)
-------------------
          .99989999

a<10选择率=(limit – low_value) / (high_value – low_value)=SQL> select (10-1)/(10000-1) from dual;

(10-1)/(10000-1)
----------------
       .00090009

c>3选择率=(high_value – limit) / (high_value – low_value)=
SQL> select (10000-3)/(10000-1) from dual;

(10000-3)/(10000-1)
-------------------
          .99979998


SQL> select .99979998*.00090009*.99989999 from dual; a>2 and b<10 and c>3选择率

.99979998*.00090009*.99989999
-----------------------------
                    .00089982

SQL> select 10000*.00089982 from dual;

10000*.00089982
---------------
         8.9982~~~~~~~~~~~~~~~~~~~`card

多个or
sel(A or B or C) =
sel(A) + sel(B) + sel(C) – Sel(A)sel(B) – Sel(B)sel(C) – sel(C)sel(A) + Sel(A)Sel(B)Sel(C)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-614640/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    427842