1.2 选择性（SELECTIVITY）
基数与总行数的比值再乘以100%就是某个列的选择性。
在进行SQL优化的时候，单独看列的基数是没有意义的，基数必须对比总行数才有实际意义，正是因为这个原因，我们才引出了选择性这个概念。
下面我们查看test表各个列的基数与选择性，为了查看选择性，必须先收集统计信息。关于统计信息，我们在第2章会详细介绍。下面的脚本用于收集test表的统计信息。
SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', 3 tabname => 'TEST', 4 estimate_percent => 100, 5 method_opt => 'for all columns size 1', 6 no_invalidate => FALSE, 7 degree => 1, 8 cascade => TRUE); 9 END; 10 / PL/SQL procedure successfully completed.
下面的脚本用于查看test表中每个列的基数与选择性。
SQL> select a.column_name, 2 b.num_rows, 3 a.num_distinct Cardinality, 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity, 5 a.histogram, 6 a.num_buckets 7 from dba_tab_col_statistics a, dba_tables b 8 where a.owner = b.owner 9 and a.table_name = b.table_name 10 and a.owner = 'SCOTT' 11 and a.table_name = 'TEST'; COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS --------------- ---------- ----------- ----------- --------- ----------- OWNER 72462 29 .04 NONE 1 OBJECT_NAME 72462 44236 61.05 NONE 1 SUBOBJECT_NAME 72462 106 .15 NONE 1 OBJECT_ID 72462 72462 100 NONE 1 DATA_OBJECT_ID 72462 7608 10.5 NONE 1 OBJECT_TYPE 72462 44 .06 NONE 1 CREATED 72462 1366 1.89 NONE 1 LAST_DDL_TIME 72462 1412 1.95 NONE 1 TIMESTAMP 72462 1480 2.04 NONE 1 STATUS 72462 1 0 NONE 1 TEMPORARY 72462 2 0 NONE 1 GENERATED 72462 2 0 NONE 1 SECONDARY 72462 2 0 NONE 1 NAMESPACE 72462 21 .03 NONE 1 EDITION_NAME 72462 0 0 NONE 0 15 rows selected.
请思考：什么样的列必须建立索引呢？
有人说基数高的列，有人说在where条件中的列。这些答案并不完美。基数高究竟是多高？没有和总行数对比，始终不知道有多高。比如某个列的基数有几万行，但是总行数有几十亿行，那么这个列的基数还高吗？这就是要引出选择性的根本原因。
当一个列选择性大于20%，说明该列的数据分布就比较均衡了。测试表test中object_name、object_id的选择性均大于20%，其中object_name列的选择性为61.05%。现在我们查看该列数据分布（为了方便展示，只输出前10行数据的分布情况）。
SQL> select * 2 from (select object_name, count(*) 3 from test 4 group by object_name 5 order by 2 desc) 6 where rownum <= 10; OBJECT_NAME COUNT(*) ------------------ ---------- COSTS 30 SALES 30 SALES_CHANNEL_BIX 29 COSTS_TIME_BIX 29 COSTS_PROD_BIX 29 SALES_TIME_BIX 29 SALES_PROMO_BIX 29 SALES_PROD_BIX 29 SALES_CUST_BIX 29 DBMS_REPCAT_AUTH 5 10 rows selected.
由上面的查询结果我们可知，object_name列的数据分布非常均衡。我们查询以下SQL。
select * from test where object_name=:B1;
不管object_name传入任何值，最多返回30行数据。
什么样的列必须要创建索引呢？当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索引，从而提升SQL查询性能。当然了，如果表只有几百条数据，那我们就不用创建索引了。
下面抛出SQL优化核心思想第一个观点：只有大表才会产生性能问题。
也许有人会说：“我有个表很小，只有几百条，但是该表经常进行DML，会产生热点块，也会出性能问题。”对此我们并不想过多地讨论此问题，这属于应用程序设计问题，不属于SQL优化的范畴。
下面我们将通过实验为大家分享本书第一个全自动优化脚本。
抓出必须创建索引的列（请读者对该脚本适当修改，以便用于生产环境）。
首先，该列必须出现在where条件中，怎么抓出表的哪个列出现在where条件中呢？有两种方法，一种是可以通过V$SQL_PLAN抓取，另一种是通过下面的脚本抓取。
先执行下面的存储过程，刷新数据库监控信息。
begin dbms_stats.flush_database_monitoring_info; end;
运行完上面的命令之后，再运行下面的查询语句就可以查询出哪个表的哪个列出现在where条件中。
select r.name owner,
o.name table_name,
c.name column_name,
equality_preds, ---等值过滤
equijoin_preds, ---等值JOIN 比如where a.id=b.id
nonequijoin_preds, ----不等JOIN
range_preds, ----范围过滤次数 > >= < <= between and
like_preds, ----LIKE过滤
null_preds, ----NULL 过滤
timestamp
from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
where o.obj# = u.obj#
and c.obj# = u.obj#
and c.col# = u.intcol#
and r.name = 'SCOTT'
and o.name = 'TEST';
下面是实验步骤。
我们首先运行一个查询语句，让owner与object_id列出现在where条件中。
SQL> select object_id, owner, object_type
2 from test
3 where owner = 'SYS'
4 and object_id < 100
5 and rownum <= 10;
OBJECT_ID OWNER OBJECT_TYPE
---------- -------------------- -----------
20 SYS TABLE
46 SYS INDEX
28 SYS TABLE
15 SYS TABLE
29 SYS CLUSTER
3 SYS INDEX
25 SYS TABLE
41 SYS INDEX
54 SYS INDEX
40 SYS INDEX
10 rows selected.
其次刷新数据库监控信息。
SQL> begin 2 dbms_stats.flush_database_monitoring_info; 3 end; 4 / PL/SQL procedure successfully completed.
然后我们查看test表有哪些列出现在where条件中。
SQL> select r.name owner, o.name table_name, c.name column_name 2 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r 3 where o.obj# = u.obj# 4 and c.obj# = u.obj# 5 and c.col# = u.intcol# 6 and r.name = 'SCOTT' 7 and o.name = 'TEST'; OWNER TABLE_NAME COLUMN_NAME ---------- ---------- ------------------------------ SCOTT TEST OWNER SCOTT TEST OBJECT_ID
接下来我们查询出选择性大于等于20%的列。
SQL> select a.owner, 2 a.table_name, 3 a.column_name, 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity 5 from dba_tab_col_statistics a, dba_tables b 6 where a.owner = b.owner 7 and a.table_name = b.table_name 8 and a.owner = 'SCOTT' 9 and a.table_name = 'TEST' 10 and a.num_distinct / b.num_rows >= 0.2; OWNER TABLE_NAME COLUMN_NAME SELECTIVITY ---------- ---------- ------------- ----------- SCOTT TEST OBJECT_NAME 61.05 SCOTT TEST OBJECT_ID 100
最后，确保这些列没有创建索引。
SQL> select table_owner, table_name, column_name, index_name 2 from dba_ind_columns 3 where table_owner = 'SCOTT' 4 and table_name = 'TEST'; 未选定行
把上面的脚本组合起来，我们就可以得到全自动的优化脚本了。
SQL> select owner, 2 column_name, 3 num_rows, 4 Cardinality, 5 selectivity, 6 'Need index' as notice 7 from (select b.owner, 8 a.column_name, 9 b.num_rows, 10 a.num_distinct Cardinality, 11 round(a.num_distinct / b.num_rows * 100, 2) selectivity 12 from dba_tab_col_statistics a, dba_tables b 13 where a.owner = b.owner 14 and a.table_name = b.table_name 15 and a.owner = 'SCOTT' 16 and a.table_name = 'TEST') 17 where selectivity >= 20 18 and column_name not in (select column_name 19 from dba_ind_columns 20 where table_owner = 'SCOTT' 21 and table_name = 'TEST') 22 and column_name in 23 (select c.name 24 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r 25 where o.obj# = u.obj# 26 and c.obj# = u.obj# 27 and c.col# = u.intcol# 28 and r.name = 'SCOTT' 29 and o.name = 'TEST'); OWNER COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY NOTICE ---------- ------------- ---------- ----------- ----------- ---------- SCOTT OBJECT_ID 72462 72462 100 Need index