Function-based index กับการใช้งานคอลัมน์ DATE

สำหรับวันนี้ผมจะมาทำจำลองเหตุการณ์ว่าทำไม? เมื่อเราใช้ TRUNC ฟังก์ชั่นบน DATE คอลัมน์ในคิวรี่แล้ว ทำไมคิวรี่ของเราจึงช้าลง ในขณะทีเราก็สร้างอินเด็กซ์บน DATE ไปให้แล้ว และในช่วงท้ายผมจะพูดถึงพระเอกของงานนี้นั้นคือ function-based index นั่นเองครับ เอาละครับมาเริ่มกันเลย
หนึ่งในสาเหตุสำหรับปัญหาเกี่ยวกับ SQL Statement ที่ได้พบได้เจอกันบ่อยๆ นั่นก็คือ Full table scan ซึ่งถูกกำหนดให้เป็น invalidating บน Index ใน SQL Statement ที่ WHERE clause ถูกเรียกใช้งาน Built-in function บน DATE column มาดูตัวอย่างกันครับ

ตัวอย่างนี้ WHERE clause เหล่านี้จะเรียกใช้ “UNEXPECTED” Full table scan

WHERE TRUNC(send_date) = TRUNC(sysdate);
WHERE TO_CHAR(ship_date,'YYYY-MM-DD') = '2014-01-31';

ลองดูตัวอย่างข้างล่างนี้ครับ

(เหตุการณ์จำลองนี้สร้างขึ้นบน Oracle 9i ครับ)

สร้าง Table T1 ขึ้นมาโดยมี 2 Column คือ OBJECT_ID (NUMBER) และ CREATED (DATE) จาก DBA_OBJECTS

SQL> CREATE TABLE T1 AS SELECT OBJECT_ID,CREATED FROM DBA_OBJECTS;

Table created.

จากนั้นทำการสร้าง Index ขึ้นมาบน CREATED column ซึ่งเป็น DATE type ครับ

SQL> CREATE INDEX IDX_T1 ON T1(CREATED);

Index created.

Gather stats ให้เรียบร้อยครับ

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3       OWNNAME => USER, 
  4       TABNAME => 'T1', 
  5       CASCADE => TRUE ); 
  6  END; 
  7  / 

PL/SQL procedure successfully completed.

เรามาดูกันครับว่า ถ้าเราใช้ Built-in function ใน SQL จะเกิดอะไรขึ้น

SQL> SELECT SYSDATE-3 FROM DUAL;

SYSDATE-3
---------
28-JAN-14

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM T1 WHERE TRUNC(CREATED) >= TRUNC(SYSDATE-3);

Explained.

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  1834 | 23842 |   280  (86)|
|*  1 |  TABLE ACCESS FULL   | T1          |  1834 | 23842 |   280  (86)|
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
        1 - filter(TRUNC("T1"."CREATED")>=TRUNC(SYSDATE@!-3))

12 rows selected.

จาก Explain plan เราจะเห็นว่า SQL จะเรียกใช้งาน Full table scan นั่นเพราะว่า WHERE clause มีการใข้งาน TRUNC ซึ่งไปมีผลทำให้ SQL กำหนดให้ Index IDX_T1 เป็น Invalidating column

แล้วทำอย่างไรหละ Index ถึงจะถูกเรียกใช้? มีหลายวิธีด้วยกันครับ
วิธีแรกง่ายๆ เลยคือ Fix predicate ดังตัวอย่างข้างล่างนี้ครับ 

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM T1 WHERE CREATED >= TO_DATE('28-JAN-14','DD-MON-YY');

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    47 |   611 |    14   (8)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |    47 |   611 |    14   (8)|
|*  2 |   INDEX RANGE SCAN          | IDX_T1      |    47 |       |     3  (34)|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
         2 - access("T1"."CREATED">=TO_DATE('28-JAN-14','DD-MON-YY') AND
                    "T1"."CREATED" IS NOT NULL)

14 rows selected.

ด้วยวิธีนี้จะเห็นว่าในที่สุด Index ก็จะถูกเรียกใช้งานเนื่องจาก เราไม่ได้ใช้ built-in function ครับ

วิธีที่ 2 Call FBI หรือ Function-based index นั่นเองครับ

SQL> CREATE INDEX IDX_FBI_T1 ON T1(TRUNC(CREATED));

Index created.

และเพื่อทำให้ Function-based index ทำงานอย่างมีประสิทธิภาพ ผมจะเพิ่ม METHOD_OPT ในการ Gather stats ลงไปด้วยตามตัวอย่างข้างล่างครับ

SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3       OWNNAME    => USER,
  4       TABNAME    => 'T1' ,
  5       CASCADE    => TRUE,
  6       METHOD_OPT => 'FOR ALL HIDDEN COLUMNS SIZE 1');
  7  END;
  8  /

PL/SQL procedure successfully completed.

มาดูผลของการใช้ Function-based index กันครับ

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM T1 WHERE TRUNC(CREATED) >= TRUNC(SYSDATE-3);

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  1834 | 25676 |    18   (6)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |  1834 | 25676 |    18   (6)|
|*  2 |   INDEX RANGE SCAN          | IDX_FBI_T1  |   330 |       |     3  (34)|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
         2 - access(TRUNC("T1"."CREATED")>=TRUNC(SYSDATE@!-3) AND
                    TRUNC("T1"."CREATED") IS NOT NULL)

14 rows selected.

สุดท้ายคิวรี่ของเราที่มีการเรียกใช้ built-in function ก็สามารถใช้ index ได้อย่างที่เราต้องการแล้วครับ ต้องขอบคุณเจ้าหน้าที่ FBI เอ้ยย Function-based index จริงๆ ครับ

ขอบคุณข้อมูลประกอบจาก

Charles Hooper’s Oracle Notes
Oracle Tips by Burleson Consulting

Author: MrNobita Namsutto

Advertisements

2 thoughts on “Function-based index กับการใช้งานคอลัมน์ DATE

  1. สรุปแล้ว การเขียน Query แต่ละครั้ง ควร หรือไม่่ควรใช้ built-in function ครับ

    • ขอบคุณครับสำหรับคำถามครับ คุณพี่ ขอตอบในลักษณะนี้แล้วกันครับ

      ขึ้นอยู่กับ Application ของคุณเองครับว่าต้องเรียกใช้ Function บน Column ใดๆ มากน้อยแค่ไหน ถ้าใช้ Function เป็นหลัก ควรพิจารณาใช้ Function-based index ครับ
      แต่ถ้าสมมุติว่าพี่จำเป็นต้องใช้ Function จริงๆ ให้เลี่ยงการครอบ Function บน Column ครับ ลองดูตามตัวอย่างข้างล่างนี้ครับ

      สมมุติว่า พี่ต้องการ WHERE Clause ว่า ให้เลือกข้อมูลที่มีการอัพเดต ณ วันนั้นๆ ในตัวอย่างแรก พี่เลือกใช้ TRUNC function เพื่อตัดส่วนที่เป็น ชั่วโมง นาที และวินาที ออกไป และมีการ create index ไว้บน column นั้นๆ ด้วยนะครับ

      SELECT *
      FROM T1
      WHERE TRUNC(upd_date) = TRUNC(sysdate);
      

      เปลี่ยนเป็นในรูปแบบนี้ครับ

      SELECT *
      FROM T1
      WHERE upd_date BETWEEN TRUNC(sysdate) AND TRUNC(sysdate)+1;
      

      แต่ในท้ายที่สุด เพื่อเลี่ยงปัญหา SQL ช้าสำหรับ Query ที่มี Build-in function ผมแนะนำว่าให้พิจารณาใช้ Function-based index ครับ

ใส่ความเห็น

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 /  เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ 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 /  เปลี่ยนแปลง )

w

Connecting to %s