Chơng III Phơng pháp tối u hoá trong
Hệ CSDL Oracle
A. Các khái niệm cần biết để tối u hoá
Phần này sẽ đa ra một số khái niệm đợc sử dụng quá trình tối u hoá.
Qui ớc rằng tên các bảng cũng nh các thuộc tính trong các ví dụ minh hoạ sẽ viết tiếng Việt cho cho dễ hiểu nh-
ng trong thực tế phải bỏ các dấu tiếng Việt đi bởi vì Oracle cha hỗ trợ triệt để cho ngôn ngữ Việt.
1. Khái niệm về tối u hoá
Nói chung các ngôn ngữ bậc cao (trong đó có ngôn ngữ SQL) đòi hỏi thực hiện trong máy đều rất tốn kém thời
gian. Đặc biệt là khi dữ liệu nhiều lên thì thời gian thực hiện các câu lên sẽ tăng theo hệ số nhân, do đó cần thiết phải
biến đổi các câu hỏi cho hợp lý để giảm thời gian tính toán.
Vậy có thể định nghĩa khái niệm tối u hoá nh sau:
Tối u hoá (optimization) câu lệnh SQL là một quá trình để tạo điều kiện cho câu lệnh SQL đa ra kết quả
đạt hiệu quả cao nhất hiểu theo nghĩa thời gian đáp ứng và sử dụng tài nguyên hệ thống là nhỏ nhất.
Theo định nghĩa này thì việc tối u hoá câu lệnh SQL không đơn thuần là tối u mã câu lệnh mà còn có thể thay
câu lệnh SQL bằng câu lệnh SQL khác cho ra cùng kết quả nhng thực hiện hiệu quả hơn, điều chỉnh các tham số hệ
thống để tài nguyên dành cho câu lệnh là hợp lý nhất, thay đổi các thực thể và quan hệ giữa các thực thể cho câu lệnh
thực hiện nhanh hơn.
Chú ý rằng việc tối u hoá không nhất thiết phải đúng là tối u trên mọi khả năng có thể của các cách cài đặt các
câu hỏi bởi vì nếu sử dụng tài nguyên càng nhiều thì câu lệnh càng có khả năng thực hiện nhanh tuy nhiên sẽ giảm số
khối lệnh đợc xử lý đồng thời và ngợc lại.
Trong thực tế thì quá trình tối u hoá phải tiến hành thờng xuyên trong mọi khâu sản xuất phần mềm để sản
phẩm ngày càng đợc hoàn thiện hơn, điều này cũng là một trong các lý do ra đời các phiên bản Version 1.0, Version
2.0 ... của phần mềm.
2. Chiến lợc tối u trên các trên các biểu thức quan hệ
Ullman J.D trong các kết quả nghiên cứu công bố lần đầu tiên của mình đã trình bày 6 chiến l ợc tổng quan cho
việc tối u hoá. Các chiến lợc này sẽ đợc thể hiện trực tiếp hay gián tiếp trong các bớc tối u cụ thể trong Oracle.
Nội dung của các chiến lợc đó nh sau:
2.1. Thực hiện phép chọn càng sớm càng tốt
Biến đổi câu hỏi để đa phép chọn vào thực hiện trớc nhằm làm bớt kích cỡ của kết quả trung gian và do vậy chi
phí phải trả giá cho việc truy nhập bộ nhớ thứ cấp cũng nh lu trữ của bộ nhớ chính sẽ nhỏ đi.
2.2. Tổ hợp những phép chọn (xác định với tích Đề-các) thành phép kết nối
một giá trị khoá Index thì sẽ nhận đợc ROWID tơng ứng và ROWID sẽ đợc dùng tìm ra hàng đợc yêu cầu trong một
nhóm các hàng có cùng giá trị khoá Index. Thông thờng các Index trong Oracle đợc tạo theo dạng B-tree tuy nhiên
Oracle còn cung cấp một mức Index khác gọi là Bitmap Index. Kiểu Index mới mà đặc biệt hiệu quả với những cột mà
số lợng nhiều nhng tập giá trị lại ít ví dụ nh cột giới tính, cột mã khu vực chẳng hạn. Bằng việc ánh xạ vị trí các cột
với các bit sẽ dễ dàng nhận đợc kết cần tìm. Ví dụ sau sẽ minh hoạ điều này.
Giả sử có bảng SinhViên có các thuộc tính MãSinhViên, GiớiTính, KhuVực có các giá trị nh sau:
MãSinhViên GiớiTính KhuVực
101 Nữ KV1
102 Nam KV2
103 Nam KV3
104 Nữ KV3
105 Nam KV2
106 Nam KV2
Khi thực hiện phép Index với cột KhuVực sẽ thu đợc bảng sau:
KhuVực=KV1 KhuVực=KV2 KhuVực=KV3
1 0 0
0 1 0
0 0 1
0 0 1
0 1 0
0 1 0
Trong bảng Index thì mỗi cột ứng với một đề mục trong cột Index, ví dụ cột KhuVực=KV1 thì giá trị 1 ứng với
hàng của Bảng SinhViên có KhuVực=KV1, ngợc lại là giá trị 0.
Giả sử để lấy ra mọi thuộc tính của bảng sinh viên với điều kiện sinh viên là nữ ở khu vực KV2 và KV3 với câu
lệnh:
SELECT * FROM SinhViên WHERE GiớiTính=Nữ AND KhuVực IN (KV2, KV3);
Bằng việc sử dụng Bitmap Index và truy nhập trên bảng Bitmap để lọc ra tập hợp các hàng thoả mãn điều kiện đã nêu,
quá trình đó đợc mô tả nh bảng dới đây:
GiớiTính = Nữ KhuVực = KV2 KhuVực = KV3
0
1
1
0
0
1
3.2. Phân lớp bằng Cluster
Cluster là một cách thức lu trữ dữ liệu của các bảng trong Oracle thành một nhóm và giữa các bảng này thờng có
một số cột là khoá ngoài tham chiếu tới nhau. Ví dụ nh bảng NhânViên và bảng Phòng sẽ có chung một cột là
MãPhòng. Khi nhóm bảng NhânViên và bảng Phòng thì Oracle sẽ chứa tất cả các hàng tơng ứng với một phòng ban
của cả hai bảng vào trong cùng một khối dữ liệu.
Cluster Key
(M Phòng)ã
10 TênPhòng TP ...
Bán hàng TP HCM ...
M NhânViênã TênNhânViên ...
1000 SMITH ...
1321 JONES ...
1841 WARD ...
20 TênPhòng TP ...
Quản trị TP Hà Nội ...
M NhânViênã TênNhânViên ...
932 KEHR ...
1139 WILSON ...
1277 NORMAN ...
Bảng NhânViên
M NhânViênã TênNhânViên M Phòngã ...
932 KEHR 20 ...
1000 SMITH 10
1139 WILSON 20 ...
1277 NORMAN 20 ...
Nghĩa là dữ liệu đợc ghi tự nhiên nh lúc tạo do vậy việc truy nhập thông tin luôn đòi hỏi phải dùng duyệt toàn
bộ bảng.
4. Các kiểu kết nối trong Oracle
Do việc đảm bảo tính chuẩn hoá của dữ liệu mà các bảng sẽ đợc kết nối với nhau thông qua các khoá. Một trong
các bớc tối u hoá là lựa chọn kiểu kết nối thích hợp. Trong Oracle có các kiểu kết nối sau:
4.1. Kết nối bằng Nested-Loop
Để thực hiện một kết nối theo kiểu Nested-Loop, Oracle sẽ chọn ra một bảng nh là bảng ngoài hay là bảng dẫn
dắt (outer table hoặc driving table), bảng còn lại đợc gọi là bảng nội (inner table). Với mỗi hàng trong bảng ngoài thì
Oracle sẽ tìm tất cả các hàng trong bảng nội thoả mãn điều kiện kết nối. Cặp hàng thoả mãn điều kiện kết nối sẽ đ ợc
hợp lại thành một và thêm vào tập các hàng kết quả.
Ví dụ: SELECT * FROM NhânViên, Phòng WHERE NhânViên.M Phòng=Phòng.M Phòng;ã ã
nesloop
NhânViên
(Full)
Phòng
(RowId)
pk_phong
(Range Scan)
4.2. Kết nối bằng sắp xếp trộn
Điều kiện có thể áp dụng đợc phơng pháp kết nối này là hai nguồn dữ liệu phải kết nối theo kiểu bằng chứ
không thể là kiểu kết nối khác.
Oracle sẽ tiến hành sắp xếp mỗi nguồn dữ liệu theo cột đợc sử dụng trong điều kiện kết nối nếu nh nguồn dữ
liệu cha đợc sắp xếp bởi thao tác trớc đó.
Oracle sẽ trộn hai nguồn dữ liệu bằng cách chọn ra các cặp hàng thoả mãn điều kiện kết nối và đa chúng vào tập
các hàng kết quả.
Ví dụ: SELECT * FROM NhânViên, Phòng WHERE NhânViên.M Phòng=Phòng.M Phòng;ã ã
merge join
sort
(join)
sort
1
Chọn một hàng bằng ROWID
2
Chọn một hàng bằng Cluster kết nối
3
Chọn một hàng bằng hàm băm với khoá
duy nhất hay khoá chính
4
Chọn một hàng bằng khoá duy nhất hay
khoá chính
5
Dùng Cluster kết nối
6
Dùng khoá của hàm băm
7
Dùng khoá Index
8
Kết hợp các khoá
9
Index các cột đơn
10
Giới hạn vùng tìm trên cột đã Index
11
Không giới hạn vùng tìm trên cột đã
Index
12
Sắp xếp kết nối bằng phơng pháp trộn
13
MAX hoặc MIN của cột đã Index
14
tạo ra một khoá duy nhất hoặc là khoá chính.
Để thực hiện câu lệnh này, Oracle dùng hàm băm để định giá trị khoá băm tơng ứng với các giá trị cung cấp bởi
câu lệnh. Giá trị khoá tìm đợc sẽ sử dụng để tìm bản ghi tơng ứng trong table.
Ví dụ: Giả sử rằng trong bảng HoáĐơn và bảng NộiDungHoáĐơn đợc lu trong một nhóm hàm băm và
SốHoáĐơn vừa là khoá chính vừa là khoá hàm băm. Câu lệnh sau sẽ sử dụng phơng thức hàm băm.
SELECT * FROM HoáĐơn WHERE SốHoáĐơn = 65118968;
5.4. Chọn một hàng bằng sử dụng khoá duy nhất hoặc bằng khoá chính
Phơng thức này chỉ dùng đợc khi câu lệnh phải có mệnh đề FROM có sử dụng tất cả các cột của khoá duy nhất
(hoặc khoá chính) trong điều kiện tơng ứng. Để có thể sử dụng đợc khoá thì các điều kiện tơng ứng với cột nằm trong
khoá phải liên kết với nhau bằng toán tử AND. Thông qua bảng Index tơng ứng với các khoá thì Oracle sẽ tìm đợc
ROWID tơng ứng để truy nhập trực tiếp tới hàng.
VD: Giả sử MãNhânViên là khoá chính của bảng NhânViên, câu lệnh sau sẽ sử dụng phơng thức truy nhập này.
SELECT * FROM NhânViên WHERE M NhânViên = 7900;ã
5.5. Kết nối Cluster (Cluster join)
Phơng thức này sẵn dùng khi mà điều kiện kết nối các bảng đều đợc chứa trong cùng một nhóm (Cluster) và nếu
điều kiện kết nối trong mệnh đề FROM thoả mãn mỗi cột trong kết nối là khoá của bảng này và là cột trong bảng kia.
Và để sử dụng đợc khoá của kết nối thì các điều kiện phải nối với nhau bởi toán tử AND. Lúc đó Oracle sẽ tiến hành
hoàn thành câu lệnh Nested-Loop.
VD: SELECT * FROM NhânViên, Phòng WHERE NhânViên.M Phòng = Phòng.M Phòng;ã ã
5.6. Khoá của các hàng có cùng giá trị băm
Câu lệnh phải có mệnh đề FROM sử dụng tất cả các cột trong khoá của hàm băm. Mặt khác để dùng đợc khoá
của hàm băm thì các điều kiện tơng ứng phải kết nối với nhau bằng toán tử AND. Oracle sẽ tiến hành duyệt bảng dựa
trên khoá hàm băm tìm đợc.
VD: Giả sử rằng trong bảng HoáĐơn và bảng NộiDungHoáĐơn đợc lu trong một nhóm hàm băm và SốHoáĐơn
vừa là khoá chính vừa là khoá hàm băm. Câu lệnh sau sẽ sử dụng phơng thức hàm băm.
SELECT * FROM NộiDungHoáĐơn WHERE SốHoáĐơn = 65118968;
5.7. Khoá của các hàng có cùng giá trị Index
Khi mà các bảng đợc Index thì cùng với giá trị của trờng Index còn có các ROWID. Nếu câu lệnh chỉ sử dụng
giá trị các trờng Index thì các giá trị này sẽ đợc lấy trực tiếp từ bảng Index thay vì lấy từ bảng dữ liệu còn trong trờng
hợp là các giá trị khác thì sẽ truy nhập ROWID tìm đợc từ bảng Index.
Phơng thức này có thể đợc sử dụng khi trong câu lệnh có chứa điều kiện giới hạn các Index đơn hoặc thành phần
đứng trớc trong Index nhiều cột có dạng nh sau
<tên cột > >[=] <biểu thức>
<tên cột> <[=] <biểu thức>
Trong trờng hợp này vùng Index bị giới hạn về một phía (chặn trên hoặc chặn dới).
Ví dụ trong trờng hợp Index đơn:
SELECT * FROM NhânViên WHERE Lơng > 2000;
Ví dụ trong trờng hợp Index trên nhiều trờng:
Giả sử có Index trên MãHoáĐơn và DòngHoáĐơn của bảng NộiDungHoáĐơn.
SELECT * FROM NộiDungHoáĐơn WHERE M HoáĐơn > 65118968;ã
Do MãHoáĐơn là phần đứng trớc trong Index nhiều cột nên phơng thức truy nhập này đợc sử dụng. Tuy nhiên
câu lệnh sau đây vì DòngHoáĐơn là thành phần đứng sau trong Index nhiều cột nên phơng thức truy nhập này không
đợc sử dụng.
SELECT * FROM NộiDungHoáĐơn WHERE DòngHoáĐơn <4;
5.12. Sắp xếp kết nối
Nếu câu lệnh đòi hỏi kết nối giữa các bảng trong cùng một nhóm hoặc nếu điều kiện trong mệnh đề FROM sử
dụng một cột từ một bảng khác trong điều kiện tơng ứng. Oracle cũng có thể sử câu lệnh SELECT đệ qui để thực hiện
câu lệnh kết nối.
VD khi bảng NhânViên và bảng Phòng không nằm trong cùng một Cluster.
SELECT * FROM NhânViên, Phòng WHERE NhânViên.M Phòng = Phòng.M Phòng;ã ã
5.13.
MAX
hoặc
MIN
của cột Index
Phơng thức này có thể đợc dùng nếu các điều kiện sau thoả mãn
10* Câu lệnh sử dụng hàm MAX hoặc MIN để chọn giá trị của các cột Index đơn hoặc các cột là phần đầu của Index
phức. Các kiểu Index này không đợc phép là Cluster Index (chỉ mục phân lớp)
11* Không có biểu thức khác trong danh sách chọn
12* Câu lệnh không chứa mệnh đề FROM hay mệnh đề GROUP BY.
25* <biểu thức> = <biểu thức>
biểu thức ở đây là một cột với một toán tử hoặc một cột khác bất chấp việc cột đã đợc Index.
26* NOT EXISTS subquery
27* Bất kì điều kiện nào đối với một cột cha đợc Index.
Các câu lệnh có chứa cấu trúc nh vậy và không có cấu trúc khác để có thể tạo phơng thức truy nhập bằng Index
thì buộc phải duyệt toàn bộ.
6. Các luật tối u
Có hai luật tối u trong Oracle là luật tối u cơ bản và luật tối u ớc lợng giá.
6.1. Luật tối u cơ bản (rule-based)
Nội dung cơ bản của luật này là chọn ra cách thức truy nhập tới hàng dựa trên mức độ u tiên của các phơng
thức.
Trớc tiên Oracle sẽ kiểm tra điều kiện sau mệnh đề WHERE của câu lệnh để xác định những phơng thức nào câu
lệnh có thể sử dụng đợc. Tiếp đó Oracle sẽ tiến hành chọn ra phơng thức có số thứ tự thấp nhất trong bảng các phơng
thức. Chú ý rằng phơng thức duyệt toàn bộ là phơng thức đứng cuối bảng cho nên Oracle sẽ chọn phơng thức truy
nhập có Index nếu có cho dù duyệt toàn bộ có thể nhanh hơn và thứ tự các điều kiện trong mệnh đề WHERE nói
chung sẽ không ảnh hởng tới cách chọn phơng thức truy nhập.
Giả sử có bảng NhânViên có những ràng buộc sau:
+ Khoá chính là M NhânViên và Index tã ơng ứng là PK_MANHANVIEN.
+Có Index tên là TENNHANVIEN_IND trên cột TênNhânViên.
+Có Index tên là LUONG_IND trên cột Lơng.
Để chọn ra những mã nhân viên có tên là 'CHUNG' và có lơng > 2000 bằng câu lệnh sau:
SELECT M NhânViên FROM NhânViên WHERE TênNhânViên='CHUNG' AND Lã ơng>2000;
Dựa vào các điều kiện trong mệnh đề WHERE của câu lệnh SQL và các ràng buộc, các Index thì các phơng truy
nhập sau có thể dùng đợc:
+ Chọn ra một hàng bằng Index khi sử dụng TENNHANVIEN_IND do điều kiện
TênNhânViên='CHUNG'. Phơng thức này có số thứ tự là 9.
+ Duyệt Index không bị giới hạn khi sử dụng LUONG_IND. Phơng thức này có số thứ tự là 11.
Copyright: Tài liệu đại học ©