Shulou(Shulou.com)05/31 Report--

This article mainly explains the "grouping function group by optimization method is what", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "grouping function group by optimization method is what" it!

Experiments are carried out to show how to optimize group by fields with empty fields.

Sql statement

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

Create an ACCT_TYPE generic index on the table by tb_info

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 forall indexed columns' methodological opt = > 'forall indexed columns')

PL/SQL procedure successfully completed.

The implementation plan is as follows:

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 |

Why not take the index? That's the reason.

238 200

5982

19692 300

There are 5982 null values in this field.

Add the index so that the null value of ACCT_TYPE is also saved in the index

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 | 157K (2) | 00:00:02 |

Thank you for your reading, the above is the content of "what is the optimization method of grouping function group by". After the study of this article, I believe you have a deeper understanding of what the optimization method of grouping function group by is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.