Phần 2: Tối ưu câu lệnh SQL có một Table và có một điều kiện
Tiếp theo Phần 1: nền tảng cơ bản tối ưu câu lệnh Oracle SQL; giới thiệu các kiến thức cơ bản cần thiết để tối ưu câu lệnh SQL, chúng ta bắt đầu đi vào tìm hiểu kỹ thuật tối ưu câu lệnh cơ bản nhất, đó là tối ưu dựa vào Index, cụ thể là B*tree-Index, loại Index thường được dùng nhất.
Chúng ta cũng cần lưu ý là Index chỉ giúp tăng tốc độ truy vấn trong một số trường hợp cụ thể, không phải bất kì trường hợp nào sử dụng Index cũng giúp tăng tốc độ. Trong thực tế, ta có thể xem phần mục lục của một cuốn sách giống như một cấu trúc Index; từ mục lục cuốn sách, bạn suy ra được số thứ tự trang sách chứa nội dung bạn cần tìm. Nếu sách vài trăm trang bạn có thể dựa vào mục lục để tìm được nội dung mong muốn một cách nhanh chóng, nhưng nếu sách chỉ vài trang thì tìm theo cách lật từng trang thì lại nhanh hơn. Cách tìm theo mục lục giống như phương thức truy cập TABLE ACCESS (BY INDEX ROWID), cách tìm lật từng trang sách giống như phương thức truy cập TABLE ACCESS (FULL).
Phần hai này sẽ tập trung chủ yếu vào kỹ thuật tối ưu câu lệnh sử dụng Index, xem xét trường hợp nào Oracle sẽ dùng Index trong câu lệnh và ngược lại. Các ví dụ minh họa trong bài sẽ dùng Table tên CUSTOMERS của tài khoản SH, cấu trúc CUSTOMERS như sau:
[oracle@localhost LABS]$ sid
ORACLE_SID=ora9i
[oracle@localhost LABS]$
[oracle@localhost LABS]$ alias sh
alias sh='sqlplus "sh/sh"'
[oracle@localhost LABS]$ sh
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Feb 16 08:56:20 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> DESC customers
Name Null? Type
----------------------------------------------------------- -------- ---------------
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER CHAR(1)
CUST_YEAR_OF_BIRTH NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_STATE_PROVINCE VARCHAR2(40)
COUNTRY_ID NOT NULL CHAR(2)
CUST_MAIN_PHONE_NUMBER VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
CUST_TOTAL VARCHAR2(14)
SQL>
Các trường hợp ứng dụng Index khi tối ưu câu lệnh SQL
Oracle không dùng Index cho điều kiện so sánh có toán tử < >, != và NOT IN
Thường chúng ta nghĩ nếu cột dữ liệu trong mệnh đề điều kiện có Index, thì câu lệnh SQL sẽ sử dụng Index để truy vấn dữ liệu cho nhanh, tuy nhiên có nhiều trường hợp dù có Index nhưng Index vẫn không được. Trường hợp đầu tiên chúng ta xét đến là đối với các toán tử <>, != và NOT IN.
Trước tiên, ta kiểm tra Table tên CUSTOMERS hiện đang có những Index nào:
SQL> @li CUSTOMERS
indexes on table CUSTOMERS%:
TABLE_NAME INDEX_TYPE INDEX_NAME
-------------------- ---------- ------------------------------
CUSTOMERS UNIQUE CUSTOMERS_PK
NONUNIQUE CUST_CREDIT_LIMIT_IDX
CUST_EMAIL_IDX
CUST_LAST_NAME_IDX
Dùng mã lệnh dai.sql xóa các nonprimary-key, khi đó CUSTOMERS chỉ còn primary-key tên CUSTOMERS_PK trên cột dữ liệu CUST_ID:
SQL> @dai
on which table: CUSTOMERS
DROP INDEX CUSTOMERS_PK
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> @li CUSTOMERS
indexes on table CUSTOMERS%:
TABLE_NAME INDEX_TYPE INDEX_NAME
-------------------- ---------- ------------------------------
CUSTOMERS UNIQUE CUSTOMERS_PK
Xem cách trình tối ưu Oracle xử lý bốn câu lệnh sau:
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT cust_first_name, cust_last_name
FROM customers
WHERE cust_id = 1030
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'
2 1 INDEX (UNIQUE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)
SQL> SELECT cust_first_name, cust_last_name
FROM customers
WHERE cust_id < 20000
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'
2 1 INDEX (RANGE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)
SQL> SELECT cust_first_name, cust_last_name
FROM customers
WHERE cust_id between 70000 and 80000
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'
2 1 INDEX (RANGE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)
SQL> SELECT cust_first_name, cust_last_name
FROM customers
WHERE cust_id <> 1030
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'
SQL>
Nhìn vào kết quả các câu lệnh, ta thấy trình tối ưu Oracle đang được chỉ định giá trị là Optimizer=CHOOSE, nghĩa là sẽ chọn CBO nếu CUSTOMERS có thông tin mô tả và chọn RBO nếu không có. Mặc định CUSTOMERS không có thông tin mô tả, vì vậy trong trường hợp này trình tối ưu sẽ dùng phương pháp RBO để xác định EP cho các câu lệnh.
Như tìm hiểu trong phần trước, RBO sử dụng lược đồ luật phân hạng để chọn EP tốt nhất, ưu tiên chọn phương thức truy cập dữ liệu theo Index hơn là phương thức duyệt từng dòng dữ liệu của Table. Nhưng theo ví dụ trên, ta thấy RBO chỉ dùng Index cho 3 câu lệnh đầu tiên, không dùng Index cho câu lệnh cuối cùng. Để ý thì ta thấy ba câu lệnh đầu sử dụng toán tử so sánh =, < và BETWEEN AND, các toán tử này đều có khuynh hướng giới hạn tập dữ liệu trả về, còn câu lệnh thứ tư thì sử dụng toán tử <>, toán tử này luôn trả về tập kết quả lớn. Như vậy, Oracle RBO ứng xử như vậy là hợp lý, dùng Index cho câu lệnh sử dụng toán tử so sánh có khuynh hướng trả về ít dữ liệu và không dùng Index cho trường hợp câu lệnh có toán tử so sánh trả về nhiều dữ liệu.
RBO sẽ ứng xử tương tự như <> cho các toán tử != và NOT IN.
Oracle không dùng Index cho cột dữ liệu kết hợp với bất kì thành phần khác
Dù cột dữ liệu có Index, nhưng nếu ta kết hợp nó với bất kì thành phần nào khác, chẳng hạn như một giá trị, một biểu thức thì Index trên cột dữ liệu đó sẽ không được trình tối ưu Oracle ứng dùng. Xem xét các ví dụ dưới đây:
SQL> SELECT *
2 FROM customers
3 WHERE cust_id + 1 = 100;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'
SQL> SELECT *
2 FROM customers
3 WHERE TO_NUMBER(cust_id) = 100;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'
SQL> SELECT *
2 FROM customers
3 WHERE cust_id + null = 100;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'
SQL> SELECT *
2 FROM customers
3 WHERE cust_id = 100;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'
2 1 INDEX (UNIQUE SCAN) OF 'CUSTOMERS_PK' (UNIQUE)
SQL>
Ta thấy ba trường hợp đầu tiên, dù cột dữ liệu kết hợp với giá trị NULL , giá trị rỗng, cũng khiến Oracle RBO không ứng dụng Index cho câu lệnh. Đối với trường hợp TO_NUMBER(cust_id) thì do hàm TO_NUMBER kết buộc với cột CUST_ID nên làm mất tác dụng của Index, tuy nhiên chúng ta có thể sử dụng kỹ thuật function-based Index; tạo Index trực tiếp trên hàm kết buộc vào cột dữ liệu, để thực hiện phương thức truy cập dữ liệu theo Index mà không cần bỏ hàm TO_NUMBER().
SQL> CREATE INDEX cust_id_tonumber_idx ON customers(to_number(cust_id));
Index created.
SQL> @li
indexes on table CUSTOMERS%:
TABLE_NAME INDEX_TYPE INDEX_NAME
-------------------- ---------- ------------------------------
CUSTOMERS UNIQUE CUSTOMERS_PK
NONUNIQUE CUST_CREDIT_LIMIT_IDX
FUNCTION-B CUST_ID_TONUMBER_IDX
ASED NORMA
L
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT *
2 FROM customers
3 WHERE to_number(cust_id) = 100;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'
SQL> ANALYZE TABLE customers COMPUTE STATISTICS;
Table analyzed.
SQL> SELECT *
2 FROM customers
3 WHERE to_number(cust_id) = 100;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=137)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (Cost=2 Card=1 Bytes=137)
2 1 INDEX (RANGE SCAN) OF 'CUST_ID_TONUMBER_IDX' (NON-UNIQUE) (Cost=1 Card=1)
SQL>
Như ta thấy, chỉ trình tối ưu CBO mới hiểu kỹ thuật Function-based Index, RBO không hiểu. Vì vậy, để trình tối ưu Oracle tự động dùng CBO thì chúng ta cần thu thập thông tin mô tả cho CUSTOMERS.
Lưu ý thêm về cách thu thập thông tin mô tả cho Table, chúng ta sử dụng cú pháp lệnh ANALYZE TABLE vì lệnh này đơn giản, dễ hiểu và cú pháp trong sáng, đáp ứng đủ cho các minh họa của chúng ta. Khi ứng dụng thu thập thông tin cho hệ thống Oracle thực tế, các bạn nên dùng gói lệnh DBMS_STATS thì hiệu quả hơn.
Oracle Index và toán tử LIKE
Để minh họa cho ý này, chúng ta sẽ tạo thêm một Index trên cột dữ liệu CUST_LAST_NAME theo mã lệnh ci.sql:
SQL> @ci
on which table : CUSTOMERS
on which column(s): cust_last_name
Creating index on: CUSTOMERS cust_last_name
Enter value for index_name: cust_last_name_idx
SQL> @li CUSTOMERS
indexes on table CUSTOMERS%:
TABLE_NAME INDEX_TYPE INDEX_NAME
-------------------- ---------- ------------------------------
CUSTOMERS UNIQUE CUSTOMERS_PK
NONUNIQUE CUST_CREDIT_LIMIT_IDX
FUNCTION-B CUST_ID_TONUMBER_IDX
ASED NORMA
L
NONUNIQUE CUST_LAST_NAME_IDX
Tiếp theo, chúng ta xét ví dụ sau:
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT cust_id
2 FROM customers
3 WHERE cust_last_name LIKE 'S%'
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS'
2 1 INDEX (RANGE SCAN) OF 'CUST_LAST_NAME_IDX' (NON-UNIQUE)
SQL> SELECT cust_last_name
2 FROM customers
3 WHERE cust_last_name LIKE '%S%’
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'
SQL> SELECT cust_id
2 FROM customers
3 WHERE cust_last_name LIKE '%S'
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'
SQL>
Như vậy, trình tối ưu Oracle RBO chỉ ứng dụng Index cho toán tử LIKE nếu giá trị so sánh không có kí tự đặc biệt % ở đầu.
Chúng ta xét tiếp ví dụ sau:
SQL> SELECT cust_last_name
2 FROM customers
3 WHERE cust_last_name like 'S%'
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'CUST_LAST_NAME_IDX' (NON-UNIQUE)
SQL>
Các bạn thấy điểm khác biệt ở đây chứ ? EP không có bước lệnh TABLE ACCESS (BY INDEX ROWID) như bình thường, mà chỉ có INDEX (RANGE SCAN).
Nguyên nhân do cột dữ liệu kết quả trả về chỉ có CUST_LAST_NAME, mà dữ liệu này chính là giá trị khóa của Index tên CUST_LAST_NAME_IDX, vì vậy Oracle chỉ cần duyệt cây Index là có thể lấy được kết quả mong muốn, không cần tốn thêm chi phí duyệt Table như bình thường.
Tiếp tục với ví dụ sau:
SQL> SELECT cust_last_name
2 FROM customers
3 WHERE cust_id LIKE '7%'
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'
SQL>
Các bạn có đoán ra được lý do tại sao trình tối ưu Oracle RBO không ứng dùng Index trên cột dữ liệu CUST_ID ? Do CUST_ID là cột dữ liệu kiểu số, nhưng câu lệnh lại so sánh với một giá trị chuỗi ‘7%’, nên trong trường hợp này Oracle tự động ngầm định chuyển đổi kiểu cho mệnh đề điều kiện thành như sau:
SQL> SELECT cust_last_name
2 FROM customers
3 WHERE TO_CHAR(cust_id) LIKE '7%'
Do cột dữ liệu CUST_ID bị kết buộc với hàm TO_CHAR() khi so sánh, nên bị mất tác dụng Index.
Tóm tắt những ý chúng ta đã tìm hiểu được trong phần này:
- Oracle chỉ ứng dụng Index cho toán tử LIKE khi giá trị so sánh không có kí tự % ở đầu
- Nếu kết quả dữ liệu của câu lệnh có thể tìm thấy đủ trong Index, thì Oracle chỉ cần duyệt cây Index để lấy kết quả trả về, mà không cần duyệt trên Table.
- Nếu cột dữ liệu trong mệnh đề điểu kiện so sánh với một giá trị khác kiểu, Oracle tự động chuyển đổi kiểu ngầm định cho cột dữ liệu đó, điều này khiến Index không được ứng dụng.
Oracle Index và giá trị NULL
Theo kiến trúc cơ sở dữ liệu Oracle, cấu trúc cây Index ,B*tree-Index, không lưu thông tin về dòng dữ liệu của Table có giá trị khóa là NULL. Vậy theo bạn, Oracle sẽ ứng xử thế nào khi so sánh điều kiện trên cột dữ liệu có giá trị NULL ? chúng ta sẽ tìm hiểu ý này ngay trong phần dưới đây.
Để chuẩn bị cho phần này, chúng ta cập nhật một số giá trị của cột dữ liệu CUST_EMAIL về NULL và tạo Index tên CUST_EMAIL_IDX trên cột này.
SQL> SET AUTOTRACE OFF
SQL> UPDATE customers
2 SET cust_email = null
3 WHERE rownum < 101
/
100 rows updated.
SQL> COMMIT;
Commit complete.
SQL> @ci
on which table : CUSTOMERS
on which column(s): CUST_EMAIL
Creating index on: CUSTOMERS CUST_EMAIL
Enter value for index_name: CUST_EMAIL_IDX
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT cust_email
2 FROM customers
3 WHERE cust_email IS NULL
/
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'
SQL>
Theo ví dụ trên, Oracle đã không dùng Index dù cột CUST_EMAIL có Index. Giải thích cho cách ứng xử này là do B*tree-Index không lưu thông tin về dòng dữ liệu chứa giá trị khóa là NULL, vì vậy Oracle phải thực hiện quét từng dòng dữ liệu của Table để tìm được các CUST_EMAIL có giá trị NULL.
Vậy các bạn thử xem tiếp ví dụ sau, tại sao Oracle vẫn không dùng Index ?
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT cust_id
2 FROM customers
3* WHERE cust_email IS NOT NULL
SQL> /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'CUSTOMERS'
SQL>
Nếu Oracle B*tree-Index không lưu giá trị NULL, vậy khi tìm giá trị khác NULL thì theo suy luận bình thường Oracle phải dùng Index trong trường hợp này ? Không như vậy, Oracle “nghĩ” rằng điều kiện IS NOT NULL sẽ trả về tập kết quả lớn, nếu dùng Index sẽ không hiệu quả, nên Oracle chọn phương thức truy cập dữ liệu TABLE ACCESS (FULL) sẽ hiệu quả hơn.
Phụ lục
Mã lệnh
Một số File mã lệnh tiện ích sử dụng trong bài viết:
Tên mã lệnh |
Mô tả chức năng |
li.sql |
Liệt kê tất cả Index của bảng nhập vào
select ui.table_name
, decode(ui.index_type
,'NORMAL', ui.uniqueness
,ui.index_type) as index_type
, ui.index_name
from user_indexes ui
where ui.table_name like upper('&1.%')
order by ui.table_name
, ui.uniqueness desc
|
dai.sql |
Xóa hết tất cả non-primary key Index trên một cột dữ liệu nhập vào của một Table
accept TABLE_NAME prompt " on which table: "
set termout off
store set saved_settings replace
set heading off verify off autotrace off feedback off
spool doit.sql
select 'DROP INDEX '||ui.index_name||';'
from user_indexes ui
where table_name like upper('&TABLE_NAME.%')
/
spool off
set termout on
@doit
@saved_settings
undef TABLE_NAME
set termout on
|
ci.sql |
Tạo mới một Non-Unique Index trên một cột dữ liệu nhập vào của một Table
accept TABLE_NAME prompt " on which table : "
|accept COLUMN_NAME prompt " on which column(s): "
set termout off
store set saved_settings replace
set heading off feedback off autotrace off
set verify off termout on
select 'Creating index on: '
, '&&TABLE_NAME'
, '&&COLUMN_NAME'
FROM DUAL
/
create index &INDEX_NAME on &TABLE_NAME(&COLUMN_NAME)
/
@saved_settings
set termout on
undef INDEX_NAME
undef TABLE_NAME
undef COLUMN_NAME
|
Lịch sử thay đổi
- 26/02/2009 - Giới thiệu Phần 2
Tham khảo
- Tài liệu chuẩn của Oracle Database 9i
- Tài liệu khóa học Oracle Database 9i: SQL Tuning
Mời các bạn đón đọc phần tiếp theo của chủ đề này:
Phần 3 – Tối ưu câu lệnh có sử dụng SORT, GROUP BY và SET-OPERATORS
Để hiểu rõ hơn về chủ đề này, các bạn có thể tham gia các khóa học sau tại tại Trung tâm VietPace:
|