Mục lục
Xem bài trước:
Giới thiệu
Trong 1 câu truy vấn Top N, bạn thường quan tâm đến việc thực hiện một số truy vấn phức tạp, sắp xếp nó và sau đó chỉ lấy ra N row đầu tiên.
ROWNUM tối ưu hóa cho loại truy vấn này. Bạn có thể sử dụng ROWNUM để tập hợp dữ liệu lớn.
Lý thuyết
Tôi sẽ thảo luận về cách nó thực hiện điều này về mặt lý thuyết
Giả sử truy vấn của bạn có dạng như sau:
select ... from ... where ... order by columns;
Giả sử rằng truy vấn này trả về nhiều dữ liệu: hàng nghìn, hàng trăm nghìn row hoặc hơn thế nữa.
Tuy nhiên, bạn chỉ quan tâm đến N row đầu tiên — có thể là top 10 hoặc top 100. Có hai cách để tiếp cận điều này:
- Cách 1: Yêu cầu ứng dụng chạy truy vấn đó và chỉ lấy N row đầu tiên.
- Cách 2: Viết truy vấn đó theo kiểu inline view và sử dụng ROWNUM để giới hạn kết quả, kiểu như sau: SELECT * FROM (your_query_here) WHERE ROWNUM <= N (giống ví dụ bên trên).
Cách 2 vượt trội hơn nhiều so với cách 1, vì hai lý do.
- Lý do đầu tiên, cách 2 đòi hỏi client xử lý ít hơn, bởi vì database instance đã làm việc giới hạn tập kết quả thay cho client rồi.
- Lý do thứ hai quan trọng hơn là Oracle có 1 quá trình xử lý đặc biệt để chỉ lấy ra cho bạn N row trên cùng.
Bạn nói với Database Instance: “Tôi chỉ quan tâm đến việc có được N row, tôi không cần xem phần còn lại”.
Điều đó nghe có vẻ đơn giản cho đến khi bạn biết máy chủ sẽ cần phải làm gì để sắp xếp toàn bộ dữ liệu.
Vi dụ, nếu tôi cần lấy ra top 10 row của 1 câu truy vấn, cách tiếp cận có thể sẽ như sau. Hãy xem ví dụ này:
select * from t order by unindexed_column;
Giả sử rằng T là một bảng lớn, với hơn một triệu row và mỗi row là row “béo”, tức là từ 100 byte trở lên.
UNINDEXED_COLUMN, như tên gọi của nó, là một cột không được đánh index.
Giả sử rằng bạn chi muốn lấy 10 row đầu tiên. Các bước thực hiện sẽ như sau:
- Full table scan bảng T
- Sắp xêp dữ liệu bảng T theo cột unindexed_column (Full sort) <– Vấn đề nan giải ở đây nè
- Từ kết quả sau khi sắp xếp, tôi sẽ lấy ra 10 row đầu tiên.
- Dọn dẹp các temporary extent sau khi đã sử dụng xong.
Wow, quá trình này hẳn sẽ tốn rất nhiều tài nguyên.
Tiếp theo, hãy xem Oracle có thể làm gì về mặt lý thuyết với truy vấn top-N:
select * from (select * from t order by unindexed_column) where ROWNUM < :N;
Trong trường hợp này, Oracle sẽ thực hiện các bước sau:
- Quét toàn bảng T (bạn không thể tránh bước này).
- Tạo một mảng gồm N phần tử (giả sử bộ nhớ có đủ để chứa N phần tử này).
- N row được lấy ra đầu tiên sẽ được cho vào 1 mảng và sắp xếp theo thứ tự.
- Khi row thứ N + 1 được nạp vào, nó sẽ được so sánh với row cuối cùng trong mảng.
- Nếu nó đi vào vị trí N + 1 trong mảng, nó sẽ bị loại ra ngoài. Còn nếu không, nó sẽ được thêm vào mảng (vào đúng vị trí được sắp xếp theo thứ tự) và một trong các row hiện có sẽ bị loại bỏ.
- Mảng của bạn chỉ chứa tối đa N row, vì vậy thay vì sắp xếp một triệu row (VD bảng T có 1 triệu row), Oracle chỉ cần sắp xếp N row.
Chi tiết nhỏ này có thể làm hiệu suất sử dụng tài nguyên tăng lên rất nhiều. Cần ít RAM hơn để sắp xếp 10 row so với sắp xếp một triệu row.
Thực tế
Bạn có thể thấy rằng, mặc dù cả hai cách tiếp cận trên đều thu được kết quả giống nhau, nhưng chúng sử dụng lượng tài nguyên hoàn toàn khác nhau:
Tạo bảng T với 1 triệu row dữ liệu (ngẫu nhiên)
create table t
as
select dbms_random.value(1,1000000)
id,
rpad('*',40,'*' ) data
from dual
connect by level <= 100000;
begin
dbms_stats.gather_table_stats
( user, 'T');
end;
/
Enable trace session
exec dbms_monitor.session_trace_enable (waits=>true);
Cách 1: Chạy 1 câu truy vấn Top n
select * from (select * from t order by id) where rownum <= 10;
Cách 2: Không dùng truy vấn Top n như trên
declare
cursor c is
select *
from t
order by id;
l_rec c%rowtype;
begin
open c;
for i in 1 .. 10
loop
fetch c into l_rec;
exit when c%notfound;
end loop;
close c;
end;
/
Sau khi thực hiện 2 truy vấn này, bạn có thể sử dụng công cụ TKPROF xem file trace để biết những gì đã xảy ra. Đầu tiên hãy kiểm tra truy vấn top-N ở ví dụ 1:
select * from (select * from t order by id) where rownum <= 10 <span> call count cpu elapsed disk query current rows</span> <span>-------- -------- ------- ------- ------- -------- -------- ------ </span> <span>Parse 1 0.00 0.00 0 0 0 0</span> <span>Execute 1 0.00 0.00 0 0 0 0</span> <span>Fetch 2 0.04 0.04 0 949 0 10</span> <span>-------- -------- ------- ------- ------- -------- -------- ------ </span> <span>total 4 0.04 0.04 0 949 0 10</span> <span>Rows Row Source Operation</span> <span>----------------- ---------------------------------------------------</span> <span>10 COUNT STOPKEY (cr=949 pr=0 pw=0 time=46997 us)</span> <span>10 VIEW (cr=949 pr=0 pw=0 time=46979 us)</span> <span>10 SORT ORDER BY STOPKEY (cr=949 pr=0 pw=0 time=46961 us)</span> <span>100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400066 us)</span>
Truy vấn sẽ quét toàn bộ bảng, nhưng bằng cách sử dụng SORT ORDER BY STOPKEY, nó có thể giới hạn việc sử dụng temp chỉ còn 10 row.
Ngoài ra, chúng ta còn thấy truy vấn đã thực hiện tổng cộng 949 logical I/O (cr = 949), không thực hiện physical read hay physical write (pr = 0 và pw = 0).
Còn với Cách thứ 2, kết quả như sau:
SELECT * FROM T ORDER BY ID <span>call count cpu elapsed disk query current rows</span> <span>-------- -------- ------- ------- ------- -------- -------- ------ </span> <span>Parse 1 0.00 0.00 0 0 0 0</span> <span>Execute 2 0.00 0.00 0 0 0 0</span> <span>Fetch 10 0.35 0.40 155 949 6 10</span> <span>-------- -------- ------- ------- ------- -------- -------- ------ </span> <span>total 13 0.36 0.40 155 949 6 10</span> <span>Rows Row Source Operation</span> <span>----------------- ---------------------------------------------------</span> <span>10 SORT ORDER BY (cr=949 pr=155 pw=891 time=401610 us)</span> <span>100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400060 us)</span> Elapsed times include waiting for the following events: Event waited on Times ------------------------------ ------------ direct path write temp 33 direct path read temp 5
Như bạn thấy, kết quả này rất khác. Đáng chú ý, elapsed time và CPU time cao hơn đáng kể.
Physical write là 891 và physical read là 155 cho thấy việc sắp xếp phải làm trên đĩa (pw = 891).
Nó phải thực hiện trên 1.000.000 row (thay vì chỉ 10 row mà chúng ta cần).
Bài viết đến đây là kết thúc. Hi vọng nó sẽ giúp ích cho các bạn khi cần tối ưu hiệu năng của câu lệnh.
Tham khảo: https://blogs.oracle.com/connect/post/on-rownum-and-limiting-results
Nguồn: https://dangxuanduy.com/
Hiện tại, tôi có tổ chức đều đặn các khóa học về quản trị Oracle Database, tôi sẽ để thông tin ở đây, để bạn nào quan tâm về lịch học cũng như chương trình học có thể theo dõi nhé.
KHOÁ DÀNH CHO NGƯỜI MỚI
KHÓA HỌC: QUẢN TRỊ ORACLE DATABASE THẬT LÀ ĐƠN GIẢN (ADMIN 1)
CÁC KHOÁ NÂNG CAO:
KHÓA HỌC ORACLE NÂNG CAO: QUẢN TRỊ KIẾN TRÚC MULTITENANT 12c
KHÓA HỌC ORACLE NÂNG CAO: QUẢN TRỊ HỆ THỐNG DATA GUARD
CÁC KHOÁ COMBO:
COMBO 1: ADMIN 1 + MULTITENANT 12c
COMBO 3: ADMIN 1 + MULTITENANT 12c + DATA GUARD
LỊCH HỌC:
Mời bạn xem tại đây: LỊCH HỌC CÁC LỚP ORACLE
ĐĂNG KÝ:
https://forms.gle/MtCAoRQFenP886y79
Hãy tham gia group “Kho tài liệu kiến thức database” để cùng học hỏi và chia sẻ nhé.
