在开发过程中，好多开发者对表的设计不合适，导致数据库性能出现问题，就像前边所写的，由于设计问题 ，应用在 where 条件中用 is null
导致全表扫面，本文是上篇的延续，在这里讨论一下 group by 的优化方法。
通过实验来说明怎么对字段为空的 group by 字段进行优化。
sql 语句
SELECT COUNT(*), ACCT_TYPE FROM tb_info GROUP by ACCT_TYPE
order by 1 ;
COUNT(*) ACCT_T
---------- ------
2 304
7 205
8 204
10 802
15 214
22 202
26 211
40 805
238 200
5982
19692 300
COUNT(*) ACCT_T
---------- ------
200761 100
在表上 tb_info 创建 ACCT_TYPE 一般索引
SQL> create index tb_ind04 on tb_info (acct_type);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'tb_info',estimate_percent => 10,method_opt=> 'for all indexed columns') ;
PL/SQL procedure successfully completed.
执行计划如下：
explain plan for
2 SELECT COUNT(*), ACCT_TYPE FROM tb_info GROUP by ACCT_TYPE;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1843165528
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 32 | 1737 (2)| 00:00:21 |
| 1 | HASH GROUP BY | | 8 | 32 | 1737 (2)| 00:00:21 |
| 2 | TABLE ACCESS FULL| tb_info | 226K| 883K| 1725 (1)| 00:00:21 |
为什么不走索引？ 原因在这
238 200
5982
19692 300
此字段有 5982 个是空值。
增加索引，让 ACCT_TYPE 空值的也保存在索引中
SQL> drop index tb_ind04;
Index dropped.
SQL>
SQL> create index tb_ind04 on tb_info (acct_type,1);
SQL> explain plan for SELECT COUNT(*), ACCT_TYPE FROM tb_info GROUP by ACCT_TYPE;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1399786149
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 32 | 169 (9)| 00:00:03 |
| 1 | HASH GROUP BY | | 8 | 32 | 169 (9)| 00:00:03 |
| 2 | INDEX FAST FULL SCAN| TB_IND04 | 226K| 883K| 157 (2)| 00:00:02 |
---------------------------------------------------------------------------------- 总结： 对 group by order by 这种排序一般会对后边的字段建索引，进行优化（多个字段的一般建复合索引）。
