{"id":93558,"date":"2022-03-18T21:16:46","date_gmt":"2022-03-18T21:16:46","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93558"},"modified":"2022-03-21T15:24:23","modified_gmt":"2022-03-21T15:24:23","slug":"oracle-subquery-caching-and-subquery-pushing","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-subquery-caching-and-subquery-pushing\/","title":{"rendered":"Oracle subquery caching and subquery pushing"},"content":{"rendered":"

Jonathan Lewis' continuing series on the Oracle optimizer and how it transforms queries into execution plans:<\/strong><\/p>\n

    \n
  1. Transformations by the Oracle Optimizer<\/a><\/li>\n
  2. The effects of NULL with NOT IN on Oracle transformations<\/a><\/li>\n
  3. Oracle subquery caching and subquery pushing<\/a><\/li>\n
  4. Oracle optimizer removing or coalescing subqueries<\/a><\/li>\n
  5. Oracle optimizer Or Expansion Transformations<\/a><\/li>\n<\/ol>\n\n

    So far, this series has examined the shape of some of the execution plans that Oracle\u2019s optimizer can produce when there are subqueries in the where<\/code> clause. It was found that the optimizer will often \u201cunnest\u201d a subquery to produce a join rather than using a filtering operation that repeatedly runs the subquery.<\/p>\n

    In this installment you\u2019ll see why you might want to stop the optimizer from unnesting some subqueries and also see a way to control where in the plan the optimizer positions each subquery, a detail of timing that can affect the overall workload quite significantly.<\/p>\n

    The examples use the emp<\/code> and dept<\/code> tables from the scott<\/code> schema ($ORACLE_HOME\/rdbms\/admin\/utlsampl.sql<\/em>), and the demonstrations run from SQL*Plus on Oracle 19.11.<\/p>\n

    Scalar subquery caching<\/h2>\n

    In case you don\u2019t have access to an instance where you can create the scott<\/code> schema, here\u2019s a query to give you the highlights of the data.<\/p>\n

    select\r\n        d.deptno, d.dname,\r\n        count(sal) dept_size,\r\n        round(avg(e.sal + nvl(e.comm,0)),2) earnings_avg\r\nfrom\r\n        dept    d,\r\n        emp     e\r\nwhere\r\n        e.deptno(+) = d.deptno\r\ngroup by\r\n        d.deptno, d.dname\r\norder by\r\n        d.deptno\r\n\/<\/pre>\n
     <\/p>\n
    <\/p>\n
    The dept<\/code> table has 4 rows with a primary key on deptno<\/code>. The emp<\/code> table has 14 rows with a primary key on empno<\/code> and a foreign key constraint to dept<\/code> based on deptno<\/code>. There are, however, no rows in the emp<\/code> table for department 40. Both tables include a few columns that I haven\u2019t yet mentioned.<\/p>\n
    Here\u2019s one way to write a query to report all details about all employees who earn more than the average for their department:<\/p>\n
    break on deptno skip 1\r\n\r\nselect\r\n        \/*+ \r\n                qb_name(main)\r\n                gather_plan_statistics \r\n        *\/\r\n        *\r\nfrom    emp e1\r\nwhere   e1.sal + nvl(e1.comm,0) > (\r\n                select  \/*+ qb_name(subq) *\/\r\n                        avg(e2.sal + nvl(e2.comm,0))\r\n                from    emp e2\r\n                where   e2.deptno = e1.deptno\r\n        )\r\norder by\r\n        e1.deptno, e1.empno\r\n\/\r\n<\/pre>\n
     <\/p>\n
    I\u2019ll just point out a couple of details about the conventions used in this query:<\/p>\n