Các cải tiến trong Firebird 5.0.1 ở bộ tối ưu hóa

(c) D.Simonov, IBSurgeon, 21-Aug-2024

Gần đây, một bản phát hành điểm của hệ quản trị cơ sở dữ liệu Firebird 5.0 đã được phát hành Firebird 5.0.1. Ngoài việc sửa lỗi, một chức năng tối ưu hóa mới mang tính thử nghiệm đã được thêm vào, sẽ được thảo luận trong bài viết này.

Chuyển đổi subquery thành ANY/SOME/IN/EXISTS trong semi-join

Semi-join là một phép toán nối hai quan hệ, chỉ trả về các hàng từ một trong hai quan hệ mà không thực hiện toàn bộ phép nối. Không giống như các toán tử nối khác, không có cú pháp tường minh nào để chỉ định có thực hiện semi-join hay không. Tuy nhiên, bạn có thể thực hiện semi-join bằng cách sử dụng subquery trong ANY/SOME/IN/EXISTS.

Theo truyền thống, Firebird chuyển đổi các subquery trong vị từ ANY/SOME/IN thành các subquery tương quan trong vị từ EXISTS, và thực thi subquery trong EXISTS cho từng bản ghi của truy vấn bên ngoài. Khi thực thi subquery bên trong vị từ EXISTS, chiến lược FIRST ROWS được sử dụng và việc thực thi dừng lại ngay sau khi bản ghi đầu tiên được trả về.

Bắt đầu từ Firebird 5.0.1, các subquery trong vị từ ANY/SOME/IN/EXISTS có thể được chuyển đổi thành semi-join. Tính năng này bị tắt theo mặc định và có thể được bật bằng cách đặt tham số cấu hình SubQueryConversion thành true trong tệp firebird.conf hoặc database.conf.

Tính năng này đang trong giai đoạn thử nghiệm, vì vậy nó bị tắt theo mặc định. Bạn có thể bật nó và kiểm tra các truy vấn của mình với subquery trong vị từ ANY/SOME/IN/EXISTS, và nếu hiệu suất tốt hơn, hãy để nó được bật, nếu không hãy đặt tham số SubQueryConversion trở lại giá trị mặc định ( false).
Giá trị mặc định cho tham số cấu hình SubQueryConversion có thể được thay đổi trong tương lai, hoặc tham số này có thể bị loại bỏ hoàn toàn. Điều này sẽ xảy ra khi cách làm mới được chứng minh là tối ưu hơn trong hầu hết các trường hợp.


	Tính năng này đang trong giai đoạn thử nghiệm, vì vậy nó bị tắt theo mặc định. Bạn có thể bật nó và kiểm tra các truy vấn của mình với subquery trong vị từ ANY/SOME/IN/EXISTS, và nếu hiệu suất tốt hơn, hãy để nó được bật, nếu không hãy đặt tham số `SubQueryConversion` trở lại giá trị mặc định ( `false`). Giá trị mặc định cho tham số cấu hình `SubQueryConversion` có thể được thay đổi trong tương lai, hoặc tham số này có thể bị loại bỏ hoàn toàn. Điều này sẽ xảy ra khi cách làm mới được chứng minh là tối ưu hơn trong hầu hết các trường hợp.

Không giống như thực hiện ANY/SOME/IN/EXISTS trực tiếp trên subquery, tức là như các subquery tương quan, việc thực hiện chúng như semi-join mang lại nhiều không gian hơn cho việc tối ưu hóa. Semi-join có thể được thực hiện bằng các thuật toán Hash Join (semi) hoặc Nested Loop Join (semi) khác nhau, trong khi các subquery tương quan luôn được thực hiện cho từng bản ghi của truy vấn bên ngoài.

Hãy thử bật tính năng này bằng cách đặt tham số SubQueryConversion thành true trong tệp firebird.conf. Bây giờ chúng ta hãy thực hiện một số thử nghiệm.

Hãy thực thi truy vấn sau:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND H.CODE_SEX = 2
  AND H.CODE_HORSE IN (
    SELECT COVER.CODE_FATHER
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND EXTRACT(YEAR FROM COVER.BYDATE) = 2023
  )

Code

Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Filter
                    -> Table "HORSE" as "H" Access By ID
                        -> Bitmap And
                            -> Bitmap
                                -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                            -> Bitmap
                                -> Index "FK_HORSE_SEX" Range Scan (full match)
                -> Record Buffer (record length: 41)
                    -> Filter
                        -> Table "COVER" Access By ID
                            -> Bitmap And
                                -> Bitmap
                                    -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
                                -> Bitmap
                                    -> Index "FK_COVER_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                  297

Current memory = 552356752
Delta memory = 352
Max memory = 552567920
Elapsed time = 0.045 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 43984
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     1516|         |         |         |
HORSE                           |         |    37069|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Trong kế hoạch thực thi, chúng ta thấy một phương thức nối mới Hash Join (semi). Kết quả của subquery trong IN đã được lưu vào bộ đệm, có thể thấy trong kế hoạch là Record Buffer (record length: 41). Nghĩa là, trong trường hợp này subquery trong IN được thực thi một lần, kết quả của nó được lưu trong bộ nhớ bảng băm, và sau đó truy vấn bên ngoài chỉ cần tìm kiếm trong bảng băm này.

Để so sánh, hãy chạy cùng một truy vấn với tính năng chuyển đổi subquery thành semi-join bị tắt.

Code

Sub-query
    -> Filter
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_COVER_FATHER" Range Scan (full match)
                    -> Bitmap
                        -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                    -> Bitmap
                        -> Index "FK_HORSE_SEX" Range Scan (full match)

                COUNT
=====================
                  297

Current memory = 552046496
Delta memory = 352
Max memory = 552135600
Elapsed time = 0.395 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 186891
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |      297|         |         |         |
HORSE                           |         |    37069|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Kế hoạch thực thi cho thấy subquery được thực thi cho từng bản ghi của truy vấn chính, nhưng sử dụng thêm chỉ mục FK_COVER_FATHER. Điều này cũng có thể thấy trong thống kê thực thi: số lần Fetches lớn hơn 4 lần, thời gian thực thi gần như tệ hơn 4 lần.


	Người đọc có thể hỏi: tại sao hash semi-join lại hiển thị số lần đọc chỉ mục của bảng COVER nhiều hơn 5 lần, nhưng ngoài ra thì tốt hơn? Thực tế là số lần đọc chỉ mục trong thống kê cho thấy số bản ghi được đọc bằng chỉ mục, chúng không cho thấy tổng số lần truy cập chỉ mục, một số trong đó không dẫn đến việc lấy bản ghi nào cả, nhưng những lần truy cập đó không miễn phí.

Người đọc có thể hỏi: tại sao hash semi-join lại hiển thị số lần đọc chỉ mục của bảng COVER nhiều hơn 5 lần, nhưng ngoài ra thì tốt hơn? Thực tế là số lần đọc chỉ mục trong thống kê cho thấy số bản ghi được đọc bằng chỉ mục, chúng không cho thấy tổng số lần truy cập chỉ mục, một số trong đó không dẫn đến việc lấy bản ghi nào cả, nhưng những lần truy cập đó không miễn phí.

Điều gì đã xảy ra? Để hiểu rõ hơn về việc chuyển đổi subquery, hãy giới thiệu một toán tử semi-join tưởng tượng “SEMI JOIN”. Như tôi đã nói, loại nối này không được biểu diễn trong ngôn ngữ SQL. Truy vấn của chúng ta với toán tử IN đã được chuyển đổi thành một dạng tương đương, có thể được viết như sau:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
  SEMI JOIN (
    SELECT COVER.CODE_FATHER
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND EXTRACT(YEAR FROM COVER.BYDATE) = 2023
  ) TMP ON TMP.CODE_FATHER = H.CODE_HORSE
WHERE H.CODE_DEPARTURE = 1
  AND H.CODE_SEX = 2

Bây giờ thì rõ ràng hơn. Điều tương tự cũng xảy ra với các subquery sử dụng EXISTS. Hãy xem một ví dụ khác:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_DEPARTURE = 1
      AND COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.CODE_MOTHER = H.CODE_MOTHER
  )

Hiện tại, không thể viết một EXISTS như vậy bằng IN. Hãy xem cách nó được triển khai mà không chuyển đổi thành semi-join.

Code

Sub-query
    -> Filter
        -> Table "COVER" Access By ID
            -> Bitmap And
                -> Bitmap
                    -> Index "FK_COVER_MOTHER" Range Scan (full match)
                -> Bitmap
                    -> Index "FK_COVER_FATHER" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                91908

Current memory = 552240400
Delta memory = 352
Max memory = 554680016
Elapsed time = 19.083 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 935679
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    91908|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Rất chậm. Bây giờ hãy đặt SubQueryConversion = true và chạy lại truy vấn.

Code

Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Filter
                    -> Table "HORSE" as "H" Access By ID
                        -> Bitmap
                            -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                -> Record Buffer (record length: 49)
                    -> Filter
                        -> Table "COVER" Access By ID
                            -> Bitmap
                                -> Index "FK_COVER_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                91908

Current memory = 552102000
Delta memory = 352
Max memory = 561520736
Elapsed time = 0.208 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 248009
Per table statistics:
--------------------------------+---------+---------+---------+---------+---------+
 Table name                     | Natural | Index   | Insert  | Update  | Delete  |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |   140254|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Truy vấn đã được thực thi nhanh hơn 100 lần! Nếu chúng ta viết lại nó bằng toán tử SEMI JOIN hư cấu của mình, truy vấn sẽ trông như thế này:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
  SEMI JOIN (
    SELECT
      COVER.CODE_FATHER,
      COVER.CODE_MOTHER
    FROM COVER
  ) TMP ON TMP.CODE_FATHER = H.CODE_FATHER AND TMP.CODE_MOTHER = H.CODE_MOTHER
WHERE H.CODE_DEPARTURE = 1

Có phải bất kỳ subquery tương quan nào trong IN/EXISTS đều có thể được chuyển đổi thành semi-join không? Không, không phải bất kỳ, ví dụ, nếu subquery chứa các bộ lọc FETCH/FIRST/SKIP/ROWS, thì subquery không thể được chuyển đổi thành semi-join và nó sẽ được thực thi như một subquery tương quan. Đây là một ví dụ về một truy vấn như vậy:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_HORSE
    OFFSET 0 ROWS
  )

Ở đây cụm từ OFFSET 0 ROWS không thay đổi ngữ nghĩa của truy vấn và kết quả thực thi của nó sẽ giống như khi không có nó. Hãy xem kế hoạch và thống kê của truy vấn này.

Code

Sub-query
    -> Skip N Records
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap
                    -> Index "FK_COVER_FATHER" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

                COUNT
=====================

10971

Bộ nhớ hiện tại = 551912944
Delta bộ nhớ = 288
Bộ nhớ tối đa = 552002112
Thời gian trôi qua = 0.201 giây
Bộ đệm = 32768
Số lần đọc = 0
Số lần ghi = 0
Số lần truy xuất = 408988
Thống kê theo bảng:
--------------------------------+---------+---------+---------+---------+---------+
 Tên bảng                       | Tự nhiên| Chỉ mục | Chèn    | Cập nhật| Xóa     |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    10971|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Như bạn có thể thấy, việc chuyển đổi thành semi-join đã không xảy ra. Bây giờ hãy xóa OFFSET 0 ROWS và lấy thống kê lại.

Code

Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Filter
                    -> Table "HORSE" as "H" Access By ID
                        -> Bitmap
                            -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
                -> Record Buffer (record length: 33)
                    -> Table "COVER" Full Scan

                COUNT
=====================
                10971

Bộ nhớ hiện tại = 552112128
Delta bộ nhớ = 288
Bộ nhớ tối đa = 585044592
Thời gian trôi qua = 0.405 giây
Bộ đệm = 32768
Số lần đọc = 0
Số lần ghi = 0
Số lần truy xuất = 854841
Thống kê theo bảng:
--------------------------------+---------+---------+---------+---------+---------+
 Tên bảng                       | Tự nhiên| Chỉ mục | Chèn    | Cập nhật| Xóa     |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |   722465|         |         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Ở đây việc chuyển đổi thành semi-join đã xảy ra, và như chúng ta có thể thấy thời gian thực thi đã trở nên tồi tệ hơn. Nguyên nhân là do hiện tại trình tối ưu hóa chưa có ước tính chi phí giữa các thuật toán join Hash Join (semi) và Nested Loop Join (semi) sử dụng chỉ mục, vì vậy quy tắc là: nếu điều kiện join chỉ chứa duy nhất phép so sánh bằng, thì thuật toán Hash Join (semi) được chọn, ngược lại các truy vấn con IN/EXISTS được thực thi như bình thường.

Bây giờ hãy vô hiệu hóa việc chuyển đổi semi-join và xem thống kê thực thi.

Code

Sub-query
    -> Filter
        -> Table "COVER" Access By ID
            -> Bitmap
                -> Index "FK_COVER_FATHER" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                10971

Bộ nhớ hiện tại = 551912752
Delta bộ nhớ = 288
Bộ nhớ tối đa = 552001920
Thời gian trôi qua = 0.193 giây
Bộ đệm = 32768
Số lần đọc = 0
Số lần ghi = 0
Số lần truy xuất = 408988
Thống kê theo bảng:
--------------------------------+---------+---------+---------+---------+---------+
 Tên bảng                       | Tự nhiên| Chỉ mục | Chèn    | Cập nhật| Xóa     |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |    10971|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Như bạn có thể thấy, số lần truy xuất hoàn toàn bằng với trường hợp truy vấn con chứa mệnh đề OFFSET 0 ROWS, và thời gian thực thi khác biệt trong phạm vi sai số. Điều này có nghĩa là bạn có thể sử dụng mệnh đề OFFSET 0 ROWS như một gợi ý để vô hiệu hóa việc chuyển đổi semi-join.

Bây giờ hãy xem xét các trường hợp sử dụng bất kỳ điều kiện tương quan nào khác ngoài phép so sánh bằng và IS NOT DISTINCT FROM trong các truy vấn con.

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.BYDATE > H.BIRTHDAY
  )

Code

Sub-query
    -> Filter
        -> Table "COVER" Access By ID
            -> Bitmap
                -> Index "COVER_IDX_BYDATE" Range Scan (lower bound: 1/1)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

Như tôi đã nói ở trên, không có chuyển đổi nào thành semi-join xảy ra, truy vấn con được thực thi cho mỗi bản ghi của truy vấn chính.

Hãy tiếp tục các thử nghiệm, viết một truy vấn sử dụng phép so sánh bằng và một vị từ khác ngoài phép so sánh bằng.

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.BYDATE > H.BIRTHDAY
  )

Code

Select Expression
    -> Aggregate
        -> Nested Loop Join (semi)
            -> Filter
                -> Table "HORSE" as "H" Access By ID
                    -> Bitmap
                        -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)
            -> Filter
                -> Filter
                    -> Table "COVER" Access By ID
                        -> Bitmap
                            -> Index "COVER_IDX_BYDATE" Range Scan (lower bound: 1/1)

Ở đây trong kế hoạch chúng ta thấy lần đầu tiên sử dụng phương pháp join Nested Loop Join (semi), nhưng tiếc là kế hoạch này tồi, vì chỉ mục FK_COVER_FATHER không được sử dụng. Bạn sẽ không nhận được bất kỳ kết quả nào từ truy vấn như vậy. Điều này có thể được khắc phục bằng cách sử dụng gợi ý OFFSET 0 ROWS.

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_DEPARTURE = 1
  AND EXISTS (
    SELECT *
    FROM COVER
    WHERE COVER.CODE_FATHER = H.CODE_FATHER
      AND COVER.BYDATE > H.BIRTHDAY
    OFFSET 0 ROWS
  )

Code

Sub-query
    -> Skip N Records
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap
                    -> Index "FK_COVER_FATHER" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Access By ID
                -> Bitmap
                    -> Index "FK_HORSE_DEPARTURE" Range Scan (full match)

                COUNT
=====================
                72199

Bộ nhớ hiện tại = 554017824
Delta bộ nhớ = 320
Bộ nhớ tối đa = 554284480
Thời gian trôi qua = 45.548 giây
Bộ đệm = 32768
Số lần đọc = 0
Số lần ghi = 0
Số lần truy xuất = 84145713
Thống kê theo bảng:
--------------------------------+---------+---------+---------+---------+---------+
 Tên bảng                       | Tự nhiên| Chỉ mục | Chèn    | Cập nhật| Xóa     |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         | 75894621|         |         |         |
HORSE                           |         |    96021|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Không phải là thời gian thực thi tốt nhất, nhưng trong trường hợp này ít nhất chúng ta đã có được kết quả.

Do đó, việc chuyển đổi các truy vấn con thành ANY/SOME/IN/EXISTS thành semi-join cho phép trong một số trường hợp tăng tốc đáng kể việc thực thi truy vấn, nhưng hiện tại tính năng này vẫn chưa hoàn thiện và do đó bị vô hiệu hóa theo mặc định. Trong Firebird 6.0, họ sẽ cố gắng thêm ước tính chi phí cho tính năng này, cũng như khắc phục một số thiếu sót khác. Ngoài ra, Firebird 6.0 dự kiến sẽ thêm chuyển đổi các truy vấn con ALL/NOT IN/NOT EXISTS thành anti-join.

Kết thúc phần xem xét việc thực thi các truy vấn con trong IN/EXISTS, tôi muốn lưu ý rằng nếu bạn có một truy vấn dạng

Code

SELECT ...
FROM T1
WHERE  IN (SELECT field FROM T2 ...)

hoặc

Code

SELECT ...
FROM T1
WHERE EXISTS (SELECT ... FROM T2 WHERE T1. = T2.field)

thì các truy vấn như vậy hầu như luôn hiệu quả hơn khi thực thi dưới dạng

Code

SELECT ...
FROM
  T1
  JOIN (SELECT DISTINCT field FROM T2) tmp ON tmp.field = T1.

Hãy để tôi đưa ra một ví dụ rõ ràng:

sql

SELECT
  COUNT(*)
FROM
  HORSE H
WHERE H.CODE_HORSE IN (
  SELECT
    CODE_FATHER
  FROM COVER
  WHERE EXTRACT(YEAR FROM COVER.BYDATE) = 2022
)

Kế hoạch thực thi và thống kê sử dụng Hash Join (semi)

Code

Select Expression
    -> Aggregate
        -> Filter
            -> Hash Join (semi)
                -> Table "HORSE" as "H" Full Scan
                -> Record Buffer (record length: 41)
                    -> Filter
                        -> Table "COVER" Access By ID
                            -> Bitmap
                                -> Index "IDX_COVER_BYYEAR" Range Scan (full match)

                COUNT
=====================
                 1616

Bộ nhớ hiện tại = 554176768
Delta bộ nhớ = 288
Bộ nhớ tối đa = 555531328
Thời gian trôi qua = 0.229 giây
Bộ đệm = 32768
Số lần đọc = 0
Số lần ghi = 0
Số lần truy xuất = 569683
Thống kê theo bảng:
--------------------------------+---------+---------+---------+---------+---------+
 Tên bảng                       | Tự nhiên| Chỉ mục | Chèn    | Cập nhật| Xóa     |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     6695|         |         |         |
HORSE                           |   525875|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Khá nhanh, nhưng bảng HORSE được đọc toàn bộ.

Kế hoạch thực thi và thống kê với việc thực thi truy vấn con cổ điển

Code

Sub-query
    -> Filter
        -> Filter
            -> Table "COVER" Access By ID
                -> Bitmap And
                    -> Bitmap
                        -> Index "FK_COVER_FATHER" Range Scan (full match)
                    -> Bitmap
                        -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" as "H" Full Scan

                COUNT
=====================
                 1616

Bộ nhớ hiện tại = 553472512
Delta bộ nhớ = 288
Bộ nhớ tối đa = 553966592
Thời gian trôi qua = 6.862 giây
Bộ đệm = 32768
Số lần đọc = 0
Số lần ghi = 0
Số lần truy xuất = 2462726
Thống kê theo bảng:
--------------------------------+---------+---------+---------+---------+---------+
 Tên bảng                       | Tự nhiên| Chỉ mục | Chèn    | Cập nhật| Xóa     |
--------------------------------+---------+---------+---------+---------+---------+
COVER                           |         |     1616|         |         |         |
HORSE                           |   525875|         |         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Rất chậm. Bảng HORSE được quét toàn bộ, và truy vấn con được thực thi nhiều lần - cho mỗi bản ghi trong bảng HORSE.

Và bây giờ là một tùy chọn nhanh với DISTINCT

sql

SELECT
  COUNT(*)
FROM
  HORSE H
  JOIN (
      SELECT
        DISTINCT
        CODE_FATHER
      FROM COVER
      WHERE EXTRACT(YEAR FROM COVER.BYDATE) = 2022
  ) TMP ON TMP.CODE_FATHER = H.CODE_HORSE

Code

Select Expression
    -> Aggregate
        -> Nested Loop Join (inner)
            -> Unique Sort (record length: 44, key length: 12)
                -> Filter
                    -> Table "COVER" as "TMP COVER" Access By ID
                        -> Bitmap
                            -> Index "IDX_COVER_BYYEAR" Range Scan (full match)
            -> Filter
                -> Table "HORSE" as "H" Access By ID
                    -> Bitmap
                        -> Index "PK_HORSE" Unique Scan

                COUNT
=====================
                 1616

Bộ nhớ hiện tại = 554349728
Delta bộ nhớ = 320
Bộ nhớ tối đa = 555531328
Thời gian trôi qua = 0.011 giây
Bộ đệm = 32768
Số lần đọc = 0
Số lần ghi = 0
Số lần truy xuất = 14954
Thống kê theo bảng:
--------------------------------+---------+---------+---------+---------+---------+
 Tên bảng                       | Tự nhiên| Chỉ mục | Chèn    | Cập nhật| Xóa     |
--------------------------------+---------+---------+---------+---------+---------+

```text
COVER                           |         |     6695|         |         |         |
HORSE                           |         |     1616|         |         |         |
--------------------------------+---------+---------+---------+---------+---------+

Không có thao tác đọc không cần thiết, truy vấn được thực thi rất nhanh. Do đó, kết luận là - luôn xem xét kế hoạch thực thi của các truy vấn con trong IN/EXISTS/ANY/SOME, và kiểm tra các biến thể viết truy vấn khác nhau.