SQL Tunning – Tránh việc Oracle sử dụng các hàm convert dữ liệu khi thực hiện query dữ liệu

Thông thường khi tối ưu hiệu năng query, chúng ta sẽ sử dụng công cụ Query explain có sẵn của Oracle để tìm ra phương pháp tối ưu.
Nhưng một số trường hợp dưới đây, cho dù chúng ta đã query vào trường primary key hay trường có index, thì cũng không tối ưu.
Chúng ta hãy thử xem nguyên nhân của nó trong các ví dụ sau:

SQL> select *
  2  from   tbl1
  3  where  rownum <= 10;

PK         DATA
---------- ---------------------------------------------------------------
1          I_FILE#_BLOCK#
2          I_OBJ3
3          I_TS1
4          I_CON1
5          IND$


10 rows selected.

SQL>
SQL> select index_name
  2  from   user_indexes
  3  where  table_name = 'TBL1';

INDEX_NAME
------------------------------
IX

SQL>
SQL> select column_name
  2  from   user_ind_columns
  3  where  index_name = 'IX';

COLUMN_NAME
------------------------------
PK

SQL>
SQL> set autotrace traceonly explain
SQL> select *
  2  from tbl1
  3  where pk = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |   152   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TBL1 |     1 |    38 |   152   (2)| 00:00:01 |
--------------------------------------------------------------------------


SQL> select /*+ index(tbl1 ix) */ *
  2  from tbl1
  3  where pk = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |   152   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TBL1 |     1 |    38 |   152   (2)| 00:00:01 |
--------------------------------------------------------------------------

Như ở trong ví dụ trên, chúng ta đã query vào trường khóa chính (PK), nhưng bảng TBL1 vẫn bị full scan, kể cả chúng ta có dùng hint chỉ rõ vào index của khóa chính.
Tại sao lại vậy nhỉ? Chúng ta hãy thử tìm tiếp nguyên nhân:

SQL> desc tbl1
 Name                          Null?    Type
 ----------------------------- -------- --------------
 PK                                     VARCHAR2(40)
 DATA                                   VARCHAR2(128)
 

Cột PK có kiểu dữ liệu là VARCHAR2, mặc dù nó lại lưu dữ liệu số. Nếu chúng ta thêm câu lệnh output khi explain plan thì chúng ta sẽ thấy được Oracle tự động thêm hàm TO_NUMBER vào trường PK dẫn tới câu lệnh không ăn index và bị quét full scan bảng

SQL> select /*+ index(tbl1 ix) */ *
  2  from tbl1
  3  where pk = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |   152   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TBL1 |     1 |    38 |   152   (2)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("PK")=12)

Vậy là chúng ta đã phát hiện ra vấn đề. Để tối ưu lại câu lệnh này, chúng ta có 2 cách như sau:

  • Nếu cột PK chỉ lưu dữ liệu dạng số –> chuyển kiểu dữ liệu sang dạng số, khi đó query chúng ta sẽ không bị Oracle tự động thêm hàm TO_NUMBER vào nữa
  • Đổi kiểu của tham số từ PK = 1 –> PK = ‘1’ tương ứng với dữ liệu của cột

Việc Oracle tự động thêm các hàm convert dữ liệu khi thực hiện query dữ liệu cũng xảy ra với một số trường hợp sau:

  • Query trường DATE, truyền tham số dạng TIMESTAMPT
  • Query trường VARCHAR2, truyền tham số dạng NVACHAR2

Vì vậy chúng ta cần chú ý tới kiểu dữ liệu của tham số truyền vào với kiểu dữ liệu của cột được filter khi thực hiện tối ưu nhé