Helpex - Trao đổi & giúp đỡ Đăng nhập

Cách viết các truy vấn TOP N hiệu quả trong SQL

Một loại truy vấn SQL rất phổ biến là truy vấn TOP-N, nơi chúng ta cần các bản ghi "TOP N" được sắp xếp theo một số giá trị, có thể theo từng danh mục. Trong bài đăng trên blog này, chúng ta sẽ xem xét nhiều khía cạnh khác nhau của vấn đề này, cũng như cách giải quyết chúng bằng SQL chuẩn và không chuẩn.

Đây là những khía cạnh khác nhau mà chúng ta sẽ thảo luận:

  • Giá trị hàng đầu
  • Giá trị hàng đầu có mối quan hệ
  • Giá trị hàng đầu cho mỗi danh mục

Nhận giá trị hàng đầu

Khi xem cơ sở dữ liệu của Sakila , chúng ta có thể muốn tìm diễn viên đóng nhiều phim nhất. Giải pháp đơn giản nhất ở đây là sử dụng GROUP BYđể tìm số lượng phim trên mỗi diễn viên, sau đó ORDER BYLIMITtìm diễn viên "TOP 1".

Đây là truy vấn trong PostgreSQL:

SELECT actor_id, first_name, last_name, count(film_id)
FROM actor
LEFT JOIN film_actor USING (actor_id)
GROUP BY actor_id, first_name, last_name
ORDER BY count(film_id) DESC
LIMIT 1;

Năng suất:

ACTOR_ID  FIRST_NAME  LAST_NAME  COUNT
--------------------------------------
107       GINA        DEGENERES  42

Các cơ sở dữ liệu khác có các cú pháp khác nhau LIMIT- hãy xem hướng dẫn sử dụng jOOQ để biết danh sách đầy đủ các mô phỏng của mệnh đề hữu ích này . Ví dụ, trong Oracle 12c, chúng tôi sẽ sử dụng FETCH:

SELECT actor_id, first_name, last_name, count(film_id)
FROM actor
LEFT JOIN film_actor USING (actor_id)
GROUP BY actor_id, first_name, last_name
ORDER BY count(*) DESC
FETCH FIRST ROW ONLY;

Hoặc, trong SQL Server, chúng tôi có thể sử dụng TOP:

SELECT TOP 1 a.actor_id, first_name, last_name, count(film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
GROUP BY a.actor_id, first_name, last_name
ORDER BY count(*) DESC;

... loại nào có ý nghĩa. Chúng tôi muốn diễn viên TOP 1, phải không?

Giải pháp thay thế có truy vấn con

Trong SQL Masterclass của tôi, chúng tôi cũng đang xem xét hiệu suất của các biến thể khác nhau trong việc giải quyết vấn đề cụ thể này. Ví dụ: chúng tôi có thể tính toán COUNT(*)giá trị trong một giá trị dẫn xuất, trước khi tham gia:

SELECT actor_id, first_name, last_name, COALESCE(c, 0)
FROM actor
LEFT JOIN (
  SELECT actor_id, count(*) AS c
  FROM film_actor
  GROUP BY actor_id
) fa USING (actor_id)
ORDER BY COALESCE(c, 0) DESC
LIMIT 1;

Hoặc, chúng tôi có thể tính toán COUNT(*)giá trị trong một truy vấn con tương quan:

SELECT actor_id, first_name, last_name, (
  SELECT count(*)
  FROM film_actor fa
  WHERE fa.actor_id = a.actor_id
) AS c
FROM actor a
ORDER BY c DESC
LIMIT 1;

Chúng hoạt động rất khác nhau, tùy thuộc vào cơ sở dữ liệu, như có thể thấy trong tweet này:

Cách viết các truy vấn TOP N hiệu quả trong SQL

Dù sao, các kỹ thuật khác nhau không thực sự ảnh hưởng đến ngữ nghĩa TOP-N, vì chúng tôi luôn sử dụng hai mệnh đề giống nhau để có được diễn viên TOP 1:

ORDER BY c DESC -- Some means of calculating "c"
LIMIT 1;        -- Some means of limiting results to 1 row

Chức năng cửa sổ thay thế

Ngày xưa, khi các cơ sở dữ liệu như Oracle không thực sự hỗ trợ  LIMIT (hoặc khi sử dụng DB2, SQL Server và Sybase, vốn hỗ trợ " LIMIT" nhưng không hỗ trợ " OFFSET"), mọi người thường sử dụng các hàm cửa sổ. Để có được FETCH FIRST n ROWS ONLYngữ nghĩa, chúng ta có thể sử dụng ROW_NUMBER():

SELECT *
FROM (
  SELECT 
    actor_id, first_name, last_name, count(film_id),
    row_number() OVER (ORDER BY count(film_id) DESC) rn
  FROM actor
  LEFT JOIN film_actor USING (actor_id)
  GROUP BY actor_id, first_name, last_name
) t
WHERE rn <= 5
ORDER BY rn;

Bảng dẫn xuất chứa ROW_NUMBER()hàm cửa sổ đó, tạo ra một số hàng liên tiếp, duy nhất cho mỗi diễn viên được sắp xếp theo số phim mà diễn viên đã đóng.

Lưu ý rằng nhờ bước tổng hợp "xảy ra trước" bước cửa sổ , chúng ta có thể sử dụng các hàm tổng hợp bên trong các hàm cửa sổ.

Chúng tôi sẽ truy cập lại phương pháp tiếp cận hàm cửa sổ sau, khi WITH TIESsử dụng ngữ nghĩa "" RANK().

Giải pháp thay thế dành riêng cho Oracle

Trong Oracle, chúng tôi có những  chức năng FIRST và điều thú vị đóLAST , với một chút cú pháp phức tạp. Chúng giúp tìm phần tử đầu tiên hoặc cuối cùng trong một nhóm được sắp xếp.

SELECT 
  max(actor_id)   KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id),
  max(first_name) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id),
  max(last_name)  KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id),
  max(c)          KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id)
FROM (
  SELECT actor_id, first_name, last_name, count(film_id) c
  FROM actor
  LEFT JOIN film_actor USING (actor_id)
  GROUP BY actor_id, first_name, last_name
) t;

Công bằng mà nói thì cú pháp hơi dài dòng.

Cái này hoạt động ra sao? Truy vấn lồng nhau đếm lại tất cả các phim trên mỗi diễn viên, tạo ra toàn bộ tập kết quả. Tuy nhiên, truy vấn bên ngoài tổng hợp tất cả các diễn viên này và phim của họ được tính vào một hàng duy nhất, chỉ hiển thị trong hàng đó (các) giá trị đầu tiên trong nhóm, được sắp xếp theo một số tiêu chí sắp xếp cụ thể. Lưu ý rằng dấu  ACTOR_ID cũng đã được thêm vào tiêu chí đặt hàng, để có được một thứ tự duy nhất (xem ngữ nghĩa "có ràng buộc", bên dưới).

Tại sao chúng ta cần MAX()hàm tổng hợp? Đơn giản vì tính năng này chỉ áp dụng cho các hàm tổng hợp và FIRST(các) giá trị có thể nhiều hơn một giá trị trong nhóm, cụ thể là khi thứ tự sẽ tạo ra một "ràng buộc" (một lần nữa, xem bên dưới).

Tôi đã thấy điều này hơi tốt hơn các lựa chọn thay thế trong các trường hợp TOP 1 (bỏ qua " TIES", xem bên dưới), bao gồm cả trường hợp này. Điểm chuẩn hiển thị thời gian thực hiện tương đối cho thấy:

Statement 1 : 1.88945  (FETCH version)
Statement 2 : 1.84442  (window function version)
Statement 3 : 1        (KEEP version)

Kỹ thuật điểm chuẩn được mô tả trong blog này và một lần nữa bên dưới. Hãy tự đo!

Nhận được giá trị hàng đầu "Có ràng buộc"

Trong truy vấn trước, chúng tôi nhận được một giá trị hàng đầu và kết quả là chính xác, như chúng ta có thể thấy với truy vấn trả về 5 giá trị hàng đầu này:

SELECT actor_id, first_name, last_name, count(film_id)
FROM actor
LEFT JOIN film_actor USING (actor_id)
GROUP BY actor_id, first_name, last_name
ORDER BY count(film_id) DESC
LIMIT 5;

Kết quả là:

ACTOR_ID  FIRST_NAME  LAST_NAME  COUNT
--------------------------------------
107       GINA        DEGENERES  42
102       WALTER      TORN       41
198       MARY        KEYTEL     40
181       MATTHEW     CARREY     39
23        SANDRA      KILMER     37

Nhưng điều gì sẽ xảy ra nếu WALTER TORN đã đóng thêm một bộ phim nữa? Chúng ta sẽ có một "mối quan hệ ràng buộc" giữa GINA DEGENERES và WALTER TORN, vậy chúng ta sẽ chọn ai là diễn viên TOP 1? Có một số câu trả lời có thể:

  • Chọn một ngẫu nhiên một cách tình cờ : Đây là những gì truy vấn của chúng tôi thực hiện ngay bây giờ. Điều này có thể được coi là công bằng, vì sở thích dành cho người chiến thắng có thể chỉ đơn thuần là kỹ thuật và có thể thay đổi giữa các lần phát hành
  • Chọn ngẫu nhiên một cách rõ ràng : Chúng ta có thể thêm một tiêu chí sắp xếp khác giới thiệu tính ngẫu nhiên (tức là một  RANDOM() lệnh gọi hàm). Hãy chỉ hy vọng rằng cuộc gọi này chỉ được thực hiện khi cần thiết, tức là khi chúng ta hòa. Nếu không, điều đó sẽ khá nguy hiểm. Nhưng nó sẽ là công bằng.
  • Chỉ định tiêu chí phân loại thêm độc đáo : Ví dụ,  ACTOR_ID. Điều đó sẽ làm cho kết quả có thể đoán trước được, nhưng trong trường hợp này thì hơi bất công.
  • Tìm nạp cả hai diễn viên bị ràng buộc . Có, chúng tôi có thể có một số người chiến thắng. Đó là những gì các sự kiện thể thao làm, và đó là những gì chúng ta sẽ thảo luận ngay bây giờ.

Vì vậy, hãy thêm một bộ phim nữa cho WALTER TORN:

INSERT INTO film_actor (actor_id, film_id)
VALUES (102, 1);

Tiêu chuẩn SQL chỉ định cách chúng ta có thể tìm nạp các hàng đầu tiên "với mối quan hệ của chúng", cụ thể là bằng cách sử dụng ... cũng ... FETCH FIRST ROWS WITH TIEScú pháp!

Điều này được thực hiện như vậy trong Oracle 12c, cơ sở dữ liệu duy nhất mà tôi đã thấy với cú pháp tiêu chuẩn này cho đến nay.

SELECT actor_id, first_name, last_name, count(film_id)
FROM actor
LEFT JOIN film_actor USING (actor_id)
GROUP BY actor_id, first_name, last_name
ORDER BY count(film_id) DESC
FETCH FIRST ROWS WITH TIES;

Lưu ý rằng tất cả các cú pháp này đều có thể thực hiện được, chúng đều tương đương nhau. Sử dụng ngôn ngữ giống với ngôn ngữ tiếng Anh nhất, theo ý kiến ​​của bạn (nói cách khác: mọi người làm theo cách khác nhau):

FETCH FIRST 1 ROWS WITH TIES
FETCH FIRST 1 ROW WITH TIES
FETCH FIRST ROWS WITH TIES
FETCH FIRST ROW WITH TIES
FETCH NEXT 1 ROWS WITH TIES
FETCH NEXT 1 ROW WITH TIES
FETCH NEXT ROWS WITH TIES
FETCH NEXT ROW WITH TIES

Cơ sở dữ liệu khác duy nhất mà tôi biết có biết tính năng này (nhưng không phải cú pháp chuẩn) là SQL Server:

SELECT TOP 1 WITH TIES 
  a.actor_id, first_name, last_name, count(*)
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
GROUP BY a.actor_id, first_name, last_name
ORDER BY count(*) DESC;

Mặc dù SQL Server cũng hỗ trợ OFFSET.. FETCHcú pháp tiêu chuẩn cho ROWS ONLYngữ nghĩa (tức là chọn một hàng ngẫu nhiên trong số những người chiến thắng), nó WITH TIESchỉ hỗ trợ với TOPcú pháp độc quyền .

Điều đó không sao, chúng ta luôn có thể sử dụng TOP, bởi vì OFFSETOFFSETphân trang dù sao cũng được viết rõ, phải không?

Sử dụng các chức năng của cửa sổ trong các cơ sở dữ liệu khác

Như đã hứa, chúng tôi hiện đang xem xét lại giải pháp dựa trên hàm cửa sổ để làm cho ngữ nghĩa "WITH TIES" cũng hoạt động trong các cơ sở dữ liệu khác. Chúng ta có thể sử dụng các chức năng xếp hạng, cụ thể là RANK()(hoặc trong những trường hợp hiếm gặp hơn DENSE_RANK()) .

Đây là cách tìm tác nhân TOP 1  WITH TIES  trong PostgreSQL, ví dụ:

SELECT *
FROM (
  SELECT 
    actor_id, first_name, last_name, count(film_id),
    rank() OVER (ORDER BY count(film_id) DESC) rk
  FROM actor
  LEFT JOIN film_actor USING (actor_id)
  GROUP BY actor_id, first_name, last_name
) t
WHERE rk = 1;

Bây giờ chúng tôi đang lồng truy vấn ban đầu vào một bảng dẫn xuất, thêm một cột bổ sung RK, chứa  hàng có thứ tự mong muốn. Kết quả là: RANK()

ACTOR_ID  FIRST_NAME  LAST_NAME  COUNT  RK
------------------------------------------
107       GINA        DEGENERES  42     1
102       WALTER      TORN       42     1

Nếu chúng ta muốn tìm lại TOP 5, chúng ta chỉ cần tìm kiếm các cấp bậc nhỏ hơn hoặc bằng 5. Và đừng quên sắp xếp lại thứ tự rõ ràng. Ngay cả khi thứ tự từ truy vấn con thể ổn định (cụ thể là thứ tự từ hàm window, thứ cuối cùng được áp dụng cho truy vấn con), chúng ta không bao giờ được dựa vào thứ tự không rõ ràng. KHÔNG BAO GIỜ .

SELECT *
FROM (
  SELECT 
    actor_id, first_name, last_name, count(film_id),
    rank() OVER (ORDER BY count(film_id) DESC) rk
  FROM actor
  LEFT JOIN film_actor USING (actor_id)
  GROUP BY actor_id, first_name, last_name
) t
WHERE rk <= 5
ORDER BY rk;
ACTOR_ID  FIRST_NAME  LAST_NAME  COUNT  RK
------------------------------------------
107       GINA        DEGENERES  42     1
102       WALTER      TORN       42     1
198       MARY        KEYTEL     40     3
181       MATTHEW     CARREY     39     4
23        SANDRA      KILMER     37     5

Bây giờ, nếu chúng ta có một diễn viên khác với 37 bộ phim, thì nam diễn viên đó cũng sẽ xuất hiện trong danh sách này và danh sách sẽ có sáu diễn viên, ngay cả khi chúng tôi đã tìm kiếm TOP 5 ( WITH TIES). Hay quá, hả?

Giải pháp dành riêng cho Oracle

Hãy nhớ rằng chúng ta đã thảo luận về các chức năng cụ thểFIRSTLAST của Oracle ? Thật không may, điều này không thể được sử dụng để lấy  WITH TIES  ngữ nghĩa vì tập hợp chỉ có thể tạo ra một hàng duy nhất, không phải nhiều hàng ràng buộc.

Là một workaround, tôi đã cố gắng kết hợp LISTAGG()với KEEPvới không có kết quả:

SELECT 
  LISTAGG (first_name, ', ')
    WITHIN GROUP (ORDER BY count(film_id) DESC)
    KEEP (DENSE_RANK FIRST ORDER BY count(film_id) DESC),
...

Mặc dù điều này có thể tạo ra tất cả các mối quan hệ trong một danh sách giá trị được phân tách bằng dấu phẩy, nhưng cú pháp này đơn giản là không được phép.

Giá trị hàng đầu cho mỗi danh mục

Bây giờ, đây là điều tuyệt vời nhất! Cho đến nay, chúng tôi đã chạy các truy vấn đơn lẻ nhận các giá trị TOP N duy nhất trên toàn bộ tập dữ liệu của chúng tôi, tức là diễn viên có nhiều phim nhất.

Tuy nhiên, hãy tưởng tượng, chúng tôi muốn đạt TOP N thứ gì đó cho mỗi diễn viên . Ví dụ, TOP 3 bộ phim thành công nhất mà một diễn viên đã đóng. Đó sẽ là một câu hỏi khá thú vị. Để đơn giản hơn trên cơ sở dữ liệu của Sakila , chúng ta hãy tìm TOP 3 bộ phim có tiêu đề dài nhất cho mỗi diễn viên .

Điều này sẽ lại sử dụng LIMIT, nhưng lần này, chúng ta cần thực hiện nó trong một truy vấn con. Hai công cụ chúng tôi đã thấy cho đến nay sẽ không thực sự hoạt động tốt:

  • Các bảng dẫn xuất (truy vấn con trong FROMmệnh đề) không thể triển khai ngữ nghĩa cho mỗi tác nhân một cách dễ dàng, ít nhất là không có LIMIT. Có thể thực hiện được với các chức năng cửa sổ như chúng ta sẽ thấy ở phần sau.
  • Các truy vấn con có liên quan (truy vấn con trong mệnh đề SELECThoặc WHERE) chỉ có thể trả về một hàng và một cột. Không hoàn toàn hữu ích khi chúng ta muốn quay lại, tức là 3 hàng TOP.

May mắn thay, tiêu chuẩn SQL chỉ định LATERAL(được triển khai bởi Oracle 12c, PostgreSQL, DB2) và SQL Server luôn có APPLY(cũng có sẵn trong Oracle 12c). Cả hai cú pháp hoàn toàn tương đương. Hãy xem xét APPLYtrước, vì tôi thích cú pháp này hơn:

SELECT actor_id, first_name, last_name, title
FROM actor a
OUTER APPLY (
  SELECT title
  FROM film f
  JOIN film_actor fa USING (film_id)
  WHERE fa.actor_id = a.actor_id
  ORDER BY length(title) DESC
  FETCH FIRST 3 ROWS ONLY
) t
ORDER BY actor_id, length(title) DESC;

Điều này sẽ tạo ra:

ACTOR_ID  FIRST_NAME  LAST_NAME  TITLE
------------------------------------------------------
1         PENELOPE    GUINESS    BULWORTH COMMANDMENTS
1         PENELOPE    GUINESS    ANACONDA CONFESSIONS
1         PENELOPE    GUINESS    GLEAMING JAWBREAKER
2         NICK        WAHLBERG   GOODFELLAS SALUTE
2         NICK        WAHLBERG   DESTINY SATURDAY
2         NICK        WAHLBERG   ADAPTATION HOLES
3         ED          CHASE      ARTIST COLDBLOODED
3         ED          CHASE      NECKLACE OUTBREAK
3         ED          CHASE      BOONDOCK BALLROOM
...

Mát mẻ. Làm thế nào nó hoạt động? Hãy coi bảng dẫn xuất như một hàm. Một hàm với một đối số:

SELECT actor_id, first_name, last_name, title
FROM actor a
OUTER APPLY( SELECT title FROM film f JOIN film_actor fa USING (film_id) WHERE fa.actor_id = a.actor_id -- this is the function argument ORDER BY length(title) DESC FETCH FIRST 3 ROWS ONLY ) t ORDER BY actor_id, length(title) DESC;

Hoặc, nếu chúng ta lưu trữ hàm này dưới dạng một hàm có giá trị bảng thực tế, tức là trong cú pháp PostgreSQL:

CREATE FUNCTION top_3_films_per_actor(p_actor_id IN INTEGER) RETURNS TABLE ( title VARCHAR(50) ) AS $$ SELECT title FROM film f JOIN film_actor fa USING (film_id) WHERE fa.actor_id = p_actor_id ORDER BY length(title) DESC LIMIT 3 $$ LANGUAGE sql;

Các chức năng có giá trị trên bảng khá tuyệt. Chúng hoạt động giống như các dạng xem được tham số hóa trong trường hợp cơ sở dữ liệu của bạn có thể nội dòng nó. DB2 và SQL Server có thể, Oracle 12c và PostgreSQL 9.6 thì không. Không có gì ngạc nhiên khi Oracle không hỗ trợ các hàm SQL, chỉ hỗ trợ các hàm PL / SQL.

Chức năng bây giờ có thể được sử dụng như sau:

SELECT actor_id, first_name, last_name, title
FROM actor a
LEFT JOIN LATERAL top_3_films_per_actor(a.actor_id) t 
ON 1 = 1 -- Silly predicate is required because of LEFT JOIN
ORDER BY actor_id, length(title) DESC;

Lưu ý rằng LATERALcú pháp hoàn toàn giống APPLYvới nghi lễ cú pháp hơn một chút, đặc biệt là khi được sử dụng với OUTER JOIN.

Bây giờ, thông thường, chúng tôi không được phép làm điều này. Chúng tôi không được phép truy cập cột A.ACTOR_IDtừ bên trong bảng dẫn xuất hoặc biểu thức hàm. Bên trong bảng dẫn xuất, bảng Akhông được xác định và phạm vi bên ngoài cũng không thể truy cập được.

APPLY(và LATERAL) thay đổi điều này. Với những công cụ này, bây giờ chúng ta có thể truy cập tất cả các bảng và các cột của chúng ở bên trái của toán tử APPLY(hoặc LATERAL). Điều này có nghĩa là truy vấn con của chúng ta hiện hoạt động giống như một truy vấn con tương quan, nhưng nó được phép trả về:

  • Nhiều hơn một hàng
  • Nhiều hơn một cột

Điều đó thực sự tuyệt vời! Nếu bạn đang làm việc với các luồng Java 8, hãy nghĩ về nó giống như Stream.flatMap()hoạt động tương đương . Nếu chúng ta muốn viết truy vấn này bằng Java với các luồng, chúng ta có thể viết một cái gì đó như:

actors
  .stream()
  .flatMap(a -> films
    .stream()
    .filter(f -> f.hasActor(a)) // Assuming some API
    .sorted(comparing(f -> f.title.length()).reversed())
    .limit(3)
    .map(f -> tuple(a, f)));

Điều này ít nhiều sẽ giống nhau, với một vài đơn giản hóa. Ví dụ, FILM_ACTOR JOIN đã bị lừa bằng một phương pháp bổ trợ Film.hasActor(Actor).

Vì vậy, theo sau flatMap()"phép ẩn dụ",  APPLYáp dụng một hàm bảng (ví dụ: một truy vấn con) cho một bảng khác (trong trường hợp của chúng ta ACTOR:). Hàm đó tạo ra một bảng cho mỗi bản ghi của ACTORbảng. Trong trường hợp của chúng tôi, chúng tôi đã chọn OUTER APPLY(thay vì CROSS APPLY) vì như LEFT JOINvậy sẽ giữ cho các diễn viên không có phim trong tập kết quả - một điều không dễ thực hiện flatMap(), tương ứng với CROSS APPLY.

VỚI Ngữ nghĩa TIES

Điều gì sẽ xảy ra nếu chúng ta muốn liệt kê TOP 3 bộ phim theo tiêu đề dài nhất trên mỗi diễn viên   WITH TIES? Ví dụ, nếu có một số bộ phim có độ dài bằng nhau, chúng ta có thể nhận được bốn hoặc năm bộ phim hoặc nhiều hơn cho bất kỳ diễn viên nhất định nào?

Trong cơ sở dữ liệu hỗ trợ  WITH TIES cú pháp, điều này thực sự đơn giản. Trong Oracle, chỉ cần thay thế ROWS ONLYbằng ROWS WITH TIES:

SELECT actor_id, first_name, last_name, title
FROM actor a
OUTER APPLY (
  SELECT title
  FROM film f
  JOIN film_actor fa USING (film_id)
  WHERE fa.actor_id = a.actor_id
  ORDER BY length(title) DESC
  FETCH FIRST 3 ROWS WITH TIES
) t
ORDER BY actor_id, length(title) DESC;

Trong SQL Server, thêm mệnh đề vào TOPmệnh đề:

SELECT actor_id, first_name, last_name, title
FROM actor a
OUTER APPLY (
  SELECT TOP 3 WITH TIES title
  FROM film f
  JOIN film_actor fa ON f.film_id = fa.film_id
  WHERE fa.actor_id = a.actor_id
  ORDER BY len(title) DESC
) t
ORDER BY actor_id, len(title) DESC;

Trong cả hai trường hợp, chúng tôi hiện nhận được:

ACTOR_ID  FIRST_NAME  LAST_NAME  TITLE
------------------------------------------------------
1         PENELOPE    GUINESS    BULWORTH COMMANDMENTS
1         PENELOPE    GUINESS    ANACONDA CONFESSIONS
1         PENELOPE    GUINESS    GLEAMING JAWBREAKER
1         PENELOPE    GUINESS    WESTWARD SEABISCUIT
2         NICK        WAHLBERG   GOODFELLAS SALUTE
2         NICK        WAHLBERG   ADAPTATION HOLES
2         NICK        WAHLBERG   WARDROBE PHANTOM
2         NICK        WAHLBERG   RUSHMORE MERMAID
2         NICK        WAHLBERG   HAPPINESS UNITED
2         NICK        WAHLBERG   FIGHT JAWBREAKER
2         NICK        WAHLBERG   DESTINY SATURDAY
3         ED          CHASE      ARTIST COLDBLOODED
3         ED          CHASE      NECKLACE OUTBREAK
3         ED          CHASE      BOONDOCK BALLROOM
...

Một kết quả khá khác biệt!

Nếu chúng tôi không có hỗ trợ gốc cho WITH TIES, thì chúng tôi có thể sử dụng RANK()lại và lần này, chúng tôi không cần APPLYhoặc LATERAL:

SELECT actor_id, first_name, last_name, title
FROM actor a
LEFT JOIN (
  SELECT 
    actor_id,
    title, 
    rank() OVER (
      PARTITION BY actor_id
      ORDER BY length(title) DESC
    ) rk
  FROM film f
  JOIN film_actor fa USING (film_id)
) t USING (actor_id)
WHERE rk <= 3
ORDER BY actor_id, rk;

Chúng tôi đang làm gì ở đây, so với APPLYphiên bản của truy vấn?

  • Trước hết, chúng tôi không còn lọc bất kỳ thứ gì trong truy vấn con nữa. WHEREMệnh đề ban đầu truy cập A.ACTOR_IDcột bên ngoài đã biến mất.
  • Thay vào đó, chúng tôi đang sử dụng ACTOR_IDcột đó trong một vị từ JOIN thông thường giữa ACTORbảng và bảng dẫn xuất của chúng tôi để tạo ra các bộ phim.
  • Chúng tôi tính toán các RANK()bộ phim cho mỗi diễn viên . Không giống như trước đây, nơi chúng tôi tính toán RANK()trên toàn bộ tập dữ liệu (mặc định PARTITIONtrong các hàm window luôn là toàn bộ tập dữ liệu), bây giờ chúng tôi phân vùng tập dữ liệu của mình theo ACTOR_ID. Vì vậy, đối với mỗi diễn viên, chúng tôi đang nhận được TOP 3 (và 4 và 5 và 6, ...) phim tính trước .
  • Chỉ sau đó , trong truy vấn bên ngoài, chúng tôi có thể lọc lại theo thứ hạng được tính trước này, hy vọng rằng cơ sở dữ liệu sẽ đủ thông minh và bằng cách nào đó đẩy vị từ xuống hàm xếp hạng.

Giải pháp nào nhanh hơn? Đừng bao giờ đoán! Luôn luôn đo lường!

Thời gian đo điểm chuẩn

Trên thực tế, FETCHnó chỉ là một đường cú pháp để lọc các hàm cửa sổ trong Oracle. Vì vậy, hai truy vấn sẽ thực sự hoạt động tốt như nhau. Tôi đang sử dụng kỹ thuật đo điểm chuẩn từ bài viết này ở đây .

Oracle 12.2.0.1.0 đầu tiên .

Đây là mã đầy đủ cho Oracle:

SET SERVEROUTPUT ON
CREATE TABLE results (
  run     NUMBER(2),
  stmt    NUMBER(2),
  elapsed NUMBER
);

DECLARE
  v_ts TIMESTAMP WITH TIME ZONE;
  v_repeat CONSTANT NUMBER := 100;
BEGIN

  -- Repeat benchmark several times to avoid warmup penalty
  FOR r IN 1..5 LOOP
    v_ts := SYSTIMESTAMP;

    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT actor_id, first_name, last_name, title
        FROM actor a
        OUTER APPLY (
          SELECT title
          FROM film f
          JOIN film_actor fa USING (film_id)
          WHERE fa.actor_id = a.actor_id
          ORDER BY length(title) DESC
          FETCH FIRST 3 ROWS WITH TIES
        ) t
        ORDER BY actor_id, length(title) DESC
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    INSERT INTO results VALUES (r, 1, 
  SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);
    v_ts := SYSTIMESTAMP;

    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT actor_id, first_name, last_name, title
        FROM actor a
        LEFT JOIN (
          SELECT 
            actor_id,
            title, 
            rank() OVER (
              PARTITION BY actor_id
              ORDER BY length(title) DESC
            ) rk
          FROM film f
          JOIN film_actor fa USING (film_id)
        ) t USING (actor_id)
        WHERE rk <= 3
        ORDER BY actor_id, rk
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    INSERT INTO results VALUES (r, 2, 
  SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);
  END LOOP;

  FOR rec IN (
    SELECT 
      run, stmt, 
      CAST(elapsed / MIN(elapsed) OVER() AS NUMBER(10, 5)) ratio 
    FROM results
  )
  LOOP
    dbms_output.put_line('Run ' || rec.run || 
      ', Statement ' || rec.stmt || 
      ' : ' || rec.ratio);
  END LOOP;
END;
/

DROP TABLE results;

Năng suất:

Run 1, Statement 1 :  9.19943
Run 1, Statement 2 :  1.07469
Run 2, Statement 1 : 12.86258
Run 2, Statement 2 :  1.03741
Run 3, Statement 1 : 13.80538
Run 3, Statement 2 :  1.09832
Run 4, Statement 1 : 13.91985
Run 4, Statement 2 :  1.16206
Run 5, Statement 1 :  9.37335
Run 5, Statement 2 :  1

Các kết quả là tương đối với nhau (theo sự hiểu biết của tôi, tôi đang làm điều này để tuân thủ giấy phép của Oracle về việc công bố kết quả điểm chuẩn - không có thời gian thực tế nào được công bố). Câu lệnh 2 (sử dụng xếp hạng rõ ràng) nhiều lần nhanh hơn ít nhất 9x so với câu lệnh 1 (sử dụng FETCH) trên Oracle 12.2.0.1.0! Bummer!

Còn SQL Server 2014 thì sao?

Logic đo điểm chuẩn:

DECLARE @ts DATETIME;
DECLARE @repeat INT = 100;
DECLARE @r INT;
DECLARE @i INT;
DECLARE @dummy1 INT;
DECLARE @dummy2 VARCHAR;
DECLARE @dummy3 VARCHAR;
DECLARE @dummy4 VARCHAR;

DECLARE @s1 CURSOR;
DECLARE @s2 CURSOR;

DECLARE @results TABLE (
  run     INT,
  stmt    INT,
  elapsed DECIMAL
);

SET @r = 0;
WHILE @r < 5
BEGIN
  SET @r = @r + 1

  SET @s1 = CURSOR FOR 
    SELECT actor_id, first_name, last_name, title
FROM actor a
OUTER APPLY (
  SELECT TOP 3 WITH TIES title
  FROM film f
  JOIN film_actor fa ON f.film_id = fa.film_id
  WHERE fa.actor_id = a.actor_id
  ORDER BY len(title) DESC
) t
ORDER BY actor_id, len(title) DESC;

  SET @s2 = CURSOR FOR 
    SELECT a.actor_id, first_name, last_name, title
    FROM actor a
    LEFT JOIN (
      SELECT 
        actor_id,
        title, 
        rank() OVER (
          PARTITION BY actor_id
          ORDER BY len(title) DESC
        ) rk
      FROM film f
      JOIN film_actor fa ON f.film_id = fa.film_id
) t ON a.actor_id = t.actor_id
    WHERE rk <= 3
    ORDER BY a.actor_id, rk

  SET @ts = current_timestamp;
  SET @i = 0;
  WHILE @i < @repeat
  BEGIN
    SET @i = @i + 1

    OPEN @s1;
    FETCH NEXT FROM @s1 INTO @dummy1, @dummy2, @dummy3, @dummy4;
    WHILE @@FETCH_STATUS = 0
    BEGIN
      FETCH NEXT FROM @s1 INTO @dummy1, @dummy2, @dummy3, @dummy4;
    END;

    CLOSE @s1;
  END;

  DEALLOCATE @s1;
  INSERT INTO @results 
  VALUES (@r, 1, DATEDIFF(ms, @ts, current_timestamp));

  SET @ts = current_timestamp;
  SET @i = 0;
  WHILE @i < @repeat
  BEGIN
    SET @i = @i + 1

    OPEN @s2;
    FETCH NEXT FROM @s2 INTO @dummy1, @dummy2, @dummy3, @dummy4;
    WHILE @@FETCH_STATUS = 0
    BEGIN
      FETCH NEXT FROM @s2 INTO @dummy1, @dummy2, @dummy3, @dummy4;
    END;

    CLOSE @s2;
  END;

  DEALLOCATE @s2;
  INSERT INTO @results 
  VALUES (@r, 2, DATEDIFF(ms, @ts, current_timestamp));
END;

SELECT 'Run ' + CAST(run AS VARCHAR) + ', Statement ' + 
  CAST(stmt AS VARCHAR) + ': ' + 
  CAST(CAST(elapsed / MIN(elapsed) OVER() AS DECIMAL(10, 5)) AS VARCHAR)
FROM @results;

Đáng ngạc nhiên, SQL Server cũng có hiệu suất kém hơn với APPLYcách tiếp cận so với các hàm cửa sổ, mặc dù sự khác biệt nhỏ hơn (tôi đang sử dụng SQL Server 2014):

Run 1, Statement 1: 5.07019
Run 1, Statement 2: 1.11988
Run 2, Statement 1: 5.48820
Run 2, Statement 2: 1.20683
Run 3, Statement 1: 5.08882
Run 3, Statement 2: 1.31429
Run 4, Statement 1: 5.31863
Run 4, Statement 2: 1.00000
Run 5, Statement 1: 5.07453
Run 5, Statement 2: 1.21491

Tôi đã mong đợi SQL Server sẽ tốt hơn nhiều ở đây, vì cú pháp này đã có từ lâu trong SQL Server. Một thách thức đối với người đọc: Tại sao cả hai cơ sở dữ liệu đều hoạt động kém như vậy CROSS APPLY? Tôi sẽ giải thích vấn đề của Oracle một chút.

Hãy xem PostgreSQL 9.6 .

Các  WITH TIES ngữ nghĩa chỉ có thể được thực hiện với các chức năng cửa sổ trong PostgreSQL, vì vậy hãy để dính để các  ROWS  ngữ nghĩa của các LIMITđiều khoản.

DO $$
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT INT := 100;
  rec RECORD;
  run INT[];
  stmt INT[];
  elapsed DECIMAL[];
  max_elapsed DECIMAL;
  i INT := 1;
BEGIN

  -- Repeat benchmark several times to avoid warmup penalty
  FOR r IN 1..5 LOOP
    v_ts := clock_timestamp();

    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT actor_id, first_name, last_name, title
        FROM actor a
        LEFT JOIN LATERAL (
          SELECT title
          FROM film f
          JOIN film_actor fa USING (film_id)
          WHERE fa.actor_id = a.actor_id
          ORDER BY length(title) DESC
          LIMIT 3
        ) t ON true
        ORDER BY actor_id, length(title) DESC
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    run[i] := r;
    stmt[i] := 1;
    elapsed[i] := 
       (EXTRACT(EPOCH FROM CAST(clock_timestamp() AS TIMESTAMP)) 
      - EXTRACT(EPOCH FROM v_ts));
    i := i + 1;
    v_ts := clock_timestamp();

    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT actor_id, first_name, last_name, title
        FROM actor a
        LEFT JOIN (
          SELECT 
            actor_id,
            title, 
            row_number() OVER (
              PARTITION BY actor_id
              ORDER BY length(title) DESC
            ) rn
          FROM film f
          JOIN film_actor fa USING (film_id)
        ) t USING (actor_id)
        WHERE rn <= 3
        ORDER BY actor_id, rn
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    run[i] := r;
    stmt[i] := 2;
    elapsed[i] := 
       (EXTRACT(EPOCH FROM CAST(clock_timestamp() AS TIMESTAMP)) 
      - EXTRACT(EPOCH FROM v_ts));
    i := i + 1;
  END LOOP;

  SELECT max(t.elapsed)
  INTO max_elapsed
  FROM unnest(elapsed) AS t(elapsed);

  FOR i IN 1..array_length(run, 1) LOOP
    RAISE INFO 'RUN %, Statement %: %', run[i], stmt[i], 
      CAST(elapsed[i] / max_elapsed AS DECIMAL(10, 5));
  END LOOP;
END$$;

Lần này, kết quả thậm chí còn hơn. Có vẻ như bạn chọn cách tiếp cận nào không thực sự quan trọng:

00000: RUN 1, Statement 1: 0.75904
00000: RUN 1, Statement 2: 0.99784
00000: RUN 2, Statement 1: 0.75907
00000: RUN 2, Statement 2: 0.95206
00000: RUN 3, Statement 1: 0.82102
00000: RUN 3, Statement 2: 0.94841
00000: RUN 4, Statement 1: 0.96208
00000: RUN 4, Statement 2: 0.96218
00000: RUN 5, Statement 1: 0.83398
00000: RUN 5, Statement 2: 1.00000

Điều này có thể có hai lý do:

  1. Lạc quan : LATERALđược tối ưu hóa tốt hơn trong PostgreSQL
  2. Bi quan : Các chức năng cửa sổ được tối ưu hóa kém hơn trong PostgreSQL

Từ kinh nghiệm điều chỉnh các truy vấn PostgreSQL, tôi sẽ đưa ra dự đoán bi quan, bởi vì thực sự, không có gợi ý nào trong kế hoạch thực thi về bất kỳ tối ưu hóa nào đang được thực hiện trên vị từ hàm window:

Sort  (cost=911.26..915.81 rows=1821 width=40)
  Sort Key: a.actor_id, t.rn
  -> Hash Join  (cost=596.44..812.65 rows=1821 width=40)
     Hash Cond: (t.actor_id = a.actor_id)
     -> Subquery Scan on t  (cost=589.94..781.11 rows=1821 width=25)
        Filter: (t.rn <= 3)
        -> WindowAgg  (cost=589.94..712.83 rows=5462 width=29)
           -> Sort  (cost=589.94..603.59 rows=5462 width=21)
              Sort Key: fa.actor_id, (length((f.title)::text)) DESC
              -> Hash Join  (cost=77.50..250.88 rows=5462 width=21)
                 Hash Cond: (fa.film_id = f.film_id)
                 -> Seq Scan on film_actor fa (cost=0.0..84.62 rows=5462 width=4)
                 -> Hash  (cost=65.00..65.00 rows=1000 width=19)
                    ->  Seq Scan on film f  (cost=0.00..65.00 rows=1000 width=19)
     -> Hash  (cost=4.00..4.00 rows=200 width=17)
        -> Seq Scan on actor a  (cost=0.00..4.00 rows=200 width=17)

Còn DB2 10.5 thì sao?

BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
  EXECUTE IMMEDIATE 'CREATE TABLE print_relative (run INTEGER, stmt INTEGER, elapsed DECIMAL(20, 4))';
END 

BEGIN
  DECLARE v_ts TIMESTAMP;
  DECLARE v_repeat INTEGER DEFAULT 100;
  DECLARE v_i INTEGER;
  DECLARE v_j INTEGER;

  -- Repeat benchmark several times to avoid warmup penalty
  SET v_i = 1;

  DELETE FROM print_relative;

  REPEAT
    SET v_j = 1;
    SET v_ts = CURRENT_TIMESTAMP;

    REPEAT
      FOR rec AS cur CURSOR FOR
        SELECT actor_id, first_name, last_name, title
        FROM actor a
        LEFT JOIN LATERAL (
          SELECT title
          FROM film f
          JOIN film_actor fa ON f.film_id = fa.film_id
          WHERE fa.actor_id = a.actor_id
          ORDER BY length(title) DESC
          FETCH FIRST 3 ROWS ONLY
        ) t ON 1 = 1
        ORDER BY actor_id, length(title) DESC
      DO
        BEGIN END;
      END FOR;

      SET v_j = v_j + 1;
      UNTIL v_j = v_repeat
    END REPEAT;

    INSERT INTO print_relative 
    VALUES (v_i, 1, (CURRENT_TIMESTAMP - v_ts));

    SET v_j = 1;
    SET v_ts = CURRENT_TIMESTAMP;

    REPEAT
      FOR rec AS cur CURSOR FOR
        SELECT a.actor_id, first_name, last_name, title
        FROM actor a
        LEFT JOIN (
          SELECT 
            actor_id,
            title, 
            row_number() OVER (
              PARTITION BY actor_id
              ORDER BY length(title) DESC
            ) rn
          FROM film f
          JOIN film_actor fa ON f.film_id = fa.film_id
        ) t ON t.actor_id = a.actor_id
        WHERE rn <= 3
        ORDER BY a.actor_id, rn
      DO
        BEGIN END;
      END FOR;

      SET v_j = v_j + 1;
      UNTIL v_j = v_repeat
    END REPEAT;

    INSERT INTO print_relative 
    VALUES (v_i, 2, (CURRENT_TIMESTAMP - v_ts));

    SET v_i = v_i + 1;
    UNTIL v_i = 5
  END REPEAT;
END

SELECT
  run, 
  stmt,
  CAST(elapsed / MIN(elapsed) OVER() AS DECIMAL(20, 4)) ratio
FROM print_relative;

DROP TABLE print_relative;

Kết quả, một lần nữa, các chức năng cửa sổ hoạt động tốt hơn LATERALviệc tham gia:

116.0353
121.0783
216.2549
221.0093
316.1067
321.0000
416.1987
421.0128

Giải thích cho Điểm chuẩn

Đối với lời giải thích này, tôi chỉ xem xét các kế hoạch thực thi của Oracle - cho thấy loại cơ sở lý luận nào có thể được rút ra từ các kế hoạch của họ. Tôi giả định rằng các vấn đề tương tự có thể xuất hiện trong ba cơ sở dữ liệu khác.

Đây là kế hoạch cho truy vấn hàm cửa sổ ..

Sau đây là kế hoạch thực hiện có được thông qua:

SELECT * FROM TABLE (
  dbms_xplan.display_cursor(format => 'ALLSTATS LAST')
)

Nó hiển thị kế hoạch thực hiện thực tế với số liệu thống kê được thu thập (sử dụng /*+GATHER_PLAN_STATISTICS*/gợi ý), không phải kế hoạch ước tính .

----------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | A-Rows |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |    767 |
|   1 |  SORT ORDER BY             |               |      1 |    767 |
|*  2 |   HASH JOIN                |               |      1 |    767 |
|   3 |    TABLE ACCESS FULL       | ACTOR         |      1 |    200 |
|*  4 |    VIEW                    |               |      1 |    767 |
|*  5 |     WINDOW SORT PUSHED RANK|               |      1 |   1079 |
|*  6 |      HASH JOIN             |               |      1 |   5463 |
|   7 |       TABLE ACCESS FULL    | FILM          |      1 |   1000 |
|   8 |       INDEX FAST FULL SCAN | PK_FILM_ACTOR |      1 |   5463 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  
   2 - access("A"."ACTOR_ID"="T"."ACTOR_ID")
   4 - filter("T"."RK"<=3)
   5 - filter(RANK() OVER ( PARTITION BY "FA"."ACTOR_ID" 
         ORDER BY LENGTH("F"."TITLE") DESC )<=3)
   6 - access("F"."FILM_ID"="FA"."FILM_ID")

Có thể thấy, có một số công việc ban đầu là tính toán thứ hạng cho hầu hếtFILM_ACTOR các hàng từ truy vấn lồng nhau. Tôi nói hầu hết bởi vì phép toán PUSHED RANKtrong WINDOW SORT PUSHED RANKdường như chỉ ra rằng thứ hạng chỉ được tính khi nó cần thiết, tức là cho đến khi vị từ xếp hạng rk <= 3không còn đúng nữa.

Đặc biệt, điều này cũng có nghĩa là chúng tôi không nhất thiết phải thực hiện toàn bộ sắp xếp, nhưng chúng tôi có thể giữ một bộ đệm của TOP 3 hiện tại, điều này có thể được thực hiện O(N)thay vì sắp xếp đầy đủ O(N log N). Tôi không biết điều này có thực sự được thực hiện trong Oracle hay không.

Trong mọi trường hợp, kết quả của hoạt động xếp hạng sau đó được kết hợp hiệu quả với bảng băm ACTOR.

Tuy nhiên, điều thú vị nhất là Starts cột cho biết số lần một hoạt động đã được bắt đầu. Mỗi hoạt động chỉ được bắt đầu một lần!

Về APPLYthì sao?

Kế hoạch còn tệ hơn nhiều:

---------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |    767 |
|   1 |  SORT ORDER BY                |                 |      1 |    767 |
|   2 |   MERGE JOIN OUTER            |                 |      1 |    767 |
|   3 |    TABLE ACCESS FULL          | ACTOR           |      1 |    200 |
|   4 |    BUFFER SORT                |                 |    200 |    767 |
|   5 |     VIEW                      | VW_LAT_14BC7596 |    200 |    767 |
|   6 |      VIEW                     | VW_LAT_A18161FF |    200 |    767 |
|*  7 |       VIEW                    |                 |    200 |    767 |
|*  8 |        WINDOW SORT PUSHED RANK|                 |    200 |   1079 |
|   9 |         NESTED LOOPS          |                 |    200 |   5463 |
|  10 |          TABLE ACCESS FULL    | FILM            |    200 |    200K|
|* 11 |          INDEX UNIQUE SCAN    | PK_FILM_ACTOR   |    200K|   5463 |
---------------------------------------------------------------------------
  
Predicate Information (identified by operation id):
---------------------------------------------------

   7 - filter("from$_subquery$_008"."rowlimit_$$_rank"<=3)
   8 - filter(RANK() OVER ( ORDER BY LENGTH("F"."TITLE") DESC )<=3)
  11 - access("FA"."ACTOR_ID"="A"."ACTOR_ID" AND "F"."FILM_ID"="FA"."FILM_ID")

Quan sát cột "Bắt đầu". Trên Id 4, chúng tôi đang trải qua 200 lần thực thi  BUFFER SORT hoạt động. Đó là bởi vì có chính xác 200 hàng trong  ACTOR bảng (như có thể thấy trên Id 3). Vì vậy, thực sự, không có tối ưu hóa nào xảy ra sẽ tính toán "truy vấn con tương quan theo chiều" cho tất cả các tác nhân trong một lần.

Hơn nữa, khá không may,  JOIN thứ tự giữa FILMFILM_ACTORcó vẻ khá sai. Đối với mỗi trong số 200 diễn viên, chúng tôi đang tải toàn bộ 1000 bộ phim, tạo ra 200 * 1000 = 200K hàng để quét những bộ phim thuộc về một diễn viên từ truy vấn bên ngoài (Id 11). Điều đó lặp đi lặp lại một cách phi lý.

Tôi hy vọng Oracle sẽ đảo ngược các truy cập bảng. Chúng tôi có thể làm điều này với một gợi ý:

SELECT actor_id, first_name, last_name, title
FROM actor a
OUTER APPLY (
  SELECT /*+LEADING(fa f) USE_NL(fa f)*/ title
  FROM film f
  JOIN film_actor fa USING (film_id)
  WHERE actor_id = a.actor_id
  ORDER BY length(title) DESC
  FETCH FIRST 3 ROWS WITH TIES
) t
ORDER BY actor_id, length(title) DESC;

Quan sát hai gợi ý:

  • LEADINGđể chỉ ra rằng FILM_ACTORbảng phải là nguồn hàng dẫn động của phép nối
  • USE_NLđể thực thi một  NESTED LOOP JOIN (không có điều này và với LEADING, Oracle sẽ thích a HASH JOIN)

Với các gợi ý, kết quả điểm chuẩn trông tốt hơn rất nhiều:

Statement 1 : 1          (window functions)
Statement 2 : 9.17483    (APPLY without hints)
Statement 3 : 4.88774    (APPLY with LEADING hint)
Statement 4 : 1.65269    (APPLY with LEADING and USE_NL hints)

Ngoài ra, kế hoạch thực thi giờ đây không còn có những   giá trị Starts và  A-Rowsgiá trị thừa nữa:

--------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | A-Rows |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |     50 |
|   1 |  SORT ORDER BY                      |                |      1 |     50 |
|   2 |   MERGE JOIN OUTER                  |                |      1 |    767 |
|   3 |    TABLE ACCESS FULL                | ACTOR          |      1 |    200 |
|   4 |    BUFFER SORT                      |                |    200 |    767 |
|   5 |     VIEW                            | VW_LAT_2880E7C5|    200 |    767 |
|   6 |      VIEW                           | VW_LAT_A18161FF|    200 |    767 |
|*  7 |       VIEW                          |                |    200 |    767 |
|*  8 |        WINDOW SORT PUSHED RANK      |                |    200 |   1079 |
|   9 |         NESTED LOOPS                |                |    200 |   5463 |
|  10 |          NESTED LOOPS               |                |    200 |   5463 |
|* 11 |           INDEX RANGE SCAN          | PK_FILM_ACTOR  |    200 |   5463 |
|* 12 |           INDEX UNIQUE SCAN         | PK_FILM        |   5463 |   5463 |
|  13 |          TABLE ACCESS BY INDEX ROWID| FILM           |   5463 |   5463 |
--------------------------------------------------------------------------------

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

   7 - filter("from$_subquery$_006"."rowlimit_$$_rank"<=3)
   8 - filter(RANK() OVER ( ORDER BY LENGTH("F"."TITLE") DESC )<=3)
  11 - access("FA"."ACTOR_ID"="A"."ACTOR_ID")
  12 - access("F"."FILM_ID"="FA"."FILM_ID")

Con số cao nhất A-Rows có thể dễ dàng giải thích. Có chính xác 5.463 hàng trong FILM_ACTOR. Tuy nhiên, tôi ước rằng 200 lần tra cứu TOP-N cho mỗi diễn viên có thể được tối ưu hóa bằng cách nào đó.

Điều thú vị là, kế hoạch này có liên quan đến chi phí cao hơn nhiều so với kế hoạch ban đầu, ngay cả khi trong trường hợp này, nó tốt hơn nhiều.

Phần kết luận

Các truy vấn TOP N rất phổ biến, chúng ta luôn cần chúng. Cách đơn giản nhất là sử dụng ORDER BYmệnh đề và LIMITmệnh đề.

Đôi khi, chúng ta cần WITH TIESngữ nghĩa và Oracle 12c cũng như SQL Server có thể cung cấp điều này với cú pháp tiêu chuẩn hoặc dành riêng cho nhà cung cấp. Các cơ sở dữ liệu khác có thể mô phỏng WITH TIESbằng cách sử dụng các chức năng cửa sổ khá dễ dàng.

Khi chúng ta cần TOP N cho mỗi danh mục , một cú pháp thú vị và thú vị sẽ có ích: APPLYhoặc LATERAL. Tuy nhiên, rất tiếc, các điểm chuẩn đơn giản cho thấy rằng chúng có thể chậm hơn nhiều so với các đối tác chức năng cửa sổ tương đương của chúng. Tất nhiên, điều này sẽ phụ thuộc nhiều vào kích thước của tập dữ liệu. Đừng đánh giá thấp chi phí của O(N log N)các loại xuất hiện trong các chức năng cửa sổ. Như mọi khi: Đo lường, không bao giờ đoán.

18 hữu ích 0 bình luận 30k xem chia sẻ

Có thể bạn quan tâm

loading