oracle實驗記錄 (oracle單表選擇率與基數計算(3))

fufuh2o發表於2009-09-14
Oracle

這次分析單表多謂詞(不同列)選擇率 和基數計算
補充不等於（<>,!=）的選擇率,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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章