选择率和基数简析

--选择率和基数简析
选择率（selectivity）是一个介于0和1之间的值，用来表示某个操作所返回的记录数的比例。
一个操作返回记录的行数称作基数（cardinality）。
**基数 = 选择率 * 行数

--创建模拟实验环境
SCOTT@PROD1> set echo on
SCOTT@PROD1> start /tmp/1.sql
SCOTT@PROD1> SET TERMOUT ON
SCOTT@PROD1> SET FEEDBACK ON
SCOTT@PROD1> SET VERIFY OFF
SCOTT@PROD1> SET SCAN ON
SCOTT@PROD1> 
SCOTT@PROD1> COLUMN pad FORMAT a20 TRUNCATE
SCOTT@PROD1> 
SCOTT@PROD1> DROP TABLE t;

Table dropped.

SCOTT@PROD1> 
SCOTT@PROD1> execute dbms_random.seed(0)

PL/SQL procedure successfully completed.

SCOTT@PROD1> 
SCOTT@PROD1> CREATE TABLE t
  2  AS
  3  SELECT rownum AS id,
  4  	    round(5678+dbms_random.normal*1234) AS n1,
  5  	    mod(255+trunc(dbms_random.normal*1000),255) AS n2,
  6  	    dbms_random.string('p',255) AS pad
  7  FROM dual
  8  CONNECT BY level <= 10000
  9  ORDER BY dbms_random.value;

Table created.

SCOTT@PROD1> 
SCOTT@PROD1> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

Table altered.

SCOTT@PROD1> CREATE INDEX t_n2_i ON t (n2);

Index created.

SCOTT@PROD1> 
SCOTT@PROD1> BEGIN
  2    dbms_stats.gather_table_stats(
  3  	 ownname	  => user,
  4  	 tabname	  => 'T',
  5  	 estimate_percent => 100,
  6  	 method_opt	  => 'for all columns size skewonly',
  7  	 cascade	  => TRUE
  8    );
  9  END;
 10  /

PL/SQL procedure successfully completed.

--select * from t
10000 rows selected.

SCOTT@PROD1> l
  1* select * from t
  
--此处没有where条件，返回所有记录，基数等于表中的行数，所以选择率为1，也可称为弱选择性。

--select * from t where n1 between 2000 and 2400;
26 rows selected.

SCOTT@PROD1> l
  1* select * from t where n1 between 2000 and 2400
  
--这个查询中基数为26，所以选择率为26/10000=0.0026，当选择率接近0时，也可称为具有强选择性。

--select * from t where n1 = 14；
SCOTT@PROD1> select * from t where n1 = 14;

no rows selected

--这个查询无返回行，所以基数为0，选择率为0。

由于以上三个查询都不包含连接或聚合操作，所以这种算法可行，下面举例。

--select sum(n2) from t where n1 between 3100 and 4300;
SCOTT@PROD1> select sum(n2) from t where n1 between 3100 and 4300;

   SUM(N2)
----------
     31554

1 row selected.

--此时不能简单的计算基数为1，选择率为万分之一，而是要先找出输入到聚合函数sum前的输入源。

SCOTT@PROD1> select count(*) from t where n1 between 3100 and 4300;

  COUNT(*)
----------
      1116

1 row selected.

--输入源为1116，所以‘select sum(n2) from t where n1 between 3100 and 4300;’基数为1116，选择率为0.1116。