Group by 最佳化

 在開發過程中，好多開發者對錶的設計不合適，導致資料庫效能出現問題，就像前邊所寫的，由於設計問題 ，應用在 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 這種排序一般會對後邊的欄位建索引，進行最佳化（多個欄位的一般建複合索引）。