這個包創(chuàng)建并執(zhí)行了一個名為 FOLIO_COUNT 的調(diào)整任務(wù)。接下來，您將需要查看任務(wù)執(zhí)行的結(jié)果（也就是說，查看建議）。 set serveroutput on size 999999 set long 999999 select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;
中級調(diào)整：查詢重構(gòu) 假定查詢更復(fù)雜： select account_no from accounts a where account_name = 'HARRY' and sub_account_name not in ( select account_name from accounts where account_no = a.old_account_no and status is not null);
顧問建議如下： 1- RestrUCture SQL finding (see plan 1 in eXPlain plans section) ---------------------------------------------------------------- The optimizer could not unnest the subquery at line ID 1 of the execution plan.
Recommendation -------------- Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used on both sides of the "NOT IN" Operator are declared "NOT NULL" by adding either "NOT NULL" constraints or "IS NOT NULL" predicates.
Rationale --------- A "FILTER" operation can be very expensive because it evaluates the subquery for each row in the parent query.The subquery, when unnested can drastically improve the execution time because the "FILTER" operation is converted into a join.Be aware that "NOT IN" and "NOT EXISTS" might produce different results for "NULL" values.
這一次顧問不會建議任何結(jié)構(gòu)上的更改（如索引），但會通過用 NOT EXISTS 取代 NOT IN的方式很聰明地猜測到調(diào)整查詢的正確方式。 由于兩種構(gòu)造相似但不相同，顧問給出了這種改變的基本原理，并把決定權(quán)留給 DBA 或應(yīng)用程序開發(fā)人員，由他們決定該建議是否對環(huán)境有效。
