ITPub博客

首页 > 数据库 > Oracle > 分组函数group by 优化

分组函数group by 优化

原创 Oracle 作者:pingdanorcale 时间:2020-08-05 13:20:12 0 删除 编辑

在开发过程中,好多开发者对表的设计不合适,导致数据库性能出现问题,就像前边所写的,由于设计问题,应用在 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=> '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 order by  这种排序一般会对后边的字段建索引,进行优化,减少临时表空间的使用等(多个字段的一般建复合索引)。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10201716/viewspace-2709367/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-02

  • 博文量
    58
  • 访问量
    106197