这篇文章主要讲解了“分组函数group by优化方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“分组函数group by优化方法是什么”吧！

通过实验来说明怎么对字段为空的group by字段进行优化。

sql语句

SELECT COUNT(*), ACCT_TYPE FROM tb_info GROUP by ACCT_TYPE

order by1 ;

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 indextb_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> dropindex tb_ind04;

Index dropped.

SQL>

SQL> create indextb_ind04 on tb_info (acct_type,1);

SQL> explain plan forSELECT 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优化方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！