Posted by: siamnobita | 05/25/2016

DISTINCT / GROUP BY PERFORMANCE

หลายวันก่อน ผมได้รับคำถามจากรุ่นน้อง 2 คนในเวลาใกล้เคียงกันว่า ระหว่างการใช้ distinct กับ group by แบบไม่มี aggregate functions แบบใด มีประสิทธิภาพดีกว่ากัน และหนึ่งในนั้นบอกกับผมด้วยว่าเขาได้รับคำแนะนำจาก ผู้รู้ ว่าควรใช้ group by มากกว่า

ผมได้ให้คำตอบไปว่า มันไม่ต่างกัน เพราะ ORACLE ใช้เทคนิคแบบเดียวกันในการหาคำตอบ ได้แก่ การทำ hash table หรือการ sort ข้อมูล โดยแบบหลังจะเกิดขึ้นใน version เก่า ๆ (ประมาณก่อน 10g ถ้าจำไม่ผิด) หรือเมื่อต้องมีการ sort จากขั้นตอนอื่น ๆ อยู่แล้ว หากทั้งสองแบบจะมีประสิทธิภาพต่างกัน ก็น่าจะเกิดเมื่อ optimizer เลือกใช้เทคนิคที่ต่างกันเท่านั้น เพราะการใช้ hash ปกติจะเร็วกว่าการ sort และผมแนะนำว่าหากต้องการกำจัดแถวที่ซ้ำออกแล้ว การใช้ distinct จะเป็นการสื่อความต้องการได้ตรงมากกว่า

อย่างไรก็ดี หลังจากตอบคำถามไปแล้ว เพื่อยืนยันความถูกต้อง (เผื่อตอบผิด จะได้รีบแก้ตัว 555) ผมจึงทำการทดสอบง่าย ๆ โดยใช้ตาราง SALES ใน schema SH ซึ่งมีข้อมูลอยู่ประมาณ 9 แสนแถว ผลลัพธ์ที่ได้น่าสนใจมาก

SQL> set timi on
SQL> set autotrace traceonly statistics explain
SQL> select distinct s.prod_id
  2  from sh.sales s ;

72 rows selected.

Elapsed: 00:00:00.01

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |    72 |   288 |   194  (86)|       |       |
|   1 |  HASH UNIQUE                  |                |    72 |   288 |   194  (86)|       |       |
|   2 |   PARTITION RANGE ALL         |                |   918K|  3589K|    29   (0)|     1 |    28 |
|   3 |    BITMAP INDEX FAST FULL SCAN| SALES_PROD_BIX |   918K|  3589K|    29   (0)|     1 |    28 |
----------------------------------------------------------------------------------------------------

SQL> select s.prod_id
  2  from sh.sales s
  3  group by s.prod_id ;

72 rows selected.

Elapsed: 00:00:00.09

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |    72 |   288 |   194  (86)|       |       |
|   1 |  HASH GROUP BY                 |                |    72 |   288 |   194  (86)|       |       |
|   2 |   PARTITION RANGE ALL          |                |   918K|  3589K|    29   (0)|     1 |    28 |
|   3 |    BITMAP CONVERSION TO ROWIDS |                |   918K|  3589K|    29   (0)|       |       |
|   4 |     BITMAP INDEX FAST FULL SCAN| SALES_PROD_BIX |       |       |            |     1 |    28 |
----------------------------------------------------------------------------------------------------

ดูเผิน ๆ ก็ใช้เวลาไม่ต่างกันมากนัก แต่จากการทดสอบซ้ำหลาย ๆ ครั้ง ผลลัพธ์ที่ได้ก็ไม่อาจจะเรียกได้ว่าใช้เวลาเท่ากัน โดยฝ่าย distinct เร็วกว่า เมื่อเข้าไปดูใน plan จึงพบความแตกต่างบางอย่าง ใน plan ที่สองมีบรรทัด BITMAP CONVERSION TO ROWIDS เพิ่มขึ้นมา ซึ่งผมก็ไม่รู้เหมือนกันว่าทำเพื่ออะไร แต่ที่แน่ ๆ มันทำให้งานเพิ่มและ query ช้าลง นอกนั้น HASH GROUP BY กับ HASH UNIQUE แทบจะใช้เวลาไม่ต่างกันเลย

ผมลองทดสอบด้วยการใส่ hint บังคับห้ามใช้ index ผลเป็นดังนี้

SQL> select /*+ no_index(s) */ distinct s.prod_id
  2  from sh.sales s ;

72 rows selected.

Elapsed: 00:00:00.17

-----------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    72 |   288 |   639  (31)|       |       |
|   1 |  HASH UNIQUE         |       |    72 |   288 |   639  (31)|       |       |
|   2 |   PARTITION RANGE ALL|       |   918K|  3589K|   474   (7)|     1 |    28 |
|   3 |    TABLE ACCESS FULL | SALES |   918K|  3589K|   474   (7)|     1 |    28 |
-----------------------------------------------------------------------------------

SQL> select /*+ no_index(s) */ s.prod_id
  2  from sh.sales s
  3  group by s.prod_id ;

72 rows selected.

Elapsed: 00:00:00.22

-----------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    72 |   288 |   639  (31)|       |       |
|   1 |  HASH GROUP BY       |       |    72 |   288 |   639  (31)|       |       |
|   2 |   PARTITION RANGE ALL|       |   918K|  3589K|   474   (7)|     1 |    28 |
|   3 |    TABLE ACCESS FULL | SALES |   918K|  3589K|   474   (7)|     1 |    28 |
-----------------------------------------------------------------------------------

SQL> select /*+ no_index(s) */ distinct s.prod_id
  2  from sh.sales s
  3  order by prod_id ;

72 rows selected.

Elapsed: 00:00:00.25

-----------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    72 |   288 |  3344  (12)|       |       |
|   1 |  SORT UNIQUE         |       |    72 |   288 |  3179   (7)|       |       |
|   2 |   PARTITION RANGE ALL|       |   918K|  3589K|   474   (7)|     1 |    28 |
|   3 |    TABLE ACCESS FULL | SALES |   918K|  3589K|   474   (7)|     1 |    28 |
-----------------------------------------------------------------------------------

SQL> select /*+ no_index(s) */ s.prod_id
  2  from sh.sales s
  3  group by s.prod_id
  4  order by prod_id ;

72 rows selected.

Elapsed: 00:00:00.23

-----------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    72 |   288 |   639  (31)|       |       |
|   1 |  SORT GROUP BY       |       |    72 |   288 |   639  (31)|       |       |
|   2 |   PARTITION RANGE ALL|       |   918K|  3589K|   474   (7)|     1 |    28 |
|   3 |    TABLE ACCESS FULL | SALES |   918K|  3589K|   474   (7)|     1 |    28 |
-----------------------------------------------------------------------------------

คราวนี้ ผลเป็นไปตามคาดหมาย คือแทบไม่ต่างกันเลยเมื่อทดสอบซ้ำหลาย ๆ ครั้ง ดังนั้นคำพูดที่ว่า GROUP BY เร็วกว่า DISTINCT นั้นเห็นได้ชัดว่า ไม่เป็นความจริง เรามีตัวอย่างกรณีที่ DISTINCT เร็วกว่ามาให้ดูแล้ว

อย่างไรก็ดี นี่เป็นการทดสอบบน ORACLE 11gR2 ผมเองก็ไม่แน่ใจว่าในเวอร์ชั่นอื่น ๆ ผลลัพธ์จะแตกต่างจากนี้หรือไม่ และส่วนใหญ่แล้วถ้าไม่ใช่ bitmap index เราก็คงไม่ได้เห็นความแตกต่างแบบนี้ หากเราจะรีบสรุปว่า DISTINCT เร็วกว่า GROUP BY งั้นเรามารอดูกันใน post หน้าครับ

สวัสดีครับ


ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s

หมวดหมู่

%d bloggers like this: