Bạn đang gặp khó khăn với hiệu suất truy vấn Oracle chậm chạp? Bài viết này sẽ khám phá một vấn đề thường gặp liên quan đến LEFT JOIN và subquery, đồng thời cung cấp các giải pháp thực tế để tối ưu hóa hiệu suất cơ sở dữ liệu của bạn. Chúng ta sẽ tập trung vào kỹ thuật subquery elision, giúp Oracle bỏ qua các subquery không cần thiết, từ đó tăng tốc độ truy vấn đáng kể.
Trong Oracle, khi bạn sử dụng LEFT JOIN với một bảng mở rộng, Oracle thường rất thông minh trong việc bỏ qua bảng đó nếu các cột của nó không được yêu cầu trong truy vấn. Tuy nhiên, điều này không phải lúc nào cũng đúng với các subquery. Ngay cả khi bạn không yêu cầu các cột từ subquery được LEFT JOIN, Oracle vẫn có thể đánh giá subquery đó, gây lãng phí tài nguyên và làm chậm hiệu suất. Điều này đặc biệt đúng khi subquery bao gồm các phép toán phức tạp như GROUP BY.
Để hiểu rõ hơn, hãy xem xét ví dụ sau:
CREATE TABLE ELIDE_MAIN (
ELIDE_KEY VARCHAR2 (10) NOT NULL,
MAIN_VALUE NUMBER NOT NULL,
CONSTRAINT ELIDE_MAIN_PK PRIMARY KEY (ELIDE_KEY)
);
CREATE TABLE ELIDE_EXTENSION (
ELIDE_KEY VARCHAR2 (10) NOT NULL,
EXTENSION_VALUE NUMBER NOT NULL,
CONSTRAINT ELIDE_EXTENSION_PK PRIMARY KEY (ELIDE_KEY)
);
CREATE TABLE ELIDE_CHILD (
ELIDE_KEY VARCHAR2 (10) NOT NULL,
CHILD_NO NUMBER NOT NULL,
CHILD_VALUE NUMBER NOT NULL,
CONSTRAINT ELIDE_CHILD_PK PRIMARY KEY (ELIDE_KEY, CHILD_NO)
);
INSERT INTO ELIDE_MAIN (ELIDE_KEY, MAIN_VALUE) VALUES ('AAA', 1);
INSERT INTO ELIDE_MAIN (ELIDE_KEY, MAIN_VALUE) VALUES ('BBB', 2);
INSERT INTO ELIDE_MAIN (ELIDE_KEY, MAIN_VALUE) VALUES ('CCC', 3);
INSERT INTO ELIDE_MAIN (ELIDE_KEY, MAIN_VALUE) VALUES ('DDD', 4);
INSERT INTO ELIDE_EXTENSION (ELIDE_KEY, EXTENSION_VALUE) VALUES ('AAA', 11);
INSERT INTO ELIDE_EXTENSION (ELIDE_KEY, EXTENSION_VALUE) VALUES ('BBB', 22);
INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('AAA', 1, 100);
INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('AAA', 2, 101);
INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('CCC', 1, 100);
INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('CCC', 2, 101);
INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('CCC', 3, 300);
INSERT INTO ELIDE_CHILD (ELIDE_KEY, CHILD_NO, CHILD_VALUE) VALUES ('CCC', 4, 301);
COMMIT;
CREATE OR REPLACE VIEW VW_ELIDED_EXAMPLE AS
SELECT m.ELIDE_KEY,
m.MAIN_VALUE,
x.EXTENSION_VALUE,
c.CHILD_VALUE_SUM
FROM ELIDE_MAIN m
LEFT OUTER JOIN ELIDE_EXTENSION x ON m.ELIDE_KEY = x.ELIDE_KEY
LEFT OUTER JOIN (
SELECT ELIDE_KEY, SUM (CHILD_VALUE) CHILD_VALUE_SUM
FROM ELIDE_CHILD
GROUP BY ELIDE_KEY
) c ON m.ELIDE_KEY = c.ELIDE_KEY
WITH READ ONLY;
EXPLAIN PLAN FOR SELECT ELIDE_KEY, MAIN_VALUE FROM VW_ELIDED_EXAMPLE;
Trong ví dụ này, truy vấn chỉ chọn `ELIDE_KEY` và `MAIN_VALUE` từ view `VW_ELIDED_EXAMPLE`. Mặc dù chúng ta không yêu cầu `EXTENSION_VALUE` và `CHILD_VALUE_SUM`, Oracle vẫn có thể đánh giá subquery tính tổng `CHILD_VALUE`, gây lãng phí tài nguyên.
Một giải pháp hiệu quả là di chuyển subquery vào mệnh đề `SELECT` dưới dạng một correlated subquery. Bằng cách này, Oracle có thể bỏ qua subquery nếu cột của nó không được yêu cầu.
CREATE OR REPLACE VIEW VW_ELIDED_EXAMPLE AS
SELECT m.ELIDE_KEY,
m.MAIN_VALUE,
x.EXTENSION_VALUE,
(SELECT SUM (CHILD_VALUE)
FROM ELIDE_CHILD cc
WHERE cc.elide_key = m.elide_key) CHILD_VALUE_SUM
FROM ELIDE_MAIN m
LEFT OUTER JOIN ELIDE_EXTENSION x ON m.ELIDE_KEY = x.ELIDE_KEY;
EXPLAIN PLAN FOR SELECT ELIDE_KEY, MAIN_VALUE FROM VW_ELIDED_EXAMPLE;
Khi bạn thực hiện `EXPLAIN PLAN` cho truy vấn chỉ chọn `ELIDE_KEY` và `MAIN_VALUE`, Oracle sẽ không còn đánh giá subquery tính tổng `CHILD_VALUE`, dẫn đến hiệu suất được cải thiện đáng kể.
Một lựa chọn khác là sử dụng một hàm (function) thay vì subquery. Tương tự như correlated subquery trong mệnh đề `SELECT`, Oracle có thể bỏ qua hàm nếu giá trị trả về của nó không được sử dụng trong truy vấn.
Bằng cách hiểu cách Oracle xử lý LEFT JOIN và subquery, bạn có thể áp dụng các kỹ thuật như di chuyển subquery vào mệnh đề `SELECT` hoặc sử dụng hàm để tối ưu hóa hiệu suất truy vấn của mình. Hãy thử nghiệm và tìm ra giải pháp phù hợp nhất cho trường hợp cụ thể của bạn. Việc tối ưu hóa cơ sở dữ liệu là một công việc liên tục, và những nỗ lực của bạn sẽ được đền đáp bằng hiệu suất hệ thống được cải thiện rõ rệt.
Bài viết liên quan