这篇文章主要讲解了“分组函数group by优化方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“分组函数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=> 'forall indexed columns') ;
PL/SQL procedure successfully completed.
执行计划如下：
explain plan for
2 SELECT COUNT(*), ACCT_TYPE FROM tb_info GROUPby 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 | SELECTSTATEMENT | | 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 | SELECTSTATEMENT | | 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优化方法是什么”的内容了，经过本文的学习后，相信大家对分组函数group by优化方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
