这次分析单表多谓词(不同列)选择率 和基数计算
补充不等于（<>,!=）的选择率,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博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-614640/ ，如需转载，请注明出处，否则将追究法律责任。
