Selectivity จงมา!!! การจัดลำดับ Column บน Index

ห่างหายไปนานเลยนะครับ พอดีช่วงนี้งานเยอะมากเลยไม่มีเวลามาแบ่งปันข้อมูลสาระดีๆ กันเลย ก็วันนี้ผมจะนำเสนอเกี่ยวกับความสำคัญในการจัดลำดับก่อนหลังในการสร้าง index เพื่อให้ไปประสิทธิภาพที่ดีที่สุดมาฝากกันครับ

จากบทความเก่าที่ผมเคยเขียนเกี่ยวกับค่า Selectivity บน Column หรือค่าเฉพาะ (Distinct value) นั้นสำคัญมากสำหรับการสร้าง Index ที่ดี ยิ่งมีค่าเฉพาะมากหรือเท่ากับจำนวนบรรทัด นั่นเท่ากับค่า Selectivity ที่เท่ากับ 100% และเราจะได้รับประสิทธิภาพเต็มที่จาก Index ตัวนั้นๆ

แต่วันนี้ผมมานำเสนอในอีกมุมหนึ่งสำหรับการสร้าง Index นั้นคือการเรียงลำดับว่า Column ใดควรขึ้นก่อน-หลัง เรามาดูกันเลยดีกว่าครับ

เริ่มต้นทดสอบค่า Selectivity บน index!

ผมมีตารางข้อมูลที่มีจำนวนบรรทัดทั้งหมด 10,000 บรรทัดชื่อว่า employees และผมจะทำการเลือกข้อมูลพนักงานทั้งหมดที่อยู่ในแผนก 30 ที่เกิดระหว่างช่วง 3 เดือนแรกของปี 1971 ซึ่งผมจะทำการสร้าง Index 2 ตัวตัวแรกชื่อว่า EMP_1 บน DATE_OF_BIRTH,SUBSIDIARY_ID ส่วนอีกตัวนึงมีชื่อว่า EMP_2 บน SUBSIDIARY_ID,DATE_OF_BIRTH ครับ

SQL> CREATE INDEX EMP_1 ON EMPLOYEES(DATE_OF_BIRTH,SUBSIDIARY_ID);

Index created.

SQL> CREATE INDEX EMP_2 ON EMPLOYEES(SUBSIDIARY_ID,DATE_OF_BIRTH);

Index created.

SQL> BEGIN
  2       DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES',
  3       METHOD_OPT=>'for all indexed columns', CASCADE => true);
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL>

จากนั้นผมจะใช้ Explain plan เพื่อทำการทดสอบ Performance ของ Index ทั้ง 2 ตัวที่เพิ่งสร้างไปด้วยคิวรี่ดังนี้ครับ

SELECT first_name, last_name, date_of_birth
  FROM sys.employees
 WHERE date_of_birth >= TO_DATE('1971-01-01', 'YYYY-MM-DD')
   AND date_of_birth <= TO_DATE('1971-03-31', 'YYYY-MM-DD')
   AND subsidiary_id  = 30;

โดยการใส่ Optimizer hint เพื่อบอก Oracle ให้รู้ว่าเราจะใช้ Index ตัวไหนในคิวรี่

1) ทดสอบ Index ตัวแรก EMP_1

SQL> SET LIN 350
SQL> SET PAGES 9999
SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ INDEX(employees emp_1) */ first_name, last_name, date_of_birth
  3    FROM sys.employees
  4   WHERE date_of_birth >= TO_DATE('1971-01-01', 'YYYY-MM-DD')
  5     AND date_of_birth <= TO_DATE('1971-03-31', 'YYYY-MM-DD')
  6     AND subsidiary_id  = 30;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 85688162

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    10 |   350 |    13   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |    10 |   350 |    13   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_1     |    10 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DATE_OF_BIRTH">=TO_DATE(' 1971-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "SUBSIDIARY_ID"=30 AND "DATE_OF_BIRTH"&lt;=TO_DATE(' 1971-03-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("SUBSIDIARY_ID"=30)

17 rows selected.

SQL>

และ 2) ทดสอบ Index IDX_T_2 ครับ

SQL> SET LIN 350
SQL> SET PAGES 9999
SQL> EXPLAIN PLAN FOR
  2  SELECT /*+ INDEX(employees emp_2) */ first_name, last_name, date_of_birth
  3    FROM sys.employees
  4   WHERE date_of_birth >= TO_DATE('1971-01-01', 'YYYY-MM-DD')
  5     AND date_of_birth <= TO_DATE('1971-03-31', 'YYYY-MM-DD')
  6     AND subsidiary_id  = 30;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 267478224

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    10 |   350 |    12   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |    10 |   350 |    12   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_2     |    10 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SUBSIDIARY_ID"=30 AND "DATE_OF_BIRTH">=TO_DATE(' 1971-01-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DATE_OF_BIRTH"&lt;=TO_DATE(' 1971-03-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

16 rows selected.

จากผลด้านบนเราจะสังเกตเห็นว่าคิวรี่จะทำการ Scan index (Access operation) บน DATE_OF_BIRTH เท่านั้นแต่สำหรับ SUBSIDIARY_ID นั้นจะต้องทำการ Scan ทั้งหมดจากผลที่เหลือจาก Range scan ของ DATE_OF_BIRTH ซึ่งใน Explain plan จะแสดงว่า Filter operation

แต่ผลจาก EMP_2 เราจะเห็นว่าคิวรี่นั้นสามารถทำ Index range scan ได้ทั้งบน 2 column (Access operation) ซึ่งในกรณีนี้จะไม่เกิด Leaf node traversal ขึ้นซึ่งจะมีผลทำให้ Performance ที่ได้จากการใช้ EMP_2 นั้นดีกว่า EMP_1

กฏเหล็ก:
*** ถ้าในคิวรี่ของคุณมีการเรียกข้อมูลเป็น Range การจัดลำดับ Index ด้วยการนำ Column ที่เรียกใช้  = หรือเท่ากับขึ้นก่อนแล้วค่อยตามด้วย Range column จะทำให้ Index นั้นๆ มีค่า Selectivity ที่ดีและ Performance ของคิวรี่นั้นดีที่สุด ***

วันนีก็พอเท่านี้ก่อนละกันนะครับ หมดพลังแล้ว ขอตัวไปพักผ่อนก่อนนะครับ สวัสดี 🙂

ขอขอบคุณข้อมูลจาก Use the index Luke!

ขอบคุณภาพสวยๆ จาก Lendingmemo.com

Advertisements

2 thoughts on “Selectivity จงมา!!! การจัดลำดับ Column บน Index

  1. เจ๋งจริงครับ.. ยิ่งดูรูปด้วยแล้วยิ่งเท่ห์ ขอให้ทำต่อไปนะครับ ติดตามดูอยู่

ใส่ความเห็น

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