CRITICAL
Rule Definition
Using the MINUS or EXCEPT operator instead of NOT IN and NOT Exists will result in a faster execution plan.
Remediation
Check the statement and if so, replace NOT EXISTS and NOT IN suqueries with MINUS or EXCEPT operator.
Violation Code Sample
The following procedure should be reviewed:
CREATE PROCEDURE INSERT_BOOK_TITLE
BEGIN
INSERT INTO BOOK_TITLE_COPY
SELECT BOOK_TITLE
FROM BOOKS
WHERE
NOT EXISTS
(SELECT BOOK_TITLE
FROM SALES
WHERE SALES.BOOK_TITLE = BOOKS.BOOK_TITLE);
END;
Fixed Code Sample
The following procedure should be reviewed:
CREATE PROCEDURE INSERT_BOOK_TITLE
BEGIN
INSERT INTO BOOK_TITLE_COPY
SELECT BOOK_TITLE
FROM BOOKS
WHERE
NOT EXISTS
(SELECT BOOK_TITLE
FROM SALES
WHERE SALES.BOOK_TITLE = BOOKS.BOOK_TITLE);
END;
Replace NOT EXISTS suquerie with MINUS operator:
CREATE PROCEDURE INSERT_BOOK_TITLE
BEGIN
INSERT INTO BOOK_TITLE_COPY
SELECT BOOK_TITLE
FROM BOOKS
MINUS
SELECT BOOK_TITLE
FROM SALES;
END;
Related Technologies
Technical Criterion
Efficiency - SQL and Data Handling Performance
About CAST Appmarq
CAST Appmarq is by far the biggest repository of data about real IT systems. It's built on thousands of analyzed applications, made of 35 different technologies, by over 300 business organizations across major verticals. It provides IT Leaders with factual key analytics to let them know if their applications are on track.