The following SQL is simple, but the explain plan is a little bit complex. Oracle SQL optimizer uses an adaptive plan to execute the SQL statement. You can see the SQL’s syntax is normal, there is not much syntax rewrite which can be helpful to improve the SQL speed manually. The emps_salary index of employees and emps_dept index of departments are both used in a proper way. The SQL takes 12.45 seconds to finish, it still seems longer than what I expected. If traditional SQL rewrite is not applicable for this SQL, how about using a SQL tuning tool, Tosska SQL Tuning Expert, to see if there are any potential better execution plans are missed out by Oracle SQL optimizer for this SQL. You can download the product from here: https://tosska.com/tosska-sql-tuning-expert-tse-oracle-free-download/ Let me paste the SQL into SQL Editor of Tosska SQL tuning Expert and press <Tune> button with default intelligent level 2. A better SQL 7 with hints injected is found after a few minutes of tuning process. The SQL 7 with a new hints /*+ NO_USE_HASH(@SEL$1 DEPARTMENTS EMPLOYEES) */ is more than 8 times faster than the original SQL statement. This hints is not to instruct Oracle SQL optimizer to do something specific, but it tells Oracle not to use hash join and have freedom to choose other query plans which are even with higher cost. With the new hints /*+ NO_USE_HASH(@SEL$1 DEPARTMENTS EMPLOYEES) */ injected, Oracle decided to use a Merge Join to execute this SQL. Although the cost of this SQL 7 has a relatively higher cost of 33,536 than the original SQL cost of 31,229, the performance of SQL 7 is significantly better. It is a typical SQL optimizer’s cost-estimation error even for those relevant tables’ statistics which are 100% accurate. If we use traditional SQL syntax rewrite technique to improve this SQL, it will be very difficult. With the AI engine provided by Tosska SQL Tuning Expert, SQL tuning is now as easy as just a point and click.
