Truy vấn Top N với ROWNUMTruy vấn Top N với ROWNUM (526 × 378 px)

Xem bài trước:

Đọc thêm  Cùng tìm hiểu cơ chế hoạt động của ROWNUM

   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é.

 

    KHÓA HỌC: QUẢN TRỊ ORACLE DATABASE THẬT LÀ ĐƠN GIẢN  Xem ngay!

 

   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é.

Đặng Xuân Duy
0 0 đánh giá
Article Rating
Theo dõi
Thông báo của
guest
0 Comments
Phản hồi nội tuyến
Xem tất cả bình luận