How to Tune SQL with IN Subquery in Certain Environment for Oracle ?

Discussion in 'Oracle' started by Richard To, Jan 13, 2021.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    64
    Likes Received:
    21
    Trophy Points:
    8
    Gender:
    Male
    Here is an example SQL that retrieves data from EMPLOYEE table with employee’s ID code in EMP_SUBSIDIARY table.

    select * from employee a
    where a.emp_id
    in (select b.emp_id
    from emp_subsidiary b)​

    Here the following are the query plan of this SQL, it takes 10.03 seconds to finish. The query plan is very simple since the syntax of the SQL is not complicated. The query plan shows a full table scan of EMPLOYEE and then nested to the index of EMPSB_EMP_ID, it looks like a good query plan, but I wonder if it is too expensive to have a full table scan of EMPLOYEE table?
    [​IMG]
    In order to ask Oracle to consider other query plans, I added a dummy function Coalesce(b.emp_id,b.emp_id) in the subquery’s select list that artificially adding cost to the driving path from EMPLOYEE to EMP_SUBSIDIARY due to the index EMPSB_EMP_ID is disabled by this dummy function.

    SELECT *
    FROM employee a
    WHERE a.emp_id IN (SELECT Coalesce(b.emp_id,b.emp_id)
    FROM emp_subsidiary b)​

    The rewritten SQL generates an adaptive query plan and a “nested loops” from EMPSB_EMP_ID to EMPLOYEE by EMPLOYEE_PK index. You can remove the steps in “Id” column with marked ‘-‘ from the plan to verify the result plan. The new plan now takes 4.13 seconds to finish only.
    [​IMG]
    This kind of rewrites can be achieved by Tosska SQL Tuning Expert for Oracle automatically, it shows that the rewrite is more than 2 times faster than the original SQL.

    https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/

    [​IMG]
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice