一个同事在做方案的时候，问我一个问题：如何快速从百万行的数据表中，快速获取到指定列的所有取定值。
这个问题的困难点在于对数据表数据列的扫描。最直接的方法无过于进行全表扫描+distinct操作。但是，因为数据量的原因，我们通常希望有更加优化的策略和方式。
Distinct+全表扫描方法
我们构建一个简单是实验环境，来模拟下执行计划。
SQL> create table tt as select * from dba_objects;
Table created
SQL> select count(*) from tt;
COUNT(*)
----------
51361 //减小规模
SQL> desc tt;
Name Type Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(128) Y
（篇幅原因，有省略…）
TEMPORARY VARCHAR2(1) Y
GENERATED VARCHAR2(1) Y
SECONDARY VARCHAR2(1) Y
注意，owner列此时是可空列选项。我们希望知道一共有多少个owner取值存在。
SQL> explain plan for select distinct owner from tt;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3008180766
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 144 | 174 (6)| 00:00:03 |
| 1 | HASH UNIQUE | | 24 | 144 | 174 (6)| 00:00:03 |
| 2 | TABLE ACCESS FULL| TT | 51361 | 300K| 167 (2)| 00:00:03 |
---------------------------------------------------------------------------
9 rows selected
注意这个执行计划的细节：对数据表TT的全表扫描。Oracle CBO选择对数据表TT的所有数据块扫描一遍，获取到所有的owner取值。之后，利用hash操作将相同的owner值进行合并。
优化尝试一
一种优化的思路就是针对全表扫描最大的消耗点进行优化。默认方案中，对数据表所有数据表进行扫描，是性能和资源消耗最大的部分。思考如何在尽可能少IO块读取的情况下，获取到所有owner列表。
答案是利用owner列上的索引。如果我们添加了owner列的索引，就可以将所有owner值汇集到索引的叶子节点上。这样就在少量索引块上集中了所有的owner值。
SQL> explain plan for select distinct owner from tt;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3008180766
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 162 | 174 (6)| 00:00:03 |
| 1 | HASH UNIQUE | | 27 | 162 | 174 (6)| 00:00:03 |
| 2 | TABLE ACCESS FULL| TT | 51361 | 300K| 167 (2)| 00:00:03 |
---------------------------------------------------------------------------
9 rows selected
出现了一些问题，CBO没有像我们预想的那样扫描索引结构，而是依然扫描数据表，之后进行Hash操作。
原因是什么呢？猜想是这样。Oracle在最下层的阶段，的确是选择有没有更快更高效的寻找owner所有值的方法。进行全表扫描必然是消耗资源最大，但也是最能保证全部覆盖的方法。进行索引扫描的时候，Oracle需要保证进行叶节点扫描后，可以获取到所有owner值（包括空值）。注意，空值null是不会进入索引树的。所以，Oracle在这里因为认为获取到的叶子节点上的owner值不全，才放弃了这种方式。
优化尝试二
那么，如何才能让Oracle相信该列全部值都在索引叶子节点上呢？答案就是对列属性进行修改。之前笔者的blog：《数据列not null对索引影响一例》（http://space.itpub.net/17203031/viewspace-682684）中，已经讲述过这种方法。
简单的说，not null虽然是一个约束，但是从侧面上是该列的一个属性，会影响到优化器工作的。
SQL> alter table TT modify OWNER not null;
Table altered
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
此时，进行执行计划获取。
SQL> explain plan for select distinct owner from tt;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3631459427
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 162 | 36 (23)| 00:0
| 1 | HASH UNIQUE | | 27 | 162 | 36 (23)| 00:0
| 2 | INDEX FAST FULL SCAN| IDX_TT_OWNER | 51361 | 300K| 29 (4)| 00:0
--------------------------------------------------------------------------------
9 rows selected
终于，我们看到了进行索引扫描的执行计划。相应的执行CPU成本也下降到原来的20%。执行方法“INDEX FAST FULL SCAN”表示的就是只进行索引叶子节点的扫描，而不进行数据块的扫描。
这个案例，我们获得如下的经验：
ü 对一些关键数据表（海量），无论是多小的一个操作，都存在性能问题的隐患。要在开发设计阶段就主动进行识别，之后密切关注。及时修正设计和开发方式。不要等待最后性能测试乃至投产之后才开始着手，此时大局已定，入手点较少；
ü 索引路径不是只在where条件后出现才会出现在执行计划中。CBO优化器智能程度很高，在统计量正常的情况下，一般是可以获取到正确的路径和方法的；
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17203031/viewspace-692915/ ，如需转载，请注明出处，否则将追究法律责任。
